Fixes / Google Sheets · Checked

Google Sheets "You need to connect these sheets": how to fix it

Short answer

This is a one-time permission prompt, not a broken formula. IMPORTRANGE shows #REF! until someone who can edit the current spreadsheet and view the source file approves the link. Click the #REF! cell, wait for the pop-up, and click Allow access. The data loads within seconds and stays connected for every editor of the file.

What this message means

IMPORTRANGE pulls cells from one Google Sheets file (the source) into another (the destination). Google won't let one file read another until a person approves the link, so the first time the two files are joined the formula returns #REF! with the note "You need to connect these sheets." Approving it gives the destination file ongoing read access to the whole source file, not just the range in your formula. You only do this once for each pair of files. After that, any editor of the destination can add more IMPORTRANGE formulas that point at the same source. The link stays in place until the person who approved it loses access to the source file.

Common causes

CauseHow to tell
The two files have never been connected before (for example, a new formula or a brand-new destination file).The cell shows #REF!. When you click or hover over it, a small box says "You need to connect these sheets" and has a blue Allow access button.
The destination file is a copy (File > Make a copy, a template, or a copy made by an automation tool like Zapier or Apps Script). The approval is tied to the original file, not the copy.The original spreadsheet shows the imported data, but the copy shows #REF! in the same cells with the connect message.
You don't have at least view access to the source spreadsheet.There is no Allow access button, or the message says you don't have permission to access the sheet. When you open the source URL in the same browser, you get a "You need access" page.
You can only view or comment on the destination file, so you can't approve the connection.The destination file shows "View only" or "Comment only" near the top right, and clicking Allow access either isn't offered or does nothing.
The person who first approved the connection was removed from the source file's sharing list, which ends the link.IMPORTRANGE formulas that used to work suddenly all show #REF! with the connect message again, often after someone left the team or sharing was cleaned up.
A mistake in the URL or range string, so the prompt never shows up or the error stays after you approve.You clicked Allow access (or never saw the button) and #REF! stays. The tab name in quotes doesn't exactly match the source tab, including spaces and capital letters, or the URL is partial.

How to fix it

1. Click Allow access on the #REF! cell

  1. Open the destination spreadsheet (the one with the IMPORTRANGE formula) in a browser on a computer.
  2. Click the cell showing #REF! and hold the pointer over it for a second or two until the pop-up appears.
  3. Click the blue Allow access button.
  4. Wait a few seconds for the data to load. Other IMPORTRANGE cells pointing to the same source file will fill in too.

2. Make the pop-up appear when the Allow access button is missing

  1. Click an empty cell, then paste the formula on its own, for example =IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID", "Sheet1!A1").
  2. Wait a few seconds, then hover over the new cell and click Allow access.
  3. If the original formula is wrapped in QUERY, FILTER, ARRAYFORMULA or IFERROR, the outer function can hide the prompt. Approving through a simple formula in a separate cell connects the files for all formulas.
  4. Delete the helper cell after the data appears.
  5. Reload the page (Ctrl+R on Windows, Cmd+R on Mac) if the formulas still show #REF!.

3. Get access to the source spreadsheet

  1. Paste the source URL from the formula into a new browser tab, signed in with the same Google account.
  2. If you see "You need access", click Request access and wait for the owner to share it with you (Viewer is enough).
  3. If you use more than one Google account, make sure the account shown in the top right of both files is the same one.
  4. Once you can open the source file, go back to the destination and click Allow access on the #REF! cell.

4. Have an editor approve it (view-only or copied files)

  1. Check that you are an Editor on the destination file (Share button, or look for "View only" at the top right).
  2. If you aren't, ask the owner for edit access, or ask someone who is an editor there and can view the source to click Allow access.
  3. For a copied file, open the copy and click Allow access once. Every new copy needs its own approval.
  4. If a script or automation makes copies automatically, there is no way to approve with code. Someone has to click Allow access in each copy, or the source has to be shared so that anyone with the link can view it (only if the data is not sensitive).

5. Check the URL and range string

  1. Copy the full source URL straight from the browser address bar, or use only the ID between /d/ and /edit.
  2. Put both arguments in straight double quotes: =IMPORTRANGE("URL", "Tab name!A1:D100").
  3. Make sure the tab name matches the source tab exactly. If it has spaces or symbols, put it in single quotes inside the string, for example "'Sales 2026'!A1:D100".
  4. Make sure the source file is a Google Sheets file. An Excel (.xlsx) file stored in Drive must first be converted with File > Save as Google Sheets, and the formula must point to the new file's URL.

6. Fix it across organizations (Google Workspace)

Google Workspace (work or school) accounts, when the source and destination belong to different organizations

  1. Check whether the source file is owned by another company or school. If it is, your account may be blocked from reaching it.
  2. Ask your Google Workspace admin to check Admin console > Menu > Apps > Google Workspace > Drive and Docs > Sharing settings > Sharing options.
  3. If sharing outside the organization is Off, or limited to Allowlisted domains, the admin has to allow the other domain.
  4. After the policy change, open the source file once to confirm you can see it, then click Allow access in the destination.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Start with the simplest test: create a new blank spreadsheet, enter =IMPORTRANGE("source URL", "A1") and try Allow access there. If it works in the blank file but not in yours, the problem is the destination's permissions or a wrapping formula. If it fails there too, the problem is your access to the source file, so contact the source file's owner. On a work or school account, your Google Workspace admin can check the sharing policies. Too many IMPORTRANGE formulas can also leave cells stuck on "Loading..." rather than #REF!. Combine them into fewer, larger ranges, and keep each import under Google's 10 MB per-request limit.

Questions people ask

Is it safe to click Allow access?

Yes. It only lets the destination file read the source file. It doesn't give anyone edit access and doesn't change who the source is shared with. Keep in mind that every editor of the destination can then import any part of the source, not just the range in your formula.

Why is there no Allow access button?

Usually you don't have view access to the source, you only have view access to the destination, or the formula is wrapped in another function that hides the prompt. Enter a plain IMPORTRANGE formula in its own cell, hover over it, and approve from there.

Do I have to allow access every time?

No. You approve once for each pair of files and it lasts until the person who approved it is removed from the source. A copy of the destination counts as a new file, so it needs its own approval.

Do other people using my sheet also need to click Allow access?

No. Once one person approves, the imported data shows for everyone who can open the destination, even if they can't open the source file.

Can I approve IMPORTRANGE automatically with Apps Script?

There is no supported way to click Allow access with code. The common workarounds are to have someone approve each copy by hand, or to share the source as "Anyone with the link can view" if the data can safely be public.

Sources

Related errors