IMPORTRANGE in Google Sheets
Use IMPORTRANGE to pull a range of cells from another Google Sheets file into this one, kept in sync automatically.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit", "Sheet1!A1:D100")
Pulls Sheet1!A1:D100 from the other file. The first time, click the cell and Allow access.
How it works
IMPORTRANGE takes two things: the spreadsheet URL or key of the source file, and the range string including the sheet name. The first time you link two files you must click Allow access. The result is a live copy that updates when the source changes. Combine it with QUERY or FILTER to bring in only the rows you need.
Variations
Use just the file key
=IMPORTRANGE("FILE_ID", "Sheet1!A:D")
The long ID between /d/ and /edit in the URL also works.
Import only matching rows (with QUERY)
=QUERY(IMPORTRANGE("FILE_ID","Sheet1!A:D"), "SELECT Col1, Col2 WHERE Col3 > 100", 1)
Inside QUERY on an IMPORTRANGE, refer to columns as Col1, Col2, ...
Import and filter
=FILTER(IMPORTRANGE("FILE_ID","Sheet1!A:D"), IMPORTRANGE("FILE_ID","Sheet1!B:B")="West")
Both IMPORTRANGE calls must point at the same file.
Examples
| Scenario | Formula |
|---|---|
| Import one column | =IMPORTRANGE("FILE_ID", "Sheet1!C:C") |
| Import a named range | =IMPORTRANGE("FILE_ID", "MyNamedRange") |
| VLOOKUP into another file | =VLOOKUP(A2, IMPORTRANGE("FILE_ID","Sheet1!A:B"), 2, FALSE) |
FAQ
Why does IMPORTRANGE show #REF!?
You need to grant access — click the cell and press Allow access. #REF! also appears if the file ID or sheet/range name is wrong.
Does IMPORTRANGE update automatically?
Yes. It refreshes about every hour, and sooner when the source file changes and both files are open.
Can I filter what IMPORTRANGE brings in?
Wrap it in QUERY or FILTER. Inside QUERY, refer to columns as Col1, Col2, and so on.