IMPORTRANGE in Google Sheets pulls a range of cells from one spreadsheet into another: type =IMPORTRANGE("spreadsheet_url","Sheet1!A2:B6") with the source's URL in the first slot, press Enter, then click Allow Access. That first #REF! error isn't a broken formula; it's Google asking you to connect the two sheets.
It's a handy way to feed a live team dashboard in Sheets. It can also quietly slow a spreadsheet to a crawl when it's used for the wrong job, so I'll cover both how to set it up and how Google says to keep it quick.
The two arguments IMPORTRANGE needs
The syntax is IMPORTRANGE(spreadsheet_url, range_string):
| Argument | What it holds | Format |
|---|---|---|
| spreadsheet_url | The URL of the spreadsheet you're importing from | In quotation marks, or a reference to a cell that holds the URL |
| range_string | The sheet name and range, such as "Sheet1!A2:B6" or just "A2:B6" | In quotation marks, or a cell reference |
If you leave the sheet name out, Google uses the first sheet. Google's sample usages also show the URL kept in a cell, as in IMPORTRANGE(A2,"B2"), plus a table reference or a named range such as "Sales_total" in place of the cell range. Keeping the URL in its own cell makes it much easier to swap sources later.
Connecting the sheets: the Allow Access step
- Copy the URL of the source spreadsheet.
- In the destination sheet, type
=IMPORTRANGE("url","Sheet1!A1:C10")with your URL and range. - Press Enter and wait a few seconds. A #REF! error appears with the prompt You need to connect these sheets.
- Click Allow Access, and the data loads.
- Check you're only importing the range you need.
A destination sheet has to be granted permission the first time it pulls from a new source. If you don't own the source, you'll need to request access from its owner. Here's the part most people don't realise: once access is granted, any editor of the destination can pull from any part of the source, not just the range you first typed.
That access stays in place until the person who granted access is removed from the source, and it counts against the source sheet's 600-user share limit. If the owner switches on Disable options to download, print, and copy, new IMPORTRANGE formulas can't export its data, although formulas that already exist keep working.
How often IMPORTRANGE refreshes
Google says IMPORTRANGE checks for updates every hour while the document is open, even when neither the formula nor the source has changed. Reopening or reloading the document doesn't force a fresh pull, although it does reload when a document is first opened or has been opened within the past 5 minutes. If you need the latest data right now, delete the formula and type it back in: that triggers a reload.
Fixing the common IMPORTRANGE errors
| What you see | Cause (Google) | Fix |
|---|---|---|
| #REF! right after typing the formula | The source isn't connected yet | Click Allow Access, or ask the owner for access |
| #ERROR! mentioning NOW, RAND or RANDBETWEEN | Import functions can't reference volatile functions, directly or indirectly | Copy the result and paste special as values only; TODAY is allowed |
| Loading... with a message about too many requests | Too much import traffic | Cut the number of imports and arguments that change often |
| A slow sheet | Large ranges, chains of imports, many receiving sheets | Summarise in the source and limit chains |
TODAY is the one exception to the volatile rule, because it doesn't update more than once a day. Usage limits are enforced on the creator of the document, and Google notes that a collaborator's edit can count against your quota.
If you're stuck on this, see how to calculate an average in Google Sheets.
Keeping big imports fast
IMPORTRANGE is an external data function, in the same family as IMPORTXML and GOOGLEFINANCE, so it needs an internet connection. The whole range is downloaded every time, and each request is capped at 10MB of received data. Google's own best practice is to condense before you import.
Its example is the clearest advice on the page. To total 1,000,000 rows from another spreadsheet, it's faster to calculate the sum in the source sheet and import that single number than to import all 1,000,000 rows and add them up locally. Google also says to:
- Limit the number of receiving sheets, because each one reads from the source.
- Keep chains short. If sheet B imports from sheet A and sheet C imports from sheet B, every update to A reloads both B and C, and long chains mean long delays.
- Avoid cycles, where two spreadsheets import from each other. They loop and never output data.
- Reduce churn. If the value an argument resolves to changes often, many external calls are issued.
There's more on this in the guide on how to automate tasks in Google Sheets.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
When to use something else
Google names two alternatives for heavier work. Apps Script can read from other documents and run on an edit or on a schedule, and Connected Sheets has scheduled refresh and is better built for larger data loads. The related import functions are IMPORTXML, IMPORTHTML, IMPORTFEED and IMPORTDATA.
If you're newer to Sheets, my Google Sheets tutorial covers the basics, and once your data's in, filtering in Google Sheets is the quickest way to cut it down to what you need. Still deciding between the two apps? Here's my take on Excel vs Google Sheets.
My take on IMPORTRANGE
IMPORTRANGE is brilliant for a live dashboard fed from a handful of team sheets, and awful for heavy data. Import the answer, not the rows: do the totals in the source and pull the one cell you need. Keep the URL in a cell, keep chains short, and remember that once you've clicked Allow Access, every editor of your sheet can reach the whole source.
For a wider view, have a look at the guide on how to calculate standard deviation in Google Sheets.
Frequently asked questions
Why does IMPORTRANGE show #REF!?
The first time you connect to a new source you'll see #REF! with an Allow Access prompt. Click it to connect the sheets.
How often does IMPORTRANGE update?
Google says it checks for updates every hour while the document is open. Deleting and re-entering the formula forces a reload.
Can IMPORTRANGE use a cell for the URL?
Yes. Both arguments can be in quotation marks or a reference to a cell holding the value.
Why is my IMPORTRANGE sheet so slow?
The whole range downloads each time. Summarise in the source sheet, limit receiving sheets and avoid long chains.
Can I use NOW or RAND with IMPORTRANGE?
No. Import functions can't reference NOW, RAND or RANDBETWEEN. TODAY is the one exception.
