# Scripts, scripts, scripts!

**URL:** <https://community.glideapps.com/t/scripts-scripts-scripts/18965>\
**Category:** Ask for Help\
**Created:** [November 26, 2020, 6:15pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965 "2020-11-26T18:15:21Z")\
**Posts on this page:** 20\
**Page:** 11

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 1, 2021, 11:32pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/203 "2021-07-01T23:32:35Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 2, 2021, 1:25am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/204 "2021-07-02T01:25:18Z")

</div>

> [@Francisco\_Maldonado](#):
>
> @Darren_Murphy Might you help me. What values ​​should I substitute in this scripts?

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:

```auto
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).

---

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 2, 2021, 2:15am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/205 "2021-07-02T02:15:22Z")

</div>

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.

```auto
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.

```auto
/**
 * 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 😳 😳

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 2, 2021, 2:19am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/206 "2021-07-02T02:19:23Z")

</div>

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

> [@Darren\_Murphy](#):
>
> An example of how it might be called is:
> 
> ```auto
> var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1');
> copyFormulasDown(sheet, 2);
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 2, 2021, 2:24am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/207 "2021-07-02T02:24:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 2, 2021, 2:42am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/208 "2021-07-02T02:42:43Z")

</div>

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:

```auto
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:

```auto
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](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) 🙂

---

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 2, 2021, 2:55am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/209 "2021-07-02T02:55:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 2, 2021, 2:27pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/210 "2021-07-02T14:27:05Z")

</div>

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.

 ![Captura de Pantalla 2021-07-02 a la(s) 10.30.05 a. m. (2)](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/a/4affc9ff1af9e3156ff30076714fd482d7327f1d.png)

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

Thanks for your help in advance.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 2, 2021, 2:44pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/211 "2021-07-02T14:44:40Z")

</div>

> [@Francisco\_Maldonado](#):
>
> Do you know what is it about? I have not been able to fix it.

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 🔽

> [@Darren\_Murphy](#):
>
> It doesn’t go inside the script, it goes _outside_ the script. Let me explain a little more…

---

<div class="post-metadata">

**Author:** ![Francisco\_Maldonado](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/francisco_maldonado/32/18423_2.png) [@Francisco\_Maldonado](https://community.glideapps.com/u/Francisco_Maldonado)\
**Post date:** [July 2, 2021, 5:34pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/212 "2021-07-02T17:34:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\
**Post date:** [July 4, 2021, 3:10pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/213 "2021-07-04T15:10:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mark\_Turrell](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark_turrell/32/26605_2.png) [@Mark\_Turrell](https://community.glideapps.com/u/Mark_Turrell)\
**Post date:** [July 4, 2021, 3:51pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/214 "2021-07-04T15:51:53Z")

</div>

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!!

---

<div class="post-metadata">

**Author:** ![Rosha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosha/32/26447_2.png) [@Rosha](https://community.glideapps.com/u/Rosha)\
**Post date:** [July 20, 2021, 1:41pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/215 "2021-07-20T13:41:18Z")

</div>

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?

 ![Cannot read property-Java error](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/f/8ff48944dc9ba980f48375850b5d3e2de1c98e8e.png)

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 20, 2021, 2:17pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/216 "2021-07-20T14:17:39Z")

</div>

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

```auto
var ss = SpreadsheetApp.getActiveSpreadsheet();

```

---

<div class="post-metadata">

**Author:** ![Rosha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosha/32/26447_2.png) [@Rosha](https://community.glideapps.com/u/Rosha)\
**Post date:** [July 23, 2021, 2:21pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/217 "2021-07-23T14:21:37Z")

</div>

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.

 ![Error GetActiveSpreadSheet(NotFunction)](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/9/a9aea7b5515e8ca881d74eb5af2aa8b1a7c19d45.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 23, 2021, 2:37pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/218 "2021-07-23T14:37:19Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Rosha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosha/32/26447_2.png) [@Rosha](https://community.glideapps.com/u/Rosha)\
**Post date:** [July 23, 2021, 3:03pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/219 "2021-07-23T15:03:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 23, 2021, 3:09pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/220 "2021-07-23T15:09:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [July 23, 2021, 3:18pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/221 "2021-07-23T15:18:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 23, 2021, 3:24pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/222 "2021-07-23T15:24:19Z")

</div>

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 🤣

[Previous page](https://community.glideapps.com/t/scripts-scripts-scripts/18965.md?page=10)

[Next page](https://community.glideapps.com/t/scripts-scripts-scripts/18965.md?page=12)