How to Fix IMPORTRANGE Not Working in Google Sheets
Freelancers, solo operators, and very small business owners using Google Sheets as a practical business operating system.
On this page
A broken IMPORTRANGE is almost always an access problem or a range-string problem, not a formula bug. Make a copy of the file, write the symptom in one sentence, then use the diagnosis table below to find the failing layer. The two most common fixes: click Allow access if the destination file was never granted permission, and re-check the source URL and the exact quoted range after a tab or column was renamed.
If IMPORTRANGE cannot pull data from the source spreadsheet, do not start by rewriting formulas. That is how a small spreadsheet problem becomes a business-system problem. Preserve the file, isolate the failure, and change only the smallest part you can prove is wrong.
IMPORTRANGE depends on two things working at once: the destination file having permission to read the source, and the quoted range string still pointing at data that exists. The fix depends on which of those broke.
If this is one of several things breaking in the same file, use the broader Google Sheets troubleshooting guide first, then come back to this specific repair path.
First make a copy and name the symptom#
Make a duplicate or version-safe copy before risky edits. You do not need a perfect backup ritual; you need one clean rollback point before you touch formulas, protected ranges, source lists, imported data, or dashboard ranges.
Then write the symptom in one sentence: what changed, where it changed, and what the sheet should have done instead. That sentence keeps you from treating every spreadsheet issue like a formula issue.
Use this diagnosis table before changing anything#
Identify the layer that failed before you edit; do not jump from symptom to fix because a forum answer sounded plausible. Read the middle column first, then apply the safest fix in the right column.
| What you see | Likely cause | Safest first fix |
|---|---|---|
| #REF! with access prompt | Destination file has not been allowed access | Click allow access if this is the correct source |
| Imported range is blank | Wrong tab, empty cells, or wrong range string | Test a tiny known range first |
| Import broke after rename | Tab/range reference changed | Update the quoted range string |
| Dashboard slow or flaky | Too many chained imports | Create a staging tab and reduce dependencies |
| Cannot access source | Sharing permissions changed | Confirm source file permissions |
Test the smallest visible part before editing anything#
Now narrow the failure to one place. Test a tiny known range first, such as a single cell or one row from the source. If access is the problem, check one blocked cell and confirm the source file's sharing. If the import is blank, check the exact tab name and range string before assuming the data is gone.
This is where most rushed fixes go wrong: they edit the final visible output while the actual failure is upstream. For formula issues, the guide on finding which cell is breaking a Google Sheets formula is the safer path.
Check whether a copied template changed the sharing#
Shared templates add another layer: other people may be using filters, protected ranges, or views that change what you see without changing the underlying data. Before you delete rows or overwrite formulas, check whether you are looking at a filtered view, a locked range, or a copied template with inherited settings.
If the problem appeared right after copying a template, pause and compare it with a clean original. The copy process itself may have changed sharing, validation, protected ranges, or references — any of which can quietly break an import. See how to make a copy of a Google Sheets template without breaking it before rebuilding the file from scratch.
Keep errors visible while you repair#
When official Google documentation defines a function or feature, use that behavior as the boundary. A workaround is fine only if you understand what it is bypassing.
Don't hide the error you're trying to fix
Wrapping an import in IFERROR can make a sheet calmer for users, but it also hides the broken cell you actually need to repair. Keep a temporary debug copy where errors are visible. Masking errors is the last polish step, not the first troubleshooting move.
A safe repair sequence for business files#
A safe repair sequence runs in order. Skipping a step is how a one-cell fix turns into a rebuild.
- Duplicate the file.
- Reproduce the issue in one place.
- Isolate the input or setting that broke.
- Make one edit.
- Test with a realistic sample row.
- Document the change.
If you cannot reproduce the problem in a small test, do not make a large change to the live sheet. For formulas specifically, start with the plain-English Google Sheets formulas guide if the syntax itself is unclear. Guessing at functions while a live template is broken is slower than learning the one pattern you need.
How to stop the same import breaking again#
Prevention is mostly structure. Keep raw data separate from formulas. Keep formulas separate from dashboard outputs. Keep source lists for dropdowns on a clearly named tab. Protect formula cells, but make input cells obvious. Add a small notes tab that explains which ranges are safe to edit.
The goal is not to make the sheet impossible to break. The goal is to make the next break obvious enough that a tired operator can fix it without opening ten tabs and hoping.
What to do next#
If the sheet is business-critical, make the smallest repair in a duplicate, test it with realistic sample data, and apply it to the live version only after the symptom is gone. If the issue turns out to be formula-related, continue with how to fix formula parse error in Google Sheets or how to fix VLOOKUP returning N/A in Google Sheets depending on what you see.
Spreadsheet confidence comes from a repeatable repair loop: preserve, isolate, test, fix, document. That loop beats almost every one-line answer pasted from a random thread.
Questions people ask
Why does IMPORTRANGE show #REF! with an access prompt?
Why is my imported range blank?
My import broke right after someone renamed a tab. What now?
My dashboard got slow and flaky after I added more imports. Is that normal?
We aim to keep this accurate and date-stamp it when product steps change. We don't claim hands-on product testing we haven't done, and we'll always point to the free, native route first. How we work.