Time stamp update

Got it! Sometimes I simply make things too hard! Really loving this product!

1 Like

I have this same issue

Hi Jeff, I am developing an app which requires multiple checkboxs to be activated and when they change they need a date time stamp in the next 2 fields, the first time stamp for the initial change and the second timestamp on any further changes (last submitted times) I have 6 of these on a posts page and therefore can only use the time stamp for the submission of the post itself. The other 5 checkboxs need a unique time stamp when they are activated or changed for the first time. These checkboxs are being run from the edit screen within the glide app. l have used scripts calling the onEdit function however they only work when I edit the sheet and not when glide posts to the sheet. Can you help me find a solution that works? Thanks

Hi Johnny,

Just my idea on this, so let me summarize what I get from your case:

  • You have 1 column for a timestamp of the “initial change”
  • You have 1 column for any further changes, which will record the last time the status was changed
  • You have 6 checkboxes and need any edits in Glide reflect in the timestamp change in the sheets.

How about writing a script that:

  • Takes the timestamp from any change. If column of initial change is empty, record it to that column, let’s call it column A.
  • If column A is not empty, we then move to column B of further changes. Simply record the newly taken value to column B, whether it already had value or not (because you want to take the latest change timestamp).

Another note is it seems that onEdit does not work, as you may have noticed, but onChange does, as the people in this thread said.

1 Like

Hi @ThinhDinh here is my script. It looks for a change within the spreadsheet for True and sets the new Date. This works with onEdit however is my code needing to be adjusted as it doesnt work if I change onEdit to onChange? Thanks

function onEdit(e){
if(e.value == “TRUE”)
e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart+1).setValue(new Date());

}

Hi, I would like to suggest you changing from onEdit to onChange and see what happens when you edit the checkboxes in Glide.

This thread says: " onEdit only works when you edit a cell (not when a row is added or when a note/comment is added) and onChange will capture that a change has occurred and trigger when appropriate."

As the Sheets may not have seen a Glide action as a “cell edit” action, onChange would suit better as it takes into account the value of the cell.

Hi I would be interested to know how you did it?

Ok give me some time I will give you a demo on what I meant.

What I could understand Glide doesnot support onEdit(e), you have to use onChange(e). Here also you cannot get any benefit of “e” parameter. Make your Function and assign to trigger during onChange in Spreadsheet.

1 Like

Thanks much appreciated I have been working on this for a number of days as I am only new to scripts :slight_smile:

Hi John, sorry for the late reply but here’s the working script.

// The column you want to check if something is entered.
var COLUMNTOCHECK = 2;
// Where you want the date time stamp offset from the input location. [row, column]
var INITIAL = [0,1];
var LATEST = [0,2];
// Sheet you are working on
var SHEETNAME = 'Change test'
 
function onChange(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if(sheet.getSheetName() == SHEETNAME) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if(selectedCell.getColumn() == COLUMNTOCHECK && selectedCell.getValue() == true) { 
      var initialcell = selectedCell.offset(INITIAL[0],INITIAL[1]);
      var latestcell = selectedCell.offset(LATEST[0],LATEST[1]);
      if(initialcell.getValue() !== ''){
      latestcell.setValue(new Date());}
      else {
      initialcell.setValue(new Date());}
      }
  }
}

The setup: a spreadsheet with column B containing the check boxes, column C as initial change and column D as latest change.

image

Input your script into the script editor, notice the fields you have to change are:

  • var COLUMNTOCHECK = 2 => Change number 2 to whatever column contains your checkbox. Here it’s column B, hence number 2.
  • var SHEETNAME = ‘Change test’ => Change ‘Change test’ to your sheet’s name.

Then navigate to Edit > Current Project’s Triggers and set the conditions like what @Mrinal_Chakraborty showed.

A demo on how it works.

ezgif-1-4a0024634334

2 Likes

And an update to confirm that it works with Glide.

Here’s the worksheet if you want to try yourself.

1 Like

I’m also struggling with this.
the function works when onChange(e) is changed to onEdit(e).
I have also set the trigger as you guys had suggested but nothing happens :frowning:

Please help!

Can you show me a copy of your data and message me your flow?

function onChange(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//checks that we’re on the correct sheet.
if(sheet.getSheetName() == “FR Input”) {
var columnOfCellEdited = ss.getActiveCell();
if (columnOfCellEdited.getColumn() == 32) {
e.range.offset(0,1).setValue(new Date ())
// range.getRange(range.getRow(),33).setValue(“new info”);
}
}
}

If I change onChange to onEdit(e) and I manually edit the column, it works just as I am hoping for.

Not sure what you mean by my flow.

I don’t think you can pass an (e) event parameter when using the onChange trigger. Try removing the parameter. You will also have to restructure your script to not use the passed in event parameter once it’s removed. Also, you don’t have to name your function onChange. You can name it anything as it’s the onChange trigger that’s calling the function you specify. @ThinhDinh is a genius at sheets and scripting, so maybe he can get you going on that.

1 Like

Thanks for the response, Jeff. I realised that I didn’t even need to make it so complicated. Since the column I’m using is either true or false (or blank), I just created a script that executes once the boolean changes to true (and have it change it false to false when done). I then set it on a timer.

Not real time, but enough to test out the concept to see how many people find the product useful :slight_smile:

2 Likes

Tell me if you need further assistance, I will try to help.

1 Like

Maybe this will solve? ⏬ In Staging: COMPOUND Actions!

Not played with it myself, yet, so cannot confirm at this stage whether it would work.

Thought it was worth mentioning though.

Enjoy

1 Like

Hi, I’m ussing this code to record the date and its working for one row, but how do i make it to record the dates of 3 diferent rows? i want to check when i turn a cell in F on and record that date, then another day i check on I and record that date, and same with L. I have very basic, more like non about coding.
As you can see, it is working for the first row.
Thanks a lot in advance!