33

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? – OnenOnlyWalter Jan 3 '13 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? – RichGonzalez Jan 3 '13 at 21:03
  • Got it, let me take a look at my original code and get back to you :) – OnenOnlyWalter Jan 3 '13 at 22:52
  • Much appreciated Onen! – RichGonzalez Jan 3 '13 at 23:15
35

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

  // 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());
    dateModifiedRange.setValue(time);
  };
 };

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! – RichGonzalez Jan 3 '13 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 – OnenOnlyWalter Jan 3 '13 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? – RichGonzalez Jan 3 '13 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 '16 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/… – Dan Nguyen Jun 15 '18 at 1:21
8

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
4

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 '16 at 18:50
  • Since user clocks can be inaccurate, is there a standard way to get a server-side, accurate timestamp? – Sim Mar 6 '17 at 23:13
  • @Sim Google Apps Script runs server-side. The time you get is from the server. – user135384 Jul 18 '17 at 20:26

protected by Community Dec 26 '13 at 18:01

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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