Automatic Last Updated Timestamp in Google Sheets - Apps Script 

Awesome, so glad this helped!

So this script isn't meant to run from the script editor, the onEdit trigger runs whenever the Google Sheet is modified. So if you make an edit in the Google sheet, it should run successfully.

@@SheetsNinja Wow, thank you for the quick reply. When I go to the sheet and make an edit, the updated field remains blank. Would it make any difference if the sheet has multiple tabs, as mine does? Thank you again for this help. I really appreciate it.

So you can't get the user email unless everyone authorizes the script. If you do have everyone authorize the script, then you can do: let email = Session.getActiveUser().getEmail() And then if you only want it to run for edits in E to H, then in the script, you would want to put this around the rest of the script (below the main variables like .getRow() and .getCol() ) If (col >= 5 && col

I just made another video with a simpler version of this script that doesn't require authorization--meaning it will automatically work for anyone who opens the Google Sheet: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-i-qd8vXUoEA.html

I'm not sure I entirely understand what you're wanting to do... However, you can pull a value from an existing cell if you like, something like this: let currentCell = sheet.getRange(row,6).get value(); sheet.getRange(row,12).setValue(currentCell); Or if you are talking about a single cell, maybe up top, with the date you want to use: let existingDate = sheet.getRange("E2").getValue() sheet.getRange(row,8).setValue(existingDate);

@@SheetsNinja I appreciate the reply back regardless. I probably didn't explain that as well as I could. >> 2 things: 1) [ I think you have it right in your reply ] • The original formula updates a designated cell with a date &/or time value, after an update occurs (after running the script). • I was hoping to pull the "last modified date" value that already is there, no edits required.. (to save me the trouble of having to go in/out of each cell to find the date for cells already edited). 2) Also, is it possible to set a condition where it only adds a date/time when the cell (where it'll put the date/time) is blank? (so it only does it once, upon the first edit/update & it doesn't continually update itself if further updates are made on that row).

The way the script is set up, it will add a timestamp regardless of the change. However, if you are editing cells in multiple rows at the same time, it will not log that correctly.

Awesome, so glad this was helpful!

Unfortunately that is a restricted scope, and the only way to get around it is using a simple onEdit trigger and have each person using the sheet to authorize the script.

Are you using a Google Account on a Workspace account? Sometimes on Workspace accounts, the admin has set it up to block any scripts and so that may be the case. A good way to test this is to try running this using a personal Google account.

Yes, in the "if" parentheses, just add "&& row != 2" for example So it would be something like: If (sheetName == 'My Tab' && row != 2) { }

So it makes it very simple then, you get rid of the if statement and inside the function, you just do the "let ss =" line and then "let sheet = ss.getSheetByName("LOG_SHEET");" Then the timestamp line and finally just "sheet.appendRow([today]);"

​@@SheetsNinja thanks for your response! I'm not well-versed in code so I am definitely not doing something right here. My sheet name is AL, I will be doing this for several sheets in one file. Where do I put the name of the sheet in with this new code? Where do I put what cell in the sheet the time stamp will go? So far I have: /* @OnlyCurrentDoc */ function onEdit(e) { let ss = SpreadsheetApp.getActiveSpreadsheet(); let sheet = ss.getSheetByName(LOG_SHEET); let timestamp = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"m/d/yy h:mm a"); sheet.appendRow([today]); }

@@JohnAmmatuna Not sure if you already got this to work or not. Disclaimer that I am not at all well-versed in code, and I'm using this for a hobby. So what I did to get this to "work" may not be the best in a professional setting. The following will pretty much only work if the timestamp cell is the same across ALL sheets in the document. If you want to exclude a sheet, you'll have to add some conditions. function onEdit(e) { let sheetName = e.source.getActiveSheet().getName(); let ss = SpreadsheetApp.getActiveSpreadsheet(); let sheet = ss.getSheetByName(sheetName); let timestamp = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"M/d/yy h:mm a"); sheet.getRange(2,15).setValue(timestamp); } I didn't need to include the spreadsheet name, but I did have to specify the row and column number for the timestamp cell (in my case, row 2 column 15). This is just kind of a hack fix, but it works for my purposes. 🤷🏻‍♀ If you only have 1 sheet, then you don't need to define sheetName (can delete the "let sheetName =") and instead can type in the name of the sheet in place of LOG_SHEET. The sheet.appendRow([timestamp]); can be used instead of specifying a cell. It adds a timestamp cell/row after all existing data.

So Google only allows you to grab the user info from people who explicitly authorized the script, so if you make everyone authorize the script, then you can use this methodnto get their email: let userEmail = Session.getActiveUser().getEmail()

@@SheetsNinja thanks for replying! i ran this query through chat gpt and it didn’t suggest needing to get other users to auth the script. i’ll test this tomorrow though. thanks again

@@JackMcDonnell91 This is why you can't rely on Chat GPT for everything. I know from experience that Google indeed does require you to have the users authorize if you want to grab their email address from an onEdit application.

Yes, I just make a video walking through how to do that here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SBE0IPu7xus.html

All you would need is the "let ss" line, and the "let today" line, and then a line to paste the timestamp: ss.getSheetByName("SheetName").getRange("A1").setValue(today);

All you need to do is put "col == 2" in the if statement. So something like this: if (col == 2) { sheet.getRange(row,5).set value(today); }

@@SheetsNinja sorry I'm terrible at all this, would I still need to specify that it's sheet1 somewhere? Or do I just replace the entire if statement with what you said

@@zackreynolds thank you that worked. I thought that might work before but I didn't do == in my troubleshooting I only did =

So in the example if statement, I exclude a column with "col != 12", so if you just replace that with "col == 2" then it will work the way you're hoping.

@@SheetsNinja thank you so much for the help!

So you just add that into the "if" conditions. For example.... If (val != "' && row >= 4 && row = 4 && col

@@SheetsNinja That worked, but I ran into another issue. The cells I want to return a timestamp for are SUM functions, they're totaling up values from another sheet. I noticed that the timestamp works if i manually change the cells, but I want a timestamp to return when I add a new value for that SUM function to calculate, but the timestamp does not update when I do that.

@@0xSICKNICKx0 This isn't working for you because the values getting changed aren't on the same tab. If the rows are tied together, you may be able to get something custom set up where the onEdit script will add the timestamp on row 15 on tab 1 when you edit cells on row 15 on tab 2.

So all you have to add inside the IF statement paratheses is: && row > 1 So something like this: if (sheetName == 'Lead Management' && col == 12 && row > 1) { }

@@SheetsNinja thank you so much!

Glad I was able to help!

Hey, so multiple onEdit files won't work, so you'll just want to combine the code within one file. Keep in mind that you can't reuse variable names so you can just set it once (e.g. you can declare "let ss = SpreadsheetApp.getActiveSpreadsheet()" once and then reference it for multiple purposes). Typically what I will do is nest each thing I want to do with an onEdit script within an "if (conditions)" statement. The last updated timestamp doesn't need an if statement since you want it to run each time the script triggers, but other things can be.

@@SheetsNinja Very nice answer! I am totally new to apps script and can't really code myself, I am only copying guides at the moment. Do you have any advice on where I can learn apps script from Zero to Hero ? :-D Also as you mentioned above: I only run the "setup" commands once, then I just call on those commands to create several different functions within my document with that one single script? No need to create more then one script file as only one can work?

That's a typical error when you authorize the script. Is the script functioning as designed now?

No. It still reads "TypeError: Cannot read properties of undefined (reading 'range') at onEdit(Code:6:17)"@@SheetsNinja

Same here. Any updates? Thanks!

Please help!!! Same problem here

Are you in the US? This sounds like in the Utilities.formatDate() part, the MM part is rendering as something other than the month If you type out Utilities.formatDate( in the script editor, you should see a tooltip popup with an explanation, and see what it says to have for the month part of the date.

@@SheetsNinja Yes Im in California - I have copied your code exactly from the link in your description and just changed my sheet name

here is the line with the format that Im using: let timestamp = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"m/d/yy h:mm a");

​@@PeterBacon-g4p Try changing "m/d/yy h:mm a" to "M/d/yy h:mm a". m is for minutes and M is for month.

​@@SheetsNinja I think the "m/d/yyyy h:mm a" needs to be changed to "M/d/yyyy h:mm a". Could you test that out and update the script if necessary?