Scripts, scripts, scripts!

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…

2 Likes

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

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.


(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

1 Like

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.

1 Like

For now, my only suggestion would be this:

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

1 Like

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

1 Like

Happy Birthday @Darren_Murphy :partying_face: :partying_face:

2 Likes

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

Will execute with time driven trigger.

You can use getRange().setFormula()

1 Like

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()");
}
3 Likes

:wave: 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? :thinking:

*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… :exploding_head:

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

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.

4 Likes

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!

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 :sunglasses:

3 Likes

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.

2 Likes

Thanks Darren, much appreciated.

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 datetime (except it isn’t :face_with_symbols_over_mouth:)

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.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);
  });
}
8 Likes

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? :woozy_face:

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.

3 Likes

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?

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]);

1 Like