Skip to content

Instantly share code, notes, and snippets.

@1mike12

1mike12/timestamps.js

Last active Oct 19, 2016
Embed
What would you like to do?
add a mechanisms to automatically add timestamp to google sheets, edit the "update" column with any value, and corresponding timestamp column will insert itself
function onEdit(){
var sheet = SpreadsheetApp.getActiveSheet();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var activeCell = sheet.getActiveCell();
var activeRow = activeCell.getRow();
var activeColumn = activeCell.getColumn();
var updateColumnName = "update";
var updateColumn = headers.indexOf(updateColumnName) + 1; //bec activeColumns are 1 indexed
var timeStampColumnName = "timestamp";
var timeStampColumn = headers.indexOf(timeStampColumnName) + 1;
if (activeColumn === updateColumn && activeCell.getValue() !== "") { //checks the column
var now = new Date();
var timeZone = Session.getScriptTimeZone();
//this format is recognized by google sheets so can be autoformated to another display type
var timestamp = Utilities.formatDate(now, timeZone, "M/d/Y H:m:s");
var targetCell = SpreadsheetApp.getActiveSheet().getRange(activeRow, timeStampColumn);
if (targetCell.getValue() === "")
targetCell.setValue(timestamp);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment