Answers / Question 6

A timestamp that doesn't change: stamp the date when a cell is first filled

Formula tricks with NOW() can recalculate later. A small onEdit script writes a fixed date-time the first time a cell is filled, including pasted blocks.

The problem

You need the date and time recorded when an entry is made, and it must never move afterwards. A formula-based workaround seemed to work at first, but one day all the stamps jumped to the current time.

Inspired by a public question on Reddit (r/googlesheets). The problem above is described in our own words and the solution below is ours.

Why formula tricks drift

Workarounds that wrap NOW() in LAMBDA or rely on iterative calculation still contain a formula, and Sheets is free to recalculate it, for example when the file is reopened or rows are copied. Only a script writes a plain value that can't recalculate.

The fix

Extensions → Apps Script, paste the code, set the tab name and columns in STAMP.
Save. That's all: a function named onEdit runs on its own. Don't press Run (there's no edit to react to) and don't deploy anything.

Code.gsApps Script

// Write a fixed date-time into column C the first time something is entered in column B.
// Just save it: onEdit runs by itself. Don't press Run (there is no edit event then).
const STAMP = {
  sheetName: 'Log',
  watchCol: 2,       // B
  stampCol: 3,       // C
  overwrite: false   // true = refresh the stamp on every later edit too
};

function onEdit(e) {
  const r = e.range, sh = r.getSheet();
  if (sh.getName() !== STAMP.sheetName) return;
  if (r.getColumn() > STAMP.watchCol || r.getLastColumn() < STAMP.watchCol) return;   // edit didn't touch column B
  for (let row = Math.max(r.getRow(), 2); row <= r.getLastRow(); row++) {           // handles pasted blocks too
    const value = sh.getRange(row, STAMP.watchCol).getValue();
    const cell = sh.getRange(row, STAMP.stampCol);
    if (value !== '' && (STAMP.overwrite || cell.getValue() === '')) cell.setValue(new Date());
  }
}

Good to know

With overwrite: false, the first stamp is kept forever. Set it to true if you want “last edited” instead.
Scripts don't run for changes made by other scripts, imports or form submissions. Forms already write their own Timestamp column.
For a quick manual stamp, Ctrl+Alt+Shift+; (Cmd+Option+Shift+; on Mac) inserts the current date and time as a value.

The script's logic is unit-tested against a simulated spreadsheet before publishing. Always try new automations on a copy of your sheet first.