Record creation date / modification date of an item

Hey,

Is there a simple way to record the creation date and modification date of an item created via a Glide app ?

For exemple somebody create a new item, and in one column of the line in the Google Sheet, the current date / time is automatically recorded.

I managed to do something with a google script, but it seems to run only when the google sheet is opened. When its closes, the script doesn’t run

Here it is :

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK1 = 9;
var COLUMNTOCHECK2 = 13;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,+1];
// Sheet you are working on
var SHEETNAME = ‘BLs’

function onFormSubmit(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() == COLUMNTOCHECK1) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if( selectedCell.getColumn() == COLUMNTOCHECK2) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
}
}

Thanks for you help.

This might help your case.

You can also record this via the special value component in the Glide form. For the modification part, you can check my script, linked above.

Thank you ! just changing onFormSubmit to onChange made it work just fine !!

1 Like

Yes, as always a reminder that onChange works with updates from Glide, onEdit doesn’t and seems like onFormSubmit is the same.

OK, I didn’t know that about the Glide form. Looks useful as well. Thank you !

1 Like

Could you just do this all with the date time special value? Only fill a Created column when in Add or Form mode, and only fill the Modified column when in Edit mode.

Now I see what you are talking about @Jeff_Hager
but here i just want to record the date at which a user selected a date through a date picker (not on a add or edit or form mode :frowning: )

@ThinhDinh i feel like you are one of the experts here in Google Script and I am struggling with my script.
I check column 9 and 13 of a specific Sheet, and if something changes in these columns i write the date stamp on the next column to the right.

It always seems to work just fine, both in through the app and though the Google sheet directly, but each day I end up with 5 to 10 script errors. And I really don’t know why. It works, and sometimes randomly it doesn’t…

the error says : Exception: Référence de cellule hors plage
at onChange(Code:13:28) which i think is “Cell out of Range” in English.

Here is my script :
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK1 = 9;
var COLUMNTOCHECK2 = 13;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,1];
// Sheet you are working on
var SHEETNAME = ‘:orange_circle:BLs’

function onChange(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks that we’re on the correct sheet.
if( sheet.getSheetName() == SHEETNAME ) {
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK1) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if( selectedCell.getColumn() == COLUMNTOCHECK2) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
}
}

is there something wrong here ?
or is it just normal that sometimes google script can produce errors ? (the error rate is still below 1% but still very annoying).

Thanks a lot for your help !!

1 Like

It’s really strange as I don’t see any problems with the script. Is there any chance you have left empty rows inside the sheet and the new record is written to row 501 or 1001?

there is no fully empty row in the sheet no :frowning: just sometimes some empty cells on a row but that’s all. could these empty celles be a problem you think ?

when this date picker is used it’s never in the context of adding a new row. The row always already exists and already has some stuff on some cells :confused:

But so far, has it affected the values that you want to be recorded?

Very good question. and the answer is yes… absolutely.
let’s say there are 5 errors one day, 1 to 3 of these errors affect what I am trying to do … sometimes more…

May I ask how is it affecting you? Like does it not record the value, or record a wrong value?

it doesn’t record anything !
like nothing happened, but I can see the date has been selected via the date picker is the column right to the left

Are there any characteristics in common among these rows? Like empty cells in certain columns or is that in row 3, row 4, row 5 something?

Not that I can think of unfortunately.

it’s really weird. Thanks for your help @ThinhDinh

if it helps somebody, here is the script I use to apply the dates when the first script doesn’t do it’s work. It runs periodically (everyday, but it could be more if I wanted).

function correctionErreursDuScriptOnChange(e) {
var sheet = SpreadsheetApp.getActive().getSheetByName(‘:orange_circle:BLs’)
var rangeData = sheet.getDataRange();
var lastRow = sheet.getLastRow();

var cell = sheet.getRange(“I2”);
for ( i = 0; i < lastRow - 1; i++){
if (cell.isBlank()) {
}
else if (cell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]).isBlank()) {
var dateTimeCell = cell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
cell = cell.offset(1,0)
}

var cell = sheet.getRange(“M2”);
for ( i = 0; i < lastRow - 1; i++){
if (cell.isBlank()) {
}
else if (cell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]).isBlank()) {
var dateTimeCell = cell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
cell = cell.offset(1,0)
}
}

1 Like

Was there ever any follow up to this? (I see there were some bugs)
Looking for a script to write a time stamp when a certain range/ column is modified.

Form submission is not an option for me in this case…

You mean modified by Glide?
That’s always going to be a challenge, because:

  • Glide only ever triggers a Change event, and
  • A Change event does not support the range object

Which means that although you can tell which sheet the change occurred in (via event.source), there is no way to directly determine which range or cell was modified.

That doesn’t mean it isn’t possible, it just means you need to get a little creative with your solution/approach.

NB. The Google Apps Script reference doc suggests that event.source isn’t available with a change event. But in this case, the reference docs are wrong :wink:

1 Like

what if i have all the data in the sheet and now i want to get the data of who and when has updated the particular cell or data , can i do it using apps script?

“Who” and “when” can be known using special values in your edit screen. You can add the email of the signed-in user and the timestamp of editing so it updates to 2 columns, let’s say “last edited by” and “last edited at”.

Knowing which particular cell or data was updated is much trickier.