Note: The other languages of the website are Google-translated. Back to English

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:

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(), "UTC+8", "MM-dd-yyyy")); 
    }
  }
}

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, charts and 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.
kte tab 201905

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!
officetab bottom
Comments (62)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
can this be modified to apply to any sheet?
This comment was minimized by the moderator on the site
Do 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")
This comment was minimized by the moderator on the site
Hi 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.
This comment was minimized by the moderator on the site
Hi 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
This comment was minimized by the moderator on the site
I also would like to know how to lock that cell after the information is inserted in the previous cell.
This comment was minimized by the moderator on the site
i have the same question
This comment was minimized by the moderator on the site
Hi. 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 :)
This comment was minimized by the moderator on the site
I love this script. How do I only get this to Print Time instead of DATE? That is what I need
This comment was minimized by the moderator on the site
Change 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
This comment was minimized by the moderator on the site
Hello,

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"));
}
}
}
This comment was minimized by the moderator on the site
How do I make it have both Time and Date?


Thanks.


Scott
This comment was minimized by the moderator on the site
Hi, 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!
This comment was minimized by the moderator on the site
You 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"));
This comment was minimized by the moderator on the site
Thank you, it's works
This comment was minimized by the moderator on the site
Hi

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
This comment was minimized by the moderator on the site
HI 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
This comment was minimized by the moderator on the site
I 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
This comment was minimized by the moderator on the site
Yup, same. Admin, pliss to help.
This comment was minimized by the moderator on the site
Use function var sheet = SpreadsheetApp.getActiveSheet();
instead var sheet = e.source.getActiveSheet(); in Line 3.
It solved my problem.
I am using it like this
f
<div data-tag="code">{
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
}
}
}
This comment was minimized by the moderator on the site
Thanks 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?
This comment was minimized by the moderator on the site
hello, I have the same problem, did you solve it?
This comment was minimized by the moderator on the site
hello I have a similar problem, did you solve it?
This comment was minimized by the moderator on the site
Hi I face an error TypeError: Cannot read property "source" from undefined. (line 3, file "Code")
Able to help on this
This comment was minimized by the moderator on the site
Same here. Please help.
This comment was minimized by the moderator on the site
same issue here
This comment was minimized by the moderator on the site
Hi
I got the code working, thanks!
If I would like to include mutiple columns, how would I alter the code?
This comment was minimized by the moderator on the site
I am trying to do the same, any luck figuring this out?
This comment was minimized by the moderator on the site
Hey! 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!
This comment was minimized by the moderator on the site
How 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.
This comment was minimized by the moderator on the site
Good 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!
This comment was minimized by the moderator on the site
Hola, 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")
This comment was minimized by the moderator on the site
I 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);
}

}
This comment was minimized by the moderator on the site
Hi 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!
This comment was minimized by the moderator on the site
Hello Jorge, no, you just need to write the number referente to column, like A = 1 , B = 2 .....
This comment was minimized by the moderator on the site
Awesome! Thanks
This comment was minimized by the moderator on the site
Genial, excelente, es lo que estaba buscando, muchas gracias, saludos
This comment was minimized by the moderator on the site
Existe una manera de hacer esto pero que la fecha se introduzca sólo si se escribe una palabra específica?
This comment was minimized by the moderator on the site
How 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?
This comment was minimized by the moderator on the site
can 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?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations