Sales live in one spreadsheet, inventory in another, the budget in a third — and the monthly report needs all three. Copy-pasting between them is how numbers go stale the moment someone updates a source. IMPORTRANGE builds a live link instead: one formula pulls a range from another spreadsheet and keeps it current, so your report reads from the originals rather than from last week’s copy. This covers the syntax, the one-time permission step, how updates and limits actually work, and the QUERY-and-FILTER combinations that turn a raw import into a real report.
The syntax and the permission prompt
IMPORTRANGE takes two arguments: the URL of the source spreadsheet and a string naming the sheet and range to pull.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/ABC123...", "Sheet1!A2:D")The first argument is the full URL (or just the long key from it); the second is a string in the form "sheet_name!range", quotes included. Open-ended ranges work here too — "Sheet1!A2:D" pulls every row from A2 down, so the import grows as the source does.
The first time a destination sheet pulls from a new source, the formula returns a #REF! error with an “Allow access” prompt. Click it once and the link is authorized — you need at least view access to the source, and if it’s your own file you have it automatically. That permission is granted per source-destination pair and persists until someone revokes it, so you authorize each new connection exactly once. After that, the data simply flows.
Your first import
Say a team’s deals live in a “Sales” spreadsheet and you want them in a reporting sheet. In the destination, the formula references the source and the import spills into place:
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | North | 1200 |
| 3 | South | 900 |
Put the IMPORTRANGE formula in the destination’s A1 and the whole range appears below it, updating as the source changes. The key habit: write IMPORTRANGE in a single anchor cell and let it spill, rather than wrapping it in every cell. One formula, one link, one place to maintain. If the source adds a region tomorrow, the open-ended range picks it up with no edit on your end — the report stays complete on its own.
This is what makes IMPORTRANGE a team tool rather than a personal one. When several people each own a sheet — one keeps sales, another inventory — a report can pull a live view of all of them without anyone exporting or pasting. It fits naturally with a shared-sheet workflow where each source has a clear owner and the report sits read-only on top. The owners keep editing their own sheets; the report just reflects whatever they’ve done the next time it refreshes, with no coordination step in between.
How updates and limits actually work
An imported range isn’t a one-time copy; it’s a live connection that refreshes on its own. While the destination is open, IMPORTRANGE checks the source for changes roughly every hour, and recalculation cycles run more often than that — so an edit in the source shows up in the report within minutes to an hour, no manual refresh required.
That size limit shapes good practice: narrow the range at the source rather than importing everything and filtering after. If you only need three columns for a summary, pull three columns. A lean import is faster to refresh and easier on every formula that reads it downstream.
The refresh also isn’t instant or guaranteed to the second. If you need a number that’s correct this exact moment — a live count during a meeting — IMPORTRANGE’s hourly check can lag reality briefly. For reporting, where “current within the hour” is fine, that’s a non-issue; for second-by-second accuracy you’d keep the data in the same file and reference it directly. Knowing which case you’re in saves a confused “why is this number old?” later on.
Combine with QUERY or FILTER for a real report
A raw import is just the source data mirrored. The power comes from wrapping it so the destination reshapes what it pulls — filtering rows, summarizing totals, sorting — without touching the source at all. FILTER narrows to the rows you want:
=FILTER(IMPORTRANGE("url", "Sheet1!A2:D"), IMPORTRANGE("url", "Sheet1!C2:C")="North")For anything more report-like — grouping, aggregating, ordering — wrap the import in the QUERY function, which treats the imported range like a small database you can run SQL-style summaries against. This is the pattern behind most live dashboards: IMPORTRANGE brings the data over, QUERY turns it into the numbers the report needs, and both update on their own. It pairs naturally with array formulas when you need per-row calculations on the imported data.
Common errors and how to clear them
Most IMPORTRANGE trouble is one of a few predictable causes. Run this list before assuming the link is broken.
- ✓
#REF!with an “Allow access” button means permission hasn’t been granted — click it once - ✓
#REF!without a button usually means the range string is malformed — check the quotes and thesheet!rangeformat - ✓ A wrong or renamed source tab breaks the string — the sheet name must match exactly
- ✓ Don’t chain imports of imports; pull from the original source, not from another sheet that itself imports
The malformed-string error is the most common after permissions. The second argument is text, so the sheet name and range both live inside one set of quotes — "Sales!A2:D", not "Sales"!A2:D. And resist building an import that reads from a sheet that is itself importing from somewhere else; each hop adds lag and a new way to break, and the chain fails entirely if any link in the middle loses access.
A practical fix when a long-broken import resists repair: delete the formula, re-enter it fresh, and re-grant access. Stale permission states sometimes cling to an edited formula, and a clean re-entry clears them faster than debugging the old one — the spreadsheet equivalent of turning it off and on again, and it works more often than it should.
When a live link is the right tool
Reach for IMPORTRANGE when data genuinely lives in separate files that different people own — sales in one, inventory in another — and a report needs a current view of all of them. It keeps one source of truth per dataset while letting any number of reports read from it, which is exactly what you want on a team where one person owns each sheet. For data that belongs in the same file, use tabs and direct references instead; IMPORTRANGE is for crossing the boundary between separate spreadsheets, and used that way it quietly retires copy-paste reporting for good. Set the links up once, keep each import lean, and your reports read from live sources every time someone opens them — no exports, no stale numbers, no Monday-morning copy-paste ritual to forget. Google’s own IMPORTRANGE documentation covers the permission model in full.