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.
  • sn

    This helps with only entering the date. What if you delete the data from “Email” it won’t delete the Date. Basically, this script only inserts the date, it doesn’t delete it also. Is there a way to make it that it does?

  • Ka Gapina

    Hi Azad Shaikh. Thank you big time for sharing this. It worked great for me. Would it be possible to stop the date/time from changing whenever the user does another entry in a previously filled cell? Thanks again for your support.

  • Ka Gapina

    Is there a way we can automatically Insert Date and Time Stamp in Excel when a data entry is made, such that it does not change every time there is a change or the workbook is saved and opened?

  • evvvvvritt

    Thanks Azad!

  • Boyd Carleson

    To have this script work on all sheets:

    Replace this line:
    var sheet = event.source.getSheetByName(‘Sheet1’);

    with this:
    var sheet = event.source.getActiveSheet();

  • Matt Becka

    How would I add the time to one column and the date to another?

  • LulamaeBroadway

    This is so cool! Got it to work. Is there a way to get it to add the functionality twice in the same sheet? I’m making a to do list and I want a Date Added & Date Completed column. I played with it, but couldn’t make it work. Any who -- thanks much.

  • geb

    FOR ANYONE WHO IS GETTING `TypeError: Cannot read property “source” from undefined. (line 9, file “Code”)`

    The way to run this as of 2/11/2017 is to click to clock symbol in the toolbar (events) then `Add a new trigger > From spreadsheet > On edit`

    I’m doing this for the first time myself, but the reason this probably works is that it’s assigning the function to be run whenever a certain event is triggered, the “event object” (information about what was happening when the sheet was edited) is passed in as an argument automatically to our onEdit function. Thus, just running it by itself, we don’t know what event is. But if we give it to google to run it whenever someone makes in edit, it get’s the information need from the user’s actions.

  • samjco

    is there a way to use your code to update a date field next to a name field where the name matches a name entered into a field of another sheet?

  • Jay Ryan Ouano

    Hello, first of all, I wan to thank you for this code. This really helped me a lot since I have a spreadsheet where I want to track the number of inputs per day at a given time. I tried this code and modified it to suit what I needed. However, one huge problem I had is whenever we go past 12PM (GMT+8), then timestamps appear in AM instead of PM. Can you help me fix this? Thanks.

    function onEdit(event)
    {
    var timezone = “GMT+8”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Geo”;
    var timeStampColName = “Date”;
    var sheet = event.source.getSheetByName(‘Data’); //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);
    }
    var timezone = “GMT+8”;
    var timestamp_format = “hh:mm”; // Timestamp Format.
    var updateColName = “Geo”;
    var timeStampColName = “Time”;
    var sheet = event.source.getSheetByName(‘Data’); //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);
    }
    }

  • Ferry

    Hey Azad, 2 quick questions:

    1) In my case, I want to monitor more than 1 column! How would I update “var updateColName” in your script in this case? I’m imagining something along the lines of

    var updateColName = OR(“Colum1”, “Column2”, “Column3”);

    2) I want a timestamp to be created not on “active” edit (user changes a cell, you currently handle with “function onEdit(event)”), but on the change of a value contained in a given cell (e.g. the result of a formula). So imagine I calculate a “discount” with a formula based on many inputs, and I want to monitor a change for that calculated cell (so I want to monitor an output, not an input). I tried to play around with “function onUpdate(event)” or “function onChange(event)” -- but did not work.

    Thanks for your feedback in advance, much appreciated!

  • Life Lines

    I’ve been trying for a long time to find a script that will allow me insert the current time in a cell using a double click. I was able to find one for excel but, I’d like to use google sheets instead! Don’t know if this script could be edited to do that, but I’m a novice so any help is appreciated, thanks!

    • HM

      If you use Data Validation to require a valid date then double cicking will give you a calander

      • Life Lines

        Yes I know, but I need a time, not a date.

  • RUBEN SENTIS GUASCH

    Hello! First of all, I want to thank you for this code.

    Please, I need the script DO NOT modify the date in case there is another date in the cellI.

    I think it should not be too complicated, but I don´t know.

    Please … Can you tell me how to get this?

    😉

  • David Manniche

    Hi, can i change this, so that it will include changes made to any column in a specific row?

  • Apple Fruit

    Hi,

    On deleting the Email I want the Time stamp to be automatically deleted too..
    Could you pls help this out???

  • Julia

    This is great, I share my sheet with around 8 people. It works great for most but then not for others. When I test it, it works fine but when a colleague uses it, it doesn’t! The colleague in question used it fine for the first few days, now it suddenly doesn’t work for her, but does for me. Any ideas?

  • jwwietsma

    Any idea how to make this work if you use IMPORTRANGE?

  • Mike Adrian De Guzman

    You can use this for multiple sheets in a spreadsheet.

    function onEdit(event)
    {
    var timezone = “GMT+8”;
    var timestamp_format = “MM/dd/yyyy HH:mm”; // Timestamp Format.
    var updateColName = “Assigned”;
    var timeStampColName = “Timestamp”;
    var sheet = event.source.getSheetByName(‘Sheet1’); //Name of the sheet where you want to run this script.

    var actRng = event.source.getActiveRange();
    var actSheet = event.source.getActiveSheet();
    var actSheetName = actSheet.getSheetName();
    var updateSheetName = sheet.getSheetName();
    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 && actSheetName == updateSheetName) { // 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);
    }
    }

    function onEdit(event)
    {
    var timezone = “GMT+8”;
    var timestamp_format = “MM/dd/yyyy HH:mm”; // Timestamp Format.
    var updateColName = “Assigned”;
    var timeStampColName = “DateTimeStamp”;
    var sheet = event.source.getSheetByName(‘Sheet2’); //Name of the sheet where you want to run this script.

    var actRng = event.source.getActiveRange();
    var actSheet = event.source.getActiveSheet();
    var actSheetName = actSheet.getSheetName();
    var updateSheetName = sheet.getSheetName();
    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 && actSheetName == updateSheetName) { // 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);
    }
    }

  • Mike Adrian De Guzman

    Thanks, Azad! Awesome script!

    For those trying to make this work on multiple sheets on a single Spreadsheet, I’ve added a few variables for the script to check that the sheet you’re editing is the same as the sheet you’ve set where you want to run the script.

    Here’s my edit on the script:

    function onEdit(event)
    {
    var timezone = “GMT+8”;
    var timestamp_format = “MM/dd/yyyy HH:mm”;
    var updateColName = “Assignee”;
    var timeStampColName = “DateTimeStamp”;
    var sheet = event.source.getSheetByName(‘Sheet1’);

    var actRng = event.source.getActiveRange();
    var actSheet = event.source.getActiveSheet();
    var actSheetName = actSheet.getSheetName();
    var updateSheetName = sheet.getSheetName();
    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 && actSheetName == updateSheetName) {
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
    }
    }

    Hit File -> Make a copy… then paste the same script changing the sheet name.

    Cheers!

  • Gina

    We have this script working but the time is off by 1 hour. I have tried editing SS settings and the script but still the time is wrong. Is there a way to fix this? Thank You