IMPORTRANGE lets one Google Sheets file pull live data from another. This guide covers the formula, the one-time Allow Access step, how refreshing works, and how to fix the errors that stop it working.
Everything below comes from Google's IMPORTRANGE help page, as of November 2026. The page shows no update date.
The IMPORTRANGE formula, argument by argument
=IMPORTRANGE(spreadsheet_url, range_string)
- spreadsheet_url is the URL of the source spreadsheet. Put it in quotation marks, or point to a cell that contains the URL.
- range_string is the range to import, written as
"[sheet_name!]range", such as"Sheet1!A2:B6"or"A2:B6". It also needs quotation marks, or a reference to a cell holding the text.
The sheet name is optional. If you leave it out, IMPORTRANGE reads the range from the first sheet in the source file. Include it anyway if the source has several tabs, so the formula doesn't change meaning when someone reorders them.
Connect two sheets with IMPORTRANGE
- Open the source spreadsheet and copy its URL from the address bar.
- In the destination spreadsheet, click the cell where the data should start.
- Type the formula with the source URL and range, for example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")
- Press Enter and wait a few seconds.
- A #REF! error appears with the prompt "You need to connect these sheets. Allow Access." Click Allow Access.
The imported data fills in. You only see this prompt the first time a destination sheet pulls from a new source sheet.
If you don't own the source sheet
You'll get a "You don't have permissions to access that sheet" message instead. To fix it:
- Open the source URL in your browser.
- Request access.
- Wait for the owner to grant it, then come back to the formula.
What Allow Access actually grants
This part is easy to miss. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source, not just the range you typed. Access stays in effect until the user who granted it is removed from the source. Google also says this access counts against the source sheet's 600-user share limit.
If the source holds data you wouldn't want every destination editor to see, put the shareable data in a separate file and import from that.
Three ways to name the range
A plain range, as above, is the most common. Google's help page also shows two alternatives that hold up better when the source layout changes.
A table reference, which pulls one column of a table:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "DeptSales[Sales Amount]")
A named range defined in the source file:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sales_total")
Keep the URL in a cell
Both arguments can be cell references. Put the source URL in A2 and the range text (such as Sheet1!A2:B6) in B2, then:
=IMPORTRANGE(A2,B2)
Leave the quotation marks off the cell references, or Sheets treats them as literal text. Keeping the URL in one cell means you change it once when you swap source files, instead of editing every formula.
How and when IMPORTRANGE refreshes
If your import looks stale, it helps to know what triggers a reload:
- Any update to the source makes all open receiving documents refresh, with a green loading bar.
- IMPORTRANGE waits for calculations in the source to finish before returning results.
- It checks for updates every hour while the document is open, even if nothing has changed.
- Deleting and re-entering the formula, or overwriting the cell with the same formula, forces a reload.
Google's page is a little contradictory on reopening. It says opening and reloading the document does not trigger a reload, but also that IMPORTRANGE reloads when a document is first opened or was opened within the past 5 minutes. If you need fresh data right now, re-entering the formula is the dependable option.
Two patterns to avoid:
- Chains. If B imports from A and C imports from B, an update to A reloads both B and C. Several layers can cause long delays.
- Cycles. If A imports from B and B imports from A, the loop never produces output.
IMPORTRANGE limits
IMPORTRANGE is an external data function, like IMPORTXML and GOOGLEFINANCE, so it needs an internet connection. It is capped at 10MB of received data per request. Google's help page lists no per-sheet cell limit, and it mentions no plan-specific differences.
To stay well under the cap, import less. Google's example: summing 1,000,000 rows is faster if you do the sum in the source and import the single result. Google also recommends limiting the number of receiving sheets, because each one has to read from the source.
Fix common IMPORTRANGE errors
| What you see | Cause | Fix |
|---|---|---|
| #REF! with "You need to connect these sheets" | The destination hasn't been granted access yet | Click Allow Access |
| "You don't have permissions to access that sheet" | You don't have access to the source | Open the source URL, request access, wait for the owner |
| "Loading…" with "Loading data may take a while because of the large number of requests" | Too much import traffic. Limits are enforced on the document's creator and count all import functions (IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML) across every open document that user created, so a collaborator's edit can count against your quota | Reduce churn: keep the URL and range fixed so the argument's value doesn't keep changing and firing many external calls |
| #ERROR! mentioning NOW, RAND or RANDBETWEEN | The formula references a volatile function, directly or indirectly | Remove the reference. For example, don't build range_string from a cell that depends on NOW(); use static text instead |
| Can't create a new IMPORTRANGE | "Disable options to download, print, and copy" is on in the source | Ask the owner to turn it off. Formulas set up before the setting was turned on keep working |
When IMPORTRANGE is the wrong tool
For big datasets or data that should update on a timetable, Google points to two alternatives: Apps Script, which can run on edit or on a schedule, and Connected Sheets, which offers scheduled refresh and suits large datasets better.
Once the data lands in your sheet, you can clean it like any other range. If two imports overlap, see how to highlight duplicates in Google Sheets.
