Google Sheets IMPORTRANGE shows #REF! "You need to connect these sheets"

Hi, it's BlueByte. If IMPORTRANGE shows #REF!, don't start rewriting the formula — this particular #REF! is usually not a broken formula at all, but a one-time permission prompt waiting for a click. Let's confirm that's what it is, grant the connection, and cover the two things that keep it from resolving.

What this #REF! actually is

An =IMPORTRANGE(...) formula shows #REF! instead of data. Hovering the cell reads:

You need to connect these sheets. Allow access.

This #REF! is not a broken formula — it is a one-time permission prompt that has not been answered yet. Recognizing that is half the fix; people often rewrite a perfectly good formula chasing a problem that is really about access.

Why IMPORTRANGE needs a connection

IMPORTRANGE reads data from another spreadsheet, so Google requires the destination sheet to be explicitly authorized to pull from that specific source. Until you approve it once, the formula returns #REF!. Two things commonly keep it from resolving:

• The connection has never been approved for this source–destination pair.
• You lack access to the source spreadsheet itself, so there is nothing to connect to.

A different-looking #REF! reading "Array result was not expanded" is a separate problem — the imported range is trying to overwrite cells that are not empty.

Read the tooltip to know which it is

Click the cell and read the tooltip. "You need to connect these sheets" with an Allow access button means the connection is unapproved — the normal case. If instead you get an error about permissions, open the source spreadsheet URL directly: if you cannot open it, you do not have access, and that is what to fix first. If the tooltip mentions "Array result", the problem is space, not access.

Grant the connection once

1. Click the cell holding the IMPORTRANGE formula.

2. Hover it (or read the error tooltip) and click Allow access on the prompt.

3. The data loads, and every IMPORTRANGE from that same source spreadsheet now works without prompting again — the grant is per source, not per formula.

If there is no prompt, check the syntax — the first argument is the source URL or ID, the second is the range in quotes:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit", "Sheet1!A1:C")

A real case: one grant covered them all

You build a dashboard that pulls from a colleague's data sheet with =IMPORTRANGE("https://.../d/1AbC.../edit","Data!A1:F") and it shows #REF!. Hovering the cell shows "You need to connect these sheets — Allow access." You click Allow access, and the range fills immediately. You add three more IMPORTRANGE formulas pointing at the same source, and none of them prompt again — the connection is granted at the source level, so one approval covered them all. A week later a teammate opens the dashboard and sees the data too, because the grant lives on the sheet, not on your account.

Confirm it pulls and updates

The cell should fill with the source data, and other IMPORTRANGE formulas pointing at the same source should resolve too. Change a value in the source and confirm it updates in the destination after a moment — that proves the live connection, not just a one-time copy.

Keep it from breaking later

Connect the source once when you first build the sheet, and make sure everyone who edits the destination has at least view access to the source, so a later permission change does not silently break the pull.

How this differs from a plain #REF! or #N/A

The "connect these sheets" #REF! is a permission gate unique to IMPORTRANGE. A plain #REF! elsewhere means an invalid cell reference (a deleted cell), and #N/A from a lookup means no match was found — neither is fixed by granting access. So when the tooltip says "connect", click, don't rewrite.