Answers / Question 4

Get an email when a formula's result changes in Google Sheets

Edit triggers ignore formula recalculations. Save a snapshot of the column and compare it on a timer to email only what changed.

The problem

The column you want to watch is filled by formulas, for example a status that depends on other tabs. You've set up an edit-based notification, but it stays quiet when those values change, because nobody is typing in that column.

Inspired by a public question on Web Applications Stack Exchange. The problem above is described in our own words and the solution below is ours.

Why edit triggers don't help

Edit triggers fire only for edits a person makes. A formula producing a new result isn't an edit, so no trigger fires. The reliable approach is to check on a schedule: keep a copy of what the column looked like last time, compare, and email the differences.

The fix

Paste the code and set the tab, column number and address in WATCH.
Run checkWatchedColumn once to approve permissions. The first run just saves a baseline and sends nothing.
Run installWatchTrigger once. From then on it checks every 15 minutes and emails the rows that changed, with the old and new value.

Code.gsApps Script

// Email when values in a formula-driven column change. Edit triggers never fire for
// formula recalculations, so this compares the column with a saved snapshot on a timer.
const WATCH = {
  sheetName: 'Sheet1',
  column: 21,               // column U
  firstRow: 2,
  to: '[email protected]',
  propertyKey: 'watch_snapshot'
};

function diffSnapshot(before, now, firstRow) {
  const changes = [];
  now.forEach(function (row, i) {
    const old = before[i] ? before[i][0] : '';
    if (row[0] !== old) changes.push('Row ' + (i + firstRow) + ': "' + old + '" -> "' + row[0] + '"');
  });
  return changes;
}

function checkWatchedColumn() {
  const sh = SpreadsheetApp.getActive().getSheetByName(WATCH.sheetName);
  const n = sh.getLastRow() - WATCH.firstRow + 1;
  if (n < 1) return;
  const now = sh.getRange(WATCH.firstRow, WATCH.column, n, 1).getDisplayValues();
  const props = PropertiesService.getDocumentProperties();
  const saved = props.getProperty(WATCH.propertyKey);
  if (saved) {                                    // first run only stores the baseline
    const changes = diffSnapshot(JSON.parse(saved), now, WATCH.firstRow);
    if (changes.length) MailApp.sendEmail(WATCH.to, sh.getName() + ': ' + changes.length + ' value(s) changed', changes.join('\n'));
  }
  props.setProperty(WATCH.propertyKey, JSON.stringify(now));
}

// Run once: checks every 15 minutes.
function installWatchTrigger() {
  ScriptApp.newTrigger('checkWatchedColumn').timeBased().everyMinutes(15).create();
}

Good to know

Document properties hold about 9 KB per value. For a few hundred rows that's fine; for thousands, store the snapshot in a hidden tab instead.
Inserting or deleting rows shifts everything below, so the next check will report those rows as changed once.
Time-driven triggers run roughly on schedule, not to the second.

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