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

  • TheresaW

    Hello Azad. Thank you so much! I have a Google Form that I am supposed to enter credit card and gas card data for one at a time. I prefer to use the Google Spreadsheet attached to the form. Because I am not using the Form it does not insert a Timestamp and I need it to… Tried entering your code but it is not working to add a timestamp when I enter the vendor. Here is how I tried changing your code to make it work on my spreadsheet. What am I doing wrong?

    Oh -- I figured it out -- you were clear but I didn’t do it at first. I had to change the Sheet name to “Form Responses 1” and it worked beautifully. You are so helpful. Thanks a million.

    var timezone = “GMT-5”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Vendor”;
    var timeStampColName = “Timestamp”;
    var sheet = event.source.getSheetByName(‘Sheet1’); //Name of the sheet where you want to run this script. THIS NEEDS TO SAY “Form Responses 1” for my Google form.

  • Mauricio Lopez Moreno

    To make the script look for the name of the actual ActiveSheet you are working in, I added:

    var actualSheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

    And changed this:

    var sheet = event.source.getSheetByName(‘Sheet1’); //Name of the sheet where you want to run this script.

    to this:

    var sheet = event.source.getSheetByName(actualSheetName); //Name of the sheet where you want to run this script.

    This way I didn’t have to create a separated script for each sheet.

  • David Manniche

    Hi, tried to get it to work, but constantly get the following error: TypeError: Cannot read property “source” from undefined. (line 7, file “Code”)Dismiss

    I have double checked and changed the sheet name in the code

  • Lance Whitehead

    Is this code still supported by goggle docs? I’ve tried several ways and it’s not doing it for me. Maybe I’m missing something. My original sheet had 48 columns. I made a fake one with just 4 columns as shown in the example, thinking maybe I can’t have too many columns. Still nothing. Do I need to set a trigger? I’ve copied the formula and changed everything that the video has suggested.

  • Alfonso Carmona

    Quick question,
    I have been using this script for several years without any issues. I realized today that the script works great in my Google account, but as soon as I share the document with other people the time stamp disappear. In other words, it works with my account, but not with share accounts. Any suggestions?

  • Woody Daniels

    Hi, Great Script,
    IS there a way to make the time stamp only work when data is entered into Multiple cells?
    For example i have three cells called Article, QTY and Source and i only want the time stamp tp complete when all three cells have data in them

  • Suspect

    Thank you for the script! How to delete a “Time Stamp” if I’m deleting a value? Or how not to update “Time Stamp” if I’m deleting a value?