Welcome to the wonderful world of Crawlan.com! Today, we’re going to dive deep into the powerful IMPORTRANGE function in Google Sheets. This function allows you to import data from one spreadsheet to another, making it an essential tool for data management and organization. We’ll also introduce you to an alternative to IMPORTRANGE that will take your data importing to the next level.
Understanding IMPORTRANGE in Google Sheets
IMPORTRANGE is a pure Google Sheets function that allows you to import a range of data from one spreadsheet to another. It’s important to note that IMPORTRANGE is exclusive to Google Sheets and does not have an equivalent function in Excel.
Don’t confuse IMPORTRANGE with IMPORTDATA, which imports data from CSV or TSV files published online. We have a dedicated article on the IMPORTDATA function in Google Sheets if you’re interested.
To use IMPORTRANGE, the syntax is as follows:
=IMPORTRANGE("spreadsheet_url", "data_range")
spreadsheet_url– Insert the URL or ID of the spreadsheet you want to import data from.data_range– Insert a string that specifies the range of data to import. The string should include the sheet name and cell range.
Example of IMPORTRANGE formula in Google Sheets
Let’s say we have a spreadsheet with data imported from Trello, and we want to extract columns A to E from that sheet. We can use the following formula with the spreadsheet URL:
=importrange("https://docs.google.com/spreadsheets/d/1jUPrXMmsZwJNnWcxm-pRVz1xBY-TEoVGsq5c2jHunvI/edit#gid=590318270","Trello Board!A:E")
Or with the spreadsheet ID:
=importrange("1jUPrXMmsZwJNnWcxm-pRVz1xBY-TEoVGsq5c2jHunvI","Trello Board!A:E")
When you import a range from an unshared spreadsheet, IMPORTRANGE will prompt you to connect the source and target sheets. Simply click “Allow access,” and IMPORTRANGE will start working. You only need to do this once, during the first data import.
Please note that authorizing access to IMPORTRANGE does not change the sharing status of your spreadsheet. As of now, you cannot revoke the connection if the source and target sheets belong to the same user.
Limitations of IMPORTRANGE in Google Sheets
While IMPORTRANGE is a useful function in Google Sheets, it does have its limitations:
-
Slow import performance with multiple IMPORTRANGE formulas: The more IMPORTRANGE formulas you have in your spreadsheet, the longer the processing time for the requests. At a certain point, it may even stop working altogether.
-
Data loads only when the spreadsheet is open: Imported data may not be available until you open the spreadsheet containing the IMPORTRANGE formula. This can be problematic if the data in the spreadsheet is synced with an external application or tool.
-
Limited functionality: With IMPORTRANGE, you can’t import the entire sheet; you have to specify a cell range. You can’t schedule data imports or import a consolidation of sheets from a spreadsheet, and so on.
If you’ve encountered any of these limitations, you may need an alternative to IMPORTRANGE.
The alternative to IMPORTRANGE in Google Sheets for automated data importing
The Google Sheets integration of Coupler.io is a powerful alternative to IMPORTRANGE that overcomes the aforementioned limitations. It allows you to reference another spreadsheet and import data between Google Sheets. Additionally, it provides you with the ability to preview and transform the imported data before loading it into the target spreadsheet. This includes column management, data filtering, and sorting, all done easily within the Coupler.io interface without any formulas.
Coupler.io is a data automation and analysis platform that enables you to automate data imports from various applications, including Airtable, Pipedrive, CSV, and more, into Google Sheets, Excel, or BigQuery. It is available as a web app and a Google Sheets add-on, each with a simple setup process.
Here’s how it works, or you can watch our YouTube video on how to use it:
- Sign up for Coupler.io and launch your data integration by creating your first importer. Click the “Add a new importer” button and select Google Sheets as the source and destination application. Then, complete the following steps:
Source
- Connect your Google account or choose one from the dropdown if you’ve already connected one.
- Select a Google Sheets file on your Google Drive to transfer data from. Choose one or multiple sheets to export data from. The latter option allows you to merge multiple sheets into a single master view.
- Optionally specify a range within the spreadsheet from which you want to export data, for example, A1:Z9.
Preview and Transformation
- Check how the imported data will look in your target spreadsheet. During this step, you can hide unnecessary columns, add calculated columns, as well as filter and sort records.
Destination
- Log in to your Google account.
- Choose a Google Sheets file on your Google Drive to transfer data to. Enter a name to create a new sheet or choose an existing sheet.
- If you want to change the first cell where your data range will be imported, specify your desired value in the Cell Address field. The default is cell A1.
- Choose the import mode for your data: you can either “Replace” previous information or “Add” new rows below the last imported entries.
Schedule
You can immediately import your data range by clicking “Save and Run.” At the same time, you can also automate imports on an hourly, daily, or custom frequency. Enable “Auto-update data” and set up a schedule:
- Select the desired “Interval” (from every 15 minutes to every month).
- Select the “Days of the Week.”
- Choose your “Time Preferences.”
- Schedule based on the “Timezone.”
To learn more about setting up the Google Sheets integration of Coupler.io, please consult our knowledge base.
You can also use Coupler.io as a Google Sheets add-on for quicker access to the tool in your Google Sheets. Simply install it from the Google Workspace Marketplace and set it up as described above.
A Guide to Using IMPORTRANGE in Google Sheets
This guide will show you how to use IMPORTRANGE in practice and the various tasks you can accomplish with this Google Sheets function.
How to Import Data from Multiple Sheets with IMPORTRANGE in Google Sheets
Let’s say we need to import the same data range (A:E) from three sheets (Airtable orders 2017, Airtable orders 2018, and Airtable orders 2019) into a single spreadsheet.
You can import and merge data vertically by utilizing an array (curly braces) and separate IMPORTRANGE formulas with semicolons. For example:
={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E21"); IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:E21"); IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:E21")}Click “Allow access” to connect the sheets.
The data will be imported and merged vertically. However, to import and merge an unlimited data range (A1:E), you’ll need to nest IMPORTRANGE with FILTER and LEN like this:
={FILTER(IMPORTRANGE("{spreadsheet_ID}", "{sheet_name#1}!{data_range#1}"),LEN(IMPORTRANGE("{spreadsheet_ID}", "{sheet_name#1}!{first_column#1}")>0)); FILTER(IMPORTRANGE("{spreadsheet_ID}", "{sheet_name#2}!{data_range#2}"),LEN(IMPORTRANGE("{spreadsheet_ID}", "{sheet_name#2}!{first_column#2}")>0)); ...}{spreadsheet_ID}– the ID or URL of the Google Sheets document from which you’re importing data{sheet_name#1}– the name of the first sheet{sheet_name#2}– the name of the second sheet{data_range#1}– the data range of the first sheet, including the header row{data_range#2}– the data range of the second sheet, excluding the header row{first_column#1}– the first column of the data range of the first sheet{first_column#2}– the first column (excluding the header row) of the data range of the second sheet
In our case, the formula would look like this:
={FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:A")))}If you don’t need it, replace A1:E with A2:E for the second and third sheets to be merged:
={FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A2:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A2:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:A")))}Importing and Merging Data Horizontally
You can import and append data horizontally with IMPORTRANGE only if the data range to be extracted in each sheet is the same. For this, use an array and multiple IMPORTRANGE formulas separated by commas:
={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A:E")}
If you try to import different data ranges, the IMPORTRANGE formula will return an error: “ARRAY_ROW parameter 3 has mismatched row size. Expected: 20. Actual: 21.”
To learn more about this subject, check out our article on why IMPORTRANGE isn’t working: errors and solutions.
Importing Data from Multiple Spreadsheets
With the Google Sheets integration of Coupler.io, you can import data from multiple sheets more easily. You need to specify the following parameters:
- Sheet names to be merged
- Data range
Once you’ve executed the integration, it will import and merge the data vertically. The Coupler.io Google Sheets integration adds a “Sheet Name” column so you can differentiate where the data comes from. It also automatically ignores column headers from added sheets if they match the column headers of the first sheet.
If the specified sheets have different column headers, the importer will vertically merge only those with identical headers, and the rest will be added horizontally to the right. Here’s what it would look like:
Using IMPORTRANGE with Conditions in Google Sheets
By combining IMPORTRANGE with the QUERY function, you can import data between spreadsheets based on various conditions. This allows you to filter, sort, and transform the imported data to fit your specific needs.
To use QUERY with IMPORTRANGE, the syntax is as follows:
=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_condition")
spreadsheet_url– The URL or ID of the spreadsheet you’re importing data from.data_range– The range of cells you want to query.query_condition– A string created using the clauses of the Google Sheets query language.
You can learn more about this function in our dedicated article: QUERY + IMPORTRANGE in Google Sheets.
Using IMPORTRANGE with VLOOKUP in Google Sheets
VLOOKUP + IMPORTRANGE allows you to import data that matches specified lookup criteria. To learn more about the VLOOKUP function, refer to our article: “VLOOKUP Explained: How to Search Data Vertically in Spreadsheets.”
The syntax for VLOOKUP with IMPORTRANGE is as follows:
=VLOOKUP(lookup_key,IMPORTRANGE("spreadsheet_url", "data_range"),index,[sorted_boolean])
lookup_key– The value (or range of cells) to search for.spreadsheet_url– The URL or ID of the spreadsheet you’re importing data from.data_range– The range of cells to query.index– The index number of the imported column in the range to return the value from.[sorted_boolean]– A TRUE/FALSE boolean indicating whether the specified column is sorted. The default is TRUE (sorted), but in most cases, FALSE is recommended.
For example, suppose we have a spreadsheet with specific customer names as lookup criteria. Another spreadsheet contains a list of orders per customer. Combining VLOOKUP with IMPORTRANGE will allow us to match the data between these spreadsheets. Here’s the formula:
=vlookup( A2:A, importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), 3, false)
If you modify the above formula by adding ARRAYFORMULA and IF + LEN, it will look like this:
=arrayformula( if(len(A2:A)=0,, vlookup( A2:A, importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), 3, false ) ))
To learn more about using ARRAYFORMULA in Google Sheets, check out our guide.
Using IMPORTRANGE with FILTER in Google Sheets
FILTER + IMPORTRANGE allows you to filter the imported data based on specific criteria.
The syntax for FILTER with IMPORTRANGE is as follows:
=FILTER(IMPORTRANGE("spreadsheet_url", "data_range"),"condition")
spreadsheet_url– The URL or ID of the spreadsheet you’re importing data from.data_range– The range of cells to query.condition– A range that contains the filtering criteria.
For example, we need to import a data range from a spreadsheet and filter the imported data based on a specific product name, let’s say “Denver sandwich.” Here’s how it can look:
=filter( importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!E2:E")="Denver sandwich" )
To learn more about the FILTER function in Google Sheets, check out our dedicated article.
Using IMPORTRANGE with SUM in Google Sheets
SUM + IMPORTRANGE allows you to sum the imported data range.
The syntax for SUM with IMPORTRANGE is as follows:
=SUM(IMPORTRANGE("spreadsheet_url", "data_range"))
spreadsheet_url– The URL or ID of the spreadsheet you’re importing data from.data_range– The range of cells to query.
For example, we need to import a column from a spreadsheet and return the sum of its values. Here’s the formula:
=sum(importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!I2:I"))
To learn more about the SUM function in Google Sheets, refer to our comprehensive guide.
Using IMPORTRANGE with SUMIF in Google Sheets
You cannot use the IMPORTRANGE function directly within a SUMIF formula. If you need to sum a range based on specific criteria, you can do one of the following:
- Use QUERY + IMPORTRANGE
- Import the data with IMPORTRANGE and then apply SUMIF to the imported data.
These are the most commonly requested functions to combine with IMPORTRANGE in Google Sheets. If you know of other requested use cases, please mention them in the comments section below, and we’ll be sure to include them in our blog post.
IMPORTRANGE or Coupler.io Google Sheets Integration?
Choosing whether to import data from Google Sheets using IMPORTRANGE or Coupler.io depends on your specific needs:
- IMPORTRANGE works well if you need to import small amounts of data. However, this Google Sheets function is limited in functionality, requires manual work, and may hinder the productivity of your spreadsheet if multiple IMPORTRANGE formulas are applied.
- Coupler.io is a reliable solution that can handle large amounts of data and multiple calculations within a single spreadsheet. It offers additional features such as scheduling data updates according to a calendar.
Take a look at the comparison table below to make your final decision:
| Entity | Limitation | Data to Import | Data Refresh | Scheduled Data Update | Data Availability | Addition of Imported Data |
|---|---|---|---|---|---|---|
| IMPORTRANGE | Yes | Yes | No | No | Yes | No |
| Coupler.io | No | Yes | Yes | Yes | Yes | Yes |
Choose the best solution for your project and good luck with your data!
That’s all for today! I hope this article has been helpful in understanding IMPORTRANGE in Google Sheets and discovering the alternative offered by the Google Sheets integration of Coupler.io.
If you have any questions or comments, feel free to reach out to us or visit our website at Crawlan.com.
