The IMPORTRANGE Google Sheets function is the only way, using Sheets’ built-in functionality, to integrate data between spreadsheets. For a person experienced with spreadsheets it’s an easy formula to use, but it can cause confusion for those without. Below I’ve described its applications, how to implement it and its good and bad points.
The objective of IMPORTRANGE is to import values from cells in another spreadsheet (to which you have access) into your own spreadsheet. It works as a simple reference, however, with IMPORTRANGE you can import a whole range of cells instead of just one cell. IMPORTRANGE is a great solution for one-time and sporadic needs to import data from one sheet to another, but it’s not recommended if you need to perform multiple imports in multiple spreadsheets (as will be explained below).
How to use IMPORTRANGE Google Sheets
To use Import Range you need 2 parameters:
1. The key or URL of the source sheet
You can copy the complete URL that has the data you’d like to import, or if you’d like the formula to be visually shorter you can copy just the spreadsheet key, the unique identifier that Google Sheets uses for each spreadsheet.
In the example below I’ve chosen to just copy the spreadsheet key in order to keep the formula visually shorter.
Type the reference range from the source sheet including the tab and cell range that you intend to import.
The selected range above represents exactly “Sheet1!B2
After typing out the entire function just press ‘Enter’. The first time you execute the function you will be prompted to permit the linking of two sheets. Click “Allow access” to do so.
After clicking the button, the function will initiate the importing of data and will display them in your sheet nearly instantly if you are importing a small range of data (it will take a few extra seconds if you are importing a very large range).
Query and IMPORTRANGE
It is possible to query information from another sheet. This can be done by using the IMPORTRANGE as the dataset in your query parameters.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10"), "select * where B contains 'denied'")
This formula will result in an error message, however, because when integrating the query and IMPORTRANGE formula, you need to refer to the columns with numbers rather than letters.
QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10"), "select * where Col2 contains 'denied'")
If you want to know more about the usage of QUERY formula, read the following blog post.
- Fast implementation
- It’s possible to choose the exact data range that you want to import
- It’s possible to include it in any cell in your sheet
- Data is updated in real-time when the spreadsheet is open
- There is no easy way to organize if you have to include many of these functions
- It’s easy to forget where they are in your spreadsheet and from where you are importing the data
- If you import data in a cascaded format, when you look at the data in the last spreadsheet in the chain there will be a long delay for the data to load as you need to wait for IMPORTRANGE in the sequence to execute. This delay can result in incorrect data or errors because there is no way of verifying that all of the data in the chain is updated (without opening every sheet). This is dangerous if you are using this data to make decisions or putting it in reports.
- You cannot import the format of the source cells.
- It’s not possible to choose when to update the data. It’s always real-time.
IMPORTRANGE Google Sheets function is recommended as a quick solution for working with small volumes of data. It’s a great way to bring information from one spreadsheet to another for spot analysis. However if connecting spreadsheets becomes a routine in your work and you depend on these connections for analysis, reports, and decision making, it’s strongly recommended that you use an application designed for data transfer, like Sheetgo. Try Sheetgo by clicking the button below:
If you want to know more about the difference between the IMPORTRANGE formula and Sheetgo, take a look at the following blog post: “Import Range vs Sheetgo: which is better?“. In this post, you will find a comparison between both functionalities and which would suit you best.
For the original IMPORTRANGE Google Sheets documentation, access: https://support.google.com/docs/answer/3093340