IMPORTRANGE not working: every error and how to fix it
By the Analistable team · Updated · 5 min read
Most IMPORTRANGE failures are one of four things: access hasn't been allowed yet (hover the #REF! and click Allow access), you don't have permission to open the source file, the range string is wrong (it must be in quotes, and sheet names with spaces need single quotes: "'Sales 2026'!A1:D"), or the import is too large or there are too many imports, which leaves it stuck on Loading.
Part of our guide: How to combine data in Google Sheets
The correct form first
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC…/edit", "'Sales 2026'!A1:D500")- The first argument is the full URL or just the spreadsheet key (the long ID between /d/ and /edit), in quotes or in a cell.
- The second is the range as text, in quotes, including the sheet name. Single quotes go around sheet names that contain spaces or symbols.
- Both can be cell references, which is useful when you import several ranges — see combining multiple IMPORTRANGE formulas.
"You need to connect these sheets"
The first time a spreadsheet imports from a particular source, the cell shows #REF!. Hover over it (or click the cell) and you'll see a message that you need to connect these sheets, with an Allow access button. Click it and the data loads.
- The person who clicks Allow access must have at least view access to the source.
- Once allowed, the connection belongs to the destination spreadsheet, not to you: anyone who can edit the destination can then import any range from that source. Think about that before connecting a sensitive file to a widely shared one.
- Access is granted per source file. Importing from a second spreadsheet asks again.
- If the button doesn't appear, the formula may be inside a larger formula (QUERY, VLOOKUP). Put a bare
=IMPORTRANGE(…)in a spare cell, allow access there, then delete it.
#REF! error messages and what they mean
| Message on hover | Cause | Fix |
|---|---|---|
| You need to connect these sheets | Access not yet allowed | Click Allow access |
| You don't have permission to access that sheet | Your account can't open the source | Ask the owner to share it with you |
| Spreadsheet not found / invalid URL | Typo in URL or key, or file deleted | Copy the URL again from the source's address bar |
| Unable to find range / sheet | Wrong sheet name, or missing quotes around a name with spaces | Check spelling; use "'Sheet name'!A1:D" |
| Result too large | The range exceeds the import size limit | Import fewer columns or rows, or split it |
| Array result was not expanded | Cells below or right of the formula aren't empty | Clear the cells in the way |
Renaming a tab in the source doesn't update the text in your range string, so a renamed sheet breaks the import. Keep sheet names stable, or put the range string in a cell so it's easy to change.
Stuck on Loading…
IMPORTRANGE fetches data from Google's servers, so it's slower than a normal reference, and many imports in one file compete with each other. Things that help:
- Import once into a staging tab, then point other formulas at that tab rather than calling IMPORTRANGE repeatedly for the same source.
- Bound the range:
A1:D5000instead ofA:Z. Whole-column imports bring in every empty row and column. - Avoid chains where file C imports from B, which imports from A; each hop adds delay.
- Remove volatile functions such as NOW, RAND or INDIRECT that cause frequent recalculation in the same sheet.
Google applies limits on the size of a single import and on how much data a sheet can pull in; the exact numbers aren't something to design right up against. If you're near them, imports fail intermittently. Google Sheets as a whole has a 10 million cell limit per spreadsheet, which large imports eat into.
Imported data doesn't update or looks wrong
- IMPORTRANGE refreshes when the source changes, but not instantly; a delay of a few minutes is normal. Editing the formula forces a reload.
- Imported values keep their type but not their formatting. Re-apply number and date formats in the destination.
- You can't sort or edit the imported cells directly. Wrap the import in SORT, FILTER or QUERY, or copy and paste values if you need a static snapshot.
- Mixed types in an imported column can make a downstream QUERY drop rows — see QUERY errors when combining ranges.
Worked example: a monthly roll-up from three regional files
| Region file | Range imported | Rows | Status before | Status after fix |
|---|---|---|---|---|
| North | 'Sales 2026'!A2:D2000 | 812 | #REF! — connect sheets | Loaded (access allowed) |
| South | Sales 2026!A2:D2000 | 0 | #REF! — unable to find range | Loaded (quotes added) |
| West | 'Sales 2026'!A:Z | 0 | Loading… | Loaded as A2:D2000 (634 rows) |
After the fixes the staging tab holds 812 + 760 + 634 = 2,206 rows (South returned 760), and the summary tab references the staging tab rather than three live imports.
Alternatives when IMPORTRANGE is the wrong tool
- Move the tab. If the data doesn't need to stay in a separate file, right-click the tab → Copy to → Existing spreadsheet.
- Apps Script. A scheduled script can copy values on a timer, which avoids live-import limits — see Google Apps Script to merge sheets.
- Analyse without importing. Analistable connects to Google Sheets through your Google account on the server side, reads the tabs you choose and loads them into DuckDB in your browser, so you can join several spreadsheets and ask questions without building import formulas. It reads the sheets; it doesn't write back to them.
For linking files more generally, see linking two Google Sheets and the combine data in Google Sheets guide.
Frequently asked questions
- Why does IMPORTRANGE say #REF!?
- Hover over the cell to read the message. Most often access hasn't been allowed yet, you don't have permission to the source, or the range string has the wrong sheet name.
- How do I fix 'You need to connect these sheets'?
- Click the cell with #REF! and choose Allow access. If the formula is nested, allow access from a bare IMPORTRANGE in a spare cell first.
- Can I IMPORTRANGE from an Excel file in Google Drive?
- No. The source must be a Google Sheets file. Open the Excel file and save it as Google Sheets first.
- Why is IMPORTRANGE stuck on Loading?
- The range is too large, there are too many imports in the file, or imports are chained across files. Bound the ranges and import each source once into a staging tab.