IMPORTRANGE is a Google Sheets formula that pulls data from one spreadsheet into another — automatically, without copying anything by hand. When the source sheet updates, the destination sheet updates too.

This tutorial covers the exact syntax, how to connect two sheets for the first time, how to fix the errors that come up, and when to use IMPORTRANGE vs other approaches.

---

When You Need IMPORTRANGE

Use IMPORTRANGE when:

  • You have a master data sheet and want to display a subset of it in another sheet
  • Multiple people own different source sheets and you want to consolidate into one view
  • You want a read-only version of a sheet you can share without giving edit access to the original
  • You're building a dashboard that pulls from several separate sheets

Don't use IMPORTRANGE when you just need data from another tab in the same spreadsheet — for that, you reference it directly with SheetName!A1 syntax. IMPORTRANGE is specifically for pulling from a different spreadsheet file.

---

The Basic Syntax

Code
=IMPORTRANGE("spreadsheet_url", "range_string")

Two arguments:

  • spreadsheet\_url — the full URL of the source spreadsheet, in quotes
  • range\_string — the sheet tab name and cell range you want to import, in quotes

A real example:

Code
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit", "Sheet1!A1:D100")

This pulls the range A1:D100 from the tab called "Sheet1" in the linked spreadsheet.

---

Step 1: Get the Source Sheet URL

Open the spreadsheet you want to pull data from. Copy the full URL from your browser's address bar. It will look like this:

Code
https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit#gid=0

You can use the full URL including the #gid=0 at the end — IMPORTRANGE ignores everything after edit.

If you want, you can also use just the spreadsheet ID (the long string of characters between /d/ and /edit). Both work:

Code
=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A1:D100")

---

Step 2: Identify the Range You Want to Import

The range string has two parts: the tab name and the cell range, separated by an exclamation mark.

Code
"Sheet1!A1:D100"
  • Sheet1 — the exact name of the tab in the source spreadsheet. If the tab is called "January Sales" or "Data — Raw", use that exact name including spaces and capitalization.
  • A1:D100 — the cell range. You can use a fixed range like A1:D100 or an open-ended column range like A:D (imports the entire column, all rows).

If the tab name has spaces or special characters, wrap it in single quotes inside the range string:

Code
"'January Sales'!A1:D100"

---

Step 3: Enter the Formula and Grant Access

Type your IMPORTRANGE formula into the destination cell and press Enter.

The first time you connect two spreadsheets, Google Sheets will show an error in the cell with a blue "Allow access" prompt. This is normal — Google requires you to explicitly authorize the connection between the two files.

Click the cell with the error, then click Allow access in the popup that appears.

After you click Allow access, the formula evaluates and your data appears. You only need to do this once per spreadsheet pair — future IMPORTRANGE formulas between the same two files won't require re-authorization.

---

Step 4: Work With the Imported Data

A few things to know about how imported data behaves:

It's live. When someone edits the source spreadsheet, the destination updates automatically — usually within a few minutes. There's no manual refresh.

It's read-only in the destination. You can't edit the imported cells. If you need to modify the data, edit it in the source sheet.

It imports values, not formulas. If the source sheet has a formula in a cell, IMPORTRANGE imports the result of that formula — not the formula itself.

It imports in order. The data lands in the destination exactly as it's arranged in the source. If column A in the source is "Name" and column B is "Email," those come in as the first and second columns in the destination.

---

Common Errors and Fixes

#REF! — You don't have permissions to access that sheet

The source spreadsheet is private and your Google account doesn't have at least View access to it. Ask the owner to share it with you, or share it with anyone with the link.

#REF! — Result was not automatically expanded

The imported range is trying to paste data into cells that are already occupied. IMPORTRANGE needs a clear block of empty cells. Move the formula to a cell with enough empty space below and to the right.

#VALUE! — range_string error

The range string format is wrong. Check that:

  • There's an exclamation mark between the tab name and the cell range: Sheet1!A1:D100
  • The tab name matches exactly — including capitalization and any spaces
  • If the tab name has spaces or special characters, it's wrapped in single quotes: 'My Sheet'!A1:D100
  • The formula uses straight quotes (") not curly quotes — pasting from a document sometimes converts these

The formula shows #N/A or loads forever

IMPORTRANGE can take 10–30 seconds to load on first use, especially for large ranges. If it keeps loading, try narrowing the range (e.g., A1:D50 instead of A:D) and see if it resolves. Very large imports (thousands of rows across many columns) can cause timeouts.

---

Useful Patterns

Import only specific columns

Use IMPORTRANGE inside a QUERY formula to filter columns:

Code
=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A:F"), "SELECT Col1, Col3, Col5")

This imports columns A, C, and E from the source — skipping the others.

Import only rows that match a condition

Code
=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A:D"), "SELECT * WHERE Col4='Active'")

Imports only rows where column D says "Active." Column numbers in the QUERY formula refer to the position in the imported range, not the original spreadsheet.

Import from multiple sheets and stack the results

Code
={IMPORTRANGE("url1", "Sheet1!A:D"); IMPORTRANGE("url2", "Sheet1!A:D")}

The curly braces and semicolons stack the two imports vertically. Both source sheets need to have the same column structure.

---

IMPORTRANGE vs Other Options

| Situation | Use This | |-----------|----------| | Pulling data from another file that updates regularly | IMPORTRANGE | | Referencing a different tab in the same file | SheetName!A1 syntax | | Pulling a value from a specific row based on a lookup | VLOOKUP or XLOOKUP | | Moving rows between tabs when a status changes | Apps Script with onEdit trigger | | Copying rows once (not live) | Copy and paste special → values only |

---

What to Build Next

IMPORTRANGE handles the data connection. Once data is flowing automatically, common next steps are:

  • Consolidate data from multiple team members' sheets into one master view
  • Build a summary dashboard that pulls from several operational sheets
  • Create a read-only shared view of a private spreadsheet

For moving rows between sheets based on a trigger (rather than just viewing them in both places), see How to Automatically Copy Rows From One Google Sheet to Another.

Don't want to set this up yourself? Describe your sheets and what data needs to flow between them, and it'll be configured for you. Get it installed