Time Stamp
Create a Time Stamp on a Google Sheet
We have this simple form of a Customer Relationship Management (CRM) Database. As an administrator, when I added an item, I wanted a timestamp to appear in the adjacent column. I also wanted a timestamp when I completed the task so I could track my efficiency.
Here's the code. Quick notes, it reads column names in the 3rd row. You'll need to modify column names and the row number to make it match your sheet.
This was one of my first times writing code. It's pretty clunky but it works!
function onEdit(event) {
var timezone = Session.getScriptTimeZone();
var timestamp_format = "MM/dd/yyyy HH:mm:ss";
var updateColName = "Complete";
var timeStampColName = "Completed Date";
var updateColName2 = "Reason";
var timeStampColName2 = "Reason Date";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var index = actRng.getRowIndex();
var headers = sheet.getRange(3, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf(timeStampColName);
var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
if (dateCol > -1 && index > 1 && editColumn == updateCol) {
var cell = sheet.getRange(index, dateCol + 1);
var updateCellVal = actRng.getValue();
if (updateCellVal == false) {cell.setValue("")}
else {
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell.setValue(date);
}
}
var dateCol2 = headers[0].indexOf(timeStampColName2);
var updateCol2 = headers[0].indexOf(updateColName2); updateCol2 = updateCol2+1;
if (dateCol2 > -1 && index > 1 && editColumn == updateCol2) {
var cell2 = sheet.getRange(index, dateCol2 + 1);
var updateCellVal2 = actRng.getValue();
if (updateCellVal2 == "") {cell2.setValue("")}
else {
var date2 = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell2.setValue(date2);
}
}
}