IMPORTRANGE
Pull a live range of cells from one Google Sheets workbook into another. Changes to the source update the destination automatically — the foundation of single-source-of-truth architectures across Sheets workbooks.
⚡ Quick Answer
IMPORTRANGE pulls cells from another Google Sheets workbook into your current workbook and keeps them live. Requires a one-time "Allow access" authorization the first time you use it against a new source.
Syntax
Arguments
| Argument | Type | Description |
|---|---|---|
| source_urlRequired | Text | Full URL of the source workbook in quotes. Copy from the address bar: "https://docs.google.com/spreadsheets/d/.../edit". The workbook key (long ID between /d/ and /edit) also works. |
| range_stringRequired | Text | The range to pull, formatted as "SheetName!A1:D10" in quotes. Include the sheet name, then !, then the range. Use "Sheet1!A:H" for entire columns. |
Authorization flow (first-time)
The first time you IMPORTRANGE from a specific source workbook, you'll see #REF! in the cell. This is expected — Sheets needs your permission to connect the two workbooks. The flow:
Authorization is one-time per source-destination pair, per user. After the first authorization, every IMPORTRANGE from that source works immediately.
Basic usage
Common patterns
Col1, Col2, Col3 instead of letters (IMPORTRANGE returns an array).Give IMPORTRANGE its own dedicated sheet in the destination workbook, then reference from other sheets locally. This keeps IMPORTRANGE ranges clean, prevents spill conflicts, and reduces cross-workbook lookups (which are slower than local ones).
Limits and quotas
| Limit | Value |
|---|---|
| Max IMPORTRANGE formulas per workbook | 50 |
| Cache refresh interval | ~30 minutes (or on edit) |
| Max cells per import (practical) | ~100,000 before performance degrades |
| Absolute cell ceiling | Google's 10M cells-per-workbook limit |
Common errors
| Error | Cause & fix |
|---|---|
#REF! + "Allow access" |
First-time authorization needed. Click the cell, hover the error, click "Allow access". |
#REF! no popup |
Spill area contains data. Clear it. Or you lost access to the source workbook. |
#ERROR! |
Malformed URL or range. Check quotes. Sheet name is case-sensitive. |
#N/A |
Source workbook or sheet doesn't exist. Verify URL still resolves and sheet wasn't renamed. |
| "Loading..." forever | Source too large or hitting quotas. Reduce range or wait a few minutes. |
📚 Want the complete deep dive?
The full pillar guide covers all 6 patterns in depth, single-source-of-truth architecture, security considerations, IMPORTRANGE vs alternatives, and includes a live-demo companion workbook.
Related functions
Frequently asked questions
Does IMPORTRANGE work in Excel?
No. IMPORTRANGE is native to Google Sheets. Excel's closest equivalent is Power Query for connecting to files, but it requires the UI-based Power Query editor rather than a cell formula and doesn't offer the same live-updating simplicity.
Why does IMPORTRANGE show #REF the first time?
Expected behavior. The first IMPORTRANGE from a specific source needs authorization. Click the cell, hover the error, click "Allow access" in the popup. Once authorized, every future IMPORTRANGE from that source works immediately.
How often does IMPORTRANGE update?
Approximately every 30 minutes via cache. Opening the destination workbook or making any edit forces immediate refresh. For sub-minute updates, use Apps Script or Data Connectors instead.
How do I combine IMPORTRANGE with QUERY?
Wrap IMPORTRANGE inside QUERY: =QUERY(IMPORTRANGE(url, "Sheet!A:H"), "SELECT Col1, Col2 WHERE Col3 = 'North'"). Use Col1, Col2, Col3 for column references — letters don't work when data comes from IMPORTRANGE.
Can I import from a workbook I don't own?
Yes, if you have at least view access to it. "Anyone with the link" shared workbooks work immediately. If access is later revoked, your IMPORTRANGE formulas break with #REF!.