IMPORTRANGE not working or showing #REF!
What you see: =IMPORTRANGE(url, range) returns a
#REF! cell with a prompt, or sits on "Loading data…" forever. Nine
times out of ten this isn't a bug — it's a permission handshake you haven't
completed, or one of four other documented causes.
The fast answer (most common cause)
Select the #REF! cell. You'll see
"You need to connect these sheets." — click
Allow Access. That single click is the fix for the majority of
broken IMPORTRANGEs. The prompt only appears for the account that entered the
formula, so each collaborator entering the formula grants their own connection.
Cause 1: Access not yet granted (most common)
- Enter the formula; it shows
#REF!. - Select the cell and click Allow Access in the "You need to connect these sheets." prompt.
- Values render within seconds. Note: granted connections count toward the source sheet's share limit (600 users).
Cause 2: Wrong URL or range syntax
The function signature is strict:
IMPORTRANGE(spreadsheet_url, range_string) — full spreadsheet URL,
then a quoted tab-qualified range:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Sheet1!A1:C10")
Check the quotes around the range, the exact tab name (spaces included), and that you used the full URL rather than a shortened one.
Cause 3: The source is too large
Google documents a hard cap: requests are limited to 10 MB of received data per request. Oversized imports hang with "Loading data may take a while because of the large number of requests…". Fix: split the import into smaller ranges or several IMPORTRANGE calls, each under the cap.
Cause 4: Circular imports
Two sheets importing each other "causes a loop… that never results in an actual data output" — Google's words. Restructure so imports flow one direction only (A→B→C, never back). Chained imports also add update delay at every hop.
Cause 5: Volatile source cells
If the imported range contains NOW(), RAND(), or RANDBETWEEN(), expect
#ERROR! behavior and constant recalculation. Import stable values;
move volatile functions to the destination sheet if you need them.
Why it won't "just refresh"
IMPORTRANGE refreshes about once an hour while the sheet is open — reopening the file alone doesn't force a reload. If you need current data, edit the formula (add and remove a space) or copy the formula to a new cell to force a fresh fetch.
You'll know it worked when
The destination range shows live values from the source sheet, and changing a source cell appears in the destination within the hour.
Still broken?
Check the Google Docs Editors Help Community for the current status of IMPORTRANGE delays (they happen in waves), or use Help > Report a problem inside Sheets if your case matches none of the five causes above.
Google Sheets power-user courses
Cross-file imports, QUERY, ARRAYFORMULA — one focused course covers the functions that do most of the work in Sheets.
Compare courses →