Hello. Thank you for this video. Update: Your script DOES run great. Thank you. There was a huge (1/2 hour or more) delay in it starting to work on my sheet, which is why I thought it didn't work. But, viola, it suddenly started worked much to my surprise. Thank you.
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.
Hi, Thanks for the script! I was wondering if I can change the date format to "Tue, 11/12" or what should I remove from the script to change the date format of my liking?
amazing video, thank you for putting this together, helped to solve an long standing challenge I had trying to provide the date last updated for a database I'm using!
Thank you for that walk through! Can you make the script populate a cell, with the CURRENT (from a pre-existing) last edited date? (It does work, as you intended, but only if you - from here forward edit a cell) I was wondering if there was a way to pull the currently listed (w/out having to edit again) - last modified date?
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).
Thank you for the video, it worked really well! Is there any way to keep one row out of the whole modified code, so it doesn't show its timestamp every time i change that row in specific?
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.
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.
Hi there! This is very helpful. How do you make this project work when you want just one time/date stamp when any part of the sheet is updated? So not a time/date stamp for every row, just the entire sheet.
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.
Hello. Thank you so much. I am trying to implement something like this and always hits the wall. In my scenario, I want sheets to get user email in cell A2, date and time stamp in cell B2 for a corresponding edit in the same row from E2 to H2. This should continue on per row basis. Please assist.
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
This is exactly what I need! I am wondering, how do I restrict the cells I want a timestamp for? For example I would like the timestamp for E4:F23 to show in G4:G23
@@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.
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.
Thank you so much it worked, the only thing is that if I change de name of the first row it changes the cell LAst updaterd to the date, how can I do it so it doesnt change the first row
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) { }
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);
I hope I can get this work, but when I attempt to run the script I get an error: "This app is blocked This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access." Any ideas?
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.
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: ruclips.net/video/i-qd8vXUoEA/видео.html
I realize I'm super late to the video on this one but I am hoping you can help me, I've tried fixing it myself and I've tried looking through other comments but I cannot find my specific issue / need. I just need a single column to make updates to the timestamp. I want changes I make in col 2 to put a timestamp in col 5 and I want it to ignore col 1,3,4, and anything over 5. Please and thank you for any assistance you might provide.
@@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
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.
I'm receiving the following error message: Nov 7, 2023, 12:23:33 PM Error TypeError: Cannot read properties of undefined (reading 'range') at onEdit(Code:6:17) Any idea how to rectify this?
Doesn't work for me. I have one project with 3 different files. It seems only one script work. Is there any way to create separate projects for each script? I really don't know whats wrong or where I can get help with this. Please 🙏
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?
Hey there - This was very helpful. Im running into an issue with the date format though. Whenever I make an edit the date is not pulling in correctly. For instance instead of the date spitting out 11/21/23 12:54pm its spitting out weird numbers like 54/21/23 12:54 PM. Please advise on what is going on?
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.
![Last Opened Timestamp in Google Sheets - Apps Script [SheetsNinja]](http://i.ytimg.com/vi/SBE0IPu7xus/mqdefault.jpg)
![Last Opened Timestamp in Google Sheets - Apps Script [SheetsNinja]](/img/tr.png)
Hello. Thank you for this video. Update: Your script DOES run great. Thank you. There was a huge (1/2 hour or more) delay in it starting to work on my sheet, which is why I thought it didn't work. But, viola, it suddenly started worked much to my surprise. Thank you.
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.
Hi, Thanks for the script! I was wondering if I can change the date format to "Tue, 11/12" or what should I remove from the script to change the date format of my liking?
Yes, if you change the format to:
"ddd M/d" it should display in the format you're looking for
@@SheetsNinja Thanks for response, I changed it to "ddd M/d" but only getting "017 11/17"
amazing video, thank you for putting this together, helped to solve an long standing challenge I had trying to provide the date last updated for a database I'm using!
Awesome, so glad this helped!
Thank you for that walk through! Can you make the script populate a cell, with the CURRENT (from a pre-existing) last edited date? (It does work, as you intended, but only if you - from here forward edit a cell) I was wondering if there was a way to pull the currently listed (w/out having to edit again) - last modified date?
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).
Thank you for the video, it worked really well! Is there any way to keep one row out of the whole modified code, so it doesn't show its timestamp every time i change that row in specific?
Yes, in the "if" parentheses, just add "&& row != 2" for example
So it would be something like:
If (sheetName == 'My Tab' && row != 2) {
}
Looks like it doesn't update the timestamp if you are editing the cell that was previously filled out.
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.
so useful! how would I extend this to also mark who in google shared made the edit?
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.
Hi there! This is very helpful. How do you make this project work when you want just one time/date stamp when any part of the sheet is updated? So not a time/date stamp for every row, just the entire sheet.
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.
Hello. Thank you so much. I am trying to implement something like this and always hits the wall. In my scenario, I want sheets to get user email in cell A2, date and time stamp in cell B2 for a corresponding edit in the same row from E2 to H2. This should continue on per row basis. Please assist.
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
@@SheetsNinja Awesome. Is there a similar way to display the Usename?
@LQ_LQ_LQ unfortunately not, Google restricted permission to emails in the last couple of years.
@@SheetsNinja Dang. Thanks for the quick reply, will stick to the email then!
This is exactly what I need! I am wondering, how do I restrict the cells I want a timestamp for? For example I would like the timestamp for E4:F23 to show in G4:G23
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.
Nice project, thank you. Can you make a project like this so that it gives a timestamp when you open the google sheet, not when we update it?
Yes, I just make a video walking through how to do that here: ruclips.net/video/SBE0IPu7xus/видео.html
Thank you! What if I wanted to add the User who last modified next to the timestamp? Is that doable?
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.
Thank you so much it worked, the only thing is that if I change de name of the first row it changes the cell LAst updaterd to the date, how can I do it so it doesnt change the first row
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!
This worked perfectly! Thanks!
Awesome, so glad this was helpful!
What's the script for displaying the timestamp in a single cell, regardless of where I edit the sheet?
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);
I hope I can get this work, but when I attempt to run the script I get an error: "This app is blocked This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access." Any ideas?
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.
Maybe a stupid question, but if I share this document to another person, will it run this function on theirs?
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: ruclips.net/video/i-qd8vXUoEA/видео.html
I realize I'm super late to the video on this one but I am hoping you can help me, I've tried fixing it myself and I've tried looking through other comments but I cannot find my specific issue / need. I just need a single column to make updates to the timestamp. I want changes I make in col 2 to put a timestamp in col 5 and I want it to ignore col 1,3,4, and anything over 5. Please and thank you for any assistance you might provide.
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!
Can't click Run.
I'm receiving the following error message:
Nov 7, 2023, 12:23:33 PM Error TypeError: Cannot read properties of undefined (reading 'range')
at onEdit(Code:6:17)
Any idea how to rectify this?
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
Doesn't work for me.
I have one project with 3 different files. It seems only one script work.
Is there any way to create separate projects for each script?
I really don't know whats wrong or where I can get help with this.
Please 🙏
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?
Thanks!! 👍
So glad this was helpful!
Hey there - This was very helpful. Im running into an issue with the date format though. Whenever I make an edit the date is not pulling in correctly. For instance instead of the date spitting out 11/21/23 12:54pm its spitting out weird numbers like 54/21/23 12:54 PM. Please advise on what is going on?
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?
thank you so much buddy
Glad I was able to help!