IMPORTRANGE in Google Sheets: The Complete Guide (Including Every Error You’ll Hit)
IMPORTRANGE in Google Sheets: The Complete Guide (Including Every Error You’ll Hit)
IMPORTRANGE does one thing: it pulls a range of cells from one Google Sheets file into another, and keeps the data live so it updates automatically. The problem is that it breaks in ways that aren’t obvious, especially around permissions, and the error messages aren’t always helpful.
Basic Syntax
=IMPORTRANGE(spreadsheet_url, range_string)
Both arguments are strings. The range_string format is SheetName!A1:D100. The sheet name is required if your source file has more than one sheet.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcD.../edit", "Sheet1!A1:E50")You can also use the spreadsheet ID alone (the string between /d/ and /edit in the URL):
=IMPORTRANGE("1aBcD...", "Sheet1!A1:E50")Pro tip: Store the URL or ID in a named cell and reference it: =IMPORTRANGE(SourceFile, “Sheet1!A1:E50”). When the source file changes, you update one cell instead of hunting through formulas.
The Permission Step Everyone Forgets
The first time you use IMPORTRANGE to connect two specific files, Google requires you to explicitly allow the connection. Until you do, the formula shows #REF! with the message ‘You need to connect these sheets.’
To grant permission: click on the cell with the #REF! error, and a prompt appears asking you to ‘Allow access.’ This is a one-time step per file pair.
Common support question: If your IMPORTRANGE works on your machine but shows #REF! for a colleague, they need to open the destination file, click the error, and grant permission themselves. You can’t grant it on their behalf — the permission is per Google account.
Error Reference
| Error | Cause | Fix |
|---|---|---|
| #REF! — connect these sheets | First-time connection, permission not granted | Click the cell → Allow access |
| #REF! — no access | Viewing account lacks access to the source file | Grant the account access to the source file |
| #ERROR! | Malformed range string or incorrect sheet name | Check sheet name spelling; range format must be SheetName!A1:B10 |
| #N/A | Source range is empty or named range doesn’t exist | Verify source range has data; check for typos |
| Loading… (never resolves) | Source file too large or Sheets quota exceeded | Reduce imported range; split into smaller IMPORTRANGE calls |
Performance: The 5 Million Cell Limit
Google Sheets has a 5 million cell limit per spreadsheet. IMPORTRANGE pulls live data into your destination file, which counts against that limit. Import only the columns you actually need, not entire sheets:
=IMPORTRANGE(url, "Data!A:A,D:D,F:H")
You can also use QUERY wrapped around IMPORTRANGE to filter rows at import time:
=QUERY(IMPORTRANGE(url, "Data!A:F"), "SELECT Col1, Col4 WHERE Col2 = 'Active'", 1)
This is more efficient than importing everything and filtering afterward.
Dynamic Range Strings
IMPORTRANGE’s range_string is just a string, which means you can build it dynamically. This is useful when your range changes based on a date or user selection.
Import data for the month selected in cell B1:
=IMPORTRANGE(url, B1 & "!A:F")
If B1 contains ‘January’, this imports January!A:F. For a dynamic row range based on a count:
=IMPORTRANGE(url, "Sheet1!A1:F" & C1)
IMPORTRANGE + QUERY: The Combination That Makes It Practical
Raw IMPORTRANGE pulls everything and dumps it. Wrapping it in QUERY lets you filter, sort, and select columns at the formula level.
Select specific columns and filter:
=QUERY(IMPORTRANGE(url, "Orders!A:G"), "SELECT Col1, Col3, Col7 WHERE Col5 = 'Shipped'", 1)
Aggregate on import — sum by category:
=QUERY(IMPORTRANGE(url, "Sales!A:D"), "SELECT Col2, SUM(Col4) GROUP BY Col2", 1)
Note: QUERY column references inside IMPORTRANGE use Col1, Col2, Col3 notation — not the column letters from the source sheet. Col1 is the first column you imported, regardless of its letter in the source.
What IMPORTRANGE Can’t Do
IMPORTRANGE pulls values only — not formulas, formatting, conditional formatting rules, data validation, or named ranges. It also can’t import from files you don’t have access to, from files in different Google Workspace domains unless external sharing is enabled, or from non-Sheets files.