# 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:** 5

<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:** [January 7, 2021, 6:39am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/82 "2021-01-07T06:39:39Z")

</div>

> [@Pratik\_Shah](#):
>
> As far as I know, Add-on is running on Form Submit trigger only and not when new row is added. However, they also give us an option to run on interval of time or Manually.
> 
> If I want Addon to run its script on new row added though Glide Form, I only have an option to manually run the Add-on.

Yes, you might be a bit stuck here.  
When Glide modifies data in your sheet, you will only ever see an `On Change` event.

I guess you could try the option of using a time based trigger, and then see if it picks up and processes any rows that Glide adds. That might be worth a try…

---

<div class="post-metadata">

**Author:** ![Pratik\_Shah](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pratik_shah/32/12416_2.png) [@Pratik\_Shah](https://community.glideapps.com/u/Pratik_Shah)\
**Post date:** [January 7, 2021, 6:40am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/83 "2021-01-07T06:40:49Z")

</div>

As of now these are the options they are giving to run it.

 ![autocrat](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/8/e8f3500844d73b2aca02320faf84d056326647e4.jpeg)

On Google Form submit and On Time trigger. I could have used time trigger if they are giving an option for a minute or 5 minutes time trigger.

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/4/d4d450aca632ce23cef5507657e01b9646b32510.png)  
(Above image), they also provide an option to manually trigger it by clicking this PLAY button.

So effectively, they are not giving an option to run it when New Row is added.  
I hope this helps. If you can find solution, please let me know

---

<div class="post-metadata">

**Author:** ![Pratik\_Shah](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pratik_shah/32/12416_2.png) [@Pratik\_Shah](https://community.glideapps.com/u/Pratik_Shah)\
**Post date:** [January 7, 2021, 6:42am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/84 "2021-01-07T06:42:40Z")

</div>

> [@Darren\_Murphy](#):
>
> I guess you could try the option of using a time based trigger, and then see if it picks up and processes any rows that Glide adds. That might be worth a try…

This will surely work but it will work in an interval of 1 hour which I do not want.

Thank you for all 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:** [January 7, 2021, 6:42am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/85 "2021-01-07T06:42:46Z")

</div>

For now, my only suggestion would be this:

> [@Darren\_Murphy](#):
>
> I guess you could try the option of using a time based trigger, and then see if it picks up and processes any rows that Glide adds. That might be worth a try…

Other than that, I’m out of ideas, sorry.

---

<div class="post-metadata">

**Author:** ![Pratik\_Shah](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pratik_shah/32/12416_2.png) [@Pratik\_Shah](https://community.glideapps.com/u/Pratik_Shah)\
**Post date:** [January 7, 2021, 6:43am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/86 "2021-01-07T06:43:14Z")

</div>

No problem, thank you so much for all your help.

---

<div class="post-metadata">

**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\
**Post date:** [January 7, 2021, 6:44am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/87 "2021-01-07T06:44:44Z")

</div>

Happy Birthday @Darren_Murphy 🥳 🥳

---

<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:** [January 9, 2021, 4:20am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/88 "2021-01-09T04:20:27Z")

</div>

What would a simple script look like to change a specific cell on a specific sheet to =today()? 🙏

Will execute with time driven trigger.

---

<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:** [January 9, 2021, 4:27am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/89 "2021-01-09T04:27:09Z")

</div>

You can use `getRange().setFormula()`

> **[Class Range  |  Apps Script  |  Google Developers](https://developers.google.com/apps-script/reference/spreadsheet/range#setformulaformula)**

---

<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:** [January 9, 2021, 4:29am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/90 "2021-01-09T04:29:33Z")

</div>

Ty! Success a simple script to change a specific cell to `=today()`

```
function weekset() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
var cell = sheet.getRange("A2");
cell.setFormula("=today()");
}
```

---

<div class="post-metadata">

**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\
**Post date:** [January 13, 2021, 7:01pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/91 "2021-01-13T19:01:18Z")

</div>

👋 Hey Gliders!

Welp, I’m not very versed in Google Sheets scripts (although eager and trying to learn as fast as I can!) and the problem I’m trying to solve - I THINK - can be done with scripts…

**Scenario** :

We have an app that has a “feed” where people can upload data via form and once data is submitted a card is created and displays said data in the “Feed” tab. This card data is then populated into the “Feed” sheet and is given a “post timestamp” as well as a “post expiry timestamp” (inside Glide Editor only; not in gsheet) using a Math column exactly one hour after the original “post timestamp”…

**Problem** :

Once Glide recognizes that “Now” is after “post expiry timestamp”, it removes the post from being visible in-app. HOWEVER, it doesn’t delete the row from the sheet, causing blank cards to show up in the “feed” tab (in-app) _ **AND it takes up row count.** _

_ **Potential Solution?** _

I’d like to have a script that automatically deletes these rows every 15 mins that are considered to be “expired” (Now = after “post expiry timestamp”) - is this possible? 🤔

\ ***IDEAL Solution:**

Have all of the “Feed” data be copied into another sheet (not connected to Glide) for data storage once posts expire - although I know this would occupy A LOT of space after some time. Example) We have 200-250 users that post once per day; 1 year = 90,000 rows… 🤯

@Darren_Murphy @Drearystate I know you guys are script wizards, but any and all help from anyone would be greatly appreciated! 🙏 🤓

---

<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:** [January 13, 2021, 7:18pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/92 "2021-01-13T19:18:45Z")

</div>

Hello good sir,

I found myself in a similar situation and with the help of @Darren_Murphy we came up with this script. It will copy the _values only_ from a source sheet to a target sheet while skipping the ones you don’t want to copy yet. Hopefully this could point you in the right direction.

With a little tweaking it could delete the values from the source sheet that have already been processed. Then it would satisfy both your needs in one execution.

> **Summary**
>
> ```
> function copy_new_rows_to_database() {
> console.time('Archive');
> // Open the source sheet and read all data into an array
> var ss = SpreadsheetApp.getActiveSpreadsheet();
> var sourcesheet = ss.getSheetByName('YOURSOURCESHEET');
> var data = sourcesheet.getDataRange().getValues();
> data.shift(); // Discard the header row
> var data_rows = data.length;
> if (data[0][2] == '') { // If there is no data in the second row, assume the sheet is empty
> console.log('No Data in source sheet')
> console.timeEnd('Archive');
> return;
> }
> var archive_data = [];
> var moved_rows = [];
> 
> // Iterate through the array of data, adding any matching rows to the archive_data array
> // and tag the original row as 'Moved'
> var row = 0;
> while (row <= data_rows) {
> var rec = data[row];
> if (typeof rec == 'undefined') { // Exit as soon as we hit the first empty row
> console.log("Done at row %s", row+1);
> break;
> }
> if (rec[5] != 'ACTIVE' && rec[16] == '') { // Set condtions to continue
> archive_data.push(rec.slice(0,16));
> moved_rows.push(['Moved']);
> }
> else {
> moved_rows.push([rec[16]]);
> }
> row++;
> }
>   
> var source_sheet_rows_processed = row;
> var archive_rows = archive_data.length;
> // If we found any rows that need moving, append them to the end of the Database sheet
> // And then write the altered data array (with newly archived rows tagged), back to the source sheet
> if (archive_rows) {
> var archive_sheet = ss.getSheetByName('YOURTARGETSHEET');
> var start_row = archive_sheet.getLastRow()+1;
> archive_sheet.getRange(start_row, 1, archive_rows, 16).setValues(archive_data);
> console.log("Archived %s rows", archive_rows);
> sourcesheet.getRange(2,17,source_sheet_rows_processed, 1).setValues(moved_rows); // Start from row 2 so we don't mess with the header
> }
> else {
> console.log('No new rows to archive.');
> }
> console.timeEnd('Archive')
> }
> 
> ```

Note: If some of the data/conditions you need to execute the script live in the Glide Data Editor only, you would need to find a way to bring that logic into GSheets to use with scripts.

---

<div class="post-metadata">

**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\
**Post date:** [January 13, 2021, 8:06pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/93 "2021-01-13T20:06:58Z")

</div>

Awesome - thanks! I’ll have to play around with it and see if I can get it work with an app copy!

Like I said, this is a new turf for me haha

Cheers!

---

<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:** [January 13, 2021, 8:15pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/94 "2021-01-13T20:15:37Z")

</div>

You’re Welcome!

In the script above I have the Trigger in column Q. Column Q should be empty when you run and it will copy all the values to the left of Q to the target sheet.

Also play around with this “if” statement to satisfy your needs  
`if (rec[5] != 'ACTIVE' && rec[16] == '') { //Set condtions to continue`

Best of luck with your project 🕶

---

<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:** [January 13, 2021, 11:39pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/95 "2021-01-13T23:39:45Z")

</div>

If you get stuck, feel free to reach out. I’m a little snowed under at the moment, but I’ll help out as/when I have some free time.

---

<div class="post-metadata">

**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\
**Post date:** [January 13, 2021, 11:43pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/96 "2021-01-13T23:43:18Z")

</div>

Thanks Darren, much appreciated.

---

<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:** [January 17, 2021, 3:41pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/97 "2021-01-17T15:41:31Z")

</div>

**Fixing broken timestamps**

I’m sure I’m not the first to notice this, but the “timestamps” that Glide inserts into GSheets often get interpreted as strings. When this happens, any date/time formatting that you try to apply will fail, and you end up with an ugly looking set of characters masquerading as an [ISO-8601](https://en.wikipedia.org/wiki/ISO_8601) datetime (except it isn’t 🤬)

This has been doing my head in recently, and it finally got the better of me and I decided to deal with it once and for all.

The following function accepts two parameters:

1. The name of a sheet
2. An array of column numbers to “fix”

I call it as part of an On Change trigger, and a typical call looks like so:

```
var sheetname = 'Sheet1';
fix_timestamps(sheetname, [3,7,8,15,16]);

```

Calling it as above will cause it to examine Sheet1, and convert any strings that it finds in the provided list of columns to proper date objects.

The function:

```
function fix_timestamps(sheetname, columns) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  columns.forEach(function (column) {
    var data = sheet.getRange(2, column, sheet.getLastRow() - 1, 1).getValues();
    data.forEach(function (item) {
      var obj = item[0];
      if (typeof (obj) == 'string' && obj != '') {
        obj = obj.slice(0, -1); // Ditch the Z, it's *not* GMT!
        var dateobj = new Date(obj);
        item[0] = dateobj;
      }
    });
    sheet.getRange(2, column, sheet.getLastRow() - 1, 1).setValues(data);
  });
}

```

EDIT: There was a bug in the original version of this script that would cause it to convert empty rows to `1970-01-01`. I’ve modified it so that it won’t do that anymore. It could be made more robust, but it serves my purpose as it is.

---

<div class="post-metadata">

**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\
**Post date:** [January 17, 2021, 4:03pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/98 "2021-01-17T16:03:28Z")

</div>

This is great!

This might be a novice question but will this adhere to the users’ device’s time zone settings too? Or is that irrelevant? 🥴

---

<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:** [January 17, 2021, 4:29pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/99 "2021-01-17T16:29:01Z")

</div>

It ignores timezones, and won’t affect what you see in Glide in any way.  
The purpose of it is to allow you to apply your desired formatting to date/times within your Google Sheet.  
For example, I prefer to have mine formatted as `yyyy-mm-dd hh:mm:ss`  
The string that Glide inserts looks like `2020-08-22T06:13:12.589Z`  
When you try to apply formatting to that, sometimes it works and sometimes it doesn’t.  
So the purpose of my script is to check that is actually a date (not a string), and convert it to a date if it isn’t. After running `2020-08-22T06:13:12.589Z` through my script and applying `yyyy-mm-dd hh:mm:ss` formatting to it, it correctly displays as `2020-08-22 06:13:12`

One thing to note is that the `Z` on the string that comes from Glide suggests that it is GMT - but it isn’t.  
That’s why I have the following line in my script:

`obj = obj.slice(0, -1); // Ditch the Z, it's *not* GMT!`

That line strips the `Z` from the end of the string, so when it gets converted to a date (the next line), it gets treated as local time.

---

<div class="post-metadata">

**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\
**Post date:** [January 17, 2021, 4:33pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/100 "2021-01-17T16:33:20Z")

</div>

Amazing! I feel many will use this! Thanks so much for writing it!

So to apply it properly, will it just convert all sheets with columns with timestamps?

---

<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:** [January 17, 2021, 4:38pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/101 "2021-01-17T16:38:23Z")

</div>

You need to tell it which sheet to process, and which columns in that sheet.

`fix_timestamps(sheetname, [3,7,8,15,16]);`

In that example, it will process columns 3,7,8,15 & 16.  
Column numbering starts at 1, so 3 is column C, 7 is column G, etc…  
It will process everything in the column starting from row 2 (ignoring the header row)

You need to take care when using it, and make sure you direct it at the correct columns.

Also note that if you have a sheet with just a single datetime column, you still need to pass an array, ie.

`fix_timestamps(sheetname, [3]);`

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

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