Quotes, captions, names, wishes & practical how-tos

Google Sheets & Drive

Google Sheets IMPORTRANGE: Link Data Between Files

Server room racks, a picture of data linked between files with Google Sheets IMPORTRANGE
Photo: BalticServers.com, CC BY-SA 3.0, via Wikimedia Commons

Google Sheets IMPORTRANGE pulls data from one spreadsheet file into another and keeps it up to date automatically. It is the standard way to build a dashboard from several source files, share only part of a workbook, or combine data from teammates without copy and paste. This guide covers the syntax, the permission prompt, practical combinations and common errors.

Key takeaways

  • The syntax is IMPORTRANGE(spreadsheet_url, range_string), and both arguments are text in quotes.
  • The first time you connect two files, you must click Allow access.
  • You need at least view access to the source file.
  • Combine IMPORTRANGE with QUERY, FILTER or VLOOKUP to import only what you need.

IMPORTRANGE Syntax Explained

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D20")
Argument What to enter
spreadsheet_url The full URL of the source file, or just its spreadsheet key, inside quotes.
range_string The sheet name and range as text, such as "Sales!A1:D20". If you leave out the sheet name, the first sheet is typically used.

The URL is the address in the browser when the source file is open. Google lists the arguments on its IMPORTRANGE help page, which is worth checking for current behavior.

  1. Open the source spreadsheet and copy its URL from the address bar.
  2. Open the destination spreadsheet and click an empty cell, for example A1 on a blank sheet.
  3. Type the formula, pasting the URL between quotes and adding the range, then press Enter.
  4. Expect a #REF! error at first. Hover over the cell and click Allow access.
  5. The data appears, filling the cells below and to the right of the formula.

You only need to approve each source file once per destination spreadsheet. After that, changes in the source flow through automatically, although they can take a moment to refresh.

Woman at a laptop in a home office, linking spreadsheets with Google Sheets IMPORTRANGE
Photo: Shixart1985, CC BY 2.0, via Wikimedia Commons (source)

Keep the URL Tidy with a Helper Cell

Long URLs make formulas hard to read. Store the address in a cell, say Settings!B1, and reference it:

=IMPORTRANGE(Settings!B1, "Sales!A1:D")

Ending the range with just a column letter, as in D, takes every row in those columns, so new data is included as it is added. Note that a cell reference such as Settings!B1 is written without quotation marks, while a typed URL or range needs them.

Import Only What You Need with QUERY

Pulling an entire sheet is rarely necessary. Wrapping IMPORTRANGE in QUERY lets you filter, sort and select columns while importing:

=QUERY(IMPORTRANGE(Settings!B1, "Sales!A1:D"), "select Col1, Col4 where Col3 = 'West'", 1)

In QUERY, imported columns are referred to as Col1, Col2 and so on. The final 1 tells QUERY that the first row is a header. If you prefer a formula without a query language, the FILTER function can wrap the imported range too.

Combine several sources into one sheet

A common use is a master sheet that gathers one tab from each team member’s file. Put the imports in curly braces, separated by semicolons, and make sure each source has the same number of columns in the same order. If the sources have header rows, import them without the header from the second import onward, for example by starting its range at row 2.

Use IMPORTRANGE Inside VLOOKUP

You can look up values in a different file by passing the imported range as the table argument:

=VLOOKUP(A2, IMPORTRANGE(Settings!B1, "Products!A:C"), 3, FALSE)

This works but recalculates every time the sheet changes, so for big lookups it is often faster to import the table once onto a hidden sheet and look up against that. See our VLOOKUP guide for the matching rules.

Different data storage media, as a metaphor for Google Sheets IMPORTRANGE moving data between files
Photo: Unnerving duck, CC0, via Wikimedia Commons (source)

Troubleshooting Google Sheets IMPORTRANGE Errors

  • #REF! “You need to connect these sheets”. Click Allow access on the cell.
  • #REF! “Array result was not expanded”. Something already occupies the cells the result needs. Clear them or move the formula.
  • #REF! about the sheet or range. Check the spelling of the sheet name and that the range exists. A sheet name with spaces is fine inside the range string, but it must match exactly.
  • Permission errors. You need access to the source file. Ask the owner for view access.
  • Result looks stale. Refreshes are not always instant. Wait, or reload the page.

Limits and Good Practice

Large imports can slow a workbook, and Google may limit very heavy or deeply chained imports. Import only the columns you need, avoid chaining IMPORTRANGE through many files and keep a stable source layout so column positions do not shift. Remember that anyone who can edit the destination file can see the imported data, even if they cannot open the source, so think about who has access. If you are comparing spreadsheet tools, our overview of Google Sheets vs Excel shows how linking works across both.

Frequently Asked Questions

Can IMPORTRANGE pull data from a file someone else owns?

Yes, if you have at least view access to that file. Without it, the formula returns an error.

Does the imported data update automatically?

Yes. Changes in the source appear in the destination, usually within a short time.

Can I import from several files into one sheet?

Yes. Stack the results with curly braces, for example ={IMPORTRANGE(...); IMPORTRANGE(...)}, provided the columns line up.

Do I have to use the whole URL?

No. The spreadsheet key, the long ID inside the URL, is also accepted.

Why does IMPORTRANGE show #REF! even after I allowed access?

Check the range text, make sure the output area is empty and confirm you are signed in to the account that has access.

Wrap-Up

Allow access once, keep the URL in a helper cell and combine the import with QUERY or FILTER to control what arrives. Used this way, Google Sheets IMPORTRANGE keeps separate files in sync with very little maintenance.

Written byEditorial Team

Part of the MarkdownToGoogleDocs editorial team, sharing copy-ready words and practical Markdown and Google Docs how-tos.