Build an audit log of every edit

Northwind’s Pricing sheet is shared with half the studio, and every so often a number changes that nobody admits to. Without a record, the conversation goes in circles — was it a typo, a deliberate update, a stale paste? — and the only fix is to lock the sheet down so hard that nobody can do their job.

An audit log gives you the middle ground. This trigger fires on every edit and writes one row to a hidden Audit tab: the timestamp, the editor, the sheet, the exact cell, the old value, and the new value. Nobody has to remember to log anything, and when a number looks wrong you can see who touched it and what it used to be.

What you’ll need

• The spreadsheet you want to monitor. Edit rights on it are enough — the script runs inside the spreadsheet itself.
• Nothing else. The script creates and hides the Audit tab on the first edit.

The script

// Name of the tab that stores the log. It is created and hidden
// automatically the first time an edit is recorded.
const AUDIT_TAB = 'Audit';

// The header row written to a freshly created Audit tab.
const AUDIT_HEADER = ['when', 'user', 'sheet', 'cell', 'old', 'new'];

/**
 * Records every edit to the spreadsheet on a hidden Audit tab. Wire this
 * up as an installable On edit trigger so it can read the editor's email.
 *
 * @param {Object} e The edit event passed in by the trigger.
 */
function onEdit(e) {
  // A manual run has no event object — there is nothing to log.
  if (!e || !e.range) return;

  const ss = SpreadsheetApp.getActive();
  const audit = getAuditSheet(ss);

  // Append one row describing this single edit.
  audit.appendRow([
    new Date(),                              // when the edit happened
    Session.getActiveUser().getEmail(),      // who made it
    e.range.getSheet().getName(),            // which tab
    e.range.getA1Notation(),                 // which cell
    e.oldValue || '',                        // value before the edit
    e.value || '',                           // value after the edit
  ]);
}

/**
 * Returns the Audit tab, creating and hiding it (with a header row) the
 * first time it is needed.
 *
 * @param {Spreadsheet} ss The active spreadsheet.
 * @return {Sheet} The Audit tab, ready to append to.
 */
function getAuditSheet(ss) {
  const existing = ss.getSheetByName(AUDIT_TAB);
  if (existing) return existing;

  // First run: create the tab, hide it, and lay down the header.
  const sheet = ss.insertSheet(AUDIT_TAB);
  sheet.hideSheet();
  sheet.appendRow(AUDIT_HEADER);
  return sheet;
}

How it works

1. onEdit is the entry point. It first checks for a real event object — a manual run from the editor has no e, so the function exits rather than logging a phantom edit.
2. getAuditSheet looks for the Audit tab. If it does not exist, it creates it, hides it from the tab bar so it stays out of the way, and writes the header row once.
3. Back in onEdit, a single row is appended capturing the moment: the timestamp, the editor’s email from Session.getActiveUser(), the tab name, the cell in A1 notation, and the values before and after.
4. e.oldValue and e.value are coalesced to empty strings, so clearing a cell or filling a previously blank one still logs a tidy row instead of undefined.

Example run

Suppose someone changes cell C4 on the Pricing tab from 49 to 59. A new row lands on the hidden Audit tab:

when	user	sheet	cell	old	new
2025-10-08 14:22:07	[email protected]	Pricing	C4	49	59

Clear that cell afterwards and a second row appears, with 59 in the old column and a blank new. The tab grows one row per edit, oldest at the top, so it reads as a plain chronological history.

Trigger it

A simple onEdit function only runs with the editor’s limited permissions and cannot reliably read who made the change. You need an installable trigger:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose onEdit, event source From spreadsheet, event type On edit.
3. Approve the authorisation prompt. From now on every edit is logged.

To review the log, right-click any tab, choose Show sheet, and pick Audit.

Watch out for

• Session.getActiveUser().getEmail() returns an empty string for users outside your Workspace domain. For external editors you will still see the change and its values, but the user column will be blank.
• Only an installable trigger can read the editor’s email. A plain onEdit saved in the project will fire, but the user column will come back empty every time.
• The log is append-only and never pruned. On a busy sheet the Audit tab grows steadily — archive or trim it every few months so the spreadsheet stays fast.
• Hiding the tab is not security. Any editor can unhide it and read or delete the log. For a tamper-proof record, write the rows to a separate spreadsheet that editors of the monitored sheet cannot open.
• Large bulk edits — a paste over many cells, or a fill-down — fire one event for the whole range, so cell may be a range like B2:B40 and old/new may be blank. The log captures the action, not every individual cell within it.