Time stamp update

Ask for Help
22

Yep, the special value columns should be available in add, edit, and form mode. Just add it on your edit screen.

23

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

1 Like
24

I have this same issue

25

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

26

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
27

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

}

28

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.

29

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

30

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

31

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.

Trigger
Trigger711Ɨ581 26 KB

1 Like
32

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

33

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
34

And an update to confirm that it works with Glide.

Hereā€™s the worksheet if you want to try yourself.

1 Like
35

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!

image
image1384Ɨ1278 75.5 KB

36

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

37

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.

38

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
39

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
40

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

1 Like
41

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