Time stamp update

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!

So have you enabled a trigger for the script? It should work for any rows.

In var COLUMNTOCHECK = 6; it works for F, but how do I make it work for the other ones at the same time? that is what i cant figure out. I have been looking, but English is no my first language so maybe is that i dont know how to look for the answer. sorry to bother you

So apart from F you want to check for updates on the L column as well?

1 Like

Yes, I want to check updates on F, I and L, and record the dates of this changes individualy in the colums next to them.

Right now im ussing this code. If I take out that first part that I saw somewhere it doesnt work. I also Changed the activators to on change like @Mrinal_Chakraborty showed before.

function createSpreadsheetChangeTrigger() {

var ss = SpreadsheetApp.getActive();

ScriptApp.newTrigger('onChange')

  .forSpreadsheet(ss)

  .onChange()

  .create();

}  

// The column you want to check if something is entered.

var COLUMNTOCHECK = 6;

// 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 = ‘Datos’

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

  }

}

}

Would have to ask @Darren_Murphy on this. I believe the right approach here is to check if selectedCell.getColumn() is one of F, I or L (hence check if the value in getColumn is 6, 9 or 12).

Can we do a nested or/and in the if condition for Google Scripts? (to combine || and &&)

2 Likes