Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing and adjusting the doc at the same time. So this request was put in. I'll admit I have not done coding, so this is beyond me.

Myanda had posted something that seemed along the right path, but we are not sure how to implement it for our needs:

Google Spreadsheet Timestamp?

  • It would help if you could link us to the document, or provide an example document of what you're looking for. I can modify my other answer accordingly, but I'm not sure exactly what you're asking. Do you just need a single cell updated with a timestamp that shows when the entire spreadsheet was updated? Jan 3, 2013 at 20:45
  • Hey Onen. What we are looking for is, in column 2, each row updates with a timestamp anytime any cell in their respective row is updated. I can't post the doc as it's confidential unfortunately. But it contains 21 columns and 60 rows. Does this help? Jan 3, 2013 at 21:03
  • Got it, let me take a look at my original code and get back to you :) Jan 3, 2013 at 22:52
  • Much appreciated Onen! Jan 3, 2013 at 23:15

3 Answers 3


Ok, here is a modified version of the code in my previous answer:

function onEdit(e) {
  // Your sheet params
  var sheetName = "MySheet";
  var dateModifiedColumnIndex = 2;
  var dateModifiedColumnLetter = 'B';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm:ss");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());

What this does is grabs the row of any cell that is changed and assigns a timestamp to the second column of that particular row.

To implement, all you need to do is go into the spreadsheet and click on Tools > Script Editor. In the resulting editor page, just paste this in there. Since this is an onEdit() function, it should work without having to add anything else to the cells of your spreadsheet, just paste it into the editor and save it.

For the timestamp, I set the format to MM/dd/yy and left the time. If you want to change it, you can just change the use of Utilities.formatDate.

  • Onen, this works perfectly! Thank you so much! Jan 3, 2013 at 23:42
  • 1
    Glad to hear it! Can you mark this answer with a check mark then :) I would say that it was so that other people can know this solution works, but it's also cuz I'd like the points :P Jan 3, 2013 at 23:49
  • Will do. It definitely works. One follow up question. Nothing as complimented. Changing to PST doesn't seem to change it to Pacific Standard time, but EST works. Does java read PST as a different abbreviation. And is there a way to display the time as a 12hr time vs military time? Jan 3, 2013 at 23:52
  • 1
    timestamp doesn't work for me. With me "20yy-MM-dd HH:mm" works instead. I think 'dd; has to be lowercase or it doesnt work (unless its January which was the case when this was written, ahahahah).
    – Riccardo
    Oct 12, 2016 at 15:19
  • 1
    Note that using "DD" for .formatDate() will get you the "Day in year" -- e.g. 365 for Dec. 31. You probably want "dd". You can see a full list of the date format values here: docs.oracle.com/javase/7/docs/api/java/text/…
    – dancow
    Jun 15, 2018 at 1:21

FYI, the accepted answer uses DD as the day format, which will get you something like "312", i.e. the 312th day of the year.

I used this:

"yyyy-MM-dd, hh:mm:ss"

to get this:

2013-11-12, 03:43:20

I hope this is OK to put here. Below is a modification of the function above but to do it for a row (row 9) rather than columns. Figuring out how to use the column assignment was very frustrating but ended up being extremely simple. Thanks to OnenOnlyWalter for the original code:

function onEdit() { 
    var s = SpreadsheetApp.getActiveSheet(); // Get spreadsheet name 
    var r = s.getActiveCell(); // store active cell name in current spreadsheet 
  var cell1 = 9 // This is the row I want to put values
  if(r.getRow() != cell1) { // Ignores this row (where I put the dates)
    var column = r.getColumn();  // Get column # from active cell
    var time = new Date(); // Get date and time of last mod
    time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss"); // Format date and time
    SpreadsheetApp.getActiveSheet().getRange(cell1,column).setValue(time); // put in time in cell
  • This seemed to work quite well for me. Thanks for fixing @OnenOnlyWalter's row 8 error.
    – Zlatty
    Aug 9, 2016 at 18:50
  • Since user clocks can be inaccurate, is there a standard way to get a server-side, accurate timestamp?
    – Sim
    Mar 6, 2017 at 23:13
  • @Sim Google Apps Script runs server-side. The time you get is from the server.
    – user135384
    Jul 18, 2017 at 20:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.