How to Use IMPORTRANGE in Google Sheets

Pull a range of data from one Google spreadsheet into another so it stays in sync — plus how to grant access when you hit #REF!, and how to import only the rows you actually want.

By Ed · Published July 25, 2026 · 5 min read

Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

The quick version: =IMPORTRANGE("spreadsheet URL", "Sheet1!A1:D100"), then click Allow access on the #REF! that appears. That connects the two sheets and pulls the data live. Here are the details.

The syntax

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Sheet1!A1:D100")

First argument: the source spreadsheet's full URL (or just its ID) in quotes. Second: the sheet name and range in quotes. You can put the URL and range in cells and reference them to keep the formula tidy.

The #REF! and "Allow access"

The first time you link two spreadsheets, the cell shows #REF! with an Allow access prompt. Click it once — this grants permission for the destination to read the source. You only do this per source spreadsheet. If no prompt appears, double-check the URL and that you can view the source.

Import only the rows you want

Pulling a whole sheet when you need part of it is wasteful. Wrap IMPORTRANGE in QUERY to filter at import time:

=QUERY(IMPORTRANGE("URL","Sheet1!A1:D100"), "SELECT Col1, Col4 WHERE Col2 = 'West'", 1)

That imports only the columns and rows you asked for — much lighter than importing everything and filtering afterward.

💡 Tip: wrap it in IFERROR so a temporary connection hiccup shows a blank instead of an error: =IFERROR(IMPORTRANGE("URL","Sheet1!A1:D100"), "").

💬 Skip the formula: if the reason you're linking sheets is to answer a question across them, the Quiriz add-in reads your data directly — ask =QUIRIZ("total sales by region across all my data", "table") and skip the plumbing.

If you are wiring sheets together because your team standardized on Google Sheets, the adoption picture is less one-sided than it looks — 3 billion+ Workspace users against 450 million+ paid Microsoft 365 seats, with most organizations running both stacks and paying for the overlap. We collected the numbers in our Google Sheets statistics roundup.

Working across several sheets?

Quiriz reads all your data and answers questions across it in plain English — no IMPORTRANGE wiring. Free to start.

Try Quiriz free →

Frequently asked questions

Why is IMPORTRANGE showing #REF!?

The two spreadsheets aren't connected yet. Click the cell with #REF!, and a prompt appears with an Allow access button — click it once to grant permission. If there's no prompt, re-check the URL and that you have view access to the source sheet.

What is the syntax for IMPORTRANGE?

=IMPORTRANGE(spreadsheet_url, range_string). The first argument is the source spreadsheet's URL or ID in quotes; the second is the sheet and range in quotes, e.g. "Sheet1!A1:D100".

How do I import only certain rows with IMPORTRANGE?

Wrap it in QUERY. For example =QUERY(IMPORTRANGE("URL","Sheet1!A1:D100"), "SELECT Col1, Col4 WHERE Col2 = 'West'", 1) imports only the rows where the second column is "West".

Does IMPORTRANGE update automatically?

Yes. It refreshes periodically and when the source changes, so the destination stays in sync. Very large imports can be slower to recalculate.