Scripts, scripts, scripts!

@Darren_Murphy Might you help me. What values ​​should I substitute in this scripts?

I understand that by default I must leave row 2, which is where the formula will be copied from.

Now my logic tells me (I have no experience in script) that I should replace the value of the column where the formula is and the name of the sheet where the new row will be created so that everything works.

It takes two parameters.

  • sheet: a Google Sheet object
  • sourceRow: the row to look for formulas. This is optional, and the default is 2.

An example of how it might be called is:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');
copyFormulasDown(sheet, 2);

In the above, the script would look for any/all formulas in row 2 of “Sheet 1” and copy them to the last row of the sheet (for all columns).

I understand what those 2 variables would be. If the source row is by default in 2, I would only have to change the name of the sheet.

But the sequence of this example doesn’t match the script below, I don’t see an option in this script to rename the sheet.

function copyFormulasDown(sheet, sourceRow) {
    if (sourceRow === undefined) {
        sourceRow = 2;
    }
    var formulas = getFormulas_(sheet, sourceRow);
    if (formulas !== {}) {
        var rows = sheet.getDataRange().getFormulas();
        for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
            copyFormulas_(sheet, r, formulas);
        }
    }
}

Could you help me to know in this same example where I should change the name of the sheet?

Note: I leave the complete example below. I took it from an example you gave.

/**
 * Copy Formulas Down
 * This script copies functions from a source row (usually the first data row in a spreadsheet) to
 * rows at the end of the sheet, where the colums for these formulas are still empty.
 * It checks row from the bottom upwards, copying formulas into the rows where there are no formulas yet,
 * and stops as soon as it finds a row which has a formula already.
 * When copying formulas, it leaves values in other cells unchanged.
 */

/**
 * Copy formulas from the a source row in a sheet to rows below
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} sourceRow 		(optional) 1-based index of source row from which formulas are copied, default: 2
 */
function copyFormulasDown(sheet, sourceRow) {
    if (sourceRow === undefined) {
        sourceRow = 2;
    }
    var formulas = getFormulas_(sheet, sourceRow);
    if (formulas !== {}) {
        var rows = sheet.getDataRange().getFormulas();
        for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
            copyFormulas_(sheet, r, formulas);
        }
    }
}
/**
 * Copy formulas into row r in sheet
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} r 				1-based index of row where formulas will be copied
 * @param  {array} formulas 	array of objects with column index and formula string
 */
function copyFormulas_(sheet, r, formulas) {
    for (var i = 0; i < formulas.length; i++) {
        sheet.getRange(r + 1, formulas[i].c + 1).setFormulaR1C1(formulas[i].formula);
    }
}
/**
 * Read formulas from the source row, creating an array of objects
 * Each objects contains the column index and formula string
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} r				1-based index of source row
 * @return {array}				array of objects
 */
function getFormulas_(sheet, r) {
    var row = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
    var formulas = [];
    for (var c = 0; c < row.length; c++) {
        if (row[c] !== '') {
            formulas.push({
                c: c,
                formula: row[c]
            });
        }
    }
    return formulas;
}

@Darren_Murphy help me :flushed: :flushed:

The script expects a sheet object, not a sheet name.
I showed you how to get a sheet object in my previous reply…

Just replace “Sheet 1” in that example with the name of your sheet.

And in which part of the complete script would I paste this call that you give me? @Darren_Murphy

It doesn’t go inside the script, it goes outside the script. Let me explain a little more…

This type of function is often referred to as a “helper function”, or a “utility function”. The general idea is that you write a function in a generic way such that it can be used over and over again.

So, let’s say you want to use it on 3 different sheets - “Sheet 1”, “Sheet 2” and “Sheet 3”. You might write something like this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');
copyFormulasDown(sheet, 2);
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 2');
copyFormulasDown(sheet, 2);
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 3');
copyFormulasDown(sheet, 2);

So in the above case, you call the function once for each of your 3 sheets. But a better way to do that would be to create an array of sheet names, and then use an iterator, like so:

var sheetnames = ["Sheet 1", "Sheet 2", "Sheet 3"];
sheetnames.forEach(function (name) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  copyFormulasDown(sheet, 2);
});

The important point is that you can use the same function (copyFormulasDown) on several different sheets without the need to modify or copy the original function. This is called DRY programming :slight_smile:

Thanks a lot. I already got it to work for a single sheet and managed to set the trigger. Tomorrow I will be trying so that it can be used for more leaves that I need, any questions I ask for your help hahaha.

Thank you very much indeed. @Darren_Murphy

Hi @Darren_Murphy. Shame to bother you, last night I set the script this way as I show you in the attached image and it worked fine for a single sheet.

Today I have tried to adapt the code that you have given me so that it functions in several sheets and I have not been able to and when I return to the same code that worked I get this error.

Do you know what is it about? I have not been able to fix it.

Thanks for your help in advance.

Yes, you’re calling the function from within itself, and creating a circular reference. Please go back and read carefully what I told you before. In particular, this :arrow_down_small:

Thank you @Darren_Murphy . I will keep trying, I am costing more than normal because I have no experience in script but I know that I will be able to configure it.

Hey Mark thank you for the advice. Though I may not have paid someone, I was inspired to redo my data structure and has been a game changer.

1 Like

I’m glad my advice helped. I have had to redesign my system at least 5 times… but I was doing unusual things from the beginning and coming into situations that are common for transactional business apps, such as ‘race conditions’ where Glide does not natively handle this situation very well.

Row Owners is another thing to get your head around as a general issue. It does make some things just a little more tricky!!

Hi,

Could you help me with this? I am new to JavaScript and is still learning. The line where it says fix_timestamps(sheetname, [36]) I am getting an error message. Are you able to assist me with this?

Thanks for your help.

Looks like you are missing the parentheses and semi colon at the end of line 5. It should be:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Thank you for that. There’s something going on with line two and five. I seem to still be getting another error. I’ve attached a screen shot of that.

That’s quite odd - I can’t see why it would be throwing that error.
Would you be able to share a copy of your Google Sheet with me so that I can take a look?

1 Like

Let me double-check with my boss to make sure it’s okay to share with you. If that doesn’t work, I may be able to just send screenshots also. But i’ll let you know as soon as possible.

I’m not sure that screenshots will help. The reason I asked for a copy is so that I can see the script in its entirety and execute it myself. If it makes it any easier:

  • make a copy of the Google Spreadsheet
  • delete all sheets from the copy except the one referred to in the script
  • remove any potentially sensitive data from that sheet
  • share the link with me privately
1 Like

Maybe try getActiveSpreadsheet (not SpreadSheet) to see if the capitalized character is the problem.

2 Likes

arg!! yes, that’s it.
I spent a good 5 minutes staring at it looking for something like that, and completely missed it.
I’m getting old :rofl:

1 Like