How to Auto Insert Timestamp in Google Sheet

How to Auto Insert Timestamp in Google SheetGoogle sheet is one of my favorite Google App and I use it . Today I came across a video on “How to add a Timestamp in Google Docs“. It shows how to install a script which will add a timestamp automatically to last column. While this is a very good script, but I wanted to make it better.

So I did some tweaking and now you can specify the column where timestamp should  get inserted. You can specify the column which trigger the timestamp script. You can specify the sheet where the script should run.

Below is the code:

/*Updated and maintain by internetgeeks.org*/

function onEdit(event)
{ 
  var timezone = "GMT-5";
  var timestamp_format = "MM-dd-yyyy"; // Timestamp Format. 
  var updateColName = "Email";
  var timeStampColName = "Date Sent";
  var sheet = event.source.getSheetByName('Sheet1'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

Watch below video on how to use this code:

Steps to follow:

  • Go to Tools -> Script editor
  • Delete code if there is any.
  • Copy above mentioned code and paste it into the script editor.
  • Change the code as instructed in the video.
  • Click save.
  • Give the project a name (e.g. “Last Updated”)
  • Done.

I hope that you find this tutorial useful. Please share this with your friends and if you have any queries then feel free to shoot them in the comment section.

The following two tabs change content below.

Azad Shaikh

Azad Shaikh is an internet geek at heart. Azad like to write about anything related to computers, internet, hacking, business and marketing. He is a computer engineering graduate and certified ethical hacker.
  • alkashaf qatar

    thank you, but if i want to make the trigger not by editing in the cell but by copy data to it , or if it is non blank, how i can do this ?

    • Marcos Menendez

      I have a similar problem, but when copying multiple lines at the same time. It just timestamps the first of them

  • Saloni

    Hello Azad,

    I am facing same problem multiple time. Please help

  • alkashaf qatar

    is there a way for user stamp as well to determine which account who makes the edit ?

  • Adnan chowdhury

    How to Auto Insert Timestamp in Google Multiple Sheet?
    pls help me
    var sheet = event.source.getSheetByName(‘Sheet1’; ‘Sheet2’); //Name of the sheet where you want to run this script.

  • [AIR] Khristian Jalandoni

    Hi,

    I used the script on one sheet and worked very well. I was hoping to use it on another sheet on the same file. I tried launching the script editor again, and it landed on the script for the previous sheet. How do I put the script to work on both sheet 1 and sheet 2?

    Thanks in advance!

  • Hollie McArthur

    I am having reference errors in lines 9+. How do I solve this. Was I supposed to edit that section as well?

  • Lukasz Gorajek

    Thank you -- love the script!

    Is there any way we can adjust it so that the timestamp automatically recognizes when it is daylight savings time and when it isn’t? This detail would really further polish out the function…

  • Craig Fish

    Hey im late to this but this is very useful. I’d like to know how to add multiple scripts to my doc so that a number of cells can then update the main cell for the timestamp. So effectively overwriting my timestamp cell with updated timestamp upon editing various cells

    Thanks