How to Auto Insert Timestamp in Google Sheet

Google 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.

[contentblock id=20 img=adsense.png]

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.

Azad Shaikh

Azad Shaikh

Azad Shaikh is an internet geek at heart. From reviews about computer components to playing X-box one games, Azad takes pleasure in writing about anything and everything about computers. He is a computer engineering graduate and certified ethical hacker. In his leisure time, he reads and explores the internet.

292 Comments
Show all Most Helpful Highest Rating Lowest Rating Add your review
  1. awesome video!! Thanks!
    but what would you need to change if you would want to get the “last Updated” time only when a single column is edited? in this example column A:A.

    thanks in advance

    • Hello,
      thanks for your script.

      I followed your procedure and updated the variables.
      but no reaction in the sheet.

      in debug mode, I get the message:
      TypeError: Unable to read source property from undefined. (line 12, “Code” file) Skip

      it is the line that specifies the name of the sheet.
      it exists well yet.

      Could you help me ?

      thank you
      Yves.

    • I did not thought of that. It would required some more code to achieve this. I will add this in my to do list. Thanks.

      • Hi
        Did you find a solution here?

        • You are a genius man, thank you

        • How would I go about adding additional columns and add additional timestamps? In other words, I have more than i column event that I’d like to timestamp.

          Thanks!

        • It would requires more custom code. You will need to create one more timestamp variable and then read it header and another if condition with timestamp insert code.

          If you understand my code then it should not be hard.

        • How do I add more cells in the code? I don’t know code. But I sure with dome help I’ll get it

          Thanks!!

        • Here is the code. Change Timestamp2 to your second timestamp column name.

          function onEdit(event)

          {

          var timezone = “GMT-5”;

          var timestamp_format = “MM-dd-yyyy”;

          var updateColName = “Email”;

          var timeStampColName = “Date Sent”

          var timeStampColName2 = “Timestamp2” // variable for second timestamp

          var sheet = event.source.getSheetByName(‘Sheet1’);

          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 dateCol2 = headers[0].indexOf(timeStampColName2); // get header for second timestamp.

          var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;

          if (dateCol > -1 && index > 1 && editColumn == updateCol) {

          var cell = sheet.getRange(index, dateCol + 1);

          var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

          cell.setValue(date);

          }

          // extra code for second timestamp

          if (dateCol2 > -1 && index > 1) {

          var cell = sheet.getRange(index, dateCol2 + 1);

          var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

          cell.setValue(date);

          }

          }

        • Thank you, I’ll give it a try!

        • This is not the most efficient code but will do the trick.

  2. It works great! Thank you!

  3. in my case i only want the date stamp only when i insert data in the “NAME” column and nowhere else, it is doing it where ever i enter data

  4. WHEN I enter in column C or column named “first name” id like to add a date stamp to column A or column named Date …. why is this so difficult to find a formula for this??

  5. Whenever I try this code, I get this error: TypeError: Cannot read property “source” from undefined. (line 9, file “Code”).

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

    Any ideas on why that is or how to get past this? When I’ve tried to take out this line, then I get stopped by this error: var actRng = event.source.getActiveRange();

    It doesn’t seem happy with any of these lines of code, and I can’t figure out how to fix it.

    Thanks.

  6. This works really good for the date stamp. How about a time stamp? How can I add the time of day in the column next to the date, with out using the Ctrl Shift ; shortcut? Will the work together on the same sheet?

  7. Hi Azad, I tried to use the updated script but it’s not working, please help. thanks

  8. Unfortunately this does not work for what I need, I am inserting rows which doesn’t fir this script’s trigger. Is there a way to modify this to fire when a new ROW is inserted?

  9. Is there any way to have all the timestamp values updated when you copy a buch of emails at one time. Currently only the timestamp for the first email gets updated

  10. Hi
    Many thanks for the code which has fixed part 1 of my problem. However it has caused another one. I have copied the code to apply to 3 different tabs so that I can track a race (start, finished and retired). I successfully enter the number and timestamp the start but when I enter the same number into the finish or retired tab, the start time is updated with the finish time. How can I stop this happening please? I am happy to share the googledoc if it helps. Thanks

  11. I keep getting an error on these lines saying there is a problem with source

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

  12. Hey, this is a really nifty script.

    2 quick things:

    1) I have a Spreadsheet with a few sheets in it. All sheets have the same headers for the most part.

    I would like this to work on all the sheets independently so that when I update the “Email” column on sheet 1 it should update the “Date Sent” column on sheet 1 and when I update the “Email” column on sheet 2 it should update the “Date Sent” column on sheet 2.

    2) Right now I have it running on only one of the sheets until a fix for my first request. Even so, when I update the “email” column in a different sheet the date stamp appears on the sheet the script is set to. Any idea how to stop that as well?

    Thanks!!

  13. I have a sheet with multiple columns, and I’d like the Timestamp in column A to update whenever a value in columns B through F were edited, is that possible with some small adjustment to this script?

  14. hi Mr. Azad how can i reach you ?

  15. How do I add to the timestamp the actual time it was entered?

  16. I am having the same issue as eric and richard

    Whenever I try this code, I get this error: TypeError: Cannot read property “source” from undefined. (line 9, file “Code”).

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

    I have tried everything they tried as well…updating the sheet name addign semi colon etc..

    any ideas why this is happening//how to fix??

    also I do want the hours and minutes in my stamp ..can you check that I added the “hh-mm” properly?

    Much appreciated

  17. Thank you! Exactly what I was looking for!

  18. Is is possible for the script to enter the date only if a specific value is filled in?
    Thanks!

  19. I am getting an error similar to the one listed below. It says, “TypeError: Cannot read property “source” from undefined. (line 7, file “Date Updated”)”. I checked for the semicolon and it’s there. Also, when I share this spreadsheet, anyone who updates the selected column, the date stamp will not update. The date stamp appears only when I update the specific column.

    Any help on this would be greatly appreciated. Thanks.

    • I am getting an error similar to the one listed below. It says, “TypeError: Cannot read property “source” from undefined. (line 7, file “Date Updated”)”. I checked for the semicolon and it’s there.

      make some changes in spreadsheet then go back and run the code. It should works.

  20. If I wanted to set a range of columns (rather than just “email”) that, if edited, would change the timestamp in the “datesent” column, how would I go about doing that? Can I assign multiple columns to “updatecolname”? I don’t think I can, but I have almost no programing experience. Do I need to create a separate variable for each column that I would like to look at to see if I need a time stamp? That does not seem very efficient.

    Thank you.

  21. One more question: I have the code working, but I now have 5 (A to E) columns before the “last update” column and one column after. Now, if I update any of the first 5 columns, the “last update” column does not get a date stamp, but if I modify the column (column G) after the “last update” column (which is column f), I get the time stamp in column F. How do I make it so that it timestamps column F if there is a change to any column other than column F?

    Thank you

    • You’ll have to update the code identifying the additional column(s). For the example I added the other two columns below.

      function onEdit(event)
      {
      var timezone = “GMT-5”;
      var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
      var updateColName = “Email”;
      var updateColName1 = “Coupon”; // define your 2nd column
      var updateColName2 = “Some Col”; // define your 3rd column
      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);
      }

      //add code to update time/date stamp if the additionally defined columns have changes
      var dateCol = headers[0].indexOf(timeStampColName);
      var updateCol = headers[0].indexOf(updateColName1); 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 dateCol = headers[0].indexOf(timeStampColName);
      var updateCol = headers[0].indexOf(updateColName2); 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);
      }
      }

      • columns name starts at G17, H17 and I17 and above columns are having company name as heading.
        how write code please

      • This works great. I have a 5 col sheet. One person does A and another does C&D. I want A to trigger a timestamp in B and D to trigger a time stamp in E.
        I ran the code list above for A to trigger B and it worked fine. my feeble attempt to copy it and change the column definitions, with A to B above and D to E below, killed the A to B but d to E works.
        How might I have two different time stamps on one sheet?
        “Name” “Date Entered” “Rcvd” “Completed” “Date Completed”

      • Thank you very much. That worked perfectly.,

  22. I tried to apply this script to multiple sheets, but if I copy the script it only runs on one sheet. Is there a way to run it on multiple sheets and how does it work? Thanks

  23. Hello Azad, what if I have spreadsheet with more than one sheet and I would like to apply your script to the entire spreadsheet and not just “sheet1” is there a way to do that?

  24. Is there anyway to get this to recognise when a cell containing the result of a formula changes? It gives a time stamp when I manually type something but not for the row containing a formula.

  25. Hi the code works great. I was wondering how i could change it so that it works across rows instead. For example if i enter something into Cell B3 then i get timestamp in Cell B1. Then the next day enter into C3 i would then get timestamp into C1. Does this make sense? If so would be great if you could help. Thanks

  26. HEY BY ANY CHANCE DOES ANYONE KNOW HOW TO TIME SAND DATE STAMP ?
    IS THERE A CODE OR A WAY TO MODIFY THE ABOVE BECAUSE IN THE CODE ABOVE I ONLY RECEIVE THE DATE STAMP NOT TIME

  27. Could you advise on how to modify this to insert the time/date stamp only when a certain letter is placed in a certain column? For example, if S in column Inv then insert date/stamp into column Date. Otherwise leave Date column as is. I used conditional formatting to insert TODAY but that changes the Date column every single day. I want to insert today’s date only once when I enter S into the Inv column. Thanks!

    • Hi Devon, Did u find a solution for your question. Even I am trying to update timestamp only when the specific column is filled by specific letter.

  28. Hi Azad and all, thanks for this. It works fine when I edit data from keyboard into the ‘updateColName’ column. However, in my spreadsheet, I have to copy and paste data from one tab to another tab. I want the timestamp to work when I paste data into a ‘History’ tab.
    The timestamp does not seem to be working when i paste data into the column. I am pasting six columns of data into my history tab, and I have an ‘updateColName’ defined as one column. Can anyone give me some advice? Much appreciated.

  29. This worked perfectly for me, I was wondering if I could apply this code to multiple sheets at once? Sheet names: 1, 2, 3, 4 etc.

  30. İf ı were use this for different two column’s, which one have updated then stump will add. How to fix solution ? Thx.

  31. Hi Azad, thanks for code to timestamp. I need to put a time as am/pm, is this possible and how do I amend code?

  32. Thanks for the code!! Worked perfectly

  33. I am trying to record 2 times, start and finish, based on a pick list in 2 other columns. While I can get it to update the start time stamp, I cannot get it to stamp the finish time. The spreadsheet is here:

    https://docs.google.com/spreadsheets/d/1BxLti03GYs6HZPH5WOLwQ3z9z1CaHin9vmx-d3V_UfA/edit?usp=sharing

    If anybody can see what I’m doing wrong, please feel free to point it out. I have done a fair amount of scripting, but I’m brand new to Google Script.

    -Jim

  34. Reply
    nuclearlaunchdetected April 2, 2015 at 8:55 am

    Hello, can you provide the code for the autofill of a particular text rather than the date? That would be much appreciated.

  35. Missing } after function body. (line 1, file “Code”)

  36. function onEdit(event) { var timezone = “UTC-5”; var timestamp_format = “MM-dd-yyyy hh:mm:ss”; // Timestamp Format. var updateColName = “Enter the Survey ID”; var timeStampColName = “Timestamp”; var sheet = event.source.getSheetByName(‘Form Responses 1’); //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); } }

    • This works great. I have a 5 col sheet. One person does A and another does C&D. I want A to trigger a timestamp in B and D to trigger a time stamp in E.
      I ran the code list above for A to trigger B and it worked fine. my feeble attempt to copy it and repeat it with the second version using changed column definitions, with A to B in the first instance of the code and D to E in the second instance, killed the A to B but D to E works.
      How might I have two different time stamps on one sheet? Here are my column names as an example.
      “Name” “Date Entered” “Rcvd” “Completed” “Date Completed”

  37. Everything worked perfectly I was just wondering on how to add an exact time stamp with the hour, minute, second.

    • Per entry below:

      Change the code at line 6 to below:

      var timestamp_format = “yyyy-MM-dd ‘Time:’ hh:mm:ss”;

      I used

      var timestamp_format = “yyyy-MM-dd ‘Time:’ hh:mm”;

    • Change the code at line 6 to below:

      var timestamp_format = “yyyy-MM-dd ‘Time:’ hh:mm a”;

  38. Still get the error code even with updated code. Any ideas?

    TypeError: Cannot read property “source” from undefined. (line 6, file “Code

  39. Its the

    var sheet = event.source.getSheetByName(‘Sheet1’)

  40. Great script !!!!! Thanks so much !!! I have been working on this for 2 days – you are my new best friend. (o:

  41. Hi Azad,

    Great Script, thanks for sharing.

    I was wondering if it is possible to make this code dependent on Column A, B, and C being populated and the timestamp generated based on the time B was populated?

  42. Hello, thanks a lot for your code! I’m trying to use your idea to be applied in a sheet I use to load timing of supporting to clients. So, I need to auto-timestamp 2 columns, one when working beings and the other when working stops. Would you please give me some tips about how to do it?
    Thank you so much!
    Jorge.

  43. I keep getting the following error when I try to add the script, please advise- thanks- Missing ; before statement. (line 3, file “Timestamp”)

  44. Hello Azad, thank you for the great code. It’s amazing. How can I apply this timestamp to a cell which is a reference? For example what if cells in your Email column were “=H3” or whatever. Is it possible by a modification of this script or it needs to be completely different? Thank you very much!

    • did you ever find out? This is the same thing I am having trouble with. Formulas don’t change and will not trigger the script. Value change but the formulas don’t.

    • …because I have the IMPORTRANGE function in my google sheet because of getting data from another google spreadsheets

  45. I’m at GMT + 5:30. What format do I write that in the code? URGENT

  46. Azad,

    This is a very useful script; thank you.

    I’m trying this on a shared Google Sheet that I’ve created and shared with a colleague. It works fine for me, but it’s not working for my colleague.

    The colleague gets an error which reports that the system cannot read the property “source” from an undefined item in this line:

    var sheet = event.source.getSheetByName(‘customers’);

    Do you have any ideas or suggestions?

    Thanks again for a great little script.

    Gary

  47. Hello Admin,
    What if I have 4 Sheets and Each sheet should Individually update the time accordingly when changes done in particular column in Same sheet.

  48. Thank you! How do you change time zones? My time stamps are only reflecting GMT time. I cannot get the time stamp to reflect any timezone other than GMT, even with: var timezone = “GMT-5”;

  49. I’ve used the base of this code in a few sheets, and it’s wonderful. However, I hit a snag in a sheet where I need the date based on a column that is importing from another document. onEdit() won’t work here since the cell isn’t being edited directly, and there is no active event to speak of in this sheet. Do you have a solution for that?

  50. Thanks for the great tip. What params control the same text updation. I wanna update a same cell over time, lets say A1 and want to record the timestamp in B1, whenever A1 is updated. What changes I need to procure. Thanks

  51. Hi Azad, I just followed the script & got this error: TypeError: Cannot read property “source” from undefined. (line 7, file “”) , you can see the code I’ve pasted in attached image.

  52. SOLUTION for: Cannot read property “source” from undefined. (line 9, file “Code”).Use:

    Use: SpreadsheetApp

    Insted of: event.source

    var sheet = SpreadsheetApp.getActiveSheet(); //Name of the sheet where you want to run this script.

  53. This is great code but have found an issue after modifying the code to use time instead of date for my project. The time is not allowing for daylight saving.
    could you show the changes to allow the correct time to show in the cell if time is used.
    I think this would be useful for anyone who need the time to be added to the date aswell.

  54. Hello Azad, I am having difficulty getting this work on my spreadsheet. Hope you could help me with this. I have a spreadsheet which has the responses of a call quality monitoring form. I need to share this with the associates (50+) for them to check the comments, score and acknowledge the same on this spreadsheet. What i need is to get a timestamp and the username/email of the associate who updated the status from blank to accepted/dispute. Hope you understood what I mean

  55. How do you change the timezone to Central standard time instead of military timeWhen I do this it types in the wrong time

  56. Hi Azad. if I want the timestamp to update on any column that has changes. What should be the code to write?

  57. Reply
    Poom Laupattarakasem October 2, 2015 at 8:17 pm

    thank you 🙂

  58. Thank you so much for the code and youtube tutorial. I was able to get the function to work for one column, called “1st Payment”, but I need to have a second column for “Payment 2 ” and a date entered. When I changed the code for the “Payment 2” I lost the function on the “Payment 1” column. Any suggestions? Thanks

  59. Amazing script! Thanks so much Azad, it has really helped us.

  60. Azad,

    I’ve tried all versions of the code and changing the name of the sheet as well and I keep getting the same error, it cannot identify the sheet I want the code applied to. I’ve switched the name to sheet1 and it still doesn’t work (the error is on line 9). Can you please help me?
    I’ve also read all the responses…. 🙁

    Thanks again!

  61. Hello Azad, what changes would be required in order to remove the “time stamp” if the “email” were deleted?

  62. It works, you don’t need to “Run” the script. Just make sure the columns names match yours and save it in the script editor. Then go back to your spreadsheet and make your updates. It took a few minutes for the spreadsheet to save and refresh with the dates.

  63. Reply
    Riccardo “Ricc” Carlesso October 22, 2015 at 2:00 pm

    Lovely video, lovely code! I’d love to have something like a LIST of columnNames where you update, like: updateColNameArray = [ ‘name’,’surname’,’email’];
    How would the code change?

  64. This is fantastic, thank you so much for publishing this. I am trying to timestamp a sales funnel, so create multiple date stamp fields for when one value changes to another value in one field. I.e. I would like to date stamp one field when the status is set to prospecting, date stamp another field when status is set to appointment, etc. Is it possible to change this code so that each field date stamps based on the value inputted?

    Thank you!

    • Use: SpreadsheetApp

      Insted of: event.source

      var sheet = SpreadsheetApp.getActiveSheet(); //Name of the sheet where you want to run this script.

      and

      var actRng = SpreadsheetApp.getActiveRange();
      Insted of: event.source.getActiveRange();

  65. what if i want time along with date?

  66. Thank you for the tutorial, this code is great. I am currently having an issue where my Last Updated column is being updated with the current date upon the edit of ANY sheet, not just the one specified in the code. Is anyone else having this problem?

    I only want the date updated when the specified columns on the the specified sheet are edited; but, it is updating the date when ANY column from ANY sheet is updated. Please advise if you know what is wrong. Code listed below. One caveat is that I created 12 projects in the script editor, all with the same base code, but updated the column names for each in order to have this apply to multiple columns.

    function onEdit(event)
    {
    var timezone = “GMT-6”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Boxx Ready”;
    var timeStampColName = “Last Updated”;
    var sheet = event.source.getSheetByName(‘Stage’); //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);
    }
    }

  67. Hi Azad – Great script
    I was wondering what I need to change to make the code only happen once. I want to have timestamp put in but if someone changes the column information to not update the time. Any help appreciated as I am not a coder. This works great but do not want update each time the field is changed, need original time.
    Thanks

    • Hi UtahValley IT i took my time to change the script. This script will only insert a timestamp when the row is first edited. After that the timestamp will stay the same:

      function onEdit(event)
      {
      var timezone = “GMT+1”;
      var timestamp_format = “dd-MM-yyyy”; // Timestamp Format.
      var updateColName = “Zoekopdracht”; //Name of column that you wish to track
      var timeStampColName = “Creation date”; //Name of timestamp column
      var sheet = event.source.getSheetByName(‘My sheet’); //Name of the sheet

      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;

      var cell = sheet.getRange(index, dateCol + 1);

      if (dateCol > -1 && index > 1 && editColumn == updateCol && cell.getValue() == “”) { // only timestamp if ‘Last Updated’ header exists, but not in the header row itself!
      var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
      cell.setValue(date);
      }
      }

  68. Hi. Please refer to the photo attached. How can I accomplish this? I know nothing about scripting.

  69. re-post
    ======
    Sorry to re-post. I can’t see my previous post.

    I would to do something like in the picture I attahed.

  70. Azad love this concept and am trying to use the script. I am reading below and I am running into the errors that everyone saw as well around getting the run function to work in the script editor. I do not seem to get my spreadsheet to work as others even though they recieved the error. Any guidance on what I can look at?

    function onEdit(event)

    {

    var timezone = “UTC-06:00”;

    var timestamp_format = “MM-dd-yyyy”;

    var updateColName = “Requestor”;

    var timeStampColName = “Date Submitted”;

    var sheet = event.source.getSheetByName(‘Main Sheet’);

    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); } }

  71. Like Steven Weldler, I’d like to know how to run the script on multiple sheets. I got it to work by copying var sheet = event.source.getSheetByName(‘Sheet1’) multiple times and changing the sheet name for each line. Then I copied the rest of the code and changed every instance of Sheet1 to the corresponding sheet name.

    Although it lagged a little, it worked! But I found that it would automatically overwrite previous entries as shown in the attached image. The dates should all be in December, but when I looked back, it changed a lot of lines randomly to 1/7/2016.

    Is there a better way to run it for multiple sheets? Also, is there a way to stop it from randomly changing entries?

    Thank you,

    Adam

  72. In case of want to 5 sheets run this script and update sheet by sheet ,how to ?

  73. If you just want to add a quick timestamp, you can also just do =round(now(),2)

  74. Hello,

    I am have successfully used this code for one column. However, I am a teacher creating a student sign in and sign out sheet. When the students type their name in column C, the autotimestamp works in column A as assigned. However, when the students sign in back in by typing in column G, the autotimestamp will not appear in Column B as directed. Can you help me?

    Thanks for your help in advance. I took this in college, but that was 20 years ago and my memory is failing me.

  75. Hi Azad, great code, thank you very much! But will it be possible for me to use this code for multiple sheets?

  76. Awesome! Clean Clear. works a treat, Thank you

  77. dear azad…

    you may be able to help me out. i am having a real hard time finding information on hotkeys and time stamps in google sheets.

    i have been using autohotkey (AHK) for a while now. and i love it. i run a simple little script to keep timing in a dogsled race via google sheets;
    ___________________

    ^Space::

    FormatTime, CurrentDateTime,, hh:mm:ss tt{Enter}{Down}

    SendInput %CurrentDateInput%

    return

    ___________________

    because everything runs of chrome, we decided to “upgrade” our very old laptops to chromebooks.

    the closest i came to find something like AHK is a google extension called Auto Text Expander (ATE) that runs on JSON. the code used is;

    { “XXX”: “%d(hh:mm:ss A)” } – typing XXX will give me the time stamp as an example.

    i can get a time stamp but can’t find a way to “enter” and to “down” in google sheets – meaning that as i enter the time stamp via “control + space bar” i want the cursor automatically go down to the next cell in the spreadsheet. i also can’t find a way to use the control key as part of my keystroke combination.

    question;

    is there a way to create a script within google sheets? your video comes really close to what i am looking for…

    thanks, kiko

  78. Thank you for sharing this! It works great for me with dates, however, I’m having an issue when inserting just the time of day into a column. I’m getting the correct time (GMT-8) but the wrong AM/PM. It’s always listed as AM and never PM. I appreciate any help.

    var timezone = “GMT-8”;
    var timestamp_format = “hh:mm”; // Timestamp Format.

  79. Hi,

    Can you help me with counting days from latest triggered timestamp till today?

    When I hit “ok” to mainsheet the timestamp will show and in the other sheet I have function TODAY(). I need time between these days. It works with DATEDIF, but i dont know how to get it calculated automaticly only from the latest timestamp. I would like to do a dashboard to show delay.

    Thanks for help!

  80. Sir I want to run this program for multiple sheets. please help me out from this problem

    • Hi you can achieve this by creating the following function:

      function activeSheetName() {
      return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
      }

      And then you search for the line:

      var sheet = event.source.getSheetByName(‘Sheet1’);

      And replace it with:

      var sheet = event.source.getSheetByName(activeSheetName());

      • function onEdit(event)
        function activeSheetName() {
        return
        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
        }
        {
        var timezone = “GMT-5”;
        var timestamp_format = “MM/dd/yyyy HH:mm:ss”; // Timestamp Format.
        var updateColName = “Email”;
        var timeStampColName = “Date Sent”;
        var sheet = event.source.getSheetByName(activeSheetName()); //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(4, 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);
        }
        }

        I add the function on top and replace sheet1 to activeSheet()

        but it do not work, any idea what might be wrong?

  81. CODE FIX:

    For some reason, when you copy the code directly from the website, it’s not registering page breaks when you try to paste it. I copied the code from the this page’s HTML directly and then put it in a JS Beautifier. Here you go:

    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);
    }
    }

  82. Hello Azad, script works great if Im adding data manually, but I want to timestamp data that Im pasting in a row at a time, and the timestamp doesnt work like this. What should I change in the script?

  83. I am not sure why it is working but it is I hit the run and it is showing and error for sheet name. “TypeError: Cannot read property “source” from undefined. (line 7, file “Code”” I go to my sheet and try it and it works. At some point will this give me a problem?

  84. I wached your vid. ingenious! I’m wondering if you could help me with this: I have a sheet where I update due dates in several columns. It would be awesome when the actual date is added just by clicking into the cell (of course just on predefined columns). Is that possible with a code?

  85. Hi Azad. if I want the timestamp to update on any column that has changes. What should be the code to write?

  86. Hello Azad,
    How to update timestamp on muliple columns edit?
    say for example: var updateColName = “Email”.”Name”;
    the above syntax is not working. Please help

  87. Hi, when I copy – paste the script above, do I need to delete
    // Timestamp Format.
    //Name of the sheet where you want to run this script.
    // only timestamp if ‘Last Updated’ header exists, but not in the header row itself!
    Sorry, I do not have any knowledge with scripting 🙁

  88. Hi. I’m very new to scripting. I’ve successfully used the timestamp script but have been unable to adapt it to also copy/stamp a cell ‘A1’ from sheet2 into each row of column A on Sheet1, the timestamp is column B, and a column C is a QR code read which causes the row to be edited. Nothing I’ve tried seems to work. Thanks for any help.

  89. Hi I copy pasted the code but its not working! Can anyone please help

    • Did you watch the video? You need to adjust the header of the columns so it detects the right column and post on the right column

    • Please help

      • CODE I HAVE ADDED::

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

  90. Will this work when there are multiple users? I have an attendance sheet, and when parents sign out with my 4 teachers who are using this, I would like the auto time stamp. It’s working for me as of now. Also, I have 4 sheets on the document–one for each teacher. Can I add the other three sheet names into the code? Do I do so with commas? Thanks!!!

  91. Hi Azad, was looking for something like this for ages :-). Working excellent.

    Question:

    I have a “Date/Time Started” Column and when I fill out the “Task” Column – obviously it gives me the start date and time… working excellent.
    Now I want to add to the current coding you give… “Date/Time End” and when I fill out the “Task Status” Column, the end date and time should appear in the “Date/Time End” Column… is it possible to help me?

    NS. I am not a coder at all 🙂

    Thanks,

    Pieter

  92. How can I had multiple timestamps to one sheet?

  93. You are a true gentleman. Thank you for sharing!

  94. Reply
    The Arc Montgomery County Fron September 2, 2016 at 1:28 am

    Help I have 2 places that i need date stamped on the same sheet after updating 2 cells is this possible ?

  95. how to add multiple time stamps in same google sheets

  96. Hi!

    I hope you can help 🙂
    I tried this code for one column, which worked fine. But now I want to use it twice. E.g. Pick update connected to name and Handed in by connected to hand in date. I have the feeling only one of the scripts is working at the same time. Is this true? How do I fix it. I tried to ways; Paste new scipt under the previous and creating a new script.

    Thanks!

  97. Thanks! it still works! 🙂

  98. Tks!!! Worked fine! And mine I installed with hours and minutes. Tks again!!

  99. This timestamp code is working great for me but I would like to take it to the next level.
    Currently, I use this as a class sign in sheet and because I have about 200 students in a single class I use a barcode scanner to quickly check in all of the students and get my timestamp so I can tell who is late. So when their “Student ID” populates in the Column A, I get a “Timestamp” in Column B. However, the barcode on the student ID badge only have the Student’s ID number, there is no name or other data contained on in the barcode itself, so I have to manually lookup each student by the Student ID number and copy and past their info onto the sheet, to see who has attended, was late, did not attend.

    What I would like is a way to have Column C,D,and E (on Sheet 1) auto populate from a full student roster, that would be maintained on Sheet 2. Sheet 2 would have Column headers of “Student ID”, “First Name”, “Middle”, “Last Name” and would have the complete roster for that class. I would like for Sheet 1 to be able to automatically identify the Student ID that is barcode reader scanned into Column A, Timestamp Column B, and auto populate the rest of the row Column C,D,and E with “First Name”, “Middle”, “Last Name”.

    Can you help? Thank you.

    • You could use Vlookup and have the table on a separate sheet. The administration should be able to I’ve you an export you can copy and paste that has the students name and number.

  100. Thank you very much… Great, clear video instructions…

    This code works on my sheet, but only if I update the “updateColName” manually. Unfortunately, the data that appears in the field is the result of a VLOOKUP formula (=VLOOKUP(A4,PLAQUEHOLDER,2,0).

    All data in the workbook is received from a google form.

    It looks like the script does not recognize that the field changed because the formula does not change. Only the value returned from the VLOOKUP chart.

    Is there a way to alter this code to recognize that the value changed and not look at the formula?

  101. Hi, This is amazing thanks. Can you advise me how to use this script for more than one colum in the same sheet?

  102. Thanks for the video Azad. I have a similar question as another commenter: I’d love to have something like a LIST of columnNames where you update, like: updateColName = [ ‘name’,’surname’,’email’];

    How would the code need to be updated? Thank you in advance.

  103. IT DOESN’T WORK!

  104. it worked! thanks so much for this helpful tutorial!!

  105. Hi ! Thanks a lot for your timestamp code ! Works well !
    One question : could you give us the code to remove the timestamp and let’s the cell empty if we erase the “email” cell ?
    Thanks a lot !!! 😉

  106. Hello ! thanks a lot for your code, It works well !
    One question : could you give us the code to remove the timestamp and get the cell empty, if the “email” cell is erasing and empty too ? Thanks a lot for your help and your answer !!

  107. It works soooo good <3 Thank you 🙂

  108. Great code Azad! I’m running into one issue. Whenever I input my correct time zone (UTC-06:00) it does not reflect the correct time on my google sheet. Can you help me out please?

  109. Hi there:

    get this error msg: Cannot find method getActiveSheet(string). (line 7, file “Code”)

    script is as per below:

    function onEdit(event)
    {
    var timezone = “GMT-1”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Date detected”;
    var timeStampColName = “Date added to report”;
    var sheet = SpreadsheetApp.getActiveSheet(‘Transactions’);

    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);
    }
    }

    Any ideas?

  110. It looks great, but doesn’t work as you can see in the picture.

  111. Hello Azad,
    How can I run this script on two sheets? Currently I have two identical scripts running but one was for a sheet called “CapSat” and the other for “csm15”. The problem is that only one scrip works.

    Thanks Claude

  112. I tried the timestamp and it works PERFECTLY!! My issue is applying that same script to multiple sheets in my spreadsheet. For instance, the script will only run on ‘Sheet1’. I need the script to run on Sheet2 as well. How to do I go about doing that? Please help!!!!

  113. https://uploads.disquscdn.com/images/2b59ee53af19fc1dc8b8945c2e5a93ab3bff55861bb409524d43550350f1630d.png Great code Azad, thank you! I’m trying to track the amount of time I’m spending on different projects. I’m using your code to enter the start time when I enter the name of the project I’m working on. I’ve added a column in front of the Project column so I can put an ‘x’ mark in it when it’s complete (see attached). So if I could somehow duplicate what you’ve done, so that I can sync another column, function on edit with a timestamp, then I can subtract the start time from the end time and track my production.

    Thanks!

    • I tried just adding a duplicate of your code and inputting the additional two column names and it worked for the new two columns but the original column and timestamp stopped working.

  114. Dear How If another Person used same sheet from another PC
    It’s not working probably.

    I used to make Staff Time sheet

    But When another user used or add any things, still not working

  115. Dear

    How If another Person used same sheet from another PC
    It’s not working probably.

    I used to make staff Time sheet

    But When another user used or add any things, still not working

  116. Hi I have a google sheet which is used by multiple users. I want to have a time stamp whenver they enter time in and time out in google sheet.

    i.e. if anyone enters time-in in column ‘A’, I want the date and time to be captured in column ‘B’ and if anyone enters time-out in column ‘C’, I want the date and time to be captured in column ‘D’

  117. Thanks Hazad, your explanations and blog follow-up are extremely helpful.
    Have approximately the same problem as user Chris has with a VLOOKUP formula at the bottom of this blog, except I have it with a QUERY formula.

    One of my (numerous) tabs is populated by the following https://uploads.disquscdn.com/images/8ad43474a98289e2dc4757a608e08f165371986faf7e5042a49a33382ebe5b5b.png QUERY formula:
    =QUERY(Apercu!B7:W; “select B, D, E, F, J, K where H = ‘Oui'”; -1)

    This sheet is populated to generate and send invoices automatically (by means of AutoCrat), the date of the invoice is the date generated by your script when a new record is added. So my column Date Sent is supposed to be autofilled by your script as soon as a new record comes in (detection column B, empty if no record is present).

    Actually, nothing happens the detection process seems to see de cell as being still empty while is is not (at least it displays a query response).

    Any idea on how we might solve this?
    Thanks to blog owner Hazad and to all members that enriched it so far!
    Friendly from Belgium.
    Pierre

  118. 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?

  119. 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.

  120. 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?

  121. Thanks Azad!

  122. To have this script work on all sheets:

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

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

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

  124. 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.

    • make a copy of the function and in this second copy change the header that you want to paste to. not the cleanest way to do it but works

  125. 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.

  126. 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?

  127. 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);
    }
    }

  128. 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!

  129. 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!

  130. Reply
    RUBEN SENTIS GUASCH March 14, 2017 at 12:44 am

    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?

    😉

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

  132. Hi,

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

  133. 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?

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

  135. Reply
    Mike Adrian De Guzman May 16, 2017 at 1:26 pm

    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);
    }
    }

  136. Reply
    Mike Adrian De Guzman May 16, 2017 at 2:54 pm

    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!

  137. 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

  138. 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 ?

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

  139. Hello Azad,

    I am facing same problem multiple time. Please help

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

  141. 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.

    • this is just a wild guess in the dark but try putting a comma instead of a ;? or make another variable called var sheet2 =……… or use mauricio’s comment from 4 months ago

  142. Reply
    [AIR] Khristian Jalandoni June 22, 2017 at 2:44 pm

    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!

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

  144. 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…

  145. 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

  146. 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.

  147. Reply
    Mauricio Lopez Moreno August 15, 2017 at 1:47 am

    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.

  148. 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

  149. 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.

  150. 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?

  151. 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

    • after the date gets inserted. use if statements combined with and and or statements. heres an example… =IF(AND(E2>=0,C2″”,E2″”),”not spawned”,IF(OR(ISBLANK(C2),ISBLANK(D2)),””,IF(D2=”Dead”, “dead”,”spawned”)))
      this is done within the sheet itself. instead of in the script

  152. 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?

  153. Hi, what if I have around 10 Columns in which I want the timestamp to be appearing?

  154. Thank you for the code.

    I have changed the code to insert the actual time of the entry and not the date. The problem I have is the that the time is displaying the AM time when it is actually PM at time of entry – so instead of inserting 13:30 it is inserting 01:30

    Could you please offer advise?

    I have ensured my google sheets timezone is correct for me, London

  155. Can two formulas have the source from one column? Example I want a date and time in separate columns. Can they both point to Name column?

  156. Thank you so much! I love this. But I am not getting the correct time. I need pacific time. I have used PDT and PST but am always 10 mins behind what my computer display shows as the time. Any suggestions? Thank you!

    • change you’re computer time to automatically use internet time. It’s important that your clock
      is accurate. can also change time zone in google sheets settings. and in the line var timezone gmt-5 change the minus 5 to 0 -1 -2 -3 etc

    • I also tried GMT-8 but I am still off by about 10 mins.

  157. I need to place the ‘Email’ at B7 and ‘Date Sent’ at F7. I had tried many time to modify the scrip but I failed. Please Help!!

    • can u try to modify this number on the script?

      var headers = sheet.getRange(7, 1, 1, sheet.getLastColumn()).getValues();

  158. please can help me? with code for diferent column ex: Day / Hour

  159. Azad, the code works wonderfully. Thanks for the post. Do you know how to alter it so that it runs only if the cell in the column to receive the timestamp is blank? Once the time stamp is generated, I do not want it to change if the referent cell in the column ‘updateColName’ is later edited. Many thanks in advance.

  160. Love this, however because I need the exact time, when I launch the script, it seems to return the time as a few minutes ago and not current time. Is that my laptop or something else?

  161. Love this, however, when I launch, the time is not exact, but is actually at least 5 minutes in the past. Is this my laptop or something else?

  162. I want to have this script run on 2 Sheets in the same spread sheet individually. What edits should i make?

    Does that make sense

  163. Hi,
    I have searched and tweaked for days and days before i wrote down this message. I am trying to add multiple update-inserts for different columns. For example:
    If Column A has update , insert timestamp Column B
    If Column C has update , insert timestamp Column D
    If Column E has update , insert timestamp Column F

    Does someone has a solution for this?

    It is great for using checkboxes with a timestamp when it is updated. Many, many thanks for the hero that knows how to get this done!

    Cheers,
    Stefan

  164. Great website. Lots of useful info here. I am sending it to
    several buddies ans additionally sharing in delicious. And obviously, thank you on your sweat!

  165. I don’t know if this has been posted, but below is the code I made using your multiple column code, problem is, they are not updating individually on that row. When you update one it updates all. Would like each action to edit only the corresponding time stamp column. Please help, Thank you !
    ——————–
    function onEdit(event)

    {

    var timezone = “GMT-8”;

    var timestamp_format = “hh:mm | M-d-YY”; // Timestamp Format.

    var updateColName = “Task”;

    var updateColName2 = “Started”;

    var updateColName3 = “Completed”;

    var timeStampColName = “Date / Time Requested”;

    var timeStampColName2 = “Time / Date Started”; // variable for second timestamp

    var timeStampColName3 = “Time / Date Completed”; // variable for third timestamp

    var sheet = event.source.getSheetByName(‘Sheet1’);

    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 dateCol2 = headers[0].indexOf(timeStampColName2); // get header for second timestamp.

    var dateCol3 = headers[0].indexOf(timeStampColName3); // get header for third timestamp.

    var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;

    if (dateCol > -1 && index > 1 && editColumn == updateCol) {

    var cell = sheet.getRange(index, dateCol + 1);

    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

    cell.setValue(date);

    }

    // extra code for second timestamp

    if (dateCol2 > -1 && index > 1) {

    var cell = sheet.getRange(index, dateCol2 + 1);

    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

    cell.setValue(date);

    }

    // extra code for third timestamp

    if (dateCol3 > -1 && index > 1) {

    var cell = sheet.getRange(index, dateCol3 + 1);

    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

    cell.setValue(date);

    }

    }

  166. Here is my script:
    /*Updated and maintain by internetgeeks.org*/

    function onEdit(event)
    {
    var timezone = “GMT-5”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “STATUS”;
    var timeStampColName = “DATE CLOSED”;
    var sheet = event.source.getSheetByName(‘BCG’); //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);
    }
    }

    How can I make it so it only auto timestamps when “done” is selected in column “status”

  167. I followed the script & what was instructed in the video but and it’s not working.

  168. The code works great. But instead of seeing the date stamp, I just want the time stamp. Does anyone have that script but with the hh:mm instead of date?

  169. I want to update 2 columns for 2 different changes, is it possible?

  170. Hello I know this is an older thread but wondered if you could advise how to edit the script to have 2 time stamps. One for an edit to a start column and one for an edit to a stop column.

  171. TypeError: Cannot read property “source” from undefined. (line 7, file “Code”)Dismiss

    I still get this error, and my sheet is called Sheet1 and in the script it still reads Sheet1

    any idea why?

  172. Super cool… works great
    what do i need to do to have it work on multiple columns in one sheet.
    and multiple columns in 3 different sheets.
    many thanks

  173. I am trying to run this code on 2 sheets and it only updates the second sheet. Did I make a mistake?

    /*Updated and maintain by internetgeeks.org*/

    function onEdit(event)
    {
    var timezone = “GMT-7”;
    var timestamp_format = “MM-dd-yyyy hh:mm a”;
    var updateColName = “Machine”;
    var timeStampColName = “Date and Time Tufted”;
    var sheet = event.source.getSheetByName(‘Observer Data’);
    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) {
    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-7”;
    var timestamp_format = “MM-dd-yyyy hh:mm a”;
    var updateColName = “Máquina”;
    var timeStampColName = “Fecha y Hora De Producción”;
    var sheet = event.source.getSheetByName(‘Operator Data’);
    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) {
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
    }
    }

  174. /Easy AllaGa Script /

    function onEdit(e) {
    var sh = e.source.getActiveSheet();
    var sheets = [‘Sheet1’, ‘Sheet2’]; //Name of the sheet’s where you want to run this script.
    var ind = [1,2].indexOf(e.range.columnStart); //change no [0,0,0] Updated’ header
    var stampCols = [5,6] //change no [0,0,0] Updated’ header
    if(sheets.indexOf(sh.getName()) ==-1 || ind == -1) return;
    sh.getRange(e.range.rowStart, stampCols[ind]).setValue(typeof e.value == ‘object’ ? null : new Date())
    }

  175. HI! thanks for this, but I have some trouble if copy it and paste to any sheet then when I use sheet1, sheet2 also updates the date too?

  176. thanks for the video
    how about if i want to have the same thing but in sheet2?

  177. Reply
    SHARIF ABDUL RAHMAN SHARIF HASAN August 24, 2019 at 11:07 am

    Thank you for your script. However, I noticed that the time or date change when I accidentally ‘touch’ the updateColName. How to make the timestamp permanent/static?

  178. Hello I made the below edit to match my code but find this error when trying to run it

    TypeError: Cannot read property “source” from undefined. (line 27, file “Code”)

    can you support, please

    function onEdit(event)
    {
    var timezone = “GMT+2”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Shooting Started (Media)”;
    var timeStampColName = “Shooting Start Date”;
    var updateColName = “Shooting Ended(Media)”;
    var timeStampColName = “Shooting End Date”;
    var updateColName = “Slides recieved from Mentor (Quality)”;
    var timeStampColName = “Slides recieved from Mentor Date”;
    var updateColName = “Actual Slides sent to Media Team (Quality)”;
    var timeStampColName = “Actual Slides sent to Media Team Date”;
    var updateColName = “Actual Editing Process Completed (Media)”;
    var timeStampColName = “Actual Editing Process Completed Date”;
    var updateColName = “Actual Comments sent to Media Team (Quality)”;
    var timeStampColName = “Actual Comments sent to Media Team Date”;
    var updateColName = “Actual Final Edits Sent For Final Review (Media) “;
    var timeStampColName = “Actual Final Edits Sent For Final Review Date”;
    var updateColName = “Actual Final Review Completed Date (Quality)”;
    var timeStampColName = “Actual Final Review Completed Date”;
    var updateColName = “Actual BC Upload (Media)”;
    var timeStampColName = “Actual BC Upload Date”;
    var updateColName = “Actual Built On Platform (Quality / Admin) “;
    var timeStampColName = “Actual Built On Platform Date”;
    var updateColName = “Actual CD Review & Release (Content Director)”;
    var timeStampColName = “Actual CD Review & Release Date”;
    var sheet = event.source.getSheetByName(‘Media’); //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);
    }
    }

  179. Great script. I got it working well. What I am trying to do it to calculate the amount of time that it takes a person to fill in all the columns on our sheet. Basically to measure speed of entry as a KPI. So my thought is to calculate time of first cell then time of last cell using the script. Then subtract the last time from the first to get the duration. Working on tweaking the script for this unless you think there is a way to do this inside the script.

  180. I keep getting this error, and the function is not working. Help Please

    TypeError: Cannot read property “source” from undefined. (line 9, file “Code”)Dismiss

  181. Dear

    I had inserted the timpstamp on one google sheet it works, but I want to add timpstamp to one more sheet of the same file its not working it is getting update on both sheets. How can I add 2 separate timestamp script to 2 different sheets of same file or is their another solution for that means script 1 but for 2 different sheets

  182. Thanks for your help! The problem I’m having is that it worked on the first row, but not for the second or other rows. Is there something I’m doing wrong?

  183. I tried it and it worked, however doesn’t play nice with Zapier, as a matter fact it stop new row from getting added, and zapier sent me a notice there was an error. How Can I make this play nice with Zapier?

  184. Need help…

    I want to run a script wherein Column 5 will trigger Column 2. And, Column 6 will trigger Column 3. To auto update columns 2 & 3 with timestamps based on PC time. Same script will be used for different tabs in the same google sheet.

    Many thanks in advance.

  185. super cool, thanks to make this code.
    Can you help me if i want to add “day” in this timestamp? many thanks

  186. Sir, it’s a very helpful code for me. I just need one more think that how to auto update a name or mail when we do group work. Like who fill the column it’s specify the other column.

  187. Thanks for it.

    Could you help me with how can I change date in two columns simultaneously?

  188. I need to have a timestamp whenever I update any column in the entire row.. How could I do that please??

  189. hello!

    Thank you a lot for what you’re doing!

    Can you help me? I need a button that will set today’s date in just 1 specific cell. What that code would be like?

  190. Curious to know how you can get the same CODE ( LINE 9) so that more than 1 sheet can use the CODE for time stamps? Sheet 2 and 3

  191. Hello, I love this script but I need it to keep the original date stamp and not change the date even if the data is edited can you help?
    function onEdit(event)
    {
    var timezone = “GMT-5”;
    var timestamp_format = “MM-dd-yyyy”; // Timestamp Format.
    var updateColName = “Employee – Copy and paste from SC”;
    var timeStampColName = “Date Stamp”;
    var sheet = event.source.getSheetByName(‘The Pipeline’); //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);
    }
    }

  192. Hello,
    I absolutely love this script but I need the date to remain the initial date and not change when edits are made. Is that possible?

  193. What if I use row 3 for the header? What code will I change?

  194. Hi Azad,
    your code helped a lot!

    can you please just explain me the following line from your code:
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    why there are three 1s in this line?

  195. Hi Rayanda, Did u find a solution for your question. Even I am trying to update timestamp only when the specific column is filled by specific letter.

Leave a reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Internet Geeks
Logo
Compare items
  • Total (0)
Compare
0