Guides

IMPORTRANGE alternatives for Google Sheets

IMPORTRANGE is great for read-only reporting. When you need to edit the data where it lands, here is what to use instead.

IMPORTRANGE is the first thing most people try, and for read-only reporting it is often exactly right. It stops being enough when you need to edit the data where it lands, keep it as plain values, or have it travel in both directions.

What IMPORTRANGE does well

Where IMPORTRANGE stops

The usual walls:
  • Read-only: imported cells belong to the formula. You cannot type into them, and nothing travels back to the source.
  • #REF! if the area is not empty: the destination range must be free, or the import is blocked.
  • Timing you do not control: there is no “sync now” and no way to see when the last refresh happened.
  • References do not follow rows: inserting rows in the source does not move your references.
  • Everything is a formula: you cannot sort, annotate or overwrite individual imported cells.

Alternative 1 — Copy values with Apps Script

A script can copy the values instead of importing them, so the destination holds ordinary cells you can edit. Run it by hand from a menu, on a time-based trigger, or after each edit:

/** Copy Sheet1!A1:D100 from this file to another file. */ function copyRange() { var source = SpreadsheetApp.getActive().getSheetByName("Sheet1"); var target = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID").getSheetByName("Sheet1"); target.getRange("A1:D100").setValues(source.getRange("A1:D100").getValues()); }
Watch out for:
  • It is one-way, hard-codes the other file’s ID and runs as the person who set it up.
  • A trigger on edit does not see changes made by other scripts, the Sheets API or an import.
  • You maintain the code: a new range or a new target means editing it.

Alternative 2 — Link the ranges with SheetSync

SheetSync keeps a range in one spreadsheet identical to a range in another, as plain values:

SheetSync must 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: Auto sync reacts to edits made by a person; use Sync now or a scheduled sync for 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 one should you use?

  IMPORTRANGE Apps Script copy SheetSync
Destination cells editable No Yes Yes
Changes flow back No Only with a second script Yes
See when it last ran No Only if you log it Per link, with errors
Needs code No Yes No
Cost Free Free Free plan for one link, paid plans for more

Stay with IMPORTRANGE when the destination only needs to display the data. Reach for a script when you need plain values and are happy to maintain the code. SheetSync is for the case where both sides have to be editable and stay identical.

SheetSync is coming soon

Replace a fragile IMPORTRANGE with a link that keeps two ranges identical — editable on both sides, set up from a dialog.

See how it works →