Guides

Two-way sync between Google Sheets

An edit in either spreadsheet should show up in the other. Google Sheets has no built-in way to do that — here are the three routes and their limits.

Two teams each keep their own copy of a table, and a change made by either should reach the other. That is two-way sync, and it is harder than copying one way: you have to carry edits in both directions without them bouncing back, and decide what happens when both sides change.

Method 1 — IMPORTRANGE in both files

The obvious idea is an IMPORTRANGE in each sheet pointing at the other. It does not work on the same cells:

The workaround is to split ownership: sheet A owns columns A–C (people type there) and imports D–F from sheet B; sheet B owns D–F and imports A–C from sheet A. Each side then sees the other’s data.

Watch out for:
  • It is not true two-way sync: the same cell can never be edited on both sides.
  • All the usual IMPORTRANGE limits apply — refresh timing you do not control, references that do not follow inserted rows, and #REF! if the destination area is not empty.
  • The layout has to be designed around who owns which columns.

Method 2 — An installable onEdit script in each file

Put a copy script in both spreadsheets, each writing to the other. In each file open Extensions → Apps Script, paste this (swap in the other file’s ID), and save:

/** * Mirror A1:C10 of "Sheet1" to the OTHER spreadsheet. * Paste into both files and set OTHER_ID to the opposite file. */ var OTHER_ID = "OTHER_SPREADSHEET_ID"; function mirrorToOther(e) { var sheet = e.range.getSheet(); if (sheet.getName() !== "Sheet1") return; if (e.range.getRow() > 10 || e.range.getColumn() > 3) return; var other = SpreadsheetApp.openById(OTHER_ID).getSheetByName("Sheet1"); other.getRange("A1:C10").setValues(sheet.getRange("A1:C10").getValues()); }

In each file, add an installable trigger: Triggers (clock icon) → Add Trigger → mirrorToOther, From spreadsheet, On edit.

Edits made by a script do not fire onEdit, so the two scripts do not copy back and forth forever.

Watch out for:
  • Two scripts, two triggers, two hard-coded IDs — and each is authorised by whoever installed it. If that person loses access, one direction silently stops.
  • The same rule that prevents loops also stops cascades: a script copy from A to B does not trigger B’s script, so A → B → C needs its own wiring.
  • Edits made by other scripts, the Sheets API or an import do not fire the trigger, so they are never copied.
  • When both sides change close together, whichever script runs last overwrites the other — with no record of what was lost.
  • It copies values only, and the whole range on every edit.

Method 3 — The SheetSync add-on

Link the two ranges once and an edit on either side updates the other. In SheetSync:

SheetSync must be installed on both spreadsheets, with edit access to both. Edits are applied in the order they arrive. The free plan covers one link with manual sync; automatic and scheduled sync and more links are on the paid plans.

Good to know: Auto sync reacts to edits made by a person. Changes written by another script or an import do not fire Google’s edit events, so use Sync now or a scheduled sync to pick those up. A link also stays attached to the same cell addresses: if rows or columns are inserted or removed on a linked sheet, SheetSync stops those links and asks you to confirm the ranges before it continues. SheetSync syncs values, not formulas or formatting.

Which method should you use?

  IMPORTRANGE Two onEdit scripts SheetSync
Same cell editable on both sides No Yes Yes
Loops back Circular reference No (script edits do not fire onEdit) No (echo protection)
Chain of three or more sheets One import per pair Does not cascade Cascades across the chain
Both sides edit at once n/a Last script wins Edits applied in arrival order
Setup Split columns + formulas Two scripts + two triggers A dialog, no code
Cost Free Free Free plan for one link, paid plans for more

IMPORTRANGE is enough when each side owns different columns. Two scripts work for one stable pair of files you can maintain. SheetSync is the choice when the same range has to stay identical on both sides, or across more than two spreadsheets, without looking after code.

SheetSync is coming soon

Keep a range in one Google Sheet identical to a range in another, in both directions — set up once from a dialog, no formulas and no script to maintain.

See how it works →