修復 IMPORTRANGE 的 #REF!：“You need to connect these sheets”

Tested in: Google Sheets API checks, November 9, 2026 (locale zh_CN). IMPORTRANGE returned the connection-permission error; the browser Allow access workflow and successful import have not been verified.

Which versions this applies to

Google Sheets on a desktop web browser. Excel has no IMPORTRANGE function (see the Excel section below). The authorization step itself should be done on a desktop browser, not the Sheets mobile app.

What the error actually means

Google’s IMPORTRANGE documentation says spreadsheets “must be explicitly granted permission to pull data from other spreadsheets”. The first time a destination file pulls from a new source file, Sheets shows #REF! and asks you to connect them.

Two details from the official help page are worth knowing before you click:

• The permission is per file pair, not per formula. Once access is granted, any editor of the destination spreadsheet can use IMPORTRANGE to pull any part of the source spreadsheet, not just the range in your formula.
• It lasts until the person who granted access is removed from the source file. If that person loses access to the source, the connection stops working.

So if the source file contains tabs you don’t want destination editors to see, don’t connect it. Move the shareable data into a separate source file instead.

Step 1: Click “Allow access” on a plain IMPORTRANGE

1. Open the destination spreadsheet in Chrome, Edge, Firefox or Safari on a computer.
2. In an empty cell, enter a simple IMPORTRANGE with nothing wrapped around it:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_SOURCE_ID", "Sheet1!A1:D100")

3. Wait a few seconds. Click (or hover over) the #REF! cell.
4. Click Allow access.

If the data appears, the connection is done. You can delete this test formula and go back to your original nested formula. It should now work, because the file pair is connected.

Tip: you don’t need the full URL. A Google Docs Editors Community guide by a Product Expert recommends using just the spreadsheet ID (the part between /d/ and /edit in the URL), which avoids problems caused by extra parts like /edit?gid=0#gid=0:

=IMPORTRANGE("1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789", "Sheet1!A1:D100")

Why the “Allow access” button doesn’t appear: 8 causes

If step 1 didn’t work, go through these causes in order.

Cause 1: IMPORTRANGE is nested inside another function

How to detect: your formula looks like =QUERY(IMPORTRANGE(...), "select ..."), =FILTER(IMPORTRANGE(...), ...), =VLOOKUP(A2, IMPORTRANGE(...), 2, FALSE) or ={IMPORTRANGE(...); IMPORTRANGE(...)}, and you get an error but no Allow access button.

The error you see may not even mention connecting. The community troubleshooting guide lists these messages as symptoms of a missing authorization:

• #VALUE! “Unable to parse query string for Function QUERY parameter 2” when IMPORTRANGE is inside QUERY
• #VALUE! “In ARRAY_LITERAL, an Array Literal was missing values for one or more rows” when IMPORTRANGE is inside { }

How to fix: authorize each source with a plain IMPORTRANGE in a spare cell (step 1), then delete it. If you stack several sources with { ; }, you need to do this once per source file.

Cause 2: You’re on the mobile app

How to detect: the error says “Please use a desktop web browser to connect this sheet”, or you’re on a phone or tablet and see no button.

How to fix: open the file on a computer and do step 1 there. According to the community troubleshooting guide, the authorization must be done in a desktop browser, not a mobile app or mobile browser. After that, the formulas work on mobile as well.

Cause 3: You don’t have access to the source file

How to detect: paste the source URL into your browser’s address bar. If you see “You need access”, that’s the problem. The error may also read “You don’t have permissions to access that sheet”.

How to fix: per Google’s help page, request access to the source spreadsheet and wait for the owner to grant it. You need at least Viewer access. If the source is in a shared drive, ask the Workspace admin or a manager of that drive.

Cause 4: The source owner turned off download, print and copy

How to detect: you can open the source file, but new IMPORTRANGE formulas never connect. Ask the owner whether “Disable options to download, print, and copy” (or “People who can download, copy and print” being turned off for your access level) is set on the source file.

How to fix: Google’s help page says that with this setting on, new IMPORTRANGE formulas can’t export data from the file, while formulas set up before the change keep working. The owner has to turn the setting off, or publish the data you need in a separate file without that restriction.

Cause 5: The source is an Excel (.xlsx) file stored in Drive

How to detect: the source file shows a green .XLSX badge next to its name when you open it.

How to fix: the community troubleshooting guide says the source must be a native Google Sheets file. Open the .xlsx, choose File > Save as Google Sheets, and point your IMPORTRANGE at the new file’s URL or ID. The converted copy has a different ID from the original .xlsx.

Cause 6: The URL or range string is wrong (so it’s not really a permission problem)

Sometimes the #REF! is a different message that looks similar. Read the hover text carefully:

Tab names with spaces or special characters need single quotes inside the range string:

=IMPORTRANGE("YOUR_SOURCE_ID", "'Sales Data'!A1:F500")

If you leave out the tab name, Google’s docs say IMPORTRANGE reads from the first sheet of the source file. That can silently import the wrong tab if someone reorders the tabs later, so it’s safer to always include it.

Cause 7: The URL comes from a cell and keeps changing

How to detect: your formula is =IMPORTRANGE(B1, "Sheet1!A1:D100") and B1 changes (for example, a drop-down of client files).

How to fix: every new source file needs its own one-time authorization. Pick each option in B1 once and click Allow access, or authorize each source with a temporary plain formula. Google also warns that if the URL argument changes a lot, it creates many external calls and can trigger “Loading data may take a while because of the large number of requests” throttling.

Cause 8: The source file has hit the share limit

How to detect: the message is something like “The source spreadsheet has reached the maximum capacity for sharing and importing.”

How to fix: Google’s help page notes that each access granted to a destination sheet counts toward the 600-user share limit of the source. The community guide suggests a hub-and-spoke setup: one intermediate file imports from the source, and the other files import from the intermediate file. Keep the chain short, because Google also says each layer adds update delay.

A safer pattern: authorize once, then wrap

When you build a QUERY or FILTER on imported data, do it in two steps:

1. Put the raw import on a helper tab, e.g. Import!A1:

=IMPORTRANGE("YOUR_SOURCE_ID", "Orders!A1:F2000")

2. Query the helper tab with normal column letters:

=QUERY(Import!A1:F2000, "select A, C, F where F > 100", 1)

This makes authorization problems obvious (the helper tab shows the #REF! and the button), and you avoid a second common error: when QUERY reads IMPORTRANGE directly, you must use Col1, Col2… instead of A, B…. See Google Sheets QUERY “Unable to Parse Query String” Error: Causes and Fixes for that one.

Note: the imported data on the helper tab is a spilled array. If anything is typed in its output area, you’ll get a different #REF!. See Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets.

Excel vs. Google Sheets

Excel has no IMPORTRANGE function and no “Allow access” step. The closest equivalents are:

• External references such as ='[Budget.xlsx]Sheet1'!A1, which link to another workbook.
• Power Query (Data > Get Data), for importing and refreshing data from other workbooks or SharePoint/OneDrive files.

If your team uses both apps, keep the source data in Google Sheets for IMPORTRANGE, or in Excel/SharePoint for Power Query. Don’t mix them in one chain.

Common mistakes

1. Trying to authorize inside a nested formula. Always connect with a plain IMPORTRANGE first.
2. Using IMPORTRANGE inside the same file. You don’t need it. Reference the tab directly, e.g. ={Sheet1!A1:D100} or =Sheet1!A1:D100.
3. Pasting the full URL with #gid= on the end. Use the clean URL or only the spreadsheet ID.
4. Connecting a source that contains private tabs. After authorization, every editor of the destination can import any range from the source.
5. Importing whole open-ended columns like "Sheet1!A:Z". Google caps each request at 10 MB of received data and recommends condensing the data before importing. Import only the columns and rows you need.

FAQ

Do I have to click “Allow access” for every IMPORTRANGE formula?

No. Permission is granted per source file and destination file pair. After one formula is connected, other IMPORTRANGE formulas in the same destination file can read from that source.

Why is there no “Allow access” button on my #REF! error?

Usually because IMPORTRANGE is wrapped in QUERY, FILTER, VLOOKUP or an array { }, or because you’re on the mobile app. Put a plain IMPORTRANGE in an empty cell on a desktop browser, click Allow access, then delete it.

Can I revoke IMPORTRANGE access after I’ve allowed it?

Google’s help page says access stays in effect until the user who granted it is removed from the source file. The community guide adds that permissions can’t be revoked otherwise. If the data is sensitive, move it to a new source file and share only that.

Does IMPORTRANGE work with Excel files in Google Drive?

Not reliably as a source. Convert the .xlsx with File > Save as Google Sheets and import from the converted file, which has a new ID.

How often does IMPORTRANGE refresh?

Google’s help page says IMPORTRANGE checks for updates every hour while the document is open, and changes in the source make open destination files refresh. Reloading the browser tab alone does not force a refresh; re-entering the formula does.

Related tutorials

• Google Sheets QUERY “Unable to Parse Query String” Error: Causes and Fixes
• Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets
• FILTER vs QUERY in Google Sheets: Which One Should You Use? (cluster hub)
• ARRAYFORMULA vs MAP/LAMBDA vs Fill Down in Google Sheets

Sources

• Google Docs Editors Help: IMPORTRANGE (permission & access, 10 MB cap, 600-user share limit, download/print/copy setting, usage limits): https://support.google.com/docs/answer/3093340
• Google Docs Editors Community guide: “Resolving IMPORTRANGE Errors & Issues” (Product Expert guide; error messages for nested QUERY / array literals, desktop-only authorization, spreadsheet ID tip, Excel source files): https://support.google.com/docs/community-guide/453661366/resolving-importrange-errors-issues