Google Sheets IMPORTRANGE Not Working

Setting it up

1. Copy the URL of the source spreadsheet from your browser's address bar.
2. In the destination sheet, enter =IMPORTRANGE("paste_the_url_here", "Sheet1!A1:D100").
3. Press Enter. The cell shows #REF! — this is expected on first use.
4. Click the cell. A prompt appears; click Allow access.
5. The data loads.

You can paste the whole URL or just the long key from the middle of it. Both work, as Google's IMPORTRANGE reference notes — the full URL is simply easier to copy.

Access is granted once per pair of spreadsheets, by the person who adds the formula, and it persists. Later formulas pointing at the same source do not re-prompt.

Why it says #REF!

The same error covers several causes, which is why it is confusing:

• Access not granted. Click the cell and look for the Allow access prompt. This is the answer most of the time.
• You cannot open the source yourself. IMPORTRANGE inherits your permissions. If you have no access to the source spreadsheet, no amount of clicking will help — you need to be given access to it first.
• The sheet name is wrong. It must match exactly, including capitals. A renamed tab silently breaks the formula.
• Not enough room. The result spills into the cells below and right; anything already there blocks it. Clear the destination area.

Sheet names with spaces

A frequent and easily-missed failure. When the tab name contains a space, it needs single quotes inside the double quotes:

=IMPORTRANGE("url", "'Sales Data'!A1:D100")

Without the single quotes, Sheets cannot tell where the name ends and returns an error. Renaming the tab to avoid spaces is the simpler long-term fix.

"Internal error" and other intermittent failures

Sometimes the formula is correct and it still fails. What actually helps:

• Wait and reload. Many of these are transient. Reloading the page resolves a good proportion.
• Reduce the number of IMPORTRANGE calls. Dozens of them in one spreadsheet is the common trigger. Import one wide range once and reference it locally rather than calling IMPORTRANGE repeatedly.
• Narrow the range. "Sheet1!A1:D100" is far lighter than "Sheet1!A:Z". Open-ended ranges pull far more than you need.
• Force re-evaluation by editing the formula's arguments — putting the URL in a cell and referencing it makes this easy.

What it will not do

Worth knowing before you build around it:

• It imports values only, never formatting. Colours, fonts, borders and conditional formatting all stay behind. Apply formatting in the destination.
• It is not instant. Results are cached and refresh on their own schedule, so treat it as near-live rather than real-time — the same behaviour covered in Google Sheets formulas not updating.
• It is read-only. Editing an imported cell is impossible; you change the source.
• Two sheets importing from each other creates a circular dependency and will not work.

Filtering and sorting what you import

IMPORTRANGE returns a block, but it nests inside other functions, which is where it becomes genuinely useful:

• Filter rows: =FILTER(IMPORTRANGE("url","Sheet1!A1:D100"), IMPORTRANGE("url","Sheet1!C1:C100")="Open")
• Sort: =SORT(IMPORTRANGE("url","Sheet1!A1:D100"), 1, TRUE)
• Pick columns: =QUERY(IMPORTRANGE("url","Sheet1!A1:D100"), "select Col1, Col3")

Note that QUERY refers to imported columns as Col1, Col2 and so on rather than letters — a common stumbling point.

A tidier pattern: put a plain IMPORTRANGE on a dedicated hidden tab, then filter and sort that locally. It avoids repeating the import inside every formula, which is both easier to read and much lighter.

Importing from another tab in the same file

You do not need IMPORTRANGE for this — it is for separate spreadsheets. Within one file, reference the tab directly:

='Sheet2'!A1:D100

Using IMPORTRANGE where a direct reference would do adds latency and a failure point for no benefit.

Frequently asked questions

Why is my IMPORTRANGE not working?

Almost always because access was never granted. The cell shows #REF! on first use — click it and press "Allow access". If that does not appear, check you can open the source spreadsheet yourself, since IMPORTRANGE uses your own permissions.

How do I use IMPORTRANGE?

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100"). Both arguments are quoted strings, with the sheet name inside the second one. Then click the cell and allow access when prompted.

Why does IMPORTRANGE say #REF!?

Four possibilities: access not granted, you cannot open the source yourself, the sheet name does not match exactly, or something is blocking the space the result needs to spill into.

How do I import from a sheet whose name has a space?

Add single quotes inside the double quotes: "'Sales Data'!A1:D100". Without them the formula cannot parse the name.

Why do I get an internal error?

Often transient — reload the page first. Persistent cases usually mean too many IMPORTRANGE calls in one spreadsheet or ranges that are too large. Import once into a dedicated tab and reference it locally.

Can IMPORTRANGE bring the formatting across?

No. It imports values only. Colours, fonts and conditional formatting stay in the source and must be reapplied in the destination.

Do I need IMPORTRANGE for another tab in the same file?

No. Reference it directly with ='Sheet2'!A1:D100. IMPORTRANGE is only for pulling from a different spreadsheet.

Conclusion

If it is not working, check access first — that single step accounts for most failures, and the #REF! on first use is expected rather than a fault.

Beyond that: quote both arguments, add single quotes around sheet names containing spaces, keep ranges tight, and import once into a dedicated tab rather than calling IMPORTRANGE from every formula.

Related: Google Sheets formulas not updating covers the caching behaviour, and drop-down lists covers pulling options from another sheet.