DEV Community

SheetError
SheetError

Posted on Originally published at sheeterror.com

IMPORTRANGE #REF! "You need to connect these sheets" in Google Sheets: click Allow access, then read the tooltip

#googlesheets #spreadsheets #productivity #tutorial

You write =IMPORTRANGE(...) to pull a range from another spreadsheet and the cell shows #REF!. The URL is right, the range is right, and the tab exists. Hover the cell and the tooltip says You need to connect these sheets. Nothing is broken yet. Sheets is waiting for permission.

Why it happens

IMPORTRANGE needs one-time permission for each pair of files: this destination spreadsheet reading that source spreadsheet. Until someone clicks Allow access, Sheets won't evaluate the range at all, so even a perfectly valid Sheet1!A1:B2 comes back as #REF!. It isn't a missing range or a deleted cell, and the formula text is fine.

Reproduce it in a minute

  1. Open a new spreadsheet. This is the source. Type sku in A1, name in B1, 101 in A2 and Widget in B2. Leave the tab named Sheet1.
  2. Copy the spreadsheet id from its URL. It's the long string between /d/ and /edit in docs.google.com/spreadsheets/d/<id>/edit.
  3. Open a second blank spreadsheet. This is the destination. In A1 enter: 
=IMPORTRANGE("SOURCE_ID", "Sheet1!A1:B2")
Enter fullscreen mode Exit fullscreen mode

A1 shows #REF! with the You need to connect these sheets prompt. (The documented first argument is the full spreadsheet URL. The id on its own also works.)

The fix

Select the #REF! cell, hover it, and click Allow access. Leave the formula as it is. The same formula spills four cells: sku, name, 101, Widget.

You only do this once per pair of files. Later IMPORTRANGE formulas in the same destination that point at the same source won't ask again.

If it's still #REF! after the click, read the tooltip again:

  • Still "You need to connect these sheets": you're signed into an account that can't open the source, or the id/URL in quotes isn't the source. Open the source URL while signed in as the same user.
  • A missing sheet or range: that's a different #REF!, covered next.

Watch out for

  • The second #REF! has no prompt. Once the files are connected, enter =IMPORTRANGE("SOURCE_ID","Sheet2!A1:B2") in A4, with a tab name that doesn't exist on the source. You get #REF! again, but with no Allow access banner. The tooltip now says the sheet or range can't be found. Fix the string: match the tab name exactly, spaces included. Clicking Allow access again does nothing.
  • Don't wrap it in IFERROR yet. IFERROR hides the permission #REF! along with the prompt, so you never see the button to click. Connect first, then add error handling if you still want it.
  • It's not #REF! after a delete. When you delete rows or columns, #REF! shows up inside the formula text. With IMPORTRANGE the references are quoted strings and nothing in the formula changes.
  • It's not "Result too large". That one happens after you're connected, usually as #ERROR!, when the pull is over the size cap. Shrink the range. A permission prompt won't help there.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it). There's also a short walkthrough: IMPORTRANGE #REF! - You need to connect these sheets.

This article was created with the help of AI.

Top comments (1)

Collapse
 
alexshev profile image
Alex Shev •

The permission click fixes the immediate symptom, but a spreadsheet workflow needs a way to surface the dependency owner and source URL before the error. A small audit tab that lists imports, last refresh, and access state can turn a confusing #REF! into an actionable maintenance task.