IMPORTRANGE "You Must Connect These Sheets" Error
What it means: IMPORTRANGE pulled a valid range, but the two spreadsheets are not linked yet. Google requires a one-time, per-pair permission before data flows.
Quick fix
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/", "Sheet1!A1:D100")
Click the red #REF! cell, then click the blue "Allow access" button that appears. Access is per source file and only asked once.
Why you see #REF! — and how to fix each cause
1. You never granted access to the source file
The first time a spreadsheet pulls from a new source file, Google shows #REF! with a hover prompt "You must connect these sheets."
Fix: Hover (or click) the cell containing the formula and press "Allow access" in the tooltip. The data loads immediately after.
2. The URL or File ID is wrong or incomplete
IMPORTRANGE needs either the full spreadsheet URL or just the long FILE_ID string between /d/ and /edit — not a sheet tab link.
Fix: Copy the URL straight from the source file’s address bar, in quotes.
=IMPORTRANGE("1AbC...long_id...XyZ", "Sheet1!A1:D100")
3. Access was revoked or the source file moved/was deleted
If the source file was deleted, un-shared, or you lost view access, the permission breaks and #REF! returns.
Fix: Confirm you can still open the source file, then delete and re-enter the formula to trigger the Allow-access prompt again.
4. The range text has a typo
The second argument is a string like "Sheet1!A1:D100". A missing quote, wrong tab name, or stray space breaks it.
Fix: Match the tab name exactly (case and spaces) and keep the whole range in one set of quotes.
Before and after
| Broken | Working |
|---|---|
=IMPORTRANGE("FILE_ID","Sheet1!A:D") (shows #REF!, never clicked Allow) |
=IMPORTRANGE("FILE_ID","Sheet1!A:D") (same formula, after clicking "Allow access") |
The formula does not change — the fix is the one-time permission click, not an edit.
How to stop it happening again
Grant access once per source file and the link stays live. If you share the destination sheet with new editors, they inherit the connection automatically. Avoid wrapping IMPORTRANGE in IFERROR before you have clicked Allow, because that hides the permission prompt and the data will never load.
FAQ
Why does IMPORTRANGE show #REF! even though the formula is correct?
Because the two spreadsheets are not connected yet. Click the cell and press "Allow access" — the #REF! clears instantly.
Do I have to allow access every time?
No. Permission is granted once per source file. After the first Allow, every IMPORTRANGE from that file works without prompting.
Where is the Allow access button?
Click or hover the cell that contains the IMPORTRANGE formula. A small tooltip appears with a blue "Allow access" button.
Can I grant access without being the owner?
You need at least view access to the source file. If you can open it, you can connect it; if not, ask the owner to share it with you first.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas