“Who moved this deal to Lost, and when?” Version history can answer that, slowly. appendStatusAuditRow(ss, entry) writes one line to an Audit tab every time a field changes: timestamp, actor, row key, field, and old → new. It creates the tab and headers on first use, and skips the write when the value didn't actually change.
When to use
- Status columns (New → Approved → Paid) that people argue about later
- Price or owner changes on a shared pipeline sheet
- Any field a client might ask “who changed this?” about
How to use this snippet
/**
* Append one change to an Audit tab (created on first use).
*
* @param {Spreadsheet|Sheet} ssOrSheet
* @param {{actor: string, key: *, field: string, oldValue: *, newValue: *}} entry
* @param {Object=} opts { tabName: 'Audit' }
* @return {boolean} false when old and new are the same (nothing written)
*/
function appendStatusAuditRow(ssOrSheet, entry, opts) {
opts = opts || {};
var ss = ssOrSheet.getSheetByName ? ssOrSheet : ssOrSheet.getParent();
var name = opts.tabName || 'Audit';
var oldV = entry.oldValue == null ? '' : String(entry.oldValue);
var newV = entry.newValue == null ? '' : String(entry.newValue);
if (oldV === newV) return false;
var sh = ss.getSheetByName(name);
if (!sh) {
sh = ss.insertSheet(name);
sh.getRange(1, 1, 1, 6).setValues([['Timestamp', 'Actor', 'Key', 'Field', 'Old', 'New']]);
sh.setFrozenRows(1);
}
sh.appendRow([new Date(), entry.actor || 'unknown', entry.key, entry.field, oldV, newV]);
return true;
}
Example
// Installable onEdit trigger (so it can run as you and write reliably)
function onStatusEdit(e) {
var sh = e.range.getSheet();
if (sh.getName() !== 'Deals' || e.range.getNumRows() > 1) return;
var head = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
if (head[e.range.getColumn() - 1] !== 'Status') return;
var id = sh.getRange(e.range.getRow(), head.indexOf('Id') + 1).getValue();
appendStatusAuditRow(sh, {
actor: activeUserEmailOr('unknown'),
key: id,
field: 'Status',
oldValue: e.oldValue,
newValue: e.value
});
}
Tips:
e.oldValueande.valueare only set for single-cell edits. Multi-cell pastes need a snapshot comparison instead.onEditdoesn't fire for script or API writes, so log those changes from the script that makes them.- For the actor, activeUserEmailOr avoids blank cells in installable triggers.
- Protect the Audit tab (Data → Protect sheets and ranges) so people can't tidy it up.
Tip: NitroGAS Co-Pilot can add the audit call to your existing edit trigger. Paste the trigger and your header row.
Happy Coding!
