How to Auto Insert Timestamp in Google Sheet

0
2897

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.

[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:

Watch this video on YouTube.

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.

1 COMMENT

  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

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

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

            }

            }

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

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

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

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

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

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

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

  9. 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();

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

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

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

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

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

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

      • 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”

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

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

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

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

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

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

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

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

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

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

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

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

  28. 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”

  29. 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”;

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

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

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

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

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

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

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

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

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

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

  37. 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”;

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

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

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

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

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

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

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

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

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

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

  48. 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();

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

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

      • Its still showing the same error can you help with this,
        TypeError: Cannot read property “source” from undefined. (line 7, file “code 5”)

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

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

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

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

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

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

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

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

    • 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?

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

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

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

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

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

  63. 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 🙁

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

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

    • 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

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

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

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

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

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

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

  71. 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 !!! 😉

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

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

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

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

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

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

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

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

  80. 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’

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

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

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

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

  85. To have this script work on all sheets:

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

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

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

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

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

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

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

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

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

    😉

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  113. 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();

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here