Skip to content

Instantly share code, notes, and snippets.

@cfg
Created April 12, 2017 17:17
Show Gist options
  • Save cfg/9b27f1da11e7973ce001cedbfcfb2ba8 to your computer and use it in GitHub Desktop.
Save cfg/9b27f1da11e7973ce001cedbfcfb2ba8 to your computer and use it in GitHub Desktop.
Google Sheets: Add a "last modified" timestamp to a row when a cell in a specific column is changed. Quick, hacky code, per usual.
function onEdit( evt ) {
var ss = SpreadsheetApp.getActiveSheet();
var sheet_name = 'Bug List';
if ( sheet_name !== ss.getName() ) {
return;
}
var status_col = 8; // column number that contains the status type
var lastmod_col = 10; // column number where the timestamp should go
if ( status_col < evt.range.getColumn() || status_col > evt.range.getLastColumn() ) {
return;
}
var ts = Utilities.formatDate( new Date(), 'America/Los_Angeles', 'yyyy-MM-dd HH:mm');
var mod_cell, row, col, cell, cell_col;
for ( row = 1; row <= evt.range.getNumRows(); row++ ) {
for ( col = 1; col <= evt.range.getNumColumns(); col++ ) {
cell = evt.range.getCell(row, col);
cell.getColumn() = cell_col;
if ( cell_col > status_col ) {
continue;
}
if ( cell_col === status_col ) {
mod_cell = ss.getRange( cell.getRow(), lastmod_col, 1, 1 );
mod_cell.setNumberFormat('@').setValue( ts ); // '@' is necessary to force plain text
}
}
}
return;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment