You keep a master range in one spreadsheet and want a copy in another file that updates by itself — without copy-pasting. Google Sheets has no built-in “sync this range to that file” command, so you pick one of three routes.
Method 1 — An IMPORTRANGE formula
No script, and it works across files. In the destination sheet:
- Click an empty cell and enter:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID", "Sheet1!A1:C10") - Click Allow access the first time Sheets asks to connect the two files.
The range then fills in and refreshes by itself.
- One-way and read-only. The imported cells belong to the formula. Typing over one breaks the import, and nothing flows back to the source.
- The destination area has to stay empty, or you get
#REF!(“Array result was not expanded because it would overwrite data”). - The source range is just text in the formula. If someone inserts a row or column in the source, the reference does not follow it.
- You do not control the refresh, and a large import can be slow.
- You must approve each pair of files once, and if access is revoked or the source is deleted the import turns into an error.
Method 2 — An installable onEdit Apps Script
You decide exactly what is copied — but you also own the upkeep. In the source spreadsheet open Extensions → Apps Script, paste this, and save:
Then install it as a trigger: in the Apps Script editor click the
clock icon (Triggers), Add Trigger,
choose copyToTarget, event source From spreadsheet,
event type On edit.
- It must be an installable trigger. A simple
onEdit(e)cannot open another spreadsheet, so naming the functiononEditwill not work. - It only fires on manual edits. Changes made by another script, the Sheets API or an import do not trigger it.
- It copies values, not formulas or formatting, and only in one direction. Syncing back means a second script in the other file.
- The IDs, sheet names and range are hard-coded. Every new pair of files means copying the script again and editing it.
- Triggers run as you. If your access to the target changes, or you hit Google’s daily trigger run-time quota, copies silently stop.
Method 3 — The SheetSync add-on
Install once and link ranges from a dialog — no formulas, no code. In SheetSync you:
- select a range, open Manage Links and pick the target spreadsheet with Google’s own file picker;
- choose the target range (it must have the same number of rows and columns as the selection) and confirm;
- keep it running in Auto mode (every edit is pushed), Manual (a “Sync now” button) or Scheduled (every hour up to once a day);
- link one range to several targets, and sync in both directions — an edit on either side updates the other.
SheetSync needs to be installed on both spreadsheets, with edit access to both. The free plan covers one link with manual sync; automatic and scheduled sync and more links are on the paid plans.
Which method should you use?
| IMPORTRANGE | onEdit script | SheetSync | |
|---|---|---|---|
| Direction | One-way, read-only | One-way (a second script for the way back) | Either way, or both |
| Edit the destination | No — it breaks the import | Overwritten on the next copy | Yes — it syncs back |
| When it updates | On Google’s schedule | On each manual edit | On edit, on demand, or on a schedule |
| Setup | One formula + Allow access | Paste code + install a trigger | A dialog, no code |
| Upkeep | Reference does not follow inserted rows | You own every fix | Updates itself |
| Cost | Free | Free | Free plan for one link, paid plans for more |
IMPORTRANGE is the quickest answer when the destination is a read-only report. An onEdit script suits a single, stable pair of files you are happy to maintain. SheetSync is the choice when the data must flow both ways, fan out to several sheets, or you do not want code to look after.
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 →