Key takeaways
- IMPORTRANGE returns a live copy of a source range; it does not link individual cells.
- Authorise each source-destination pair once, using an account that will still exist later.
- Import one clean table per call and keep tab names and column order stable.
- Anyone with access to the destination file can see imported data, so review sharing.
On this page
What the function actually does
IMPORTRANGE reads a range from another spreadsheet and returns the values into the destination file. The destination holds a copy that refreshes when the source changes, not a cell-level link. Formatting, formulas, comments, and protected ranges do not travel; the import is data.
The function takes two text arguments: the source URL and a range string that names the tab and the cells. A readable setup keeps both in a configuration block: =IMPORTRANGE(Config!B2, Config!B3), where B2 holds the URL and B3 holds a range such as Data!A1:H. When the source moves, one cell changes instead of every formula.
Grant access once, on purpose
The first formula returns #REF until someone with access to the source approves the connection. Clicking the cell reveals a prompt; approving it authorises that source-destination pair. A second range from the same source does not ask again.
The authorisation is tied to the account that grants it. If that person leaves, loses access, or the file changes owner, the import can fail for everyone. Use a shared account or a service account for production reports, and record who approved the link.
IMPORTRANGE does not bypass sharing. The destination audience can see the imported values once the link is approved, even if they cannot open the source. Importing a confidential tab into a widely shared dashboard leaks it. Check the destination sharing settings before adding the formula, not after.
Import one stable table
Each IMPORTRANGE call is a separate dependency with its own load time and failure mode. Importing ten small ranges from one file multiplies the overhead. Import one clean table, then derive the small views with FILTER, QUERY, or pivot tables on a staging sheet.
The source must stay predictable. Renaming a tab breaks the range string, inserting a column inside the range shifts every downstream reference, and deleting rows truncates the import. Agree the tab name and column order with the source owner and treat them as an interface.
- Keep a staging sheet that only holds imports; do not let users type in it.
- Import the narrowest range that covers the report, with room for growth.
- Record the source URL, tab, range, and owner in a README tab.
- Avoid importing formatted report tabs; import the underlying data.
Wrap it in a query or filter
The usual pattern is =QUERY(IMPORTRANGE(Config!B2, Config!B3), Query!B2, 1). The query runs in the destination, but the full source range still crosses the network first. A narrow import is faster than importing everything and filtering afterwards.
Avoid chains where file A imports from B and B imports from C. Each hop adds latency and another permission dependency, and a break in the middle is hard to diagnose. Point every reporting file at the source of truth, or consolidate in a database when the chain becomes long.
Do not expect instant refresh. The import recalculates on the destination's schedule, and a large range can sit in a loading state. A workflow that requires the number to be current the moment someone opens the file needs a different design.
Diagnose the common errors
Most failures produce one of a small set of errors, and the fix is usually in the configuration block rather than the formula. Test the bare IMPORTRANGE before adding a query layer, because an error inside the source becomes an error in the import.
| Error | Likely cause | Fix |
|---|---|---|
| #REF | Access was never granted, the URL is wrong, or the source was deleted | Approve access, paste the URL from the source address bar, and confirm the file exists |
| #N/A | The range text does not match a tab or range in the source | Check the tab name, including spaces, and the A1 range |
| Loading | The range is large or the destination is recalculating | Narrow the range, wait for the calculation, and remove unused imports |
| #ERROR | An argument is missing or the URL is not a valid spreadsheet link | Use two cell references and check both values |
A governance checklist
Cross-file reporting fails slowly: a permission changes, a tab is renamed, and a dashboard keeps showing last month's numbers because nobody checked. Assign an owner to each link and review the list when people change roles.
- One named owner for every source-destination link.
- Access granted by a durable account, not a personal one.
- Destination sharing reviewed before importing sensitive data.
- Source structure changes announced before they happen.
- A reconciliation check against the source after any change.
- A fallback snapshot for month end if the link fails.
Frequently asked questions
Why does IMPORTRANGE show #REF after working for months?+
The permission may have been revoked, the source moved or was deleted, or the approving account lost access. Open the source and re-approve the connection.
Does IMPORTRANGE update in real time?+
It refreshes when the source changes and the destination recalculates, but it is not a streaming connection. Large ranges can lag, and a viewer may see the last saved state.
Can I import a whole sheet?+
Use a bounded range. Whole-column imports pull far more data than a report needs and slow the destination file.
Can viewers see data from a source they cannot open?+
Yes. Once the link is approved, anyone with access to the destination can see the imported values. That is a sharing decision, not just a formula detail.
Need help applying this?
Improve your excel & google sheets.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Google Sheets data validation and protection
Data validation shapes what users enter; protection limits what they can change. Together they prevent most spreadsheet accidents, but neither is security.
Google Sheets dashboards with pivot tables
A pivot dashboard is a data source, a pivot layer, and a presentation sheet. Get the source clean, keep the pivot stable, and make refresh an explicit step.