“When was this row added, and when did someone last touch it?” stampEditTimestamps(e, opts) answers both from an edit trigger. It fills Created only when it's blank, always refreshes Updated, skips header rows, ignores edits to the timestamp columns themselves, and handles a multi-row paste with one read and one write per column instead of a cell-by-cell loop.
When to use
- Request, lead or task sheets where people add and edit rows by hand
- Any sheet where “last updated” matters for follow-ups
- Replacing a
NOW()column that keeps changing on its own
How to use this snippet
/**
* Stamp Created (once) and Updated (every edit) for the edited rows.
*
* @param {Object} e onEdit event object
* @param {Object} opts { createdCol: number, updatedCol: number, headerRows: 1 }
* @return {number} how many rows were stamped
*/
function stampEditTimestamps(e, opts) {
opts = opts || {};
var r = e && e.range;
if (!r) return 0;
var sh = r.getSheet();
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
var created = opts.createdCol, updated = opts.updatedCol;
var first = Math.max(r.getRow(), headerRows + 1);
var last = r.getLastRow();
if (last < first) return 0; // header-only edit
// Ignore edits that only touched the timestamp columns themselves
var c1 = r.getColumn(), c2 = r.getLastColumn();
var onlyStampCols = true;
for (var c = c1; c <= c2; c++) {
if (c !== created && c !== updated) { onlyStampCols = false; break; }
}
if (onlyStampCols) return 0;
var n = last - first + 1;
var now = new Date();
if (created) {
var cr = sh.getRange(first, created, n, 1);
var vals = cr.getValues().map(function (row) { return [row[0] === '' ? now : row[0]]; });
cr.setValues(vals);
}
if (updated) {
sh.getRange(first, updated, n, 1).setValues(Array.from({ length: n }, function () { return [now]; }));
}
return n;
}
Example
// Installable "On edit" trigger pointing at this function
function onRequestsEdit(e) {
if (e.range.getSheet().getName() !== 'Requests') return;
stampEditTimestamps(e, { createdCol: 6, updatedCol: 7, headerRows: 1 });
}
Tips:
- Format the two columns as date-time (Format → Number → Date time) so the stamps are readable.
- The script's own
setValuesdoesn't fireonEdit, so the stamp can't trigger itself in a loop. - Edits made by other scripts or imports don't fire
onEditeither. Stamp those rows from the script that writes them. - A simple
onEditworks too, but an installable trigger runs as you, so it can write to protected timestamp columns.
Tip: NitroGAS Co-Pilot can find your Created/Updated columns by header and wire up the trigger. Paste your header row.
Happy Coding!
