Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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:


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Daniel Méndez · 1 months ago
    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")
    • To post as a guest, your comment is unpublished.
      Fabricio Rodrigues · 17 days ago
      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);
      }

      }
      • To post as a guest, your comment is unpublished.
        Jorge · 11 days ago
        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!
        • To post as a guest, your comment is unpublished.
          Fabricio Rodrigues · 10 days ago
          Hello Jorge, no, you just need to write the number referente to column, like A = 1 , B = 2 .....
  • To post as a guest, your comment is unpublished.
    Rej · 1 months ago
    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!
  • To post as a guest, your comment is unpublished.
    ScottC · 2 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Annette · 4 months ago
    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!
  • To post as a guest, your comment is unpublished.
    James · 6 months ago
    Hi
    I got the code working, thanks!
    If I would like to include mutiple columns, how would I alter the code?
    • To post as a guest, your comment is unpublished.
      Blaze · 4 months ago
      I am trying to do the same, any luck figuring this out?