Technology Geeks

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:

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.

author-avatar

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

Related Posts

292 thoughts on “How to Auto Insert Timestamp in Google Sheet

  1. Alvaro says:

    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

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

      1. Rayan says:

        Hi
        Did you find a solution here?

          1. Rayan says:

            You are a genius man, thank you

          2. Gary says:

            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!

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

          4. Gary says:

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

          5. 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);

            }

            }

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

          7. Gary says:

            Thank you, I’ll give it a try!

    2. scherpereel Yves says:

      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.

  2. Alexander says:

    It works great! Thank you!

  3. Joe says:

    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

    1. Check out the updated code. Also this new video: https://www.youtube.com/watch?v=j659vOc96SA

  4. Joe says:

    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. Eric says:

    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.

    1. Change ‘Sheet1’ to name of your current sheet where you want this script to run.

      1. Peng Her says:

        I am having the same problem. Confirm the sheet name; even change it back to ‘Sheet 1’…. still not working. 🙁

        1. Richard says:

          I get the same… 🙁

          1. Richard says:

            I can change the sheet name back and forth in the formula in yours and it still works, yet doesn’t work in mine. How strange! Maybe there’s a version issue or something? :S

          2. Richard says:

            Ok it is working now! Very strange…the only thing I changed was add a semicolon (;) after the date stamp column variable like every other line (not in the code above), and it started working in the spreadsheet.

            Note that when I ‘ran’ the script, it still had the same error message as above, but now it works in the spreadsheet which is all that matters 🙂

          3. Sorry my bad. Thanks for letting me know. I have updated the code with ‘;’.

          4. Peng Her says:

            Adding the semicolon works for me now. Thanks to both of you for troubleshooting.

          5. Peter says:

            Hello! First great thanks for your work Azad, I find it very useful. Secondly I am having the same problem, like other guys. Although I used the new code and inserted triggers I am still getting error in line 7.
            Please help.

        2. Its ‘Sheet1’ and NOT ‘Sheet 1’

      2. Alfonso Carmona says:

        Azad,
        Do you think you can help me with an issue I have with one of my documents?

    2. Pollyana Braga says:

      Same problem I’m having, please help me!

      1. Ethan Frame says:

        ** Potential solution for some of you **

        You must ensure the COLUMN NAMES (‘updateColName’ and ‘timeStampColName’ ) that are being referenced exist in the very first ROW of your spreadsheet (A1, B1, C1, D1, etc…. wherever your desired columns are).

        I was attempting to reference these columns that were three rows down in my spreadsheet, thus it did not work.

        Hope this helps!

        1. Jimbo says:

          Just change the first value of sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); to the row with headers.

          E.g. if your column names are in row 4 it would be getRange(4,1,1,etc etc)

  6. Todd says:

    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?

    1. Laura Walker says:

      hi – change DD:MM:YY to HH:MM

    2. josh says:

      Hi Azad, similiar question. I only need the time, not date. I have updated for the time function, but would like to only update with the original entry, not any time this cell is changed. How would i do that?

  7. JD says:

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

    1. What is the problem?

  8. Logan says:

    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. junglis25 says:

    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. Laura Walker says:

    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

    1. SHARIF ABDUL RAHMAN SHARIF HASAN says:

      I am facing the same problem. Did you find the solution?

  11. Derek Medeiros says:

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

    1. Derek Medeiros says:

      I change the sheet1 to the sheet I want and I still get an error

  12. Steven Weldler says:

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

    1. Tejash Parekh says:

      did you ever figure it out? I’m trying to do the same thing

  13. Ross says:

    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?

    1. Its possible but need some customization. The code is very simple you should be able to do this.

  14. Rayan says:

    hi Mr. Azad how can i reach you ?

  15. Britt says:

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

  16. OE says:

    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. Ryan says:

    Thank you! Exactly what I was looking for!

  18. gilles says:

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

  19. Larissa says:

    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.

    1. Hai Nguyen says:

      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. jp says:

    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. jp says:

    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

    1. nerdtastic says:

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

      1. jp says:

        Thank you very much. That worked perfectly.,

      2. Dave Woodland says:

        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”

      3. manu says:

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

  22. Antonia says:

    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

    1. Hai Nguyen says:

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

      change it to

      var sheet = event.source.getActiveSheet().

  23. monica donati says:

    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?

    1. Hai Nguyen says:

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

      change it to

      var sheet = event.source.getActiveSheet().

      1. Henri says:

        Thanks a lot Hai, it works perfectly !!!

  24. Rob says:

    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. Ryan says:

    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. ALBOOGIE says:

    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. Devon says:

    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!

    1. Navya Paravasthu says:

      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. Chris says:

    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. Maxwell says:

    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. a.hamdy says:

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

  31. Kevin Mccarthy says:

    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?

    1. Change the code at line 6 to below:

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

      1. Kevin Mccarthy says:

        Thanks. Can code be used for more than one sheet?

        1. Yes. With some modifications, it’s possible.

  32. PureGuava says:

    Thanks for the code!! Worked perfectly

  33. Jim Ryan says:

    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. nuclearlaunchdetected says:

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

    1. Azad Shaikh says:

      use cell.setValue(date); to do that.

  35. esr says:

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

  36. esr says:

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

    1. Dave Woodland says:

      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. Will Keith says:

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

    1. Azad Shaikh says:

      Change the code at line 6 to below:

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

    2. Dave Woodland says:

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

  38. Jacob says:

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

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

  39. Jacob says:

    Its the

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

  40. Connie says:

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

    1. Connie says:

      Is it possible to put 2 scripts on one page? Like a check in time and check out time?

  41. Misty Wegman says:

    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. Jorge Minassian says:

    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. Farley says:

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

  44. Peter says:

    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!

    1. Peter says:

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

    2. Chris says:

      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.

  45. Anirudh Kanga says:

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

    1. BigVicSA says:

      I think if you added a “please” your response might have been better (Suggestion for next time).

  46. Gary Jones says:

    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. Ram says:

    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. Jorja says:

    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. ydf says:

    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. vanbug says:

    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. Mr. Orthodox says:

    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.

    1. Jennifer Kyle says:

      I am getting the same message. HELP!

      1. Mr. Orthodox says:

        Its alright, just ignore the message – it’ll come whatever you do, check in spread sheet, it works fine. Just see you have kept your name of spreadsheet & columns correct in the code.

  52. brunitob says:

    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. Kevin West says:

    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. Abdulhakim Khan says:

    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. ismerald says:

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

  56. James Yang says:

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

  57. Poom Laupattarakasem says:

    thank you 🙂

  58. Lisa says:

    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. Stephen Davie says:

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

  60. Pollyana Braga says:

    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. RE says:

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

  62. James Leung says:

    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. Riccardo “Ricc” Carlesso says:

    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?

    1. Stephen Brown says:

      I would love a response to this as well.

  64. jenny says:

    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!

    1. kavi karnapura says:

      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. Abhijit Deshmukh says:

    what if i want time along with date?

  66. Stephen Brown says:

    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. UtahValley IT says:

    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

    1. SK45 says:

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

      1. aman aneja says:

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

      2. SHARIF ABDUL RAHMAN SHARIF HASAN says:

        Thank you very much Sir

  68. RAZMAN BIN RAMEDAN says:

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

    1. RAZMAN BIN RAMEDAN says:

      here is the photo

  69. RAZMAN BIN RAMEDAN says:

    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. Matthew Brown says:

    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. Adam Westbrook says:

    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. Omo says:

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

  73. wangkai says:

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

  74. Natalie Schie says:

    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. Shiroyukineko says:

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

  76. Ben says:

    Awesome! Clean Clear. works a treat, Thank you

  77. DrunkTrainPolka says:

    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. TommyJ says:

    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. R says:

    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. priyatam saha says:

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

    1. SK45 says:

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

      1. avengine says:

        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. Dominic says:

    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. Eddie Maughan says:

    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. Robert says:

    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. Miguel says:

    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. Emerson says:

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

  86. Sri devi says:

    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. Sunshine says:

    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. Lori says:

    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. zeba shabnam says:

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

    1. zeba shabnam says:

      Please help

      1. zeba shabnam says:

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

        1. zeba – is this add on code or stand alone code?

    2. Ausa Mo Kitipan says:

      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

  90. Mindy Nash says:

    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. Pieter J Els says:

    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. Pete says:

    How can I had multiple timestamps to one sheet?

  93. suprasoma says:

    You are a true gentleman. Thank you for sharing!

  94. The Arc Montgomery County Fron says:

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

  95. Anbarasan says:

    how to add multiple time stamps in same google sheets

  96. Iris says:

    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. Yederith Loner says:

    Thanks! it still works! 🙂

  98. Marcelo C. S. Filho says:

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

  99. C School Director says:

    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.

    1. Tony Jacob says:

      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. Chris says:

    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. Ihs Domain says:

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

  102. pm says:

    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. L.N says:

    IT DOESN’T WORK!

  104. L.N says:

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

  105. marty says:

    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. Marty says:

    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. Ausa Mo Kitipan says:

    It works soooo good <3 Thank you 🙂

  108. angie farina says:

    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. AG says:

    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. Miroslav Švarc says:

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

    1. Claude says:

      In line 9 add on more ; a the end of the code, that should work.

      1. Miroslav Svarc says:

        Thanky you for answer but still doesnt work. It says “;” is missing in front of the order

  111. Claude says:

    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. Davada Harmon says:

    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. Ed Hull says:

    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!

    1. Ed Hull says:

      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. Faisal J Hu says:

    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. Faisal J Hu says:

    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. Amrit says:

    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. Operation Briques says:

    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. sn says:

    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. Ka Gapina says:

    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. Ka Gapina says:

    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. evvvvvritt says:

    Thanks Azad!

  122. Boyd Carleson says:

    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. Matt Becka says:

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

  124. LulamaeBroadway says:

    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.

    1. Skyler Dunham says:

      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. geb says:

    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. samjco says:

    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. Jay Ryan Ouano says:

    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. Ferry says:

    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. Life Lines says:

    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!

    1. HM says:

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

      1. Life Lines says:

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

  130. RUBEN SENTIS GUASCH says:

    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. David Manniche says:

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

  132. Apple Fruit says:

    Hi,

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

  133. Julia says:

    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. jwwietsma says:

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

  135. Mike Adrian De Guzman says:

    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. Mike Adrian De Guzman says:

    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!

    1. Clark Llorador says:

      This is very helpful. Thank you, Mike

    2. Armin Nasser says:

      thanks for the script, but id doesnt work for the second sheet

  137. Gina says:

    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

    1. Skyler Dunham says:

      if it’s off by an hour change the timezone 1 hour ahead or behind. maybe daylight saving?

  138. alkashaf qatar says:

    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 ?

    1. Marcos Menendez says:

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

  139. Saloni says:

    Hello Azad,

    I am facing same problem multiple time. Please help

  140. alkashaf qatar says:

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

  141. Adnan chowdhury says:

    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.

    1. Skyler Dunham says:

      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. [AIR] Khristian Jalandoni says:

    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!

    1. Chetan wag he says:

      I also have the same issue…is their any solution

  143. Hollie McArthur says:

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

    1. Skyler Dunham says:

      just ignore the error message. it still works perfectly regardless of what that part says

  144. Lukasz Gorajek says:

    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. Craig Fish says:

    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. TheresaW says:

    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. Mauricio Lopez Moreno says:

    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. David Manniche says:

    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

    1. Skyler Dunham says:

      that error is fine. just ignore the error and continue to use it. still functions even with error message

  149. Lance Whitehead says:

    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.

    1. Skyler Dunham says:

      share the sheet with us so people can help?

  150. Alfonso Carmona says:

    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. Woody Daniels says:

    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

    1. Skyler Dunham says:

      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. Suspect says:

    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. Jessica says:

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

    1. Skyler Dunham says:

      I just made copies of the whole function and changed the name of the update column as a temporary jerryrig

  154. MarK says:

    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

    1. Skyler Dunham says:

      change the time zone by 12 hours?

  155. CJ says:

    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. cj says:

    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!

    1. cj says:

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

    2. Skyler Dunham says:

      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

  157. Delhen says:

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

    1. Juan Manrique says:

      can u try to modify this number on the script?

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

  158. andres says:

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

  159. Austin says:

    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.

    1. SHARIF ABDUL RAHMAN SHARIF HASAN says:

      Did you find a solution?

  160. vesta says:

    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. vesta says:

    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. Tejash Parekh says:

    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. Stefan says:

    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. stemple says:

    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. Oscar says:

    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. Richard Mastria says:

    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. Jho says:

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

  168. Nick Wolfe says:

    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. Akbar says:

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

  170. Lisa A Hagin says:

    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. Mohamed says:

    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. Bim says:

    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. Tom Cupp says:

    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. HeMa AllaGa says:

    /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. Piseth Em says:

    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. Armin Nasser says:

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

  177. SHARIF ABDUL RAHMAN SHARIF HASAN says:

    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. Moamen Said says:

    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. Rich Tanksley says:

    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. Colin Martin says:

    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. Chetan waghe says:

    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. Tony says:

    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. Ernie espinoza says:

    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. Kay says:

    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. Natasha says:

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

  186. Enamul Khan says:

    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. Nirmal says:

    Thanks for it.

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

  188. Gaurav says:

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

  189. Bohdan says:

    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. ernesto says:

    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. Kathleen Willis says:

    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. Kathleen Willis says:

    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. Aj says:

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

  194. Chaudhry Umar says:

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

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