How to Use IMPORTRANGE in Google Sheets
=IMPORTRANGE(spreadsheet_url, range_string). The first time you use a new source, you must click Allow access.
What IMPORTRANGE does
IMPORTRANGE connects two spreadsheets. You keep the data in one file and show it in another, and the copy updates when the source changes. People use it to build a summary file from several team sheets, or to share part of a sheet without sharing the whole file.
IMPORTRANGE syntax
=IMPORTRANGE(spreadsheet_url, range_string)
spreadsheet_url: the URL of the source spreadsheet, in quotation marks, or a cell that contains it.range_string: the range to import, in the form"[sheet_name!]range", such as"Sheet1!A2:B6". If you leave out the sheet name, Sheets imports from the first sheet. This can also be in quotation marks or a cell reference.
A step-by-step example
Suppose the source spreadsheet has the URL https://docs.google.com/spreadsheets/d/abc123/edit and you want cells A1 to C10 of the sheet named Sheet1. In the destination spreadsheet, type:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123/edit", "Sheet1!A1:C10")
(The URL above is a made-up example. Use the address of your own source file.) Here is what happens:
- Sheets opens the source spreadsheet.
- It reads
A1:C10on the sheet named Sheet1. - It writes those 30 cells into your sheet, starting at the cell with the formula.
Allow access the first time
The first time a sheet pulls from a new source, Sheets shows a #REF! error. If you own the source, point at the cell and click Allow access. If you do not own it, you need to request access and wait for the owner to approve. After access is granted, any editor of the destination spreadsheet can import from that source.
Keep the URL and range in cells
You can store the URL and the range text in cells, so they are easy to change:
| A | |
|---|---|
| 1 | https://docs.google.com/spreadsheets/d/abc123/edit |
| 2 | Sheet1!A1:C10 |
With the URL in A1 and the range in A2, the formula becomes:
=IMPORTRANGE(A1, A2)
Common IMPORTRANGE problems
- #REF! error. Access has not been allowed yet. Click Allow access, or ask the owner for access.
- The range is not found. Check the sheet name and the range. The sheet name, an exclamation mark, then the range:
"Sheet1!A1:C10". - Loading with a message about many requests. Your import functions across files are over the usage limits. Reduce how often the imported values change.
- An error about NOW, RAND or RANDBETWEEN. Import functions cannot reference those volatile functions. Paste their results as static values first.
- Slow sheets. Each import downloads the whole range. Summarize data in the source before importing, and limit chained imports.
Try it yourself
Type your formula into the box, starting with =, then press Check. The exercises check the formula text; they do not connect to a real spreadsheet.
Exercise 1: Import a range
Write a formula that imports cells A1:C10 of the sheet named Sheet1 from the spreadsheet at https://docs.google.com/spreadsheets/d/abc123/edit.
Exercise 2: Use cell references
The URL is in cell A1 and the range text is in cell A2. Write the IMPORTRANGE formula that uses those two cells.
What to learn next
- QUERY: filter, group and sort with one formula.
- FILTER: pull out the rows that match.
- VLOOKUP: fetch one value from a table.
- IMPORTXML, IMPORTHTML and IMPORTDATA: bring in data from the web.
Syntax and rules in this guide were checked against Google's IMPORTRANGE help page. See all guides.