How to insert timestamp automatically when data is updated in another column in Google sheet?
If you have a range of cells and you want to insert a timestamp automatically into the adjacent cell when the data is modified or updated in another column. How could you solve this task in Google sheet?
Insert timestamp automatically when data is updated in another column with script code
Insert timestamp automatically when data is updated in another column with script code
The following script code can help you to finish this job quickly and easily, please do as this:
1. Click Tools > Script editor, see screenshot:
2. In the opened project window, please copy and paste the below script code to replace the original code, see screenshot:
Note: In the above code, order data is the sheet name that you want to use, Date is the column header which you want to insert timestamp, and Order is the column header which cell values you want to be updated. Please change them to your need.
3. Then save the project window, and enter a name for this new project, see screenshot:
4. And then go back to the sheet, now, when the data in Order column is modified, the current timestamp is inserted into the cell of Date column automatically which is adjacent to the modified cell, see screenshot:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More... Free Download... Purchase...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Read More... Free Download... Purchase...
- 62 Comments
- Login
- Sort by Newest
- Best
- Popular
- Newest
- Oldest
Say something here...
symbols left.
You are guest
- Login Now
or post as a guest, but your post won't be published automatically. Post
Loading comment... The comment will be refreshed after 00:00.
- To post as a guest, your comment is unpublished.betha · 5 months agoHi.. Thank you very much,I was struggling to get the below excel macro script into google sheet script. Can you please help me out."Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 10 Then
Application.EnableEvents = False
If Target.Value <> """" Then
Target.Offset(, 6).Value = Environ(""username"")
Else
Target.Offset(, 6).ClearContents
End If
Application.EnableEvents = True
End If
If Target.Column = 11 And Target.Column Mod 1 = 0 And Target.Row >= -8 Then
For Each c In Target
If c.Value = c.Offset(0, -4).Value Then
c.Offset(0, -8).Value = Format(Date, ""DD/MMM/YYYY"")
Else
c.Offset(0, -8).Value = """"
End If
Next c
End If
If Target.Column = 10 And Target.Column Mod 3 = 1 And Target.Row >= 6 Then
For Each c In Target
If c.Value = """" Then
c.Offset(0, 7).Value = """"
Else
c.Offset(0, 7).Value = Format(Time, ""h:mm AM/PM"")
End If
Next c
End If
End Sub
"- Reply
- To post as a guest, your comment is unpublished.Geneva · 5 months agoThis worked for me perfectly. I had three separate columns that I had to update with static dates. Just made three diff arguments for each column and it worked. Thank you!
- Reply
- To post as a guest, your comment is unpublished.Michelle · 3 months agoCan u show how do you do that?
- Reply
- To post as a guest, your comment is unpublished.MissBFierce · 6 months agoHow do i apply this to work in multiple tabs in google sheet?
- Reply
- To post as a guest, your comment is unpublished.Belinda · 6 months agoI need this to apply in multiple sheets. How do I do that
- Reply
- To post as a guest, your comment is unpublished.SKD · 9 months agoReplace e.source by SpreadsheetApp.getActiveSpreadsheet()
- Reply
- To post as a guest, your comment is unpublished.manish · 11 months agoTypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3pl. give solution ,,, on google sheet- Reply
- To post as a guest, your comment is unpublished.MANISH · 11 months agoTypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3- Reply
- To post as a guest, your comment is unpublished.Vinay Pal · 1 years agoTHANK YOU IT WORKED
- Reply
- To post as a guest, your comment is unpublished.uma · 1 years agoHi all, will this script work for the column populated by a formula or auto populated. depending on a auto populated column how can i generate the the updated date.
- Reply
- To post as a guest, your comment is unpublished.Allyson · 1 years agoHello I found this very helpful yet have a additional step in my spreadsheet. My Column A data remains untouched which is what I want. I need Column B when updated by staff date&time stamp Column C. I have this so far BUT I also need Column D when updated to date&time stamp Column E. So B&C interact and D&E interact. Advice?
- Reply
- To post as a guest, your comment is unpublished.Michael · 1 years agoHow do i set the script so that id E11 is changed then the time stamp auto populates in E10 ?
- Reply
- To post as a guest, your comment is unpublished.Vince M · 1 years agoThanks, so much! The was exactly what I needed. My use case: Collecting devices from students. I'll be scanning the barcode from each device, and want to note when I receive the device. I adjusted code, obviously, to reference my column names, and I added the time of day to the date format so that I can be more precise.Vince MIT AdminPreK-8 School
- Reply
- To post as a guest, your comment is unpublished.jag · 1 months agoHi Vince. Your case sounds very much alike what I'm about to do at this moment. It is possible to see your code for this case? Thank you very much in advance.
- Reply
- To post as a guest, your comment is unpublished.skyyang · 1 years agoHello, Vince M,
Thank you for your comment, do you solve your problem?- Reply
- To post as a guest, your comment is unpublished.Mas JO · 1 years agoWell its works
- Reply
- To post as a guest, your comment is unpublished.eddie · 2 years agohi when i run this script it give me a error on line 3 ? can someone help me get this fix please
- Reply
- To post as a guest, your comment is unpublished.Carlotta · 2 years agoSame problem here.
- Reply
- To post as a guest, your comment is unpublished.skyyang · 2 years agoHello, eddie,
Could you change the sheet name in the above code to your own sheet?
Please check it, thank you!- Reply
- To post as a guest, your comment is unpublished.eddie · 2 years agofunction onEdit()
{
var sheet = SpreadsheetApp.getActiveSheet();
var capture = sheet.getActiveCell();
if (sheet.getName() == "Jewel Inbound trailer") //"order data" is the name of the sheet where you want to run this script.
{
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf("Time") + 1;
var orderCol = headers[0].indexOf("Tractor") + 1;
if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
{
sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC-6", "MM-dd-yyyy"));
}
}
}- Reply
- To post as a guest, your comment is unpublished.eddie · 2 years agocan someone help me to make this work correctly on google sheets
thank you- Reply
- To post as a guest, your comment is unpublished.Javier · 2 years agoIt didn't work for me
- Reply
- To post as a guest, your comment is unpublished.skyyang · 2 years agoHello, eddie,
I have tried your code, it works well in my Google sheet, please try it again! Thank you!- Reply
- To post as a guest, your comment is unpublished.isrami · 2 years agocan we change this to track changes on certain range of column instead of column? assuming that our column to be tracked is at the middle of our sheet?
- Reply
- To post as a guest, your comment is unpublished.Sarah · 2 years agoHow do you track changes on more than one column though? Using your example, how do you edit the script to track changes in both "product" and "order" columns?
- Reply
- To post as a guest, your comment is unpublished.Simón Blanco · 2 years agoExiste una manera de hacer esto pero que la fecha se introduzca sólo si se escribe una palabra específica?
- Reply
- To post as a guest, your comment is unpublished.Ricardo · 2 years agoGenial, excelente, es lo que estaba buscando, muchas gracias, saludos
- Reply
- To post as a guest, your comment is unpublished.Juan Hernnandez · 2 years agoAwesome! Thanks
- Reply
- To post as a guest, your comment is unpublished.Daniel Méndez · 2 years agoHola, hice los pasos que mencionas pero me aparece un error: TypeError: No se puede leer la propiedad "source" de undefined. (línea 3, archivo "Código")
- Reply
- To post as a guest, your comment is unpublished.Fabricio Rodrigues · 2 years agoI fix it whit this code.
function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var capture = sheet.getActiveCell();
if (sheet.getName() == "Updates") //"Updates" is the sheet name.
if(capture.getColumn() == 1 ) {
var add = capture.offset(0, 1); //"0" is the line in reference the cell updated, ''0'' same line, "1" reference at column "1" is 1 column to the right.
var data = new Date();
data = Utilities.formatDate(data, "GMT-03:00","dd/MM' 'HH:mm' '");
add.setValue(data);
}
}- Reply
- To post as a guest, your comment is unpublished.Jorge · 2 years agoHi Fabricio!
On the 1 I have to write the Date column (where I want to get the date) and on 0 column where I write text?
Do I need "" or similar?
Thanks!- Reply
- To post as a guest, your comment is unpublished.· 2 years agoHello Jorge, no, you just need to write the number referente to column, like A = 1 , B = 2 .....
- Reply
- To post as a guest, your comment is unpublished.Rej · 2 years agoGood day! I'm just wondering if it's possible to add a code for the timestamp to automatically disappear once the main cell has been cleared. Thank!
- Reply
- To post as a guest, your comment is unpublished.ScottC · 2 years agoHow should the script be modified to look for changes in a contiguous range of columns rather than a single column? e.g. trigger the script if there are changes in columns labeled, "Amount", "Category" and "Type" rather than the single column labeled "Order" in the example script.
- Reply
- To post as a guest, your comment is unpublished.Annette · 3 years agoHey! I got this code "Missing } after function body. (line 18, file "Code")" How do I fix this issue? Thank you so much! This is amazing!
- Reply
- To post as a guest, your comment is unpublished.James · 3 years agoHi
I got the code working, thanks!
If I would like to include mutiple columns, how would I alter the code?- Reply
- To post as a guest, your comment is unpublished.Blaze · 3 years agoI am trying to do the same, any luck figuring this out?
- Reply
- To post as a guest, your comment is unpublished.Nicky · 3 years agoHi I face an error TypeError: Cannot read property "source" from undefined. (line 3, file "Code")
Able to help on this- Reply
- To post as a guest, your comment is unpublished.Anton · 3 years agoSame here. Please help.
- Reply
- To post as a guest, your comment is unpublished.Dhaval · 2 years agosame issue here
- Reply
- To post as a guest, your comment is unpublished.Willy · 3 years agoThanks for this code, it's exactly what I need. The only problem is I am running a script that sends some data to google sheet, but the time stamp doesn't trigger for this data, only when I edit the cell manually. Any advice?
- Reply
- To post as a guest, your comment is unpublished.· 3 years agohello, I have the same problem, did you solve it?
- Reply
- To post as a guest, your comment is unpublished.Nikita · 2 years agohello I have a similar problem, did you solve it?
- Reply
- To post as a guest, your comment is unpublished.Ryan · 3 years agoI am getting an error "TypeError: Cannot read property "source" from undefined. (line 3, file "Code"). Do I have to provide the link of the sheet in this line?
thanks,
Ryan- Reply
- To post as a guest, your comment is unpublished.Superbyte · 1 years agoUse functionvar sheet = SpreadsheetApp.getActiveSheet();instead var sheet = e.source.getActiveSheet(); in Line 3.It solved my problem.I am using it like thisf{ var sheet = SpreadsheetApp.getActiveSheet(); if (sheet.getName() == "Sheet1") //"Sheet1" is the name of the sheet not File name { var actRng = sheet.getActiveRange(); var editColumn = actRng.getColumn(); var rowIndex = actRng.getRowIndex(); var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); var dateCol = headers[0].indexOf("Time Stamp") + 1; var orderCol = headers[0].indexOf("Date Entry") + 1; if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol) { sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+5.30", "MM-dd-yyyy HH:MM:SS")); //I am using it in India GMT 5.30+ with Time also } } }
- Reply
- To post as a guest, your comment is unpublished.Shivam · 3 years agoYup, same. Admin, pliss to help.
- Reply
- To post as a guest, your comment is unpublished.KLip123 · 3 years agoHI there,
I'm looking to do the equivalent get a timestamp in the "date" column whenever the "Order" is updated, but also whenever the "Delivery Status" or "Payment Status" is updated as well (making up column heading but I hope you get my drift).
Is this possible?
Thanks- Reply
- To post as a guest, your comment is unpublished.Mirzaei · 3 years agoI love this script. How do I only get this to Print Time instead of DATE? That is what I need
- Reply
- To post as a guest, your comment is unpublished.skyyang · 3 years agoHello,
you also can apply the following code, but, you should change the time zone to your own. Please try it.
function onEdit(e)
{
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
{
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf("Date") + 1;
var orderCol = headers[0].indexOf("Order") + 1;
if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
{
sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "GMT+8:00", "HH:mm:ss"));
}
}
}- Reply
- To post as a guest, your comment is unpublished.· 3 years agoHow do I make it have both Time and Date?
Thanks.
Scott- Reply
- To post as a guest, your comment is unpublished.Guy · 3 years agoYou can simply add hh:mm:ss after the date in line 14 of the code (copied below). Note: I had to change the UTC+8 to GMT-5 to get it to stamp the correct time for US Eastern.
sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy hh:mm:ss"));- Reply
- To post as a guest, your comment is unpublished.Marco · 2 years agoHi
Can you help me please? I need to display the Central America time, I have changed the time zone to GTM-6 but it does not work, it still shows me Pacific time, (6 hours apart)
Regards- Reply
- To post as a guest, your comment is unpublished.iqbal · 2 years agoThank you, it's works
- Reply
- To post as a guest, your comment is unpublished.skyyang · 3 years agoHi, Scott,
To make the column have both date and time, you should apply the following script code. After inserting the code, and then select the column that you want to insert the date and time, then click Format > Number > Date time to format the cells as date time formatting.
function onEdit(e)
{
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
{
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf("Date") + 1;
var orderCol = headers[0].indexOf("Order") + 1;
if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
{
sheet.getRange(rowIndex, dateCol).setValue(new Date());
}
}
}
Please try it, hope it can help you!- Reply
- To post as a guest, your comment is unpublished.Basir · 3 years agoChange the last line to sheet.getRange(rowIndex, dateCol).setValue(new Date());
This will return a date time, but you can show only the time if you want from Format -> Number -> Time- Reply
- To post as a guest, your comment is unpublished.PatKat · 3 years agoHi. Thanks for the solution. I have a shared file and I would like the time to be reflected when anyone edits the sheet. Currently, this works only when I edit the sheet. How do I do that? Thanks in advance :)
- Reply
- To post as a guest, your comment is unpublished.· 3 years agoI also would like to know how to lock that cell after the information is inserted in the previous cell.
- Reply
- To post as a guest, your comment is unpublished.jv · 2 years agoi have the same question
- Reply
- To post as a guest, your comment is unpublished.· 3 years agoHi there, thanks for the code it worked perfectly for what i needed. However I would need your help to know how to add a condition for this date to appear.
In fact, I would like to have this date only when numbers are inserted and nothing else.
Do you know what I should add to the code for that?
I am not a coder at all, only a copy paster, this is why I really need help and can't figure it out by myself.
thanks a lot already for your help
cesar- Reply
- To post as a guest, your comment is unpublished.Jeff Oxford · 4 years agoDo I need to run the function in the script editor for this to work? I keep getting this error when I try it: TypeError: Cannot read property "source" from undefined. (line 3, file "Code")
- Reply
- To post as a guest, your comment is unpublished.Nathan · 3 years agoHi there!
I had this issue too. It ended up being that I renamed my file to "order data", but my sheet name was still "Sheet1" once I renamed the sheet and not the workbook to "order data" everything worked.- Reply
- To post as a guest, your comment is unpublished.David · 4 years agocan this be modified to apply to any sheet?
- Reply