# Record creation date / modification date of an item

**URL:** <https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354>\
**Category:** Ask for Help\
**Created:** [June 5, 2020, 3:37pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354 "2020-06-05T15:37:05Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 5, 2020, 3:37pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/1 "2020-06-05T15:37:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 5, 2020, 3:40pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/2 "2020-06-05T15:40:11Z")

</div>

This might help your case.

> [@Time stamp update](https://community.glideapps.com/t/time-stamp-update/1382/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.getShe…

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 5, 2020, 3:41pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/3 "2020-06-05T15:41:23Z")

</div>

> [@caffinho](#):
>
> 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.

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.

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 5, 2020, 3:56pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/4 "2020-06-05T15:56:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 5, 2020, 3:58pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/5 "2020-06-05T15:58:20Z")

</div>

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

> [@Time stamp update](https://community.glideapps.com/t/time-stamp-update/1382/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.

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 5, 2020, 3:58pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/6 "2020-06-05T15:58:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [June 5, 2020, 4:21pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/7 "2020-06-05T16:21:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 23, 2020, 9:08am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/8 "2020-06-23T09:08:37Z")

</div>

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 ☹ )

@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 = ‘🟠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 !!

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2020, 9:17am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/9 "2020-06-23T09:17:14Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 23, 2020, 9:23am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/10 "2020-06-23T09:23:21Z")

</div>

there is no fully empty row in the sheet no ☹ 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 😕

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2020, 9:42am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/11 "2020-06-23T09:42:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 23, 2020, 9:47am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/12 "2020-06-23T09:47:07Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2020, 9:47am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/13 "2020-06-23T09:47:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 23, 2020, 10:01am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/14 "2020-06-23T10:01:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2020, 10:02am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/15 "2020-06-23T10:02:52Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![caffinho](https://avatars.discourse-cdn.com/v4/letter/c/ac8455/32.png) [@caffinho](https://community.glideapps.com/u/caffinho)\
**Post date:** [June 23, 2020, 12:23pm UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/16 "2020-06-23T12:23:57Z")

</div>

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(‘🟠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)  
}  
}

---

<div class="post-metadata">

**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\
**Post date:** [June 14, 2021, 6:18am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/17 "2021-06-14T06:18:19Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 14, 2021, 7:28am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/18 "2021-06-14T07:28:21Z")

</div>

> [@Eric\_Penn](#):
>
> Looking for a script to write a time stamp when a certain range/ column is modified.

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

- Glide _only_ ever triggers a [Change](https://developers.google.com/apps-script/guides/triggers/events?hl=en#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 😉_

---

<div class="post-metadata">

**Author:** ![Hemant\_Lambat](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hemant_lambat/32/36847_2.png) [@Hemant\_Lambat](https://community.glideapps.com/u/Hemant_Lambat)\
**Post date:** [January 3, 2022, 5:47am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/19 "2022-01-03T05:47:33Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [January 3, 2022, 8:48am UTC](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354/20 "2022-01-03T08:48:17Z")

</div>

“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.

[Next page](https://community.glideapps.com/t/record-creation-date-modification-date-of-an-item/10354.md?page=2)