Answers / Question 7

Add a timestamp only when a dropdown is set to a specific value

An onEdit script that records the time in another column only when a dropdown changes to one value, such as Shipped, plus how to reuse it across many files.

The problem

A status dropdown tracks each job, and you want to capture the moment it reaches one particular stage. Other choices in the dropdown should be ignored, and you have several files with the same layout that all need the same behavior.

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

The fix

Paste this into Extensions → Apps Script, set the tab, the two column numbers and the trigger value, and save:

Code.gsApps Script

// Stamp the time in another column when a dropdown is set to one particular value.
const STATUS_STAMP = {
  sheetName: 'Sheet1',
  statusCol: 2,              // B: the dropdown
  stampCol: 5,               // E: where the time goes
  value: 'shipped',          // compared case-insensitively
  format: 'yyyy-mm-dd hh:mm'
};

function onEdit(e) {
  const r = e.range, sh = r.getSheet(), c = STATUS_STAMP;
  if (sh.getName() !== c.sheetName || r.getColumn() !== c.statusCol || r.getRow() < 2) return;
  if (r.getNumRows() !== 1 || r.getNumColumns() !== 1) return;
  if (String(e.value || '').trim().toLowerCase() !== c.value) return;
  sh.getRange(r.getRow(), c.stampCol).setValue(new Date()).setNumberFormat(c.format);
}

Using it in many files

Scripts attached to a spreadsheet belong to that file. The low-maintenance route is to set up one file with the script, keep it as your template, and create new files from it with File → Make a copy; the script comes along. Files you've already made need the script pasted in once each.

Good to know

The comparison ignores case and surrounding spaces, so Shipped and shipped both count.
Changing the status away and back again writes a new time. Pasting several cells at once is ignored on purpose.
Only one function can be called onEdit per project. If you already have one, move this logic inside it.

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