Build a two-way sync between two spreadsheets

Northwind keeps two views of the same work: an internal Pipeline sheet the team edits freely, and a client-facing Status sheet the client edits too. When both sides can change a row, keeping them in step by hand turns into a game of copy-paste — and somebody always pastes over a change they did not mean to.

This script syncs the two sheets in both directions. The trick is a saved snapshot from the last run: by comparing each row to that snapshot, the script can tell which side actually changed since last time and copy that side’s version across. When both sides changed the same row, it falls back to a fixed rule — the internal sheet wins — so the result is at least predictable.

What you’ll need

• Two spreadsheets, each with a header row and an id column that uniquely identifies a row. The script matches rows across the two sheets by id.
• Both sheets sharing the same set of column headers, so a row from one can be written into the other unchanged.
• Edit access to both files from the account that owns the script.
• The first sheet (tab) in each spreadsheet is the one synced.

The script

// The two spreadsheets to keep in step. SHEET_A wins on a conflict.
const SHEET_A = '1abcInternalPipelineId'; // internal Pipeline
const SHEET_B = '1abcClientStatusId';     // client-facing Status

// Script Property key for the snapshot of the last successful sync.
const SNAPSHOT_KEY = 'LAST_SYNC';

/**
 * Syncs SHEET_A and SHEET_B in both directions. Uses a saved
 * snapshot to decide which side changed each row since last run.
 */
function syncTwoWay() {
  // 1. Read both sheets and the snapshot from the previous run.
  const a = readSheet(SHEET_A);
  const b = readSheet(SHEET_B);
  const snapshot = JSON.parse(
    PropertiesService.getScriptProperties().getProperty(SNAPSHOT_KEY) || '{}'
  );

  // 2. Index every row set by its id for quick lookup.
  const byId = (rows) => Object.fromEntries(rows.map((r) => [r.id, r]));
  const aById = byId(a);
  const bById = byId(b);

  // 3. Walk every id seen in either sheet and decide its winner.
  const ids = new Set([...Object.keys(aById), ...Object.keys(bById)]);
  const merged = [];

  for (const id of ids) {
    const rowA = aById[id];
    const rowB = bById[id];
    const rowLast = snapshot[id];

    if (rowA && !rowB) {
      // New row in A — carry it into B.
      merged.push(rowA);
    } else if (rowB && !rowA) {
      // New row in B — carry it into A.
      merged.push(rowB);
    } else if (JSON.stringify(rowA) === JSON.stringify(rowLast)) {
      // A is unchanged since last sync, so B holds the new edit.
      merged.push(rowB);
    } else if (JSON.stringify(rowB) === JSON.stringify(rowLast)) {
      // B is unchanged since last sync, so A holds the new edit.
      merged.push(rowA);
    } else {
      // Both sides changed — A wins, but keep any fields only B has.
      merged.push({ ...rowB, ...rowA });
    }
  }

  // 4. Write the merged result back to both sheets.
  writeSheet(SHEET_A, merged);
  writeSheet(SHEET_B, merged);

  // 5. Save the merged state as the snapshot for the next run.
  PropertiesService.getScriptProperties().setProperty(
    SNAPSHOT_KEY,
    JSON.stringify(Object.fromEntries(merged.map((r) => [r.id, r])))
  );
  Logger.log('Synced ' + merged.length + ' rows.');
}

/**
 * Reads the first sheet of a spreadsheet and returns its rows as
 * objects keyed by the header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) =>
    Object.fromEntries(header.map((key, i) => [key, r[i]]))
  );
}

/**
 * Clears the first sheet of a spreadsheet and writes `rows` back,
 * using the keys of the first row as the header.
 */
function writeSheet(id, rows) {
  const sheet = SpreadsheetApp.openById(id).getSheets()[0];
  const header = Object.keys(rows[0] || {});
  sheet.clear();
  if (!header.length) return;

  sheet.getRange(1, 1, 1, header.length).setValues([header]);
  if (rows.length) {
    const out = rows.map((r) => header.map((key) => r[key] ?? ''));
    sheet.getRange(2, 1, out.length, header.length).setValues(out);
  }
}

How it works

1. syncTwoWay reads both sheets through readSheet, which returns each row as an object keyed by header, and loads the snapshot of the last successful sync from Script Properties.
2. Both row sets are indexed by id, so any row can be found in one lookup.
3. The script collects every id that appears in either sheet and decides a winner for each:
   • present in only one sheet → that row is new, carry it across;
   • unchanged in A since the snapshot → B holds the real edit, take B;
   • unchanged in B since the snapshot → A holds the real edit, take A;
   • changed on both sides → a genuine conflict, so A wins by rule, with { ...rowB, ...rowA } keeping any field B has that A lacks.
4. The merged rows are written back to both sheets, leaving them identical.
5. The merged state is saved as the new snapshot, so the next run has a fresh baseline to compare against.

Example run

Suppose the snapshot from the last run had row C-104 with stage “Proposal”. Between runs, the client edited only that row on the Status sheet:

Sheet	id	client	stage
Pipeline (A)	C-104	Drayton Mills	Proposal
Status (B)	C-104	Drayton Mills	Approved

A matches the snapshot, B does not — so B holds the real change. After the sync, both sheets show C-104 at stage “Approved”, and the new snapshot records it. Had the team also moved C-104 on the Pipeline sheet in the same window, A would win and the client’s “Approved” would be overwritten.

Trigger it

Run the sync on a short interval so the two sheets never drift far apart:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for syncTwoWay, time-driven, minutes timer, every 5 minutes.

A tighter interval narrows the window in which both sides can edit the same row before a sync runs — which is exactly the window where conflicts happen.

Watch out for

• Two-way sync without a true diff history is lossy. If both sides edit the same row inside one interval, the losing side’s change is gone with no record.
• The snapshot lives in Script Properties as JSON. A very large sheet can outgrow the property size limit — for big or busy datasets, see Build a two-way Sheets-Airtable sync.
• Both sheets are cleared and rewritten on every run. Formulas, formatting, and notes on the synced tab will not survive — keep only plain data there.
• Rows are matched on id. A blank or duplicated id will merge or drop rows unpredictably, so make sure every row has a unique one.
• A row deleted on one side is not deleted on the other — it reappears from the side that still has it. Deletion needs its own handling, such as a deleted flag column.