Skip to content

Instantly share code, notes, and snippets.

@bwhaley
Created April 11, 2024 23:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bwhaley/3af5e5870d0e42948757957febf8ea60 to your computer and use it in GitHub Desktop.
Save bwhaley/3af5e5870d0e42948757957febf8ea60 to your computer and use it in GitHub Desktop.
Google Apps scripts that adds a timestamp to a column
'use strict';
// Adds a timestamp to the LAST_MODIFIED_COLUMN whenever the value of any of the preceding columns has changed.
// Deletes the timestamp if all preceding columns are blank.
function onEdit(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
}
const row = e.range.getRow();
const col = e.range.getColumn();
const SHEET_NAME= "Foobar";
const FIXEDROW = 1;
const LAST_MODIFIED_COLUMN = 7;
const range = e.source.getActiveSheet().getRange(row, 1, 1, LAST_MODIFIED_COLUMN - 1);
if (range.isBlank()) {
e.source.getActiveSheet().getRange(row,LAST_MODIFIED_COLUMN).clearContent();
return;
}
if (e.source.getActiveSheet().getName() === SHEET_NAME && col < LAST_MODIFIED_COLUMN && row > FIXEDROW) {
const CURRENT_DATE = new Date();
e.source.getActiveSheet().getRange(row,LAST_MODIFIED_COLUMN).setValue(CURRENT_DATE).setNumberFormat("yyyy-MM-dd h:mm:ss");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment