Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

3 Answers

up vote 9 down vote accepted

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

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).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 format text (in red).

share|improve this answer
    
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
    
@RichGonzalez k, I updated my code for your case. Timezones are always based in GMT, so you just subtract (or add) to get there: GMT-08:00. For the 12 hr format, just change the HH to hh (lower case). My code block above reflects these changes. –  OnenOnlyWalter Jan 4 '13 at 0:07
    
Awesome. Thank you! Answer checked off. –  RichGonzalez Jan 4 '13 at 0:11
show 1 more comment

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
  };
 };
share|improve this answer
add comment

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

Thanks to the accepted answerer though!

share|improve this answer
add comment

protected by Community Dec 26 '13 at 18:01

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

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.