Two-Way Sync for Google Sheets

I built the first version of this for a referral system that had outgrown a single spreadsheet: one intake tracker, several team members who each needed their own view, and no appetite for a database migration to get there. What follows is the general-purpose version of that engine, rebuilt column-agnostic so it fits whatever you're tracking, not just referrals.

By the end you'll have a master Google Sheet that fans rows out to however many child spreadsheets you register, syncing whichever columns you choose in whichever direction you choose, plus a generated SOP you can hand to whoever else touches the system. Everything here is real code, verified against real spreadsheets before this page was written from it.

Why two-way sync doesn't loop

The first question anyone asks about two-way sync is the right one: if the master writes to the child, and the child writes back to the master, what stops that from bouncing forever?

The answer is a property of Apps Script itself, not something this engine has to build: a script-made change to a spreadsheet does not fire that spreadsheet's own edit trigger. When this engine writes a value into a cell, that write is a script action, so it never re-triggers the handler that made it, or any other handler. Only a genuine edit, made by a human through the Sheets UI, starts a sync in motion. A script write is a leaf node, not a branch.

That single fact is most of why this is possible to build safely. It doesn't mean two-way sync has no sharp edges, two people editing the same field at nearly the same moment is a real case this engine has to handle deliberately, covered in Step 9 and What this doesn't do, but the loop question itself is closed, structurally, before any of that.

Why one script is safer than many

The obvious architecture for "sync N spreadsheets" is N separate scripts, one bound to each child, plus a shared library they all import. I built it that way first. Here's why it's wrong.

A script bound to a spreadsheet can be edited by anyone with edit access to that spreadsheet, through Extensions › Apps Script. An installable trigger on that script runs with the authority of whoever created the trigger, not whoever triggered it. Put those two facts together: if a child spreadsheet has its own bound script, anyone editing that child sheet can edit code that then runs with the admin's authority against the master and every other child. That's a privilege escalation sitting in the architecture, not a bug you'd find by testing the happy path.

This engine avoids that by construction: one Apps Script project, bound to the master spreadsheet, and nothing at all on any child. There's no code on a child sheet for anyone to edit, because there's no script there. Every sync handler runs from the master's project, under the one account that deployed it.

What this costs you

Every child needs a trigger registered on the master's script, and Apps Script caps a script at 20 triggers per user. One goes to the master's own edit trigger, one to a daily maintenance job, leaving a hard ceiling of 18 children. Step 10 covers what that means in practice and why there's no clean workaround.

The shape of the system

One master spreadsheet. Up to eighteen child spreadsheets. One Apps Script project, bound to the master, with an installable trigger watching the master and one watching each registered child. A new row starts life in the master (or, once you're on Level 2, in a child, see Step 12), gets a unique ID, and routes to exactly one child based on a column you choose. Edits flow down to whichever fields you've declared as master-to-child, up for child-to-master, or both ways for fields that can be edited from either side.

Nothing lives outside that: no database, no server, no deployment. The master spreadsheet's Apps Script project is the whole system.

The FIELDS model

Every other spreadsheet sync tool I've seen assumes a fixed column layout: column D always means this, column K always means that. That breaks the moment your master and your child sheets use different column orders, which they will, because different teams organize things differently.

This engine is keyed on header text instead of column position, declared once per field in Config.js:

Config.js (excerpt)

var FIELDS = [
  { key: 'Unique ID', sync: 'id' },
  { key: 'Assigned To', sync: 'route' },
  { key: 'Date Inquired', sync: 'down' },
  { key: 'Client', sync: 'down' },
  { key: 'Status', sync: 'up' },
  { key: 'Notes', sync: 'both' },
  { master: 'Date of Consult', child: 'Consult Date', sync: 'up' }
];

A field with the same header on both sides uses key. A field whose header text differs between the master and its children (the last row above) uses master and child instead. sync is one of four directions:

Direction	What it means
`down`	Master to child. Edit it in the master; every child gets the update.
`up`	Child to master. Edit it in a child; the master gets the update.
`both`	Either side can edit it. Both sides converge to whichever edit lands first.
`route`	Master-only. Its value decides which child a row belongs to.

Any column you don't declare in FIELDS is left alone entirely, on both sides. Add a private scratch column to your own copy and nothing about it ever touches the engine.

Be honest with yourself about both

A both field converges: after a burst of edits from either side settles, master and child end up equal. But if the two sides are edited within moments of each other, which edit survives is not something you can predict or control. Use both for fields where "one of us gets the last word" is fine. For anything where you genuinely need to know whose edit won, split it into two fields instead: one down, one up.

0Quick check

If you haven't already, work through Set up Node, clasp, and Apps Script access first, it's a five-minute one-time setup this guide assumes is done. Then confirm:

terminal

node -v
npm -v
clasp -v
clasp show-authorized-user

Four version numbers and an email address, no errors. If any of those fail, that page's troubleshooting section covers the common causes.

1Create your sheets

Create your master Google Sheet and at least one child spreadsheet, separate files, not tabs in the same file. In each, the tab that holds your actual rows needs the same name on the master and on every child; the engine defaults to calling it Tracker, and you can rename that default in Config.js if you'd rather call it something else.

Add whatever columns your business actually tracks. Before you move on, decide three things:

Which column is the unique ID. Add an empty column for it now, something like Unique ID. The engine fills it in automatically; you never type into it by hand.
Which column decides where a row goes. This one lives on the master only, never on a child, something like Assigned To.
For every other column, which direction it should sync: master to child, child to master, or both. Note the exact header text on each side, especially anywhere a child sheet uses different wording for the same thing.

Grab the spreadsheet ID for the master and every child now too, it's the long string in the URL between /d/ and /edit. You'll need the master's in Step 3 and every child's in Step 6.

2Scaffold the directory

Create a project folder and clone this engine's src/ layout into it:

terminal

mkdir sheet-sync && cd sheet-sync
mkdir src

3Clone the master's bound script

Open your master spreadsheet in the browser, then Extensions › Apps Script. That opens a script project already bound to your spreadsheet, empty for now. Copy its script ID from the editor's URL (the segment after /d/), then clone it locally:

terminal

clasp clone <scriptId> --rootDir src

Don't try to create a project instead

clasp create makes a new spreadsheet; it can't bind to one that already exists. Cloning the ID from an existing bound script, as above, is the only path that attaches this engine to the sheet you already built in Step 1.

4Paste the engine

Nine files, unedited, exactly as shown. This is the part where you resist the urge to let an AI agent "clean up" or "improve" anything, sync logic that looks simple is usually simple because a hard case already got handled; rewriting it tends to silently remove the handling, not the complexity. Create each file below inside src/ with the exact name shown and paste its contents in full.

Config.js The FIELDS map, sentinels, tab names, and the child resolver. (edit this one in Step 5)

src/Config.js

/**
 * Config.js — the FIELDS map, sentinels, tab names, and resolveChild().
 *
 * This is the file you edit for your own sheet. Everything else in this
 * engine reads FIELDS and SENTINELS; nothing else needs to change to adapt
 * the engine to a different set of columns.
 */

// Declare every synced field once, keyed by header text (case-insensitive,
// trimmed at resolution time). A field with the same header on both sides
// uses `key`; a field whose master and child headers differ uses
// `master`/`child` instead. Any column NOT declared here is local to
// whichever sheet it lives on and is never read or written by the engine.
// A field becomes a dropdown by naming it in the Field Options tab (see
// FIELD_OPTIONS_TAB below) — not here. Dropdown VALUES are what changes
// often, so they live in a sheet a non-technical owner can edit directly,
// the same reasoning as the Runbook tab: no clasp push to update a list.
var FIELDS = [
  { key: 'Unique ID', sync: 'id' },
  { key: 'Assigned To', sync: 'route' },
  { key: 'Date Inquired', sync: 'down' },
  { key: 'Client', sync: 'down' },
  { key: 'Status', sync: 'up' },
  { key: 'Notes', sync: 'both' },
  { master: 'Date of Consult', child: 'Consult Date', sync: 'up' }
];

// Route values that keep a row in the master and remove it from every
// child. Add your own here (e.g. 'On Hold') if you need more than two.
var SENTINELS = ['Waitlisted', 'Closed'];

// Tab names. DATA_TAB must exist, with this name, on the master and on
// every child. The other tabs live on the master only and are created
// automatically by setup() if missing. KEY_TAB's title is "Assigned To"
// (matching the route field's own header) so its purpose reads plainly
// in the sheet UI, even though the code still calls it KEY_TAB internally
// (its role — the roster lookup table — hasn't changed, only the label).
var DATA_TAB = 'Tracker';
var KEY_TAB = 'Assigned To';
var LOG_TAB = 'Sync Log';
var RUNBOOK_TAB = 'Runbook';
var DRIFT_LOG_TAB = 'Drift Log';
var FIELD_OPTIONS_TAB = 'Dropdown Menu Config';

// Script lock timeout for every read-modify-write in Sync.js, in ms.
var LOCK_TIMEOUT_MS = 30000;

// Apps Script's hard per-user trigger ceiling is 20. One trigger goes to
// the master itself and one to the daily reconciliation clock, which caps
// registered children at 18 regardless of what the live count says.
var TRIGGER_QUOTA = 20;
var RESERVED_TRIGGERS = 2; // master onEdit + daily reconciliation clock
var MAX_CHILDREN = TRIGGER_QUOTA - RESERVED_TRIGGERS;

/**
 * Default child resolver. Reads the route field's value from a row object
 * (already normalized to field keys — see Schema.readRow_) and returns the
 * child's registered name, or null to hold the row in the master.
 *
 * Swap this function's body for load-balancing, region routing, etc. Its
 * signature (row object in, child name or null out) is the only contract
 * the rest of the engine depends on.
 */
function resolveChild(row) {
  var routeVal = row['Assigned To'];
  if (!routeVal) return null;
  if (SENTINELS.indexOf(routeVal) !== -1) return null;
  return routeVal;
}

/** Field declared with `sync: 'route'`. Exactly one is expected. */
function routeField_() {
  for (var i = 0; i < FIELDS.length; i++) {
    if (FIELDS[i].sync === 'route') return FIELDS[i];
  }
  return null;
}

/** Field declared with `sync: 'id'`. Exactly one is expected. */
function idField_() {
  for (var i = 0; i < FIELDS.length; i++) {
    if (FIELDS[i].sync === 'id') return FIELDS[i];
  }
  return null;
}

/** Header text a field uses on a given side ('master' or 'child'). */
function fieldHeader_(field, side) {
  if (field.key) return field.key;
  return side === 'master' ? field.master : field.child;
}

function normalizeHeader_(text) {
  return String(text || '').trim().toLowerCase();
}

/**
 * The master spreadsheet — never SpreadsheetApp.getActive() anywhere else
 * in this engine. Confirmed empirically: inside an installable trigger
 * fired by an EXTERNAL spreadsheet (i.e. every handleChildEdit call),
 * getActive() returns the CHILD that fired the event, not the spreadsheet
 * this script is bound to. Every handler that needs "the master" goes
 * through this instead, which resolves it from Script Properties —
 * seeded once, reliably, by setup() (which only ever runs from a genuine
 * menu click on the master itself, where getActive() is unambiguous).
 */
function getMaster_() {
  var id = PropertiesService.getScriptProperties().getProperty('MASTER_SPREADSHEET_ID');
  if (id) return SpreadsheetApp.openById(id);
  return SpreadsheetApp.getActive(); // only correct before setup() has ever run
}

Schema.js Header resolution, the two validators, and dropdown menus. (paste as-is)

src/Schema.js

/**
 * Schema.js — header resolution against FIELDS, the Key-tab roster reader,
 * the two validators (validateSchema, validateData), and dropdown
 * validation (applyDropdowns_).
 */

/**
 * Row 1 of `sheet` as {normalizedHeader: [1-based col, ...]}. An array
 * value (length > 1) means that header appears more than once — the
 * caller decides whether that matters for the field it's resolving.
 */
function getHeaderMap_(sheet) {
  var lastCol = sheet.getLastColumn();
  if (lastCol === 0) return {};
  var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  var map = {};
  headers.forEach(function (raw, i) {
    var norm = normalizeHeader_(raw);
    if (!norm) return; // blank header: fine if undeclared, checked separately
    if (!map[norm]) map[norm] = [];
    map[norm].push({ col: i + 1, raw: raw });
  });
  return map;
}

/**
 * Resolves one field's column on one side. Returns {col, raw} on a clean
 * single match, {error: 'missing'} if the header isn't present, or
 * {error: 'duplicate'} if it appears more than once on that side.
 */
function resolveFieldColumn_(headerMap, field, side) {
  var header = fieldHeader_(field, side);
  var norm = normalizeHeader_(header);
  var matches = headerMap[norm];
  if (!matches || matches.length === 0) return { error: 'missing' };
  if (matches.length > 1) return { error: 'duplicate' };
  return { col: matches[0].col, raw: matches[0].raw };
}

/**
 * True only if every declared field resolves cleanly on this side. A
 * handler must check this before touching any row: resolveFieldColumn_
 * failing for one field still lets the others resolve, and a caller that
 * only checks id/route (as the edit handlers used to) lets an unresolved
 * field's value reach writeFieldValue_ as undefined, which silently
 * blanks the destination cell instead of failing closed (decision 1).
 */
function allFieldsResolve_(headerMap, side) {
  for (var i = 0; i < FIELDS.length; i++) {
    var field = FIELDS[i];
    if (field.sync === 'route' && side === 'child') continue; // never present on a child
    if (resolveFieldColumn_(headerMap, field, side).error) return false;
  }
  return true;
}

/**
 * Reads Key tab rows 2..lastRow as {name, spreadsheetId}. Does not
 * validate uniqueness — call validateData_() for that. Returns
 * {ok, children, issues} so a missing Key tab degrades instead of
 * throwing (matters for S3: SOP with zero children registered).
 */
function getChildRoster_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(KEY_TAB);
  if (!sheet) return { ok: true, children: [], issues: [] };
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return { ok: true, children: [], issues: [] };
  var rows = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  var children = [];
  rows.forEach(function (r) {
    var name = String(r[0] || '').trim();
    var id = String(r[1] || '').trim();
    if (!name && !id) return; // blank row, skip
    children.push({ name: name, spreadsheetId: id });
  });
  return { ok: true, children: children, issues: [] };
}

/** Field's canonical identity — the master-side header text. Used as the key in row objects regardless of which side they were read from. */
function canonicalName_(field) {
  return field.key || field.master;
}

/**
 * Reads one data row into a canonical-name-keyed object, translating
 * per-side header names (e.g. child's "Consult Date" lands under the
 * master's "Date of Consult" key). Only declared fields are read — this
 * is deliberate: undeclared columns must never reach the rest of the
 * engine, since Sop.js's collector guarantee depends on nothing upstream
 * of it ever touching row data outside FIELDS.
 */
function readRow_(sheet, headerMap, side, rowIndex) {
  var lastCol = sheet.getLastColumn();
  var values = sheet.getRange(rowIndex, 1, 1, lastCol).getValues()[0];
  var row = {};
  FIELDS.forEach(function (field) {
    if (field.sync === 'route' && side === 'child') return; // never present on a child
    var resolved = resolveFieldColumn_(headerMap, field, side);
    if (resolved.error) return; // caller's schema check surfaces this; reading stays silent
    row[canonicalName_(field)] = values[resolved.col - 1];
  });
  return row;
}

/**
 * Full cross-child schema check. Menu action. Returns every problem found
 * rather than stopping at the first, since a beginner fixing one header
 * at a time against a single error is a bad loop.
 */
function validateSchema() {
  var issues = validateSchema_();
  var ui = SpreadsheetApp.getUi();
  if (issues.length === 0) {
    ui.alert('validateSchema()', 'No issues found. Master and every registered child resolve cleanly against FIELDS.', ui.ButtonSet.OK);
  } else {
    ui.alert('validateSchema() — ' + issues.length + ' issue(s)', issues.join('\n'), ui.ButtonSet.OK);
  }
}

function validateSchema_() {
  var issues = [];
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  if (!masterSheet) {
    return ['Master is missing its "' + DATA_TAB + '" tab.'];
  }

  // Config-level: duplicate field declarations (same canonical name twice).
  var seenNames = {};
  FIELDS.forEach(function (field) {
    var name = normalizeHeader_(canonicalName_(field));
    if (seenNames[name]) issues.push('FIELDS declares "' + canonicalName_(field) + '" more than once.');
    seenNames[name] = true;
  });

  var masterHeaderMap = getHeaderMap_(masterSheet);
  issues = issues.concat(duplicateHeaderIssues_(masterHeaderMap, 'master (' + DATA_TAB + ')'));

  var masterCols = {};
  FIELDS.forEach(function (field) {
    var resolved = resolveFieldColumn_(masterHeaderMap, field, 'master');
    if (resolved.error === 'missing') {
      issues.push('Master: field "' + canonicalName_(field) + '" (' + field.sync + ') has no matching header.');
    } else if (resolved.error === 'duplicate') {
      issues.push('Master: header "' + fieldHeader_(field, 'master') + '" appears more than once — field "' + canonicalName_(field) + '" is ambiguous.');
    } else {
      if (masterCols[resolved.col]) {
        issues.push('Master: columns for "' + masterCols[resolved.col] + '" and "' + canonicalName_(field) + '" both resolved to the same column.');
      }
      masterCols[resolved.col] = canonicalName_(field);
    }
  });

  var roster = getChildRoster_();
  var masterTz = master.getSpreadsheetTimeZone();

  roster.children.forEach(function (child) {
    var childSs;
    try {
      childSs = SpreadsheetApp.openById(child.spreadsheetId);
    } catch (e) {
      issues.push('Child "' + child.name + '": cannot open spreadsheet ' + child.spreadsheetId + ' (' + e.message + ').');
      return;
    }
    var childSheet = childSs.getSheetByName(DATA_TAB);
    if (!childSheet) {
      issues.push('Child "' + child.name + '": missing its "' + DATA_TAB + '" tab.');
      return;
    }
    var childHeaderMap = getHeaderMap_(childSheet);
    issues = issues.concat(duplicateHeaderIssues_(childHeaderMap, 'child "' + child.name + '"'));

    var childCols = {};
    FIELDS.forEach(function (field) {
      if (field.sync === 'route') {
        var routeResolved = resolveFieldColumn_(childHeaderMap, field, 'master'); // route's only header is the master one
        if (!routeResolved.error) {
          issues.push('Child "' + child.name + '": route field "' + fieldHeader_(field, 'master') + '" must not exist on a child sheet.');
        }
        return;
      }
      var resolved = resolveFieldColumn_(childHeaderMap, field, 'child');
      if (resolved.error === 'missing') {
        issues.push('Child "' + child.name + '": field "' + canonicalName_(field) + '" (' + field.sync + ') has no matching header ("' + fieldHeader_(field, 'child') + '").');
      } else if (resolved.error === 'duplicate') {
        issues.push('Child "' + child.name + '": header "' + fieldHeader_(field, 'child') + '" appears more than once — field "' + canonicalName_(field) + '" is ambiguous.');
      } else {
        if (childCols[resolved.col]) {
          issues.push('Child "' + child.name + '": columns for "' + childCols[resolved.col] + '" and "' + canonicalName_(field) + '" both resolved to the same column.');
        }
        childCols[resolved.col] = canonicalName_(field);
      }
    });

    var childTz = childSs.getSpreadsheetTimeZone();
    if (childTz !== masterTz) {
      issues.push('Child "' + child.name + '": time zone (' + childTz + ') does not match master (' + masterTz + '). Fix this deliberately in the child’s spreadsheet settings — the engine will not change it for you.');
    }
  });

  return issues;
}

function duplicateHeaderIssues_(headerMap, label) {
  var issues = [];
  for (var norm in headerMap) {
    if (headerMap[norm].length > 1) {
      var raws = headerMap[norm].map(function (m) { return m.raw; }).join('", "');
      issues.push(label + ': header "' + raws + '" is duplicated (' + headerMap[norm].length + ' columns normalize to "' + norm + '").');
    }
  }
  return issues;
}

/**
 * Data-integrity check across master and the Key tab. Menu action.
 * Does not touch child data tabs — cross-sheet placement is the
 * reconciliation sweep's job (Sync.js), not this one's.
 */
function validateData() {
  var issues = validateData_();
  var ui = SpreadsheetApp.getUi();
  if (issues.length === 0) {
    ui.alert('validateData()', 'No issues found.', ui.ButtonSet.OK);
  } else {
    ui.alert('validateData() — ' + issues.length + ' issue(s)', issues.join('\n'), ui.ButtonSet.OK);
  }
}

function validateData_() {
  var issues = [];
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  if (!masterSheet) return ['Master is missing its "' + DATA_TAB + '" tab.'];

  var headerMap = getHeaderMap_(masterSheet);
  var idFld = idField_();
  var routeFld = routeField_();
  var idResolved = resolveFieldColumn_(headerMap, idFld, 'master');
  var routeResolved = resolveFieldColumn_(headerMap, routeFld, 'master');
  if (idResolved.error || routeResolved.error) {
    return ['validateSchema() must pass before validateData() can run — id or route column did not resolve.'];
  }

  var roster = getChildRoster_();
  var rosterByNormName = {};
  var rosterIdCount = {};
  roster.children.forEach(function (c) {
    var norm = normalizeHeader_(c.name);
    if (!rosterByNormName[norm]) rosterByNormName[norm] = [];
    rosterByNormName[norm].push(c);
    rosterIdCount[c.spreadsheetId] = (rosterIdCount[c.spreadsheetId] || 0) + 1;
  });

  for (var norm in rosterByNormName) {
    var group = rosterByNormName[norm];
    var distinctIds = {};
    group.forEach(function (c) { distinctIds[c.spreadsheetId] = true; });
    if (Object.keys(distinctIds).length > 1) {
      issues.push('Key tab: name "' + group[0].name + '" is registered with more than one spreadsheet ID (' + Object.keys(distinctIds).join(', ') + ') — resolveChild() would be ambiguous.');
    } else if (group.length > 1) {
      issues.push('Key tab: name "' + group[0].name + '" is registered more than once.');
    }
  }
  for (var id in rosterIdCount) {
    if (rosterIdCount[id] > 1) {
      issues.push('Key tab: spreadsheet ID ' + id + ' is registered under more than one name.');
    }
  }
  roster.children.forEach(function (c) {
    if (SENTINELS.indexOf(c.name) !== -1) {
      issues.push('Key tab: child name "' + c.name + '" collides with a sentinel value. Rename the child or the sentinel.');
    }
  });

  var lastRow = masterSheet.getLastRow();
  if (lastRow >= 2) {
    var idVals = masterSheet.getRange(2, idResolved.col, lastRow - 1, 1).getValues();
    var routeVals = masterSheet.getRange(2, routeResolved.col, lastRow - 1, 1).getValues();
    var seenIds = {};
    for (var i = 0; i < idVals.length; i++) {
      var uid = String(idVals[i][0] || '').trim();
      var route = String(routeVals[i][0] || '').trim();
      var rowNum = i + 2;
      if (!uid && !route) continue; // fully blank row
      if (!uid) {
        issues.push('Master row ' + rowNum + ': blank Unique ID.');
      } else if (seenIds[uid]) {
        issues.push('Master row ' + rowNum + ': duplicate Unique ID "' + uid + '" (also row ' + seenIds[uid] + ').');
      } else {
        seenIds[uid] = rowNum;
      }
      if (route && SENTINELS.indexOf(route) === -1 && !rosterByNormName[normalizeHeader_(route)]) {
        issues.push('Master row ' + rowNum + ': "' + route + '" is not a sentinel and not a registered child name.');
      }
    }
  }

  return issues;
}

/** Prints the resolved header -> column-letter map for master and every registered child. */
function showSchema() {
  var lines = [];
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  lines.push('Master (' + DATA_TAB + '):');
  lines.push(schemaLines_(getHeaderMap_(masterSheet), 'master'));

  var roster = getChildRoster_();
  roster.children.forEach(function (child) {
    lines.push('');
    lines.push('Child "' + child.name + '":');
    try {
      var childSheet = SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
      lines.push(schemaLines_(getHeaderMap_(childSheet), 'child'));
    } catch (e) {
      lines.push('  (could not open: ' + e.message + ')');
    }
  });

  SpreadsheetApp.getUi().alert('showSchema()', lines.join('\n'), SpreadsheetApp.getUi().ButtonSet.OK);
}

function schemaLines_(headerMap, side) {
  var out = [];
  FIELDS.forEach(function (field) {
    if (field.sync === 'route' && side === 'child') return;
    var resolved = resolveFieldColumn_(headerMap, field, side);
    var letter = resolved.error ? '(' + resolved.error + ')' : columnToLetter_(resolved.col);
    out.push('  ' + canonicalName_(field) + ' [' + field.sync + '] -> ' + letter);
  });
  return out.join('\n');
}

function columnToLetter_(col) {
  var letter = '';
  while (col > 0) {
    var rem = (col - 1) % 26;
    letter = String.fromCharCode(65 + rem) + letter;
    col = Math.floor((col - 1) / 26);
  }
  return letter;
}

/**
 * Menu action: Refresh dropdowns. Applies applyDropdowns_() and reports
 * any Field Options rows that didn't match a declared field.
 */
function applyDropdowns() {
  var issues = applyDropdowns_();
  var ui = SpreadsheetApp.getUi();
  if (issues.length === 0) {
    ui.alert('Refresh dropdowns', 'Done. Route dropdown derived from the Key tab; field dropdowns applied from the Field Options tab.', ui.ButtonSet.OK);
  } else {
    ui.alert('Refresh dropdowns — ' + issues.length + ' issue(s)', issues.join('\n'), ui.ButtonSet.OK);
  }
}

/**
 * The route field always gets a DERIVED list — registered child names
 * (Key tab) plus SENTINELS — so it can never drift out of sync with the
 * roster the way a hand-maintained dropdown would. Every other row in
 * the Field Options tab names a declared field and a comma-separated
 * option list, applied to the master and to every registered child
 * where that field resolves. Returns unmatched-field-name issues rather
 * than throwing, so one typo doesn't stop every other dropdown from
 * refreshing.
 */
function applyDropdowns_() {
  var issues = [];
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  if (!masterSheet) return issues;
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var roster = getChildRoster_();

  var routeResolved = resolveFieldColumn_(masterHeaderMap, routeField_(), 'master');
  if (!routeResolved.error) {
    var routeOptions = roster.children.map(function (c) { return c.name; }).concat(SENTINELS);
    if (routeOptions.length > 0) setColumnDropdown_(masterSheet, routeResolved.col, routeOptions);
  }

  var fieldsByName = {};
  FIELDS.forEach(function (field) {
    fieldsByName[normalizeHeader_(canonicalName_(field))] = field;
  });

  readFieldOptions_().forEach(function (row) {
    var field = fieldsByName[normalizeHeader_(row.fieldName)];
    if (!field) {
      issues.push('Field Options: "' + row.fieldName + '" does not match any field declared in FIELDS.');
      return;
    }
    if (field.sync === 'route' || field.sync === 'id') {
      issues.push('Field Options: "' + row.fieldName + '" is the ' + field.sync + ' field, which ignores Field Options — its dropdown is always derived.');
      return;
    }

    var masterFieldResolved = resolveFieldColumn_(masterHeaderMap, field, 'master');
    if (!masterFieldResolved.error) setColumnDropdown_(masterSheet, masterFieldResolved.col, row.options);

    roster.children.forEach(function (child) {
      try {
        var childSheet = SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
        if (!childSheet) return;
        var childHeaderMap = getHeaderMap_(childSheet);
        var resolved = resolveFieldColumn_(childHeaderMap, field, 'child');
        if (!resolved.error) setColumnDropdown_(childSheet, resolved.col, row.options);
      } catch (e) {
        // unreadable child; healthCheck() surfaces this elsewhere
      }
    });
  });

  return issues;
}

/** Field Options tab rows as {fieldName, options}, skipping blank rows. */
function readFieldOptions_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(FIELD_OPTIONS_TAB);
  if (!sheet) return [];
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return [];
  var rows = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  var out = [];
  rows.forEach(function (r) {
    var fieldName = String(r[0] || '').trim();
    var raw = String(r[1] || '').trim();
    if (!fieldName || !raw) return;
    var options = raw.split(',').map(function (o) { return o.trim(); }).filter(function (o) { return o; });
    if (options.length > 0) out.push({ fieldName: fieldName, options: options });
  });
  return out;
}

/**
 * Warning-only, never rejecting (setAllowInvalid(true)). A rejecting rule
 * throws on Range.setValue() too, not just UI typing — confirmed live: it
 * crashed the reconciliation sweep mid-pass on an ordinary blank Status
 * field with no Sync Log entry at all, since the throw is uncaught. The
 * dropdown is a data-entry convenience; it must never be able to abort a
 * sync write.
 */
function setColumnDropdown_(sheet, col, options) {
  var range = sheet.getRange(2, col, Math.max(sheet.getMaxRows() - 1, 1), 1);
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).setAllowInvalid(true).build();
  range.setDataValidation(rule);
}

Sync.js The edit handlers, the lock, route moves, and the reconciliation sweep. (paste as-is)

src/Sync.js

/**
 * Sync.js — the edit handlers, the lock contract, route moves, the
 * reconciliation sweep, and orphan recovery.
 *
 * One deliberate simplification runs through this whole file: instead of
 * tracking exactly which cell an edit touched, every handler re-syncs the
 * FULL set of declared fields (in the owning direction) for every row the
 * edit's range intersects. This is what makes multi-cell paste, fill-down,
 * and clear-contents correct without special-casing them, and it costs
 * nothing extra: re-writing an already-equal value is a no-op in effect,
 * and undeclared columns are never read or written regardless.
 */

// ---------------------------------------------------------------------
// Lock contract — the single code path every handler funnels through.
// Acquire, run the caller's function, flush, release in finally. This is
// asserted structurally (test 21b): there is exactly one place that calls
// flush() immediately before releaseLock(), and this is it.
// ---------------------------------------------------------------------
function withLock_(operation, fn) {
  var lock = LockService.getScriptLock();
  var acquired = false;
  try {
    acquired = lock.tryLock(LOCK_TIMEOUT_MS);
  } catch (e) {
    acquired = false;
  }
  if (!acquired) {
    appendLog_('', getMaster_().getId(), operation, STAGES.LOCK, MESSAGES.LOCK_TIMEOUT);
    return;
  }
  try {
    fn();
  } finally {
    try {
      SpreadsheetApp.flush();
    } finally {
      lock.releaseLock();
    }
  }
}

// ---------------------------------------------------------------------
// Handlers
// ---------------------------------------------------------------------

function handleMasterEdit(e) {
  if (!e || !e.range) return;
  var sheet = e.range.getSheet();
  if (sheet.getName() !== DATA_TAB) return; // gate: Key/Sync Log/Runbook/Drift Log/other tabs are never touched
  withLock_(OPERATIONS.MASTER_EDIT, function () {
    processMasterEditLocked_(e, sheet);
  });
}

function handleChildEdit(e) {
  if (!e || !e.range) return;
  var sheet = e.range.getSheet();
  if (sheet.getName() !== DATA_TAB) return;
  withLock_(OPERATIONS.CHILD_EDIT, function () {
    processChildEditLocked_(e, sheet);
  });
}

function processMasterEditLocked_(e, sheet) {
  var headerMap = getHeaderMap_(sheet);
  var idFld = idField_();
  var routeFld = routeField_();
  var idResolved = resolveFieldColumn_(headerMap, idFld, 'master');
  var routeResolved = resolveFieldColumn_(headerMap, routeFld, 'master');
  if (idResolved.error || routeResolved.error || !allFieldsResolve_(headerMap, 'master')) {
    appendLog_('', getMaster_().getId(), OPERATIONS.MASTER_EDIT, STAGES.VALIDATE, MESSAGES.SCHEMA_FIELD_MISSING);
    return;
  }

  editedRows_(e.range).forEach(function (rowIndex) {
    processMasterRow_(sheet, headerMap, idResolved.col, rowIndex, e.range, e.oldValue);
  });
}

/**
 * `oldValue` is Apps Script's e.oldValue — populated only when the edit was
 * a genuine single-cell edit, undefined otherwise (multi-cell paste, fill,
 * clear). It is the only source we have for "what was the route before
 * this edit", since the sheet itself already reflects the new value by the
 * time onEdit fires.
 */
function processMasterRow_(sheet, headerMap, idCol, rowIndex, editedRange, oldValue) {
  var masterId = getMaster_().getId();
  var idFld = idField_();

  if (rangeIntersectsColumn_(editedRange, rowIndex, idCol) &&
      rowIsEstablished_(sheet, headerMap, 'master', rowIndex, canonicalName_(idFld))) {
    appendLog_('', masterId, OPERATIONS.MASTER_EDIT, STAGES.WRITE, MESSAGES.UID_PROTECTED_EDIT_REJECTED);
    return;
  }

  var row = readRow_(sheet, headerMap, 'master', rowIndex);
  var uid = row[canonicalName_(idFld)];

  if (!uid) {
    if (!rowHasOtherDeclaredContent_(row, canonicalName_(idFld))) return; // truly blank row, nothing to do
    uid = Utilities.getUuid();
    sheet.getRange(rowIndex, idCol).setValue(uid); // script write; does not re-fire onEdit
    SpreadsheetApp.flush();
    row[canonicalName_(idFld)] = uid;
  }

  var newChild = resolveChild(row);
  var oldChild = null;
  var routeResolved = resolveFieldColumn_(headerMap, routeField_(), 'master');
  if (typeof oldValue !== 'undefined' &&
      editedRange.getNumRows() === 1 && editedRange.getNumColumns() === 1 &&
      editedRange.getRow() === rowIndex && !routeResolved.error && editedRange.getColumn() === routeResolved.col) {
    oldChild = resolveChild({ 'Assigned To': oldValue });
  }

  if (newChild) {
    var childCtx = openChildByName_(newChild);
    if (!childCtx) {
      return; // openChildByName_ already logged CHILD_UNREADABLE; don't touch the old child until the new one is reachable
    }
    upsertChildFromMaster_(childCtx, row, uid);
    SpreadsheetApp.flush();
    var verifyRow = findRowByUid_(childCtx.sheet, childCtx.headerMap, 'child', uid);
    if (verifyRow === -1) {
      appendLog_(uid, childCtx.spreadsheetId, OPERATIONS.ROUTE_MOVE, STAGES.VERIFY, MESSAGES.CHILD_UNREADABLE);
      return;
    }
  }

  if (oldChild && oldChild !== newChild) {
    var oldCtx = openChildByName_(oldChild);
    if (oldCtx) {
      deleteRowByUid_(oldCtx.sheet, oldCtx.headerMap, 'child', uid);
      SpreadsheetApp.flush();
    }
  }
}

function processChildEditLocked_(e, sheet) {
  var childId = e.source.getId();
  var headerMap = getHeaderMap_(sheet);
  var idFld = idField_();
  var idResolved = resolveFieldColumn_(headerMap, idFld, 'child');
  if (idResolved.error || !allFieldsResolve_(headerMap, 'child')) {
    appendLog_('', childId, OPERATIONS.CHILD_EDIT, STAGES.VALIDATE, MESSAGES.SCHEMA_FIELD_MISSING);
    return;
  }

  editedRows_(e.range).forEach(function (rowIndex) {
    processChildRow_(sheet, headerMap, idResolved.col, rowIndex, e.range, childId);
  });
}

function processChildRow_(sheet, headerMap, idCol, rowIndex, editedRange, childId) {
  var idFld = idField_();

  if (rangeIntersectsColumn_(editedRange, rowIndex, idCol) &&
      rowIsEstablished_(sheet, headerMap, 'child', rowIndex, canonicalName_(idFld))) {
    appendLog_('', childId, OPERATIONS.CHILD_EDIT, STAGES.WRITE, MESSAGES.UID_PROTECTED_EDIT_REJECTED);
    return;
  }

  var row = readRow_(sheet, headerMap, 'child', rowIndex);
  var uid = row[canonicalName_(idFld)];

  if (!uid) {
    if (!rowHasOtherDeclaredContent_(row, canonicalName_(idFld))) return; // partial/blank row, ignored until real content exists (test 29)
    uid = Utilities.getUuid();
    sheet.getRange(rowIndex, idCol).setValue(uid); // persist child UID first (decision 5 ordering contract)
    SpreadsheetApp.flush();
    row[canonicalName_(idFld)] = uid;
    createMasterRowFromChild_(row, uid, childId);
    return;
  }

  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var masterRowIndex = findRowByUid_(masterSheet, masterHeaderMap, 'master', uid);
  if (masterRowIndex === -1) return; // orphan; the sweep owns detection and recovery, not this handler

  var masterRow = readRow_(masterSheet, masterHeaderMap, 'master', masterRowIndex);
  var ownerName = resolveChild(masterRow);
  var ownerId = ownerName ? childSpreadsheetIdByName_(ownerName) : null;
  if (!ownerName || ownerId !== childId) {
    appendLog_(uid, childId, OPERATIONS.CHILD_EDIT, STAGES.VALIDATE, MESSAGES.OWNERSHIP_MISMATCH);
    return;
  }

  var revertedAny = false;
  FIELDS.forEach(function (field) {
    if (field.sync !== 'down') return;
    var name = canonicalName_(field);
    if (!valuesEqual_(masterRow[name], row[name])) {
      var childCol = resolveFieldColumn_(headerMap, field, 'child').col;
      writeFieldValue_(sheet, rowIndex, childCol, masterRow[name], uid, childId, OPERATIONS.CHILD_EDIT);
      revertedAny = true;
    }
  });
  if (revertedAny) {
    SpreadsheetApp.flush();
    appendLog_(uid, childId, OPERATIONS.CHILD_EDIT, STAGES.WRITE, MESSAGES.DOWN_FIELD_REVERTED);
  }

  FIELDS.forEach(function (field) {
    if (field.sync !== 'up' && field.sync !== 'both') return;
    var name = canonicalName_(field);
    var masterCol = resolveFieldColumn_(masterHeaderMap, field, 'master').col;
    writeFieldValue_(masterSheet, masterRowIndex, masterCol, row[name], uid, childId, OPERATIONS.CHILD_EDIT);
  });
}

// ---------------------------------------------------------------------
// Row-level primitives
// ---------------------------------------------------------------------

function editedRows_(range) {
  var rows = [];
  var start = range.getRow();
  var count = range.getNumRows();
  for (var i = 0; i < count; i++) rows.push(start + i);
  return rows;
}

function rangeIntersectsColumn_(range, rowIndex, col) {
  if (rowIndex < range.getRow() || rowIndex >= range.getRow() + range.getNumRows()) return false;
  return col >= range.getColumn() && col < range.getColumn() + range.getNumColumns();
}

/** True if any declared field other than `excludeName` has a non-blank value. */
function rowHasOtherDeclaredContent_(row, excludeName) {
  for (var key in row) {
    if (key === excludeName) continue;
    if (row[key] !== '' && row[key] !== null && typeof row[key] !== 'undefined') return true;
  }
  return false;
}

/** Established = has content beyond the UID field. See decision 2 for why this, not "did the value change", is the signal. */
function rowIsEstablished_(sheet, headerMap, side, rowIndex, idCanonicalName) {
  var row = readRow_(sheet, headerMap, side, rowIndex);
  return rowHasOtherDeclaredContent_(row, idCanonicalName);
}

function findRowByUid_(sheet, headerMap, side, uid) {
  var idResolved = resolveFieldColumn_(headerMap, idField_(), side);
  if (idResolved.error) return -1;
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return -1;
  var vals = sheet.getRange(2, idResolved.col, lastRow - 1, 1).getValues();
  for (var i = 0; i < vals.length; i++) {
    if (String(vals[i][0]) === String(uid)) return i + 2;
  }
  return -1;
}

function deleteRowByUid_(sheet, headerMap, side, uid) {
  var rowIndex = findRowByUid_(sheet, headerMap, side, uid);
  if (rowIndex === -1) return false;
  sheet.deleteRow(rowIndex);
  return true;
}

function readAllRows_(sheet, headerMap, side) {
  var out = [];
  var lastRow = sheet.getLastRow();
  var idFld = idField_();
  for (var r = 2; r <= lastRow; r++) {
    var row = readRow_(sheet, headerMap, side, r);
    if (!rowHasOtherDeclaredContent_(row, canonicalName_(idFld)) && !row[canonicalName_(idFld)]) continue;
    out.push({ rowIndex: r, row: row });
  }
  return out;
}

function valuesEqual_(a, b) {
  if (a instanceof Date && b instanceof Date) return a.getTime() === b.getTime();
  if (a instanceof Date && typeof b === 'string') return dateMatchesIsoString_(a, b);
  if (b instanceof Date && typeof a === 'string') return dateMatchesIsoString_(b, a);
  return a === b;
}

/**
 * True if a Date cell and a plain ISO (YYYY-MM-DD) string represent the
 * same calendar day. Guards a confirmed live bug: writeFieldValue_'s
 * destination auto-coerces a date-looking source string into a real Date
 * (Range.setValue() does this even though the source cell itself stayed
 * plain text — e.g. a CSV import or a value entered via the Sheets API),
 * so a value that started as a string and its synced, auto-coerced Date
 * copy never satisfy === again. Without this, repairFieldDivergence_
 * "repairs" and logs the same non-divergence on every sweep run forever,
 * including the daily one, and it shows up in healthCheck() as a
 * permanent false "recent failure". Compares calendar components, not
 * timestamps: the string carries no time zone, so parsing it through
 * `new Date()` would introduce a spurious UTC-vs-spreadsheet-time-zone
 * offset. Only ISO-format strings are normalized; anything else falls
 * back to strict inequality rather than risk a false match.
 */
function dateMatchesIsoString_(dateVal, stringVal) {
  var m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(String(stringVal).trim());
  if (!m) return false;
  return dateVal.getFullYear() === Number(m[1]) &&
    dateVal.getMonth() + 1 === Number(m[2]) &&
    dateVal.getDate() === Number(m[3]);
}

/**
 * Values-only write with the two formula guards from decision 6: refuse
 * to overwrite a destination cell that currently holds a live formula,
 * and refuse to write a source value that is itself a leading-'=' string
 * (which setValue() would turn into a live formula on the destination).
 */
function writeFieldValue_(sheet, rowIndex, col, value, uid, sheetId, operation) {
  operation = operation || OPERATIONS.MASTER_EDIT;
  var destRange = sheet.getRange(rowIndex, col);
  if (destRange.getFormula() !== '') {
    appendLog_(uid, sheetId, operation, STAGES.WRITE, MESSAGES.FORMULA_REJECTED);
    return false;
  }
  if (typeof value === 'string' && value.trim().charAt(0) === '=') {
    appendLog_(uid, sheetId, operation, STAGES.WRITE, MESSAGES.FORMULA_REJECTED);
    return false;
  }
  destRange.setValue(value);
  return true;
}

// ---------------------------------------------------------------------
// Child lookup and directional upserts
// ---------------------------------------------------------------------

function openChildByName_(name) {
  var roster = getChildRoster_();
  var match = null;
  for (var i = 0; i < roster.children.length; i++) {
    if (roster.children[i].name === name) { match = roster.children[i]; break; }
  }
  if (!match) return null;
  try {
    var ss = SpreadsheetApp.openById(match.spreadsheetId);
    var sheet = ss.getSheetByName(DATA_TAB);
    if (!sheet) throw new Error('missing data tab');
    return { sheet: sheet, headerMap: getHeaderMap_(sheet), spreadsheetId: match.spreadsheetId, name: match.name };
  } catch (e) {
    appendLog_('', match.spreadsheetId, OPERATIONS.ROUTE_MOVE, STAGES.READ, MESSAGES.CHILD_UNREADABLE);
    return null;
  }
}

function childSpreadsheetIdByName_(name) {
  var roster = getChildRoster_();
  for (var i = 0; i < roster.children.length; i++) {
    if (roster.children[i].name === name) return roster.children[i].spreadsheetId;
  }
  return null;
}

function childNameBySpreadsheetId_(id) {
  var roster = getChildRoster_();
  for (var i = 0; i < roster.children.length; i++) {
    if (roster.children[i].spreadsheetId === id) return roster.children[i].name;
  }
  return null;
}

/**
 * Master -> child: writes id + down + both always. Writes up fields too,
 * but ONLY on first placement in this child (new row or a reassignment
 * that just arrived here) — otherwise master's stale copy of an
 * up field would clobber the child's own ongoing edits to it. Without
 * this, a reassigned row shows blank in the new child for a field the
 * master still displays a value for, which reads as "the new owner
 * already worked this" when they haven't (Paul, 2026-08-22).
 */
function upsertChildFromMaster_(childCtx, masterRow, uid, operation) {
  operation = operation || OPERATIONS.MASTER_EDIT;
  var rowIndex = findRowByUid_(childCtx.sheet, childCtx.headerMap, 'child', uid);
  var isNewPlacement = rowIndex === -1;
  var idResolved = resolveFieldColumn_(childCtx.headerMap, idField_(), 'child');
  if (isNewPlacement) {
    rowIndex = childCtx.sheet.getLastRow() + 1;
  }
  if (idResolved.col) childCtx.sheet.getRange(rowIndex, idResolved.col).setValue(uid);
  FIELDS.forEach(function (field) {
    var carriesOnPlacement = field.sync === 'down' || field.sync === 'both' || (field.sync === 'up' && isNewPlacement);
    if (!carriesOnPlacement) return;
    var resolved = resolveFieldColumn_(childCtx.headerMap, field, 'child');
    if (resolved.error) return;
    writeFieldValue_(childCtx.sheet, rowIndex, resolved.col, masterRow[canonicalName_(field)], uid, childCtx.spreadsheetId, operation);
  });
}

/** Child -> new master row: writes id + route + every field the child currently holds. */
function upsertFullMasterRow_(row, uid, originChildId, operation, message) {
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var rowIndex = findRowByUid_(masterSheet, masterHeaderMap, 'master', uid);
  if (rowIndex !== -1) return; // already adopted; refuse to duplicate (test 28b)

  rowIndex = masterSheet.getLastRow() + 1;
  var idResolved = resolveFieldColumn_(masterHeaderMap, idField_(), 'master');
  var routeResolved = resolveFieldColumn_(masterHeaderMap, routeField_(), 'master');
  var originName = childNameBySpreadsheetId_(originChildId);
  masterSheet.getRange(rowIndex, idResolved.col).setValue(uid);
  if (originName) masterSheet.getRange(rowIndex, routeResolved.col).setValue(originName);

  FIELDS.forEach(function (field) {
    if (field.sync === 'id' || field.sync === 'route') return;
    var resolved = resolveFieldColumn_(masterHeaderMap, field, 'master');
    if (resolved.error) return;
    var val = row[canonicalName_(field)];
    if (typeof val === 'undefined') return;
    writeFieldValue_(masterSheet, rowIndex, resolved.col, val, uid, originChildId, operation);
  });

  SpreadsheetApp.flush();
  appendLog_(uid, originChildId, operation, STAGES.COMPLETE, message);
}

function createMasterRowFromChild_(row, uid, childId) {
  upsertFullMasterRow_(row, uid, childId, OPERATIONS.CHILD_CREATE, MESSAGES.OK);
}

// ---------------------------------------------------------------------
// Manual force-push
// ---------------------------------------------------------------------

/** Menu action. Run with the cursor on a master data row: force every field to match master, including up. */
/**
 * Menu action, so SpreadsheetApp.getActiveSheet()/getActiveRange() ARE
 * reliable here — unlike inside a handleChildEdit/handleMasterEdit fired
 * by an installable trigger, this only ever runs from a genuine menu
 * click on the master itself. Do NOT route through getMaster_(): a
 * Spreadsheet obtained via SpreadsheetApp.openById() does not track the
 * live UI selection, so getActiveSheet()/getActiveCell() on it silently
 * default to row 1 regardless of what's actually selected — confirmed
 * live, this made resyncRow() read the header row as data on every call
 * (uid="Unique ID", no such child, silent no-op) with no error at all.
 */
function resyncRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() !== DATA_TAB) {
    SpreadsheetApp.getUi().alert('resyncRow()', 'Select a row on the "' + DATA_TAB + '" tab first.', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
  var rowIndex = SpreadsheetApp.getActiveRange().getRow();
  withLock_(OPERATIONS.RESYNC, function () {
    var headerMap = getHeaderMap_(sheet);
    var row = readRow_(sheet, headerMap, 'master', rowIndex);
    var uid = row[canonicalName_(idField_())];
    if (!uid) return;
    var childName = resolveChild(row);
    if (!childName) return;
    var childCtx = openChildByName_(childName);
    if (!childCtx) return;
    var childRowIndex = findRowByUid_(childCtx.sheet, childCtx.headerMap, 'child', uid);
    if (childRowIndex === -1) childRowIndex = childCtx.sheet.getLastRow() + 1;
    var idResolved = resolveFieldColumn_(childCtx.headerMap, idField_(), 'child');
    childCtx.sheet.getRange(childRowIndex, idResolved.col).setValue(uid);
    FIELDS.forEach(function (field) {
      if (field.sync === 'id' || field.sync === 'route') return;
      var resolved = resolveFieldColumn_(childCtx.headerMap, field, 'child');
      if (resolved.error) return;
      writeFieldValue_(childCtx.sheet, childRowIndex, resolved.col, row[canonicalName_(field)], uid, childCtx.spreadsheetId, OPERATIONS.RESYNC);
    });
  });
}

// ---------------------------------------------------------------------
// Reconciliation sweep — repairs placement and field divergence, reports
// (never reconstructs or deletes) child-only orphans. See decision 4.
// ---------------------------------------------------------------------

function runReconciliation() {
  withLock_(OPERATIONS.RECONCILE, runReconciliationLocked_);
}

function runReconciliationLocked_() {
  var master = getMaster_();
  checkDrift_(); // safe here specifically because this path holds the script lock — see DriftLog.js header
  var masterSheet = master.getSheetByName(DATA_TAB);
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var idResolved = resolveFieldColumn_(masterHeaderMap, idField_(), 'master');
  var routeResolved = resolveFieldColumn_(masterHeaderMap, routeField_(), 'master');
  if (idResolved.error || routeResolved.error) {
    appendLog_('', master.getId(), OPERATIONS.RECONCILE, STAGES.VALIDATE, MESSAGES.SCHEMA_FIELD_MISSING);
    return;
  }

  var roster = getChildRoster_();
  var childCtxByName = {};
  for (var i = 0; i < roster.children.length; i++) {
    var child = roster.children[i];
    try {
      var ss = SpreadsheetApp.openById(child.spreadsheetId);
      var sheet = ss.getSheetByName(DATA_TAB);
      if (!sheet) throw new Error('missing data tab');
      childCtxByName[child.name] = { sheet: sheet, headerMap: getHeaderMap_(sheet), spreadsheetId: child.spreadsheetId, name: child.name };
    } catch (e) {
      appendLog_('', child.spreadsheetId, OPERATIONS.RECONCILE, STAGES.READ, MESSAGES.SWEEP_ABORTED_UNREADABLE);
      return; // abort without deleting anything — a partial inventory makes deletion unsafe
    }
  }

  var masterEntries = readAllRows_(masterSheet, masterHeaderMap, 'master');
  var masterByUid = {};
  masterEntries.forEach(function (entry) {
    var uid = entry.row[canonicalName_(idField_())];
    if (uid) masterByUid[uid] = entry;
  });

  var childInventories = {};
  for (var name in childCtxByName) {
    var entries = readAllRows_(childCtxByName[name].sheet, childCtxByName[name].headerMap, 'child');
    var byUid = {};
    entries.forEach(function (entry) {
      var uid = entry.row[canonicalName_(idField_())];
      if (uid) byUid[uid] = entry;
    });
    childInventories[name] = byUid;
  }

  Object.keys(masterByUid).forEach(function (uid) {
    var entry = masterByUid[uid];
    var expectedChild = resolveChild(entry.row);
    for (var cname in childCtxByName) {
      var present = childInventories[cname][uid];
      if (cname === expectedChild) {
        if (!present) {
          upsertChildFromMaster_(childCtxByName[cname], entry.row, uid, OPERATIONS.RECONCILE);
          SpreadsheetApp.flush();
        } else {
          repairFieldDivergence_(masterSheet, masterHeaderMap, entry.rowIndex, childCtxByName[cname], present.rowIndex, entry.row, present.row, uid);
        }
      } else if (present) {
        deleteRowByUid_(childCtxByName[cname].sheet, childCtxByName[cname].headerMap, 'child', uid);
        SpreadsheetApp.flush();
      }
    }
  });

  for (var cname2 in childInventories) {
    Object.keys(childInventories[cname2]).forEach(function (uid2) {
      if (!masterByUid[uid2]) {
        appendLog_(uid2, childCtxByName[cname2].spreadsheetId, OPERATIONS.RECONCILE, STAGES.VERIFY, MESSAGES.ORPHAN_CHILD_ONLY);
      }
    });
  }
}

/** down: master wins. up: current owner-child wins. both: master-wins-and-log. */
function repairFieldDivergence_(masterSheet, masterHeaderMap, masterRowIndex, childCtx, childRowIndex, masterRow, childRow, uid) {
  var changed = false;
  FIELDS.forEach(function (field) {
    if (field.sync === 'id' || field.sync === 'route') return;
    var name = canonicalName_(field);
    var mVal = masterRow[name];
    var cVal = childRow[name];
    if (valuesEqual_(mVal, cVal)) return;

    if (field.sync === 'down' || field.sync === 'both') {
      var childCol = resolveFieldColumn_(childCtx.headerMap, field, 'child').col;
      writeFieldValue_(childCtx.sheet, childRowIndex, childCol, mVal, uid, childCtx.spreadsheetId, OPERATIONS.RECONCILE);
    } else if (field.sync === 'up') {
      var masterCol = resolveFieldColumn_(masterHeaderMap, field, 'master').col;
      writeFieldValue_(masterSheet, masterRowIndex, masterCol, cVal, uid, childCtx.spreadsheetId, OPERATIONS.RECONCILE);
    }
    changed = true;
  });
  if (changed) {
    SpreadsheetApp.flush();
    appendLog_(uid, childCtx.spreadsheetId, OPERATIONS.RECONCILE, STAGES.WRITE, MESSAGES.FIELD_DIVERGENCE_REPAIRED);
  }
}

// ---------------------------------------------------------------------
// Orphan recovery — human decides; the engine never guesses (decision 4).
// ---------------------------------------------------------------------

function findOrphans_() {
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var masterUids = {};
  readAllRows_(masterSheet, masterHeaderMap, 'master').forEach(function (entry) {
    var uid = entry.row[canonicalName_(idField_())];
    if (uid) masterUids[uid] = true;
  });

  var roster = getChildRoster_();
  var orphans = [];
  roster.children.forEach(function (child) {
    try {
      var sheet = SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
      if (!sheet) return;
      var headerMap = getHeaderMap_(sheet);
      readAllRows_(sheet, headerMap, 'child').forEach(function (entry) {
        var uid = entry.row[canonicalName_(idField_())];
        if (uid && !masterUids[uid]) {
          orphans.push({ uid: uid, childName: child.name, spreadsheetId: child.spreadsheetId, row: entry.row });
        }
      });
    } catch (e) {
      // unreadable child: skip, healthCheck() already surfaces this
    }
  });
  return orphans;
}

function pickOrphan_(actionLabel) {
  var orphans = findOrphans_();
  if (orphans.length === 0) {
    SpreadsheetApp.getUi().alert(actionLabel, 'No orphans found.', SpreadsheetApp.getUi().ButtonSet.OK);
    return null;
  }
  var ui = SpreadsheetApp.getUi();
  var lines = orphans.map(function (o, i) { return (i + 1) + '. UID ' + o.uid + ' — child "' + o.childName + '"'; });
  var resp = ui.prompt(
    actionLabel,
    lines.join('\n') +
      '\n\nType just the LIST NUMBER of the row you want (for example, type 1 to pick the first row above — the UID is shown for reference only, don\'t type it), then click OK.',
    ui.ButtonSet.OK_CANCEL
  );
  if (resp.getSelectedButton() !== ui.Button.OK) return null;
  var idx = parseInt(resp.getResponseText(), 10) - 1;
  if (isNaN(idx) || idx < 0 || idx >= orphans.length) {
    ui.alert(actionLabel, 'Not a valid number.', ui.ButtonSet.OK);
    return null;
  }
  return orphans[idx];
}

/** Menu action. Inserts the master row on the child's persisted UID, route pre-set to that child. */
function adoptOrphan() {
  var picked = pickOrphan_('Adopt orphan');
  if (!picked) return;
  var ui = SpreadsheetApp.getUi();
  var confirm = ui.alert('Adopt orphan', 'Adopt UID ' + picked.uid + ' from child "' + picked.childName + '" into the master?', ui.ButtonSet.YES_NO);
  if (confirm !== ui.Button.YES) return;
  withLock_(OPERATIONS.ADOPT_ORPHAN, function () {
    upsertFullMasterRow_(picked.row, picked.uid, picked.spreadsheetId, OPERATIONS.ADOPT_ORPHAN, MESSAGES.ORPHAN_ADOPTED);
  });
}

/** Menu action. Deletes the child's copy of an orphaned row. Confirms before deleting — this is destructive. */
function discardOrphan() {
  var picked = pickOrphan_('Discard orphan');
  if (!picked) return;
  var ui = SpreadsheetApp.getUi();
  var confirm = ui.alert('Discard orphan', 'Permanently delete UID ' + picked.uid + ' from child "' + picked.childName + '"? This cannot be undone.', ui.ButtonSet.YES_NO);
  if (confirm !== ui.Button.YES) return;
  withLock_(OPERATIONS.DISCARD_ORPHAN, function () {
    var sheet = SpreadsheetApp.openById(picked.spreadsheetId).getSheetByName(DATA_TAB);
    var headerMap = getHeaderMap_(sheet);
    if (deleteRowByUid_(sheet, headerMap, 'child', picked.uid)) {
      appendLog_(picked.uid, picked.spreadsheetId, OPERATIONS.DISCARD_ORPHAN, STAGES.COMPLETE, MESSAGES.ORPHAN_DISCARDED);
    }
  });
}

Triggers.js Installs and reconciles the triggers that wire everything together. (paste as-is)

src/Triggers.js

/**
 * Triggers.js — installTriggers(), reconciling against getProjectTriggers()
 * rather than delete-all-then-recreate (a mid-run failure there would
 * leave the system partially unwired).
 */

/**
 * Menu action. Diffs the desired (handler, event type, source ID) set
 * against what's actually installed: keeps correct triggers, removes only
 * stale or duplicate ones, creates only what's missing. Safe to run
 * repeatedly (test 31) and safe to re-run after a mid-run failure
 * (test 31b) — whatever's already correct survives.
 */
function installTriggers() {
  var master = getMaster_();
  var masterId = master.getId();
  var roster = getChildRoster_();

  if (roster.children.length > MAX_CHILDREN) {
    SpreadsheetApp.getUi().alert(
      'installTriggers()',
      'Key tab has ' + roster.children.length + ' children registered. The hard ceiling is ' + MAX_CHILDREN +
        ' (Apps Script allows ' + TRIGGER_QUOTA + ' triggers per script per user; ' + RESERVED_TRIGGERS +
        ' are reserved for the master trigger and the daily reconciliation clock). Remove some before installing.',
      SpreadsheetApp.getUi().ButtonSet.OK
    );
    appendLog_('', masterId, OPERATIONS.INSTALL_TRIGGERS, STAGES.VALIDATE, MESSAGES.TRIGGER_QUOTA_EXCEEDED);
    return;
  }

  var desired = [{ handler: 'handleMasterEdit', sourceId: masterId }];
  roster.children.forEach(function (c) {
    desired.push({ handler: 'handleChildEdit', sourceId: c.spreadsheetId });
  });

  var existing = ScriptApp.getProjectTriggers();
  var existingOnEdit = existing.filter(function (t) { return t.getEventType() === ScriptApp.EventType.ON_EDIT; });
  var existingClock = existing.filter(function (t) { return t.getEventType() === ScriptApp.EventType.CLOCK; });

  // Key existing triggers by (handler, sourceId) so exact-match survivors are never touched.
  var existingByKey = {};
  existingOnEdit.forEach(function (t) {
    var key = t.getHandlerFunction() + '|' + t.getTriggerSourceId();
    if (!existingByKey[key]) existingByKey[key] = [];
    existingByKey[key].push(t);
  });

  var keep = {};
  desired.forEach(function (d) {
    var key = d.handler + '|' + d.sourceId;
    var matches = existingByKey[key] || [];
    if (matches.length > 0) {
      keep[matches[0].getUniqueId()] = true; // keep exactly one; extras are duplicates
    } else {
      if (d.handler === 'handleMasterEdit') {
        ScriptApp.newTrigger(d.handler).forSpreadsheet(masterId).onEdit().create();
      } else {
        ScriptApp.newTrigger(d.handler).forSpreadsheet(d.sourceId).onEdit().create();
      }
    }
  });

  existingOnEdit.forEach(function (t) {
    if (!keep[t.getUniqueId()]) ScriptApp.deleteTrigger(t); // stale or duplicate
  });

  if (existingClock.length === 0) {
    ScriptApp.newTrigger('runReconciliation').timeBased().everyDays(1).atHour(3).create();
  } else {
    // Keep exactly one daily reconciliation trigger; delete any duplicates.
    for (var i = 1; i < existingClock.length; i++) ScriptApp.deleteTrigger(existingClock[i]);
  }

  appendLog_('', masterId, OPERATIONS.INSTALL_TRIGGERS, STAGES.COMPLETE, MESSAGES.OK);
  SpreadsheetApp.getUi().alert('installTriggers()', 'Done. ' + roster.children.length + ' child trigger(s) + 1 master trigger + 1 daily reconciliation trigger.', SpreadsheetApp.getUi().ButtonSet.OK);
}

Log.js The Sync Log schema and Health check. (paste as-is)

src/Log.js

/**
 * Log.js — the one authoritative Sync Log schema, plus healthCheck().
 *
 * Every rejected event, lock timeout, and partial route move writes a row
 * here: timestamp, UID, source sheet ID, operation, stage, message-code.
 * No free-text parameter exists to be misused — only enum values are ever
 * written, so a future caller can't accidentally log a raw exception
 * string (which could carry a cell value) into a file people rely on.
 */

var OPERATIONS = {
  MASTER_EDIT: 'MASTER_EDIT',
  CHILD_EDIT: 'CHILD_EDIT',
  ROUTE_MOVE: 'ROUTE_MOVE',
  RECONCILE: 'RECONCILE',
  CHILD_CREATE: 'CHILD_CREATE',
  RESYNC: 'RESYNC',
  ADOPT_ORPHAN: 'ADOPT_ORPHAN',
  DISCARD_ORPHAN: 'DISCARD_ORPHAN',
  INSTALL_TRIGGERS: 'INSTALL_TRIGGERS',
  SETUP: 'SETUP',
  SOP_GENERATE: 'SOP_GENERATE'
};

var STAGES = {
  VALIDATE: 'VALIDATE',
  LOCK: 'LOCK',
  READ: 'READ',
  WRITE: 'WRITE',
  VERIFY: 'VERIFY',
  DELETE: 'DELETE',
  COMPLETE: 'COMPLETE'
};

var MESSAGES = {
  OK: 'OK',
  LOCK_TIMEOUT: 'LOCK_TIMEOUT',
  SCHEMA_FIELD_MISSING: 'SCHEMA_FIELD_MISSING',
  SCHEMA_COLLISION: 'SCHEMA_COLLISION',
  ROUTE_FIELD_ON_CHILD: 'ROUTE_FIELD_ON_CHILD',
  UID_BLANK: 'UID_BLANK',
  UID_DUPLICATE: 'UID_DUPLICATE',
  UID_PROTECTED_EDIT_REJECTED: 'UID_PROTECTED_EDIT_REJECTED',
  OWNERSHIP_MISMATCH: 'OWNERSHIP_MISMATCH',
  DOWN_FIELD_REVERTED: 'DOWN_FIELD_REVERTED',
  FORMULA_REJECTED: 'FORMULA_REJECTED',
  TIMEZONE_MISMATCH: 'TIMEZONE_MISMATCH',
  ORPHAN_CHILD_ONLY: 'ORPHAN_CHILD_ONLY',
  ORPHAN_ADOPTED: 'ORPHAN_ADOPTED',
  ORPHAN_DISCARDED: 'ORPHAN_DISCARDED',
  SWEEP_ABORTED_UNREADABLE: 'SWEEP_ABORTED_UNREADABLE',
  FIELD_DIVERGENCE_REPAIRED: 'FIELD_DIVERGENCE_REPAIRED',
  UNKNOWN_ROUTE_VALUE: 'UNKNOWN_ROUTE_VALUE',
  SENTINEL_NAME_COLLISION: 'SENTINEL_NAME_COLLISION',
  TRIGGER_QUOTA_EXCEEDED: 'TRIGGER_QUOTA_EXCEEDED',
  CHILD_UNAUTHORIZED: 'CHILD_UNAUTHORIZED',
  CHILD_UNREADABLE: 'CHILD_UNREADABLE',
  SNAPSHOT_CREATED: 'SNAPSHOT_CREATED',
  DRIFT_DETECTED: 'DRIFT_DETECTED'
};

function isKnownEnum_(map, value) {
  for (var k in map) {
    if (map[k] === value) return true;
  }
  return false;
}

/**
 * The only way a row is ever written to Sync Log. uid/sheetId may be ''
 * (e.g. an install-time event with no row context) but operation, stage,
 * and message must be one of the enums above — unknown values are
 * rejected rather than silently coerced to a string.
 */
function appendLog_(uid, sheetId, operation, stage, message) {
  if (!isKnownEnum_(OPERATIONS, operation)) {
    throw new Error('appendLog_: unknown operation "' + operation + '"');
  }
  if (!isKnownEnum_(STAGES, stage)) {
    throw new Error('appendLog_: unknown stage "' + stage + '"');
  }
  if (!isKnownEnum_(MESSAGES, message)) {
    throw new Error('appendLog_: unknown message "' + message + '"');
  }
  var master = getMaster_();
  var sheet = master.getSheetByName(LOG_TAB);
  if (!sheet) return; // setup() hasn't run yet; nothing to log to
  sheet.appendRow([new Date(), uid || '', sheetId || '', operation, stage, message]);
}

/**
 * Master menu action. Reports recent Sync Log failures, config drift
 * against the Key tab roster and the live trigger inventory, and SOP
 * snapshot staleness. This is the answer to "nothing happened and there
 * is no error" — a beginner's actual failure experience.
 */
function healthCheck() {
  var report = [];
  report.push(healthCheckRecentFailures_());
  report.push(healthCheckTriggerDrift_());
  report.push(healthCheckSopStaleness_());
  SpreadsheetApp.getUi().alert('Health Check', report.join('\n\n'), SpreadsheetApp.getUi().ButtonSet.OK);
}

function healthCheckRecentFailures_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(LOG_TAB);
  if (!sheet || sheet.getLastRow() < 2) return 'Sync Log: no entries yet.';
  var lastRow = sheet.getLastRow();
  var start = Math.max(2, lastRow - 199); // most recent 200 rows
  var rows = sheet.getRange(start, 1, lastRow - start + 1, 6).getValues();
  var failures = rows.filter(function (r) { return r[5] !== MESSAGES.OK; });
  if (failures.length === 0) return 'Sync Log: no failures in the last ' + rows.length + ' entries.';
  var lines = ['Sync Log: ' + failures.length + ' non-OK entries in the last ' + rows.length + ':'];
  failures.slice(-10).forEach(function (r) {
    lines.push('  ' + r[0] + ' — ' + r[3] + '/' + r[4] + ': ' + r[5] + (r[1] ? ' (UID ' + r[1] + ')' : ''));
  });
  return lines.join('\n');
}

function healthCheckTriggerDrift_() {
  var roster = getChildRoster_();
  if (!roster.ok) return 'Trigger inventory: could not read Key tab (' + roster.issues.join('; ') + ').';

  var triggers = ScriptApp.getProjectTriggers();
  var triggerSourceIds = {};
  triggers.forEach(function (t) {
    if (t.getEventType() === ScriptApp.EventType.ON_EDIT) {
      var sourceId = t.getTriggerSourceId();
      if (sourceId) triggerSourceIds[sourceId] = (triggerSourceIds[sourceId] || 0) + 1;
    }
  });

  var masterId = getMaster_().getId();
  var lines = [];
  var missing = [];
  var duplicate = [];
  var unauthorized = [];

  roster.children.forEach(function (child) {
    var count = triggerSourceIds[child.spreadsheetId] || 0;
    if (count === 0) {
      missing.push(child.name);
    } else if (count > 1) {
      duplicate.push(child.name + ' (' + count + ')');
    }
    try {
      SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
    } catch (e) {
      unauthorized.push(child.name);
    }
    delete triggerSourceIds[child.spreadsheetId];
  });

  var stale = Object.keys(triggerSourceIds).filter(function (id) { return id !== masterId; });

  if (missing.length) lines.push('Missing triggers: ' + missing.join(', '));
  if (duplicate.length) lines.push('Duplicate triggers: ' + duplicate.join(', '));
  if (unauthorized.length) lines.push('Unauthorized / inaccessible: ' + unauthorized.join(', '));
  if (stale.length) lines.push('Stale trigger source IDs (not in Key tab): ' + stale.join(', '));

  var used = triggers.length;
  var headroom = TRIGGER_QUOTA - used;
  lines.push('Trigger headroom: ' + headroom + ' of ' + TRIGGER_QUOTA + ' remaining (' + roster.children.length + '/' + MAX_CHILDREN + ' children registered).');

  if (lines.length === 1) return 'Trigger inventory: in sync.\n' + lines[0];
  return 'Trigger inventory:\n  ' + lines.join('\n  ');
}

function healthCheckSopStaleness_() {
  var props = PropertiesService.getScriptProperties();
  var lastGenerated = props.getProperty('SOP_LAST_GENERATED');
  if (!lastGenerated) return 'SOP: never generated. Menu › SOP › Generate and open.';
  var since = countDriftEntriesSince_(new Date(lastGenerated));
  var ageMs = new Date() - new Date(lastGenerated);
  var ageDays = Math.floor(ageMs / (24 * 60 * 60 * 1000));
  return 'SOP: last generated ' + ageDays + ' day(s) ago. ' + since + ' Drift Log entr' + (since === 1 ? 'y' : 'ies') + ' recorded since.';
}

Menu.js The Advanced menu and one-time setup(). (paste as-is)

src/Menu.js

/**
 * Menu.js — onOpen() and setup(). All admin actions live on the master's
 * menu because child sheets carry no code and therefore no menu of their
 * own (decision: single-controller architecture).
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Advanced')
    .addItem('Run setup', 'setup')
    .addItem('Install triggers', 'installTriggers')
    .addSeparator()
    .addItem('Validate schema', 'validateSchema')
    .addItem('Validate data', 'validateData')
    .addItem('Show schema', 'showSchema')
    .addSeparator()
    .addItem('Resync selected row', 'resyncRow')
    .addItem('Run reconciliation sweep', 'runReconciliation')
    .addItem('Health check', 'healthCheck')
    .addItem('Generate dropdown menus', 'applyDropdowns')
    .addSeparator()
    .addItem('Adopt orphan', 'adoptOrphan')
    .addItem('Discard orphan', 'discardOrphan')
    .addSeparator()
    .addSubMenu(ui.createMenu('SOP').addItem('Generate and open', 'generateAndOpenSop'))
    .addToUi();
}

/**
 * Menu action. Creates the "Assigned To" / Sync Log / Runbook / Drift Log
 * / "Dropdown Menu Config" tabs if missing, seeds Runbook and Dropdown
 * Menu Config starter content, and protects the Unique ID column on the
 * master and on every currently-registered child. Re-run after
 * registering new children — protection only covers sheets that exist
 * in the "Assigned To" tab at the time setup() runs.
 */
function setup() {
  var master = SpreadsheetApp.getActive(); // reliable here only: setup() always runs from a real menu click on the master
  PropertiesService.getScriptProperties().setProperty('MASTER_SPREADSHEET_ID', master.getId());
  ensureTab_(master, KEY_TAB, ['Child Name', 'Spreadsheet ID']);
  ensureTab_(master, LOG_TAB, ['Timestamp', 'UID', 'Sheet ID', 'Operation', 'Stage', 'Message']);
  ensureRunbookTab_(master);
  ensureDriftLogTab_(master);
  ensureFieldOptionsTab_(master);

  var masterData = master.getSheetByName(DATA_TAB);
  if (masterData) protectUidColumn_(masterData, 'master');

  var roster = getChildRoster_();
  var unreadable = [];
  roster.children.forEach(function (child) {
    try {
      var childSheet = SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
      if (childSheet) protectUidColumn_(childSheet, 'child');
    } catch (e) {
      unreadable.push(child.name);
    }
  });

  var dropdownIssues = applyDropdowns_();

  appendLog_('', master.getId(), OPERATIONS.SETUP, STAGES.COMPLETE, MESSAGES.OK);

  var msg = '"' + KEY_TAB + '", Sync Log, Runbook, Drift Log, and "' + FIELD_OPTIONS_TAB + '" tabs are ready. The Unique ID column is now protected on the master and on every currently-registered child, and dropdown menus are generated.';
  if (unreadable.length) msg += '\n\nCould not protect: ' + unreadable.join(', ') + ' (unreadable — check sharing).';
  if (dropdownIssues.length) msg += '\n\n"' + FIELD_OPTIONS_TAB + '" issues:\n' + dropdownIssues.join('\n');
  SpreadsheetApp.getUi().alert('Setup complete', msg, SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * Seeds "Dropdown Menu Config" with the Status field as a working
 * example — never blank on day one. Column A names a field declared in
 * FIELDS (case-insensitive); column B is its comma-separated option
 * list. Menu › Advanced › Generate dropdown menus reads this tab and applies
 * each row as a dropdown on the master and every registered child where
 * that field exists — edit this tab any time and re-run that menu item,
 * no code change or push required. The "Assigned To" dropdown itself is
 * NOT configured here — it's derived automatically from the "Assigned
 * To" tab's roster plus SENTINELS in Config.js.
 */
function ensureFieldOptionsTab_(spreadsheet) {
  var sheet = ensureTab_(spreadsheet, FIELD_OPTIONS_TAB, ['Field Name', 'Options (comma-separated)']);
  if (sheet.getLastRow() < 2) {
    sheet.getRange(2, 1, 1, 2).setValues([['Status', 'New, Contacted, Scheduled, Not Responding']]);
  }
  return sheet;
}

function ensureTab_(spreadsheet, name, headers) {
  var sheet = spreadsheet.getSheetByName(name);
  if (!sheet) sheet = spreadsheet.insertSheet(name);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }
  return sheet;
}

/**
 * Restricts everyone except the effective user (the deploying admin,
 * since installable triggers run as their creator) from editing the
 * Unique ID column. The script's own writes still go through — they
 * execute with the admin's authority, which the protection never
 * revokes from itself. This is prevention; the reject-on-intersect check
 * in Sync.js is the backstop for whatever gets past it (decision 2).
 */
function protectUidColumn_(sheet, side) {
  var headerMap = getHeaderMap_(sheet);
  var resolved = resolveFieldColumn_(headerMap, idField_(), side);
  if (resolved.error) return; // schema doesn't resolve yet; validateSchema() reports it

  sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function (p) {
    if (p.getDescription() === 'sheet-sync: Unique ID column' && p.getRange().getColumn() === resolved.col) {
      p.remove(); // avoid stacking duplicate protections on repeated setup() runs
    }
  });

  var range = sheet.getRange(2, resolved.col, Math.max(sheet.getMaxRows() - 1, 1), 1);
  var protection = range.protect().setDescription('sheet-sync: Unique ID column');
  var effectiveEmail = Session.getEffectiveUser().getEmail();
  var toRemove = protection.getEditors().filter(function (u) { return u.getEmail() !== effectiveEmail; });
  if (toRemove.length > 0) protection.removeEditors(toRemove);
  if (protection.canDomainEdit()) protection.setDomainEdit(false);
}

Sop.js Generates your SOP from live configuration. (paste as-is)

src/Sop.js

/**
 * Sop.js — the generated half of the SOP (decision 15). No web app: a
 * menu action writes a timestamped, self-contained HTML snapshot to
 * Drive and opens it. Current by construction, since it runs head code
 * instead of a redeployed version of it.
 *
 * SECURITY BOUNDARY: it lives in the COLLECTOR, not in SopModel's shape.
 * collectSopModel_() and its helpers below read only: Key tab roster
 * columns, the Runbook tab, the Drift Log tab, the live trigger
 * inventory, and — via findOrphanUidsForSop_(), which reads only the ID
 * column — orphan UIDs. Nothing here ever calls readRow_() or otherwise
 * touches a declared field's data value. If a future change to this file
 * needs a data-row value, that is the SOP's scope boundary being
 * crossed, not a detail to work around.
 */

function findOrphanUidsForSop_() {
  var master = getMaster_();
  var masterSheet = master.getSheetByName(DATA_TAB);
  var masterHeaderMap = getHeaderMap_(masterSheet);
  var idResolvedMaster = resolveFieldColumn_(masterHeaderMap, idField_(), 'master');
  var masterUids = {};
  if (!idResolvedMaster.error) {
    var lastRow = masterSheet.getLastRow();
    if (lastRow >= 2) {
      masterSheet.getRange(2, idResolvedMaster.col, lastRow - 1, 1).getValues().forEach(function (r) {
        var v = String(r[0] || '').trim();
        if (v) masterUids[v] = true;
      });
    }
  }

  var roster = getChildRoster_();
  var orphans = [];
  roster.children.forEach(function (child) {
    try {
      var sheet = SpreadsheetApp.openById(child.spreadsheetId).getSheetByName(DATA_TAB);
      if (!sheet) return;
      var headerMap = getHeaderMap_(sheet);
      var idResolved = resolveFieldColumn_(headerMap, idField_(), 'child');
      if (idResolved.error) return;
      var childLastRow = sheet.getLastRow();
      if (childLastRow < 2) return;
      sheet.getRange(2, idResolved.col, childLastRow - 1, 1).getValues().forEach(function (r) {
        var uid = String(r[0] || '').trim();
        if (uid && !masterUids[uid]) {
          orphans.push({ uid: uid, childName: child.name, spreadsheetId: child.spreadsheetId });
        }
      });
    } catch (e) {
      // unreadable child: healthCheck() surfaces this elsewhere, not the SOP's job
    }
  });
  return orphans;
}

function readDriftLogForSop_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(DRIFT_LOG_TAB);
  if (!sheet || sheet.getLastRow() < 2) return [];
  var lastRow = sheet.getLastRow();
  var start = Math.max(2, lastRow - 19); // most recent 20
  return sheet.getRange(start, 1, lastRow - start + 1, 4).getValues().map(function (r) {
    return { timestamp: r[0], component: r[1], oldHash: r[2], newHash: r[3] };
  });
}

function countRecentFailuresForSop_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(LOG_TAB);
  if (!sheet || sheet.getLastRow() < 2) return 0;
  var lastRow = sheet.getLastRow();
  var start = Math.max(2, lastRow - 199);
  var rows = sheet.getRange(start, 1, lastRow - start + 1, 6).getValues();
  return rows.filter(function (r) { return r[5] !== MESSAGES.OK; }).length;
}

/** Output schema only — shape validation, not the security boundary (r7 #4). */
function collectSopModel_() {
  var master = getMaster_();
  var roster = getChildRoster_();

  var fieldMap = FIELDS.map(function (f) {
    return {
      name: canonicalName_(f),
      masterHeader: fieldHeader_(f, 'master') || '',
      childHeader: f.sync === 'route' ? '' : (fieldHeader_(f, 'child') || ''),
      sync: f.sync
    };
  });

  var triggers = ScriptApp.getProjectTriggers().filter(function (t) {
    return t.getEventType() === ScriptApp.EventType.ON_EDIT || t.getEventType() === ScriptApp.EventType.CLOCK;
  });
  var triggerRows = triggers.map(function (t) {
    return { handler: t.getHandlerFunction(), type: t.getEventType().toString(), sourceId: t.getTriggerSourceId() || '(none)' };
  });

  var rosterRows = roster.children.map(function (c) {
    var readable = true;
    try {
      SpreadsheetApp.openById(c.spreadsheetId).getSheetByName(DATA_TAB);
    } catch (e) {
      readable = false;
    }
    return { name: c.name, spreadsheetId: c.spreadsheetId, readable: readable };
  });

  return {
    generatedAt: new Date(),
    masterName: master.getName(),
    masterId: master.getId(),
    fieldMap: fieldMap,
    sentinels: SENTINELS.slice(),
    roster: rosterRows,
    maxChildren: MAX_CHILDREN,
    triggers: triggerRows,
    triggerQuota: TRIGGER_QUOTA,
    headroom: TRIGGER_QUOTA - triggers.length,
    runbook: readRunbook_(),
    orphans: findOrphanUidsForSop_(),
    driftEntries: readDriftLogForSop_(),
    recentFailureCount: countRecentFailuresForSop_()
  };
}

/**
 * Renders via HtmlService's templating so every dynamic value goes
 * through the auto-escaping `<?= ?>` printing scriptlet — never `<?!= ?>`.
 * .getContent() returns a plain string with none of the iframe sandboxing
 * GAS adds only when a template is SERVED as a web page; there is no web
 * page here, just a string written to a file.
 */
function renderSop_(model) {
  var template = HtmlService.createTemplate(sopTemplateSource_());
  template.model = model;
  return template.evaluate().getContent();
}

/** Menu action: SOP > Generate and open. */
function generateAndOpenSop() {
  var model = collectSopModel_();
  var html = renderSop_(model);
  var timestamp = Utilities.formatDate(model.generatedAt, Session.getScriptTimeZone(), 'yyyy-MM-dd HHmmss');
  var name = 'Sync SOP - ' + model.masterName + ' - ' + timestamp + '.html';
  var blob = Utilities.newBlob(html, 'text/html', name);

  // drive.file scope, advanced service only. Never DriveApp.getFileById()
  // after this — that call would silently restore the broad Drive scope
  // this narrow path was chosen to avoid.
  var file = Drive.Files.create({ name: name, mimeType: 'text/html' }, blob);

  PropertiesService.getScriptProperties().setProperty('SOP_LAST_GENERATED', model.generatedAt.toISOString());
  appendLog_('', getMaster_().getId(), OPERATIONS.SOP_GENERATE, STAGES.COMPLETE, MESSAGES.SNAPSHOT_CREATED);

  var url = 'https://drive.google.com/file/d/' + file.id + '/view';
  var dialogHtml = HtmlService
    .createHtmlOutput('<div style="font-family:sans-serif;padding:8px"><p>SOP generated.</p><p><a href="' + url + '" target="_blank" rel="noopener">Open it in Drive</a></p></div>')
    .setWidth(320)
    .setHeight(120);
  SpreadsheetApp.getUi().showModalDialog(dialogHtml, 'SOP ready');
}

function sopTemplateSource_() {
  return '' +
'<!doctype html><html><head><meta charset="utf-8"><title>Sync SOP</title><style>' +
'  body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif; color: #1a1a1a; background: #fff; max-width: 860px; margin: 40px auto; padding: 0 24px; line-height: 1.5; }' +
'  h1 { font-size: 26px; margin-bottom: 4px; }' +
'  .meta { color: #666; font-size: 13px; margin-bottom: 32px; }' +
'  h2 { font-size: 18px; border-bottom: 2px solid #ddd; padding-bottom: 6px; margin-top: 40px; }' +
'  table { border-collapse: collapse; width: 100%; margin: 12px 0; font-size: 14px; }' +
'  th, td { border: 1px solid #ddd; padding: 6px 10px; text-align: left; vertical-align: top; }' +
'  th { background: #f5f5f5; }' +
'  .note { color: #666; font-size: 13px; margin: 8px 0 20px; }' +
'  .runbook-section { margin-bottom: 20px; }' +
'  .runbook-section h3 { font-size: 15px; margin-bottom: 4px; }' +
'  .runbook-section p { margin: 0; white-space: pre-wrap; }' +
'  .empty { color: #999; font-style: italic; }' +
'  @media print {' +
'    body { max-width: 100%; margin: 0; padding: 16px; }' +
'    h2 { break-after: avoid; }' +
'    table, tr { break-inside: avoid; }' +
'  }' +
'</style></head><body>' +

'<h1>Sync SOP &mdash; <?= model.masterName ?></h1>' +
'<div class="meta">Generated <?= model.generatedAt ?> from the live system. This is a snapshot, not a live page &mdash; regenerate from Menu &rsaquo; SOP &rsaquo; Generate and open whenever it needs to be current.</div>' +

'<h2>Field Map</h2>' +
'<table><tr><th>Field</th><th>Master Header</th><th>Child Header</th><th>Direction</th></tr>' +
'<? for (var fi = 0; fi < model.fieldMap.length; fi++) { var f = model.fieldMap[fi]; ?>' +
'<tr><td><?= f.name ?></td><td><?= f.masterHeader ?></td><td><?= f.childHeader || "(master only)" ?></td><td><?= f.sync ?></td></tr>' +
'<? } ?>' +
'</table>' +
'<div class="note">Route sentinels (hold a row in the master, remove it from every child): <?= model.sentinels.join(", ") ?></div>' +

'<h2>Registered Children</h2>' +
'<? if (model.roster.length === 0) { ?><p class="empty">No children registered yet.</p><? } else { ?>' +
'<table><tr><th>Name</th><th>Spreadsheet ID</th><th>Readable</th></tr>' +
'<? for (var ri = 0; ri < model.roster.length; ri++) { var c = model.roster[ri]; ?>' +
'<tr><td><?= c.name ?></td><td><?= c.spreadsheetId ?></td><td><?= c.readable ? "yes" : "NO — check sharing" ?></td></tr>' +
'<? } ?>' +
'</table>' +
'<? } ?>' +
'<div class="note"><?= model.roster.length ?> of <?= model.maxChildren ?> children registered (hard ceiling, driven by the <?= model.triggerQuota ?>-trigger-per-script quota).</div>' +

'<h2>Trigger Inventory</h2>' +
'<table><tr><th>Handler</th><th>Type</th><th>Source ID</th></tr>' +
'<? for (var ti = 0; ti < model.triggers.length; ti++) { var t = model.triggers[ti]; ?>' +
'<tr><td><?= t.handler ?></td><td><?= t.type ?></td><td><?= t.sourceId ?></td></tr>' +
'<? } ?>' +
'</table>' +
'<div class="note">Headroom: <?= model.headroom ?> of <?= model.triggerQuota ?> triggers remaining.</div>' +

'<h2>Health</h2>' +
'<p><?= model.recentFailureCount ?> non-OK Sync Log entr<?= model.recentFailureCount === 1 ? "y" : "ies" ?> in the most recent 200.</p>' +
'<? if (model.orphans.length === 0) { ?><p class="empty">No open orphans.</p><? } else { ?>' +
'<table><tr><th>UID</th><th>Child</th></tr>' +
'<? for (var oi = 0; oi < model.orphans.length; oi++) { var o = model.orphans[oi]; ?>' +
'<tr><td><?= o.uid ?></td><td><?= o.childName ?></td></tr>' +
'<? } ?>' +
'</table>' +
'<div class="note">Resolve from Menu &rsaquo; Advanced &rsaquo; Adopt orphan / Discard orphan.</div>' +
'<? } ?>' +

'<h2>Drift Log (most recent)</h2>' +
'<? if (model.driftEntries.length === 0) { ?><p class="empty">No drift recorded yet.</p><? } else { ?>' +
'<table><tr><th>Timestamp</th><th>Component</th><th>Old Hash</th><th>New Hash</th></tr>' +
'<? for (var di = 0; di < model.driftEntries.length; di++) { var d = model.driftEntries[di]; ?>' +
'<tr><td><?= d.timestamp ?></td><td><?= d.component ?></td><td><?= d.oldHash ?></td><td><?= d.newHash ?></td></tr>' +
'<? } ?>' +
'</table>' +
'<? } ?>' +
'<div class="note">This detects drift in the field map, sentinels, tab names, and the child roster &mdash; nothing else. It is not an audit trail: it does not see A&rarr;B&rarr;A round trips between checkpoints, several changes collapsed into one entry, Runbook edits, header renames, source-code changes outside this hashed config, missed or failed checkpoints, tampering with this log itself, the actual time a change was made, who made it, or any row-level data change.</div>' +

'<h2>Runbook</h2>' +
'<? for (var bi = 0; bi < model.runbook.length; bi++) { var b = model.runbook[bi]; ?>' +
'<div class="runbook-section"><h3><?= b.section ?></h3><p><?= b.content ?></p></div>' +
'<? } ?>' +

'</body></html>';
}

Runbook.js Starter content for the SOP’s Runbook tab. (paste as-is)

src/Runbook.js

/**
 * Runbook.js — the authored half of the SOP. Starter rows so the document
 * is never blank on day one; the business owner edits this tab directly,
 * no code, no clasp. See decision 15.
 */

var RUNBOOK_STARTER_ROWS = [
  ['Onboarding a new team member', 'Add them as an editor on their assigned child sheet. They never need access to the master spreadsheet or to Apps Script.'],
  ['Offboarding a team member', 'Remove their editor access from their child sheet. Their existing rows stay in the master and in the Sync Log exactly as they left them.'],
  ['"Nothing is syncing"', 'Menu › Advanced › Health check first — it reports recent failures, trigger drift, and SOP staleness in one place. If it shows no issues, confirm the edit landed on the "' + DATA_TAB + '" tab and not a different one.'],
  ['Resolving an orphaned row', 'Menu › Advanced › Health check lists orphans. Adopt orphan brings it into the master; Discard orphan deletes the stray copy. Ask whoever made the edit before discarding.'],
  ['Customizing dropdown menus', 'The "' + KEY_TAB + '" tab lists your children — its dropdown is generated from that list automatically, nothing to configure. For any other column that should be a dropdown (like Status), edit the "' + FIELD_OPTIONS_TAB + '" tab: column A is the field name (must match a field declared in Config.js), column B is a comma-separated list of allowed values. Then run Menu › Advanced › Generate dropdown menus. Editing that tab is enough — no code change or push needed. It applies to the master and to every registered child where the field exists.'],
  ['Escalation contact', 'Replace this row with who to contact when something breaks that this runbook does not cover.']
];

function ensureRunbookTab_(spreadsheet) {
  var sheet = ensureTab_(spreadsheet, RUNBOOK_TAB, ['Section', 'Content']);
  if (sheet.getLastRow() < 2) {
    sheet.getRange(2, 1, RUNBOOK_STARTER_ROWS.length, 2).setValues(RUNBOOK_STARTER_ROWS);
  }
  return sheet;
}

/**
 * Merges the Runbook tab against the starter rows per section: a filled-in
 * section wins, a blank or missing one falls back to starter content, and
 * any section the owner added beyond the starter five is carried through.
 * Never returns a blank document (S5).
 */
function readRunbook_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(RUNBOOK_TAB);
  var userRows = {};
  var order = [];
  if (sheet && sheet.getLastRow() >= 2) {
    var lastRow = sheet.getLastRow();
    sheet.getRange(2, 1, lastRow - 1, 2).getValues().forEach(function (r) {
      var section = String(r[0] || '').trim();
      if (section && r[1]) {
        userRows[section] = String(r[1]);
        order.push(section);
      }
    });
  }

  var starterSections = {};
  var out = RUNBOOK_STARTER_ROWS.map(function (starter) {
    starterSections[starter[0]] = true;
    return { section: starter[0], content: userRows[starter[0]] || starter[1] };
  });
  order.forEach(function (section) {
    if (!starterSections[section]) out.push({ section: section, content: userRows[section] });
  });
  return out;
}

DriftLog.js Detects when your configuration or roster has changed since the last SOP. (paste as-is)

src/DriftLog.js

/**
 * DriftLog.js — canonicalized hashing of FIELDS/SENTINELS/tab-names and
 * the child roster, with the log itself as the baseline.
 *
 * The baseline is the LAST APPENDED Drift Log row, never a
 * PropertiesService value living alongside it. Two separate storage
 * writes (a property plus a log row) would need a cross-service
 * transaction to stay consistent; reading the baseline back out of the
 * log removes that problem instead of trying to make it atomic (r7 #6).
 *
 * Scope, stated plainly: only config and roster are hashed. checkDrift_()
 * is called from inside the reconciliation sweep specifically because
 * that path already holds the script lock — running it anywhere
 * unprotected would reopen the exact race S6b tests against.
 */

function ensureDriftLogTab_(spreadsheet) {
  return ensureTab_(spreadsheet, DRIFT_LOG_TAB, ['Timestamp', 'Component', 'Old Hash', 'New Hash']);
}

function sortKeysDeep_(value) {
  if (Array.isArray(value)) return value.map(sortKeysDeep_);
  if (value && typeof value === 'object') {
    var out = {};
    Object.keys(value).sort().forEach(function (k) { out[k] = sortKeysDeep_(value[k]); });
    return out;
  }
  return value;
}

function sha256_(text) {
  var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, text, Utilities.Charset.UTF_8);
  return digest.map(function (b) { return ('0' + (b & 0xff).toString(16)).slice(-2); }).join('');
}

function canonicalConfigString_() {
  var cfg = {
    fields: FIELDS.map(function (f) {
      return { key: f.key || null, master: f.master || null, child: f.child || null, sync: f.sync };
    }),
    sentinels: SENTINELS.slice().sort(),
    tabs: { data: DATA_TAB, key: KEY_TAB, log: LOG_TAB, runbook: RUNBOOK_TAB, driftLog: DRIFT_LOG_TAB }
  };
  return JSON.stringify(sortKeysDeep_(cfg));
}

function canonicalRosterString_() {
  var roster = getChildRoster_();
  var sorted = roster.children.map(function (c) { return c.name + '::' + c.spreadsheetId; }).sort();
  return JSON.stringify(sorted);
}

/** Scans the whole Drift Log for the most recent hash per component. */
function lastDriftHashes_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(DRIFT_LOG_TAB);
  var hashes = { config: null, roster: null };
  if (!sheet || sheet.getLastRow() < 2) return hashes;
  var lastRow = sheet.getLastRow();
  sheet.getRange(2, 1, lastRow - 1, 4).getValues().forEach(function (r) {
    if (r[1] === 'config') hashes.config = r[3];
    if (r[1] === 'roster') hashes.roster = r[3];
  });
  return hashes;
}

/** Call only while holding the script lock (see file header). */
function checkDrift_() {
  var master = getMaster_();
  var sheet = master.getSheetByName(DRIFT_LOG_TAB);
  if (!sheet) return; // setup() hasn't run yet

  var baseline = lastDriftHashes_();
  var configHash = sha256_(canonicalConfigString_());
  var rosterHash = sha256_(canonicalRosterString_());
  var appended = false;

  if (baseline.config !== configHash) {
    sheet.appendRow([new Date(), 'config', baseline.config || '', configHash]);
    appended = true;
  }
  if (baseline.roster !== rosterHash) {
    sheet.appendRow([new Date(), 'roster', baseline.roster || '', rosterHash]);
    appended = true;
  }
  if (appended) {
    appendLog_('', master.getId(), OPERATIONS.RECONCILE, STAGES.VALIDATE, MESSAGES.DRIFT_DETECTED);
  }
}

function countDriftEntriesSince_(since) {
  var master = getMaster_();
  var sheet = master.getSheetByName(DRIFT_LOG_TAB);
  if (!sheet || sheet.getLastRow() < 2) return 0;
  var lastRow = sheet.getLastRow();
  var count = 0;
  sheet.getRange(2, 1, lastRow - 1, 1).getValues().forEach(function (r) {
    if (r[0] instanceof Date && r[0] > since) count++;
  });
  return count;
}

appsscript.json Project manifest: time zone, Drive access, and permissions. (check the time zone)

src/appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "version": "v3",
        "serviceId": "drive"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/userinfo.email",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

Ten files total, counting appsscript.json. Push them:

terminal

clasp push

If clasp says "Skipping push," on a brand-new project, add --force. appsscript.json above sets a fixed time zone (America/Los_Angeles); if that's not yours, change it to match your master spreadsheet's own time zone (File › Settings in the Sheets UI) before you push, mismatched time zones between master and child are one of the checks Step 7 runs.

5Write your FIELDS config

The Config.js you just pasted ships with an example configuration, a small referral tracker. Now point it at the sheet you actually built in Step 1. This is exactly the kind of repetitive, mechanical rewrite a coding agent is good at and you shouldn't do by hand. Open Claude Code or Codex in your project folder and hand it your master's real headers:

prompt

My master spreadsheet's "Tracker" tab has these column headers, in order:
[paste your header row here]

My unique ID column is "[your column]" and my routing column is "[your column]".
For every other column, here's the direction it should sync (down = master to
child, up = child to master, both = either side):

[list each column and its direction]

Some of my child sheets use different header text for the same field:
[list any that differ, e.g. "Date of Consult" on master, "Consult Date" on child]

Rewrite the FIELDS array in Config.js to match this. Don't change anything
else in the file.

Check the result against the direction table in The FIELDS model above before you push again.

6Register your children

Open your master spreadsheet in the browser. Reload it, a new menu called Advanced should appear (give it a few seconds after the push). Run Advanced › Run setup once; it creates the Assigned To, Sync Log, Runbook, Drift Log, and Dropdown Menu Config tabs for you and protects the Unique ID column against direct edits.

Open the new Assigned To tab. It has two columns: Child Name and Spreadsheet ID. Add one row per child, using the exact name you'll route rows to (this is what your routing column's values need to match) and the spreadsheet ID you noted in Step 1.

Run Advanced › Run setup a second time now that children are registered, it protects the Unique ID column on each one too.

7Validate before you sync anything

Two menu items check your setup without touching a single row of data:

Advanced › Validate schema confirms every field in FIELDS resolves to a real column on the master and on every registered child, and that your routing field doesn't accidentally exist on a child (it shouldn't, it's master-only).
Advanced › Validate data checks the rows themselves: no blank or duplicate unique IDs, no ambiguous child names in the Assigned To tab, no routing value that doesn't match a real child or a sentinel.

Fix everything either one reports before moving on. Advanced › Show schema is there any time you want to see exactly which column each field resolved to, on every sheet.

8Install triggers

Run Advanced › Install triggers. Google will ask you to authorize the script the first time, review the permissions and approve them, they're what let this engine read and write your spreadsheets and manage its own triggers. You'll see a confirmation naming how many child triggers, plus the one master trigger and one daily maintenance trigger, got installed.

This is safe to run again any time, later, it reconciles against whatever's already installed rather than tearing everything down and rebuilding it, so re-running it after registering a new child never disturbs the ones already working.

9Test it

Add a new row in the master's data tab with a real value in your routing column. Within a second or two it should appear in that child's spreadsheet. Edit a down field in the master, watch it update in the child. Edit an up field in the child, watch it update in the master.

Checkpoint

It works, or it doesn't. If a row didn't appear or an edit didn't sync, don't guess, go straight to the Sync Log below; it names exactly what happened on every rejected edit.

10Add more children

Same three moves each time: add a row to the Assigned To tab with the new child's name and spreadsheet ID, run Advanced › Run setup to protect its Unique ID column, then Advanced › Install triggers to wire it in. Another coding-agent handoff that's genuinely useful here, if you're registering several children with headers you haven't typed out yet:

prompt

Here are the names and spreadsheet IDs for N more children I need registered
in the "Assigned To" tab: [list them]. Tell me the exact rows to add.

The ceiling is 18 children, fixed by Apps Script's 20-triggers-per-script limit (one goes to the master, one to the daily maintenance job). Advanced › Install triggers will refuse and name the limit rather than silently failing partway if you try to exceed it. There's no clean workaround, a second account can't see or manage the first account's triggers, so splitting across accounts would need its own ownership and health-check logic this engine doesn't have. If you're going to need more than 18, that's worth knowing before you build the other 17.

11The Sync Log

Every rejected edit, timeout, and partial move writes a row to the Sync Log tab: timestamp, unique ID, which sheet, what operation, what stage, and a specific reason. This is the answer to the single most common failure mode in any sync system, an edit silently doesn't sync and there's no error dialog anywhere to explain why.

Advanced › Health check summarizes the log for you: recent failures, whether your trigger inventory still matches your Assigned To tab, and how old your last generated SOP is (more on that in Step 14). Run it first, always, before digging through the raw log rows yourself.

What this doesn't do

Stated plainly, rather than discovered the hard way:

Deleting a row isn't an edit, it's a structural change, and Apps Script doesn't report which row got deleted when one does. Delete a child's copy and the engine notices and restores it from the master. Delete the master's copy and the engine reports an orphan for a human to resolve (Step 13). Delete an unrouted row, or delete it everywhere at once, and it's gone with no trace, nothing to notice it's missing. Retire a row with a routing sentinel like Closed instead of deleting it.
Formulas and script-made edits never sync. Only a genuine edit through the Sheets UI fires the trigger this engine depends on. A cell driven by a formula, or written by some other script or API call, is invisible to it.
Two edits at nearly the same instant on a both field converge, but which one wins is unpredictable. Covered in The FIELDS model above; it's the honest tradeoff of not building a full revision-history system.
A dropped event needs the reconciliation sweep to repair it. If a lock times out (rare, but possible under real concurrent load), that one edit is lost rather than retried. The nightly sweep, and Advanced › Run reconciliation sweep on demand, is what brings both sides back into agreement afterward.

12Child-origin rows

Everything so far assumes rows start in the master. If your team also needs to create new rows directly from a child spreadsheet, entering a new client themselves, for instance, that works too: fill in a new row on a child (leaving the ID column blank, since the engine fills it in), and it flows up into the master automatically, with its routing value already set to whichever child it came from.

A row is treated as ready to sync once it has real content beyond a blank ID, not the instant you touch the row at all, so a half-filled-in row waits quietly until there's something worth creating.

13Repair tools

Four menu items exist for when something needs a manual push rather than waiting for the next edit or the nightly sweep:

Resync selected row (with your cursor on a master row) force-pushes every field to that row's child, overwriting whatever's there.
Run reconciliation sweep runs the same repair the nightly job does, on demand: it restores misplaced or missing child copies, repairs any field that's drifted out of agreement, and reports (never silently fixes) rows that exist in a child but nowhere in the master.
Adopt orphan takes one of those reported rows and brings it into the master, keyed on the child's own ID, exactly as if it had synced up the first time.
Discard orphan deletes the child's stray copy instead. It asks you to confirm first; this one's not reversible.

An orphan (a row that exists in a child but not in the master) almost always means a sync got interrupted partway. The engine never guesses which of Adopt or Discard is correct, that's a judgment call about your actual data, so it surfaces the row and waits for a person to decide.

14Generate your SOP

Once this is running for more than just you, someone else needs to understand it without reading the code: what syncs, where, who's registered, what's currently broken. Menu › SOP › Generate and open builds that document live, from your actual configuration, and writes it to your Drive as a standalone HTML file you can share like any other Drive file. Here's a sample, built from fabricated data, showing exactly what you'll get: view the sample SOP.

Most of the document is generated and never needs your input: your field map, registered children, trigger inventory, and current health. One tab, Runbook, is yours to fill in by hand, no code, no clasp push, and it starts with sensible defaults so the SOP is never blank. Take a minute now to open that tab and replace the "Escalation contact" row with a real name or channel; a generated document that just says "contact your admin" isn't much of a handoff.

Why a file instead of a live page

A live web page sounds better until you look at who's allowed to see it: it either runs with the admin's full access (which means anyone who can view the page can act with that authority) or with the viewer's own access (which means it reports an empty trigger inventory to anyone who isn't the admin, since Apps Script only shows a script's triggers to whoever's account owns them). A generated file, regenerated whenever it needs to be current, sidesteps both problems entirely.

15Share it

Share the generated file the normal way, through Drive's own sharing dialog, per person or per group, same as any document. It's inert HTML: no script tag, no live connection back to your spreadsheet, safe to hand to someone outside your organization if you need to.

Advanced › Health check tells you when the copy you shared has gone stale: it reports how many days old your last generated SOP is, and how many configuration changes have happened since. When that number stops being zero, regenerate and re-share, one click each.

Troubleshooting: nothing happened and there's no error

The trigger isn't installed

Run Advanced › Install triggers again; it's safe to re-run and reports exactly what got installed.

The Apps Script API is off

This only affects clasp itself (cloning, pushing), not live syncing. See the setup guide.

Wrong spreadsheet ID in the Assigned To tab

Advanced › Health check reports a child as unauthorized or unreadable if its ID is wrong or you've lost access to it.

A header got renamed

Run Advanced › Validate schema; it names the exact field and sheet that stopped resolving.

The edit was made by a script, a formula, or an API call

None of these fire the trigger this engine depends on. See What this doesn't do.

A lock timed out under heavy simultaneous editing

Rare, but it happens. Advanced › Health check surfaces it, and Run reconciliation sweep repairs whatever drifted as a result.

You've hit the trigger quota

Advanced › Install triggers refuses and names the limit rather than partially installing. See Step 10.

What's next

If you built this and it's syncing, that was the whole point, you now own a working system instead of a monthly bill for one. If you'd rather not run the setup yourself, or you need it adapted to something more specific than this guide covers, that's what I do for a living.

Two-way sync for Google Sheets

Why two-way sync doesn't loop

Why one script is safer than many

The shape of the system

The FIELDS model

0Quick check

1Create your sheets

2Scaffold the directory

3Clone the master's bound script

4Paste the engine

5Write your FIELDS config

6Register your children

7Validate before you sync anything

8Install triggers

9Test it

10Add more children

11The Sync Log

What this doesn't do

12Child-origin rows

13Repair tools

14Generate your SOP

15Share it

Troubleshooting: nothing happened and there's no error

The trigger isn't installed

The Apps Script API is off

Wrong spreadsheet ID in the Assigned To tab

A header got renamed

The edit was made by a script, a formula, or an API call

A lock timed out under heavy simultaneous editing

You've hit the trigger quota

What's next