Imports a range of cells from a specified spreadsheet.
Sample Usage
IMPORTRANGE("
https://docs.google.com/spreadsheets/d/abcd123abcd123
", "sheet1!A1:C10")
IMPORTRANGE(A2,"B2")
Use table references: IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "DeptSales[Sales Amount]")
Learn more about how to use tables in Google Sheets.
Use named range: IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sales_total")
Learn more about how to name a range of cells.
Syntax
IMPORTRANGE(spreadsheet_url, range_string)
-
spreadsheet_url
- The URL of the spreadsheet from where data will be imported.- The value for
spreadsheet_url
must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
- The value for
-
range_string
- A string, of the format"[sheet_name!]range"
(e.g."Sheet1!A2:B6"
or"A2:B6"
) specifying the range to import.-
The
sheet_name
component ofrange_string
is optional; by defaultIMPORTRANGE
will import from the given range of the first sheet. -
The value for
range_string
must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
-
Technical details & best practices
Any update to the source document IMPORTRANGE
will cause all open receiving documents to refresh and show a green loading bar. IMPORTRANGE
also waits for calculations to complete on the source document before it returns results to the receiving doc, even if there is no calculation to be done in the source range.
Best Practices
- Limit the number of receiving sheets because each receiving sheet must read from the source sheet
- Restructure and condense your result before you use
IMPORTRANGE
, especially if you import from a frequently updated Sheet.- For example, your goal is to calculate the sum of 1,000,000 rows in from another spreadsheet. It’s faster to calculate the sum on that spreadsheet and then use
IMPORTRANGE
to fetch that single-number sum than to useIMPORTRANGE
to directly transfer all 1,000,000 rows of data and calculate the sum in your spreadsheet. This method condenses and aggregates information forIMPORTRANGE
before it transfers.
- For example, your goal is to calculate the sum of 1,000,000 rows in from another spreadsheet. It’s faster to calculate the sum on that spreadsheet and then use
IMPORTRANGE
updates can propagate to other sheets when you chain sheets together. If sheet B has an IMPORTRANGE(sheet A)
and sheet C has an IMPORTRANGE(sheet B)
, it creates a chain. Any update to sheet A causes sheet B and sheet C to reload.
Best Practices
- Limit chains of
IMPORTRANGE
across multiple sheets. - Avoid cycles of
IMPORTRANGE
. For example, you create a cycle if you useIMPORTRANGE
on multiple spreadsheets that reference each other: Spreadsheet A’s data usesIMPORTRANGE
on data from Spreadsheet B, and Spreadsheet B also usesIMPORTRANGE
to fetch data from Spreadsheet A. This causes a loop between them where each continuously tries to reference the other and never results in an actual data output. - There's a delay between the change in the source sheet and the affected sheet. This means that multiple layers of
IMPORTRANGE
across several chained documents can result in long delays between updates of the source sheet and results on the affected sheet.
IMPORTRANGE
reloads when a document is first opened or opened within the past 5 minutes. As with the chain of updates, this requires IMPORTRANGE
to "wake up" all documents it imports from.
Best Practices
- Understand that documents becoming active may take a while to update. Consider limiting chains of
IMPORTRANGE
.
Permission & access
IMPORTRANGE
. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission.IMPORTRANGE
to transfer data from a spreadsheet you own, this message appears:- Wait a few seconds after you complete your
IMPORTRANGE
function. - A #REF! error opens and prompts, “You need to connect these sheets. Allow Access.”
- To grant the permission to the source spreadsheet, click Allow Access.
If you try to use IMPORTRANGE
to transfer data from a spreadsheet that you don't own, in a few seconds this message appears:
- Enter your source spreadsheet URL on the browser.
- Request your access to the spreadsheet.
- Wait for the owner of the spreadsheet to grant access to you.
IMPORTRANGE
to pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source. Note that the access granted to the destination sheet counts against the 600-user share limit for the source sheet.Performance
IMPORTRANGE
is an external data function, just like IMPORTXML
and GOOGLEFINANCE
. That means it requires an internet connection to work. Sheets must download the entire range to your computer and will be affected by slow network, and is capped at 10MB of received data per request. If you experience slow performance with IMPORTRANGE
, consider limiting the size of the imported ranges. Alternatively, put summarizing calculations in the source doc, so that less data needs to be transferred to your local Sheet and more of the calculation can be done remotely.
Tip: You can use other tools. AppsScript can read from other documents and can be triggered on edit and on a predefined schedule. Connected Sheets has scheduled refresh and is also better built for larger data set loads and imports.
Usage Limits
IMPORTRANGE
functions create too much traffic, you might view the in-cell message “Loading…” with the detailed error message “Error Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML
, IMPORTDATA
, IMPORTFEED
, or IMPORTXML
functions across spreadsheets you’ve created.”=IMPORTRANGE(arg)
frequently changes, many external calls may be issued, which may cause the throttle.Data Freshness
IMPORTRANGE
automatically checks for updates every hour while the document is open, even if the formula and spreadsheet don’t change. If you delete, read, or overwrite the cells with the same formula, the reload of the functions trigger. If you open and reload the document, it doesn’t trigger a reload on IMPORTRANGE
.Volatility
IMPORTRANGE
, you may receive an in-cell “#ERROR!” with a detailed error message “Error This function is not allowed to reference a cell with NOW
, RAND
, or RANDBETWEEN
." Import functions can't directly or indirectly reference a volatile function like NOW
, RAND
, or RANDBETWEEN
. This prevents our users’ spreadsheet from overload, as these volatile functions update frequently.TODAY
function, which is volatile but doesn’t update more than once per day.We recommend you:
- Copy the result of those volatile functions.
- Use Special paste Values only.
- Reference those static values.
If you do this, all values become static. For example, if you copy and special paste NOW
results as values, then the values you paste no longer change.
If you still have any questions, you can visit the Google Docs Editors Help Community to seek help.
See Also
IMPORTXML
: Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
IMPORTHTML
: Imports data from a table or list within an HTML page.
IMPORTFEED
: Imports a RSS or ATOM feed.
IMPORTDATA
: Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format.