# IMPORTRANGE Not Working in Google Sheets: 7 Causes and Fixes

**By StackTrekker Editorial** · Published October 2, 2026
IMPORTRANGE stops working for one of seven reasons, and the error message narrows it down fast. A #REF! that asks you to connect the sheets is a permission step, not a broken formula. When the message points at a range or a file that no longer exists, the reference itself is wrong. When the cell sits under a loading bar and never settles, the cause is a circular reference, a long chain of imports, or a range heavier than the 10 MB transfer cap. Work through the checks in order, because each cause has its own repair. Below is the exact fix for every message, then what to change when the data is genuinely too large to pull into a second file.

## What IMPORTRANGE Actually Does

IMPORTRANGE copies a range from one spreadsheet into another and keeps it updated. The syntax is short, `IMPORTRANGE(spreadsheet_url, range_string)`, and the second argument is written as `"[sheet_name!]range"`, for example `"Sheet1!A1:F20"`. Google classifies it as an external data function, which is why it behaves nothing like SUM or VLOOKUP. It reaches over the network, it needs a connection, and a single request is capped at 10 MB. [Google's IMPORTRANGE documentation](https://support.google.com/docs/answer/3093340) also notes that the result refreshes when a file is opened rather than on a fixed clock, so a figure can look stale in a tab that has been open all morning.

## The Seven Causes at a Glance

Almost every failure falls into one of seven buckets. The table maps the symptom to the cause.

| What You See | Likely Cause | The Fix |
|---|---|---|
| #REF! with a connect prompt | The files were never linked | Click Allow Access |
| #REF! on a cell that worked before | The source file was moved, deleted or restored | Re-paste the source URL |
| #REF! naming a range | The tab was renamed | Update the range string |
| #N/A or an empty grid | The URL is not the full spreadsheet URL | Copy the address bar URL |
| A loading bar that never finishes | A circular reference or a long chain | Break the loop |
| Slow file, cells that spin | The range is heavier than the 10 MB cap | Summarize in the source |
| Blank result after a sharing change | Download, print and copy is disabled | Re-enable it in the source |

## Why Does IMPORTRANGE Show #REF! and Ask You to Connect the Sheets?

The first time a sheet imports from another file, Google will not move the data until someone approves the connection. The cell shows #REF! and, on hover, a line such as "You need to connect these sheets". That is not a broken formula. Select the cell, click Allow access in the prompt, and the range fills in.

If no prompt appears, the problem is on the source side. Open the second spreadsheet in its own tab and confirm you can open it. If you cannot, ask the owner for view access, then repeat the import. The connection is approved per pair of files and per account, so a colleague opening the same document for the first time sees the prompt again.

## How Do You Fix a URL or Range String That IMPORTRANGE Rejects?

Two arguments, two ways to get them wrong, and they look alike in the formula bar.

The first must be the full address of the spreadsheet, copied from the address bar while the file is open. It contains the file ID and ends in /edit. Three lookalikes fail: the published address with /pubhtml in it, the copy link ending in /copy, and the address of a single tab. A wrong URL returns #N/A or an empty grid rather than #REF!.

The second is the range string, and it must sit inside straight double quotes. Write the sheet name, an exclamation mark, then the cells, as in "Q3 Sales!A2:D50". Spaces in a tab name are fine. The failure comes when a tab is renamed in the source, because the old name no longer exists and the formula returns #REF! even though the file is healthy. A range that runs past the last column breaks the same way, and a formula pasted from a document can fail on a curly quote with no visible change.

## Why Does the Cell Sit on Loading and Never Finish?

A circular reference is the quietest failure in this list, because nothing errors out. If sheet A imports from sheet B and sheet B imports from sheet A, neither can finish calculating and every imported cell stays on a loading bar. A long chain produces the same symptom, with each hop waiting for the one behind it. Google's guidance is to keep chains short and to import into as few files as possible.

The fix is to name one spreadsheet the single source of truth, let the others read from it, and never let the flow turn back.

## What Does a Blank Cell or a Slow File Tell You?

Volume is a real limit, not a vague performance warning. A single import request is capped at 10 MB, and a range near that ceiling either fails or drags the document down as the browser recalculates it on every edit.

The durable fix lives in the source file. Build a short summary block there, then import ten rows instead of ten thousand. [A pivot table](/pivot-table-beginner-guide/) or a small totals range that answers the actual question imports faster and breaks less often than a raw dump. [Google's function reference](https://support.google.com/docs/answer/3093340) lists summarizing in the source as its first best practice.

## When Sharing Settings Break the Import

Three sharing changes break a working import without touching the formula.

A source file that was moved to the trash or deleted returns #REF! even though the receiving file is untouched. Restore it from Drive, or re-point the URL argument at whatever replaced it.

The setting that disables options to download, print and copy is the second. [Google Drive's sharing documentation](https://support.google.com/drive/answer/2494822) confirms that new import formulas cannot export data from a file with that restriction switched on, although formulas created before the change keep working.

The third is reach. The 600-user limit covers import connections too, so a document that is already shared very widely can refuse another link. Copy the range you need into a smaller source file you control.

## What Should You Check Before You Blame the Formula?

- Open the source file and confirm the data is there.
- Check that the URL argument ends in /edit.
- Compare the sheet name in the range string with the real tab name.
- Confirm both arguments sit inside straight double quotes.
- Ask whether the source file imports anything from the file you are editing.
- Estimate the range size. Hundreds of cells is normal, hundreds of thousands is not.

Most failures show up in the second and third checks, and both take under a minute. For a neighbouring failure, [the VLOOKUP error guide](/sheets-vlookup-error/) walks through lookup messages in the same order. When a report has to arrive on a schedule, [an Apps Script report](/automate-email-report-google-sheets/) removes the formula entirely, and the documented [Apps Script quotas](https://developers.google.com/apps-script/guides/services/quotas) set the ceiling for that route.

## FAQ

### Why does IMPORTRANGE show #REF! the first time I use it?

The two files are not connected yet. Click the cell, then click Allow access in the prompt that appears. If no prompt appears, confirm you can open the source file. If you cannot, ask the owner for at least view access.

### Why does IMPORTRANGE return #N/A instead of data?

The first argument is usually wrong. It must be the full address from the address bar, with the file ID in it. The published version link, the copy link and a link to a single tab all fail.

### Can IMPORTRANGE read a sheet that was renamed?

Only if the range string matches the new name. When the tab is renamed, the sheet name inside the string no longer exists and the formula returns #REF! until you update it.

### Why is my imported data stale?

The import is not a live push. It refreshes when the receiving file is opened, not on a fixed schedule. Close the tab and reopen the file to force a refresh.

## Sources

- [IMPORTRANGE function, Google Docs Editors Help](https://support.google.com/docs/answer/3093340)
- [Share files from Google Drive, Google Drive Help](https://support.google.com/drive/answer/2494822)
- [Quotas for Google Services, Apps Script documentation](https://developers.google.com/apps-script/guides/services/quotas)

---

Source: https://stacktrekker.com/importrange-not-working/