Guides

Copy one range to several Google Sheets

Maintain a list once and have it appear in every other spreadsheet that needs it. Here are the three routes and their limits.

One master list — a price list, a staff roster, a product catalogue — that has to appear in several other spreadsheets. You maintain it in one place and every copy should follow. That is a hub: one source, several targets.

Method 1 — An IMPORTRANGE in every target

In each target spreadsheet, put a formula that reads the master:

=IMPORTRANGE("MASTER_SPREADSHEET_URL", "Prices!A1:D200")

It is live and needs no script. Each target pulls the data itself, so adding a target means adding a formula there.

Watch out for:
  • The imported cells belong to the formula: nobody can type into them, and the destination area must be empty or you get #REF!.
  • Each target has to click Allow access to the master once.
  • Refresh timing is not something you control, and references do not follow rows inserted in the master.
  • Nothing flows back: a change made in a target never reaches the master.

Method 2 — A script on the master that writes to every target

Put an installable onEdit trigger on the master that copies the range to a list of target files:

/** Copy Prices!A1:D200 to every target after an edit. */ var TARGET_IDS = ["TARGET_ID_1", "TARGET_ID_2", "TARGET_ID_3"]; function copyToTargets(e) { var sheet = e.range.getSheet(); if (sheet.getName() !== "Prices") return; var values = sheet.getRange("A1:D200").getValues(); TARGET_IDS.forEach(function(id) { SpreadsheetApp.openById(id).getSheetByName("Prices") .getRange("A1:D200").setValues(values); }); }

Add it under Triggers → Add Trigger → copyToTargets, From spreadsheet, On edit.

Watch out for:
  • Adding or removing a target means editing the code.
  • Every edit rewrites the whole range in every target, and the run gets slower with each target you add.
  • Edits made by another script, the Sheets API or an import do not fire onEdit, so they are never copied.
  • The trigger runs as the person who installed it. If that person loses access to a target, that copy silently stops.
  • It only goes one way and copies values only.

Method 3 — One range, several links in SheetSync

In SheetSync, link the same range of the master to a range in each target. One link per target, all sharing the same master range:

Every target counts as one link on your plan, and SheetSync has to be installed on each spreadsheet with edit access to it.

Good to know: links work both ways. An edit made in a target travels back to the master and on to the other targets. That is what you want for a shared list, but if the targets must only read the master, protect the linked range in each target (Data → Protect sheets and ranges), or use IMPORTRANGE for those. Auto sync reacts to edits made by a person; use Sync now or a scheduled sync to pick up changes written by scripts or imports. 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. It syncs values, not formulas or formatting.

Which method should you use?

  IMPORTRANGE Script on the master SheetSync
Add a target New formula in the target Edit the code Add a link in a dialog
Targets can edit the data No (read-only) Yes, but never flows back Yes, flows back and out
One target fails Only that target Can stop the whole run Others keep syncing
Catches script or import changes Yes (live formula) No With Sync now or scheduled sync
Setup One formula per target Script + trigger A dialog, no code

Use IMPORTRANGE when the targets only need to show the master. Use a script for a small, stable set of targets you are willing to maintain. SheetSync fits when you add and remove targets regularly, or when the targets also need to edit the data.

SheetSync is coming soon

Keep one master range identical across several Google Sheets — one link per target, set up from a dialog, no script to maintain.

See how it works →