You want a “Last updated” or “Date added” column that fills itself in. The obvious formula, =IF(A2<>"", NOW(), ""), doesn’t work: NOW() recalculates, so every row shows the current time instead of when it was edited. The iterative-calculation trick some guides suggest is fragile: it changes a file-wide setting and is easy to break.
A short script does it properly. This guide uses Sheets Edit Tracker, a free, MIT-licensed Apps Script that also keeps a log of every change.
Code.gs and paste in this Code.gs.At the top of the script is a RULES list. Each rule says: on this tab, when these columns change, stamp this column.
const RULES = [
{ tab: '*', watch: 'A:D', stamp: 'E', by: '', once: false },
];
tab: a tab name like 'Orders', or '*' for every tab.watch: the columns that count as an edit, like 'A:D' or a single 'C'.stamp: the column that gets the date and time.by: optionally, a column for the editor’s email (see the note below).once: true keeps the first timestamp (a “created” date); false updates it on every edit (“last updated”).Click Save. There’s nothing to run and no trigger to set up. Go back to your sheet, type in column A, and the time appears in column E.
After the first edit, a new Edit Log tab appears with one row per change: time, editor, tab, cell, old value, new value. It’s useful for shared sheets (“who changed this price?”). To stop people editing the log, right-click the tab and choose Protect sheet. If you don’t want a log, set LOG_SHEET = '' in the script.
| You want | Rule |
|---|---|
| “Last updated” on every tab | { tab: '*', watch: 'A:D', stamp: 'E', by: '', once: false } |
| “Date added” on Orders, never changes | { tab: 'Orders', watch: 'A:F', stamp: 'G', by: '', once: true } |
| When Status changed, and who changed it | { tab: 'Tasks', watch: 'C', stamp: 'H', by: 'I', once: false } |
You can list several rules, one per line.
Why is the editor “(unknown)”? Google only tells scripts an editor’s email when the editor is in the same Google Workspace organization as the sheet’s owner, or is the owner. For personal Gmail accounts editing someone else’s sheet, the email stays private.
Does it stamp rows added by a Google Form? No. Form submissions, formulas and other scripts don’t count as edits. Form responses already get a Timestamp column from Google Forms.
What about pasting many rows? Every pasted row in the watched columns gets a timestamp. The log records the paste as one line with the number of cells changed.
Can I format the timestamp? Yes. Select the stamp column and use Format → Number → Date time (or a custom format).