Your sales data lives in one spreadsheet. Finance has a different spreadsheet. Your dashboard needs data from both. IMPORTRANGE is the bridge.

The Syntax

IMPORTRANGE syntax =IMPORTRANGE(spreadsheet_url, range_string)
  • spreadsheet_url: The full URL of the source spreadsheet (or just the spreadsheet ID)
  • range_string: The sheet name and range (e.g., "Sheet1!A1:D100")
Basic example =IMPORTRANGE( "https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!A1:F500" )

First-Time Access: The "Allow Access" Popup

The first time you use IMPORTRANGE with a new source spreadsheet, you'll see a #REF! error with a popup saying "You need to connect these sheets." Click Allow access.

#REF! - You need to connect these sheets

This is not an error. It's a one-time permission prompt. Hover over the cell, click "Allow access," and it will work. You only need to do this once per source spreadsheet.

Access requirements

You must have at least "Viewer" access to the source spreadsheet. If you don't, you'll get a persistent #REF! error. Ask the owner to share the file with you.

Using the Spreadsheet ID Instead of Full URL

The URL can be long. You can use just the spreadsheet ID, the string between /d/ and /edit in the URL:

Full URL vs ID only Full URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit Just the ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms Both work: =IMPORTRANGE("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms", "Sheet1!A:D")

IMPORTRANGE + QUERY: The Power Combo

This is the most useful pattern in multi-sheet systems. Import data from another spreadsheet and immediately filter it:

Import and filter in one formula =QUERY( IMPORTRANGE("spreadsheet_id", "Orders!A1:G"), "SELECT * WHERE Col5 = 'Shipped'", 1 )

Important: When IMPORTRANGE is inside QUERY, columns are referenced as Col1, Col2, etc., not letters.

Import and aggregate - total sales by region =QUERY( IMPORTRANGE("spreadsheet_id", "Sales!A1:F"), "SELECT Col3, SUM(Col5) GROUP BY Col3 ORDER BY SUM(Col5) DESC LABEL Col3 'Region', SUM(Col5) 'Revenue'", 1 )

IMPORTRANGE + VLOOKUP

Look up a value from another spreadsheet =VLOOKUP(A2, IMPORTRANGE("spreadsheet_id", "Products!A:C"), 3, FALSE )

This looks up the value in A2 against the Products sheet in another spreadsheet and returns column 3.

Caching IMPORTRANGE for Performance

IMPORTRANGE recalculates frequently, which can slow down large sheets. The solution: import data into a hidden "cache" tab, then query from that tab.

Step 1: Hidden _cache tab, cell A1 =IMPORTRANGE("spreadsheet_id", "Data!A1:F5000")
Step 2: Dashboard tab queries the cache =QUERY(_cache!A:F, "SELECT A, B, SUM(E) GROUP BY A, B ORDER BY SUM(E) DESC", 1)

This way, IMPORTRANGE runs once (into the cache), and all your analysis formulas query the local copy. Much faster.

Common IMPORTRANGE Errors

#REF! - You need to connect these sheets

First-time permission prompt. Click "Allow access."

#REF! - Results too large

Google Sheets has a limit on cells that can be imported. Narrow your range: instead of A:Z, use A:F.

#VALUE! - Could not find sheet

The sheet name in your range_string doesn't exist. Check for typos and exact capitalization.

Slow / Loading forever

IMPORTRANGE has rate limits. If you have 20+ IMPORTRANGE formulas, some will queue. Use the caching pattern above to reduce the number of active IMPORTRANGE calls.

Limits and Alternatives

  • IMPORTRANGE limit: Google doesn't publish an exact number, but practical limits are around 50 concurrent IMPORTRANGE calls per spreadsheet.
  • Data freshness: IMPORTRANGE updates roughly every 30 minutes for large datasets. It's not real-time.
  • For real-time sync: Use Google Apps Script with time-based triggers to copy data between sheets on a schedule you control.
  • For large datasets (100K+ rows): Consider BigQuery connected to Sheets, or Apps Script that copies only incremental changes.

Building Multi-Sheet Systems

The standard architecture for business systems across multiple spreadsheets:

  1. Source spreadsheets: Where data is entered (Orders, Customers, Inventory)
  2. Dashboard spreadsheet: Imports from all sources via IMPORTRANGE, has analysis/charts
  3. Cache tabs: Hidden tabs in the dashboard that hold imported data
  4. Analysis tabs: QUERY formulas that read from cache tabs, not directly from IMPORTRANGE

This is exactly how we build client dashboards at sheets.works. The pattern scales to 5-10 source spreadsheets feeding into one master dashboard.

IMPORTRANGE turns separate spreadsheets into a connected system. Add QUERY on top, and you have a real-time reporting engine.