Tracking when a task row last changed usually means someone manually typing today's date into a column, which works right up until they forget - this example writes that date automatically whenever any of the tracked fields change.
The trickiest part of any timestamp trigger is avoiding an infinite loop: if the script writes to the same range it's watching, that write fires onEdit again. This example sidesteps the problem by watching columns A through D and writing to column F, well outside the watched range.
onEditAddTimestamp also distinguishes a brand-new row from an update to an existing one, filling in a separate 'Created On' column only the first time a row picks up a timestamp.
You'll finish with a Tasks sheet where 'Last Updated' and 'Created On' populate themselves, giving you an audit trail without a single manual keystroke.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Sheet | Purpose |
|---|---|---|
| A-D | Tasks | Watched fields - editing any of these triggers a stamp |
| F | Tasks | Last Updated - overwritten on every relevant edit |
| G | Tasks | Created On - written only the first time a row is stamped |
What it does
Every edit to the Tasks sheet passes through onEditAddTimestamp, which immediately checks whether the edited column falls inside the watched range (columns 1 through 4). If it does, the function writes the current time into column F and, on a row's first stamp, also into column G.
- Watches four columns but writes to two entirely different ones
- Distinguishes first-time stamps from routine updates
- Skips the header row explicitly by row number
Prerequisites
Because the watched and written columns must never overlap, confirm your real sheet's layout before reusing this - if column F already holds real task data, move the timestamp columns further right.
- A sheet named 'Tasks' with editable data in columns A-D
- Columns F and G reserved for timestamps only
- An installed onEdit trigger, simple triggers are sufficient here since no external services are called
Walkthrough
The column-range guard is what prevents an infinite loop: because the watched range (1-4) and the written column (6) never intersect, writing to column F never re-triggers the part of the function that would write to F again.
existingStamp is read before the new stamp is written, which is the only way to tell whether this is the row's first edit; reading it after the write would always show a value and break the Created On logic.
Edge cases
Editing multiple cells across columns A-D in a single paste only fires onEdit once for the whole pasted range, so the timestamp still updates correctly, but e.range.getColumn() on a multi-column paste refers to the top-left cell of that paste.
- A paste that starts outside A-D but extends into it may not trigger a stamp, since getColumn() reads the start of the range
- Undoing an edit does not fire onEdit, so a stamp written earlier will remain even after the undo
- Clearing a cell's contents still counts as an edit and will refresh the Last Updated stamp
Testing
Edit a cell in column B on a fresh row and confirm both F and G populate; then edit column C on the same row and confirm only F updates while G stays untouched.
- Edit column E directly to confirm no stamp is written
- Clear a cell in column A and verify the stamp still refreshes
- Check row 1 stays untouched regardless of what's edited there
Hardening
The current guard only checks a single edited column, so a multi-column paste starting in column A and ending in column E would still register based on the start column.
- Check e.range.getLastColumn() as well as getColumn() to correctly detect multi-column pastes
- Add a check for e.range.getSheet().getName() at the very top to avoid running this on the wrong tab entirely
- Format columns F and G as date-time explicitly so the raw Date objects don't render as serial numbers for some users
Variations
The same watched-range pattern generalizes to any 'last touched by' tracking, not just timestamps - swap Date() for Session.getActiveUser().getEmail() to log who made the change instead of when.
- Log the editor's email alongside the timestamp using Session.getActiveUser()
- Track a per-column timestamp instead of one shared stamp for the whole row
- Combine with auto-sort-sheet-on-edit to resort the sheet by Last Updated after stamping
Full code: onEditAddTimestamp()
The function deliberately separates the columns it watches from the columns it writes, which is the core trick to writing a timestamp trigger that never triggers itself.
function onEditAddTimestamp(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Tasks') return;
var watchedStartColumn = 1;
var watchedEndColumn = 4;
var timestampColumn = 6;
var editedColumn = e.range.getColumn();
if (editedColumn < watchedStartColumn || editedColumn > watchedEndColumn) return;
var row = e.range.getRow();
if (row === 1) return;
var existingStamp = sheet.getRange(row, timestampColumn).getValue();
var newStamp = new Date();
sheet.getRange(row, timestampColumn).setValue(newStamp);
if (!existingStamp) {
var createdColumn = timestampColumn + 1;
sheet.getRange(row, createdColumn).setValue(newStamp);
}
}- Line 3: Limits the trigger to the Tasks sheet so other tabs with their own onEdit logic aren't affected.
- Line 7: Defines column F as the single timestamp column the function is allowed to write to.
- Line 10: Exits immediately if the edited column falls outside A-D, which is also what prevents the write below from causing a loop.
- Line 15: Reads the existing timestamp before overwriting it, the only way to detect a first-time edit.
- Line 18: Overwrites column F with the current moment on every qualifying edit.
- Line 20: Checks whether this row has ever been stamped before, based on the value read earlier.
- Line 22: Writes the Created On timestamp only once, the first time a row picks up any stamp at all.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before enabling the timestamp trigger
- 1Watched columns (A-D) and written columns (F, G) confirmed non-overlapping
- 2Sheet name check matches your real tab name
- 3Header row (row 1) excluded from stamping
- 4Date-time number format applied to F and G
- 5Trigger installed as onEdit, not run manually
- 6Tested with both single-cell edits and multi-cell pastes
Frequently asked questions
NOW() recalculates on every sheet recalculation, not just when the row changes, so every timestamp in the column would update simultaneously any time the spreadsheet recalculates - a script-written Date value only changes when that specific row is actually edited.
Yes - if the watched range and the written column overlap, the script's own write becomes a new edit event, which can loop until Apps Script's execution time limit kills it, so keep the two ranges separate.
Replace the range check with an explicit array of allowed column numbers and use indexOf to check membership instead of a min/max comparison.
new Date() captures the moment in UTC internally, but Sheets displays it using the spreadsheet's Locale and time zone settings under File > Settings, so it will appear correct without extra formatting code.
Apps Script processes onEdit events one at a time per script, so the second edit's stamp simply overwrites the first - there's no race condition, just a Last Updated value reflecting whichever edit was processed last.
Yes, but that requires a display formula in a separate column reading from the F timestamp, or a periodic script that recalculates a human-readable string like '3 days ago'.