IMPORTRANGE

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 use IMPORTRANGE to directly transfer all 1,000,000 rows of data and calculate the sum in your spreadsheet. This method condenses and aggregates information for IMPORTRANGE before it transfers.

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 use IMPORTRANGE on multiple spreadsheets that reference each other: Spreadsheet A’s data uses IMPORTRANGE on data from Spreadsheet B, and Spreadsheet B also uses IMPORTRANGE 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.

1. Wait a few seconds after you complete your IMPORTRANGE function.
2. A #REF! error opens and prompts, “You need to connect these sheets. Allow Access.”
3. 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:

1. Enter your source spreadsheet URL on the browser.
2. Request your access to the spreadsheet.
3. Wait for the owner of the spreadsheet to grant access to you.

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.

We recommend you:

1. Copy the result of those volatile functions.
2. Use Special paste Values only.
3. 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.