Guides

Sync a range between two Google Sheets automatically

A range in one spreadsheet should always match a range in another. Here are the three ways to do it, and where each one falls short.

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:

  1. Click an empty cell and enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID", "Sheet1!A1:C10")
  2. Click Allow access the first time Sheets asks to connect the two files.

The range then fills in and refreshes by itself.

Watch out for:
  • 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:

/** * Copy A1:C10 of "Sheet1" to the same cells of another spreadsheet * whenever a cell inside that range is edited. */ function copyToTarget(e) { var sheet = e.range.getSheet(); if (sheet.getName() !== "Sheet1") return; if (e.range.getRow() > 10 || e.range.getColumn() > 3) return; var target = SpreadsheetApp .openById("TARGET_SPREADSHEET_ID") .getSheetByName("Sheet1"); var values = sheet.getRange("A1:C10").getValues(); target.getRange("A1:C10").setValues(values); }

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.

Watch out for:
  • It must be an installable trigger. A simple onEdit(e) cannot open another spreadsheet, so naming the function onEdit will 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:

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.

Good to know: a link stays attached to the same cell addresses on each side. 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, so data never lands in the wrong cells unnoticed. SheetSync syncs values, not formulas or formatting.

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 →