Excel

Create a timestamp when a cell is updated in Google Sheets

As much as I love Excel, I’ve totally transitioned to Google Sheets for all my data tweaking. The fact you can collaborate with many people all at once makes a lot of tasks much easier.

I’m also constantly tracking data and Google Sheets has a nifty feature to check the edit history. But using this feature to track progress for example would make things pretty convoluted. Therefore I needed a way to create a timestamp each time a cell was edited.

timestamp-1

Luckily this is possible with a little script:

/*CyberHacktivist.com*/

function onEdit(event)
{ 
 var timezone = "GMT+1";
 var timestamp_format = "MM/dd/yyyy"; // Timestamp Format. 
 var updateColName = "Updated";
 var timeStampColName = "Timestamp";
 var sheet = event.source.getSheetByName('Timestamp'); //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 &amp;gt; -1 &amp;amp;&amp;amp; index &amp;gt; 1 &amp;amp;&amp;amp; 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);
 }
}

You can specify which sheet the script should monitor (line 9), and even the column that will be updated (line 7) and the column that will be timestamped (line 8).

Here you can see a working example with this script: http://bit.ly/google-sheet-timestamp-demo. And you can make a copy for yourself here: http://bit.ly/google-sheet-timestamp-demo-copy

I hope this comes in handy. I’d be interested to know what usages you have for it.