Auto-Add Date and Time to an Adjacent Cell Upon Edit in Google Sheets with Apps Script

[@yagisanatode]

NOTE! If you try and run the onEdit(e) function from the Apps Script IDE you will get an error saying that you "Cannot read properties of undefined (reading 'range') onEdit". This is because the function can only read the event ("e") parameter when it is generated as a simple trigger in the Google Sheet. Go ahead and make an edit in the sheet at the assigned locations an then come back and see the results in the log. You can learn more about this in the written version of the tutorial found here: yagisanatode.com/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/

[@philiphopkins8827]

I have several sheets on which I would like to run this code to add the date. I've tried removing the lines that check the sheet tab but it will not run. Is there a way to amend this to run on any sheet in the workbook? Thanks.

[@yagisanatode]

Sure, check out the expanded written tutorial in the link in the description of the video.

[@pumba1267]

Very useful lesson and explained in a very very simple way that as a beginner helped me immensely.

[@yagisanatode]

Fantastic to hear.

[@luisc1402]

Is it to early on our relationship to say I love you? Thanks a lot for this video! Running (or trying to) a board game library in our small town. Don't know how to manage databases, so just making a lending system to keep track of stuff with google sheets. I wanted to make it as simple to users as possible. So, now they don't even need to fill in the date! Thanks a lot for the video!

[@yagisanatode]

Awh shucks. Great usecase! My wife and I are board game fans too. We have our regular board game day tomorrow at our local game cafe.

[@DisciplineBeatsMotivation8053]

perfect job sir, i wonder if i could does this with cells changing values in the first sheet but making time stamps in the second sheet?

[@yagisanatode]

Absolutely!

[@FernandoFischer6048]

I do not know you but I love you Brother, thank you very much!

[@AmmarMerhbi77]

Thank you so much... I was looking for this script.

[@yagisanatode]

You're welcome

[@temyraverdana6421]

Thanks a lot, a very useful lesson

[@yagisanatode]

Glad you liked it!

[@katrinachong6188]

Thank you! Big help.

[@Mohammed-gy2rc]

Thanks a lot very helpful. How can i use it for more than one sheet?

[@yagisanatode]

Do you mean sheet tab or spreadsheet file? For another tab, check out the written version of this tutorial found in the description. For a separate spreadsheet, either copy and paste in the code, duplicate the current sheet or make a template with the script bound to it.

[@Mohammed-gy2rc]

@@yagisanatode I mean for more than one spreadsheet in the same workbook

[@yagisanatode]

@@Mohammed-gy2rc yagisanatode.com/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/#Inserting_the_automatic_date-time_stamp_updater_into_multiple_sheet_tabs

[@AmmarMerhbi77]

Does it work if column uses query or importrange from different sheet?

[@yagisanatode]

No. Unfortunately this is not considered an onEdit condition. Clock Triggers may be your friend here.

[@barryrodgers]

Hi. Thank for this. I have a sheet where data is added in using Zapier. The time and date doesn't appear when the data is added, only if the data is edited. Is there a fix for this please?

[@jaroooddd]

This is just what i needed! I do however have a small problem. I have a script that copies specific cells in sheet 1 and pastes them in sheet 2 for data entry and data recording, however when the script runs and pastes the values in their cells, the onedit script doesn't run, even though the correct cell has been edited. Is there a way to make this function react to a script pasting the information, rather than user input?

[@yagisanatode]

I would append the script and add the date to that. As you have found, onEdit() requires user interaction. Hope this helps.

[@RahulBhattaray]

it's really help full me, Thank You.

[@yagisanatode]

Glad to hear that

[@ajduke]

Is there a way to get this to trigger when the sheet is being updated via an API call and not a human editing it?

[@yagisanatode]

You could run the function each time the API call is made with a webapp. If you mean that you are calling a service from an addon or connected service, then sadly no.

[@Raf-h7k]

is there any way for the script to only enter date and not the time?

[@yagisanatode]

Sure, you can use either set the formatting in the Google Sheet to only show the date. Format - Number - Date. Or you can use the JavaScript toLocaleDateString() method on the new date. developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString Hope this helps.

[@derejebizualem2112]

Thank you.

[@yagisanatode]

You're welcome!

[@golammostofa9255]

I was looking for it. But where can i get this code?

[@yagisanatode]

Link to the written tutorial in the description.

[@SierraofTerra]

is there a way to modify the script to add a timestamp when another cell's formula changes its result? I.e i have a cell that sums up a series of other cells and I'm trying to have that summed cell be the trigger that adds the time stamp. thank you 🙏 edit: i tried to create a "onChange" trigger in the triggers tab but it appears i have much to learn as i got an error that says: TypeError: Cannot read properties of undefined (reading 'getSheet') at onChange(Code:7:30) 😅

[@yagisanatode]

Hey there! As you have discovered, the onEdit() simple trigger function requires a user edit of a cell. You could trigger onEdit when the source cell has been modified and then have the code look at your formula cells each time, and if they meet your required parameters, add the date-time stamp. This is usually the common practice here. Alternatively, you could set a short clock trigger if you don't require any immediate changes or include a button for the user to submit for the change. Clock Triggers: developers.google.com/apps-script/reference/script/clock-trigger-builder

[@iamdeepakchawla]

What If I want the Date on the column entry instead of column Edit. Like one column is blank and someone from my team has added the Ticket ID to the column, so I want the date to be printed in another column. What should I use instead of "function onEdit(e)"

[@yagisanatode]

The onEdit function is a special simple trigger function used in Google Apps Script. To change columns for the input and the triggered response, update the variables to the new locations. Check out the written tutorial in the link in the description.

[@kiralyszabolcsingatlan]

Hello! If we want to use the same code on several tabs within a file, how to change "const SHEET_TAB_NAME ="? THX

[@yagisanatode]

Have a look at the written version of this tutorial for a heap of variations. Link in the description of the video.

[@jugbrewer]

When I try to run this I'm getting this error: "Cannot read properties of undefined (reading 'range') onEdit" Any idea why this might be happening? I've entered the code exactly as you have presented it

[@yagisanatode]

If you are running from the Apps Script UI , you will get an error because there is no event ("e") parameter for the script to read. Try running the script as intended from the onEdit trigger insise the Google Sheet. If the error persists, please let me know. I'll pin a comment about his for others.

[@rockwithkunal]

Hey , Please let me know how to solve this issue. I cant understand just by reading ur text, can you please make a video on how to deal with this error ? please?@@yagisanatode

[@ScottDonald78]

​@rockwithkunal you can find more details in the written version of the tutorial via the link in the description.

[@rockwithkunal]

@ScottDonald78 yeah I hv checked that as well still facing same error. can u please make video on that ?? please

[@OfficialJohnnyNelson]

Why not just use the formula =IFS(B2="","",A2"",A2,1*1,NOW()) and then drag and auto-fill the rest of column A?

[@yagisanatode]

The date will adjust dynamically, usually after the edit on any cell on the sheet. The Apps Script solution proposed is a static change. You can also use the shortcuts: - ctrl + ; for date - ctrl + shift + ; for time - ctrl + shift + alt + ; for date and time. The benefit of a programmatic solution here is that it removes a step that the user needs to take. You can learn more here: yagisanatode.com/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/

[@iliaskyvos953]

​@@yagisanatodeis this going to work even if I change a color of the cell?

[@yagisanatode]

@@iliaskyvos953 Test it to see if a manual colour change is an onEdit() event. You can learn more about this script's capabilities in the link in the description.

[@aicendio]

date/time is a great way to ensure uid's never get duplicated. I'd love to have say a customer like George Smith followedd by other names where each gets a uid of first 5letters of name paired with date/time. For example. as I ad names to column 2 column 1 populates with georg20240828_22-24 or if tom lee then tomle20240828_22-25 etc. Thanks for this video!

[@yagisanatode]

Great use case! You might find these tutorials helpful: Google Apps Script - Create Custom Unique IDs in Google Sheets: yagisanatode.com/google-apps-script-create-custom-unique-ids-in-google-sheets/ Generate UUIDs in Google Sheets with Apps Script (Unique IDs): kzbin.info/www/bejne/rnm3immrj5Z-q7M