Back to Blog

How to Use IMPORTRANGE in Google Sheets to Import Data Across Spreadsheets

To import a data range from another sheet, you need to select the range, and wrap it in curly brackets. For example, here is the formula to get the cell range A1:C21 from the sheet titled “All orders“:

={'All orders'!A1:C21}

This works for importing data across sheets, but within the same spreadsheet only. It won’t pull data from another spreadsheet (Google Sheets document). To do this, you’ll need the IMPORTRANGE function. Let’s check out how you can use it and discover an alternative option to automate data import across spreadsheets.

Understanding IMPORTRANGE Google Sheets

IMPORTRANGE allows you to import a data range from one spreadsheet to another. 

Do not confuse IMPORTRANGE with IMPORTDATA, which imports data from online published CSV or TSV files. Check out our dedicated blog post about it: IMPORTDATA Function: Why It’s Not the Best Option to Import CSV Data to Google Sheets.

IMPORTRANGE syntax

=IMPORTRANGE("spreadsheet","range_string")
  • spreadsheet – insert the URL or ID of the spreadsheet to import data from. 
  • range_string – insert a string that specifies the data range to import. The string should include the name of the sheet and the range of cells. 

IMPORTRANGE formula example

We have a spreadsheet with the data imported from Trello. Let’s pull columns A to E from it. Use the 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")

IMPORTRANGE Allow Access to the spreadsheet

When you import a range from an unshared spreadsheet, IMPORTRANGE will require you to connect the source and the target spreadsheets. 

Click Allow access, and IMPORTRANGE will then work. You’ll have to do this only once, at the first data import.

Note: When you allow access for IMPORTRANGE, you do NOT change the SHARE status of your spreadsheet.

Meanwhile, you can’t revoke the connection if both the source and the target spreadsheets belong to the same user. 

IMPORTRANGE drawbacks

  1. Poor import performance if multiple IMPORTRANGE formulas are used. The more IMPORTRANGE formulas your spreadsheet has, the more time it will require to process requests. At some point, it can even stop working completely. 
  2. Data loads only when the spreadsheet is open. The imported data may not be available until you open the spreadsheet with the IMPORTRANGE formula. This is troublesome if the data in the spreadsheet is synchronized with a third-party app or tool.
  3. Limited functionality. With IMPORTRANGE, you can’t import the entire sheet; you have to specify a cell range; you can’t schedule data import; you can’t import a consolidation of sheets from a spreadsheet, etc.

If you’ve faced any of the issues above, you might need an alternative to IMPORTRANGE. 

Coupler.io – an IMPORTRANGE alternative, which automates data import on a schedule

Coupler.io is a Google Sheets add-on that lets you import raw data from third-party apps and sources, including Airtable, Pipedrive, CSV, and others. Coupler.io connects to each data source via their respective importers. In our case, this is a Google Sheets importer. It’s free of the IMPORTRANGE drawbacks mentioned above, and allows you to automate data import across Google Sheets. Read or watch our YouTube video about how to get it on board:

  • Install Coupler.io from the G Suite Marketplace.
  • Open your target spreadsheet, go to the Add-ons menu and run Coupler.io.
  • Click +Add Importer and select Google Sheets in the dashboard.
  • Next, set up the Google Sheets importer.
  • Fill out the following fields:
    • Add the name of your data source.
    • Type in the name of your sheet. 
    • Insert a spreadsheet URL to export data from (spreadsheet ID doesn’t work here).
    • Choose the frequency of Automatic data refresh if needed (optional).
    • Specify the cell range to import. If you skip this step, the entire sheet specified in the spreadsheet URL field will be imported.
    • Specify the sheets to join (optional).
  • Next, click on Connect to connect Coupler.io to your Google account.
  • Click Add Importer to save the setup and run your Google Sheets importer afterwards to import data. 

Note: For information about the Settings section of the Google Sheets importer, read our knowledge base.

Google Sheets importer (Coupler.io)  vs. IMPORTRANGE

Google Sheets importerIMPORTRANGE
Google Sheets add-onEntityGoogle Sheets function
Google Spreadsheets limits or Google Sheets API limitsLimitationMax 50 connections per spreadsheet
– Specified data range on a single sheet
– Entire sheet
– Consolidation of sheets of a spreadsheet
Data to import– Specified data range on a single sheet
– Consolidation of sheets of a spreadsheet
Automatic and manual options are availableData refreshAutomatic within a few seconds
AvailableData update on scheduleNot supported
No limitationsData availabilityWhen you open the spreadsheet
AvailableAppend imported dataNot supported

The IMPORTRANGE how-to guide

This will cover how to use IMPORTRANGE in practice, and various tasks that you can carry out with this Google Sheets function. 

How to import data from multiple sheets with IMPORTRANGE

We need to import the same data range (A:E) from three sheets (Airtable orders 2017, Airtable orders 2018, and Airtable orders 2019) of one spreadsheet.

You can import data with IMPORTRANGE with the following options:

Import and merge data vertically

Use an array (curly brackets) and IMPORTRANGE formulas separated with semicolons to import and append data vertically. Use the same spreadsheet ID for all IMPORTRANGE formulas within this array: 

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A:E");
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A:E");
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A:E")}

Click Allow access to connect the sheets.

The data will be imported and merged vertically. In our formula, we used the same data range for all sheets including the first rows from each sheet.  

If you don’t need this, amend the formula as follows:

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A:E");
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A2:E");
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A2:E")}

Import and merge data horizontally

You can import and append data horizontally with IMPORTRANGE only if the data range to fetch in each sheet is the same. In order to do this correctly, use an array (curly brackets) and several IMPORTRANGE formulas separated with 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: Function ARRAY_ROW parameter 3 has mismatched row size. Expected: 20. Actual: 21.

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A2:E"),
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A2:E"),
IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A:E")}

A better way to import data from multiple sheets

Let’s do the same (import data from multiple sheets) but using the Google Sheets importer of Coupler.io. You need to specify the URL of the Google Sheets document, the cell range, and the sheets to join. Here is how it looks:

Google Sheets importer setup

Click Connect to connect Coupler.io to your Google account, and save the importer by clicking Add Importer. Now you can run it and welcome the data merged vertically from the specified sheets.

The Google Sheets importer adds a Sheet Name column, so you could differentiate where the data came from. It also automatically skips the column headers from the appended sheets if they are identical to the column headers of the first sheet. If the specified sheets have different column headers, the importer will merge vertically only the identical ones, and the rest will be appended horizontally to the right. Here is how it will look:

How to import data from multiple spreadsheets with IMPORTRANGE

We need to import the same data range (A:E) from three separate spreadsheets.

Import and merge data vertically

Use an array (curly brackets) and IMPORTRANGE formulas separated with semicolons to import and append data vertically. Use the IDs of the spreadsheets you need to import and merge: 

={IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A:E");
IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A2:E");
IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A2:E")}

Import and merge data horizontally

You can import and append data horizontally with IMPORTRANGE only if the data range to fetch in each sheet is the same. To do this in the correct way, use an array (curly brackets) and several IMPORTRANGE formulas separated with commas: 

={IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A:E"),
IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A:E"),
IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A:E")}

How to IMPORTRANGE with conditions?

If you combine IMPORTRANGE with the QUERY function, you can import data across spreadsheets according to different conditions such as:

  • Import a specific range of data
  • Cut off unnecessary rows of the imported data
  • Change names of the imported columns
  • Format values in the imported columns
  • Import the filtered data
  • Sort the imported data
  • Perform arithmetic, aggregation, and scalar operations on the imported data

For more on Google Sheets QUERY Function, read our dedicated blog post.
Let’s check out how it works on a simple example: We’ll import a data range A:E from a spreadsheet, and filter the imported data on the values in column E that start with the letter “S“.

Here is how the formula will look:

=QUERY(IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A:E"),
  "select * where Col5 starts with 'S'")

How to hide formulas with IMPORTRANGE?

Let’s say you have a spreadsheet that some stakeholders can view. This spreadsheet contains specific formulas you’re not willing to disclose. How can you let the stakeholders see the values, and hide the formulas at the same time? 

If this is what you need, IMPORTRANGE can offer you a workaround to hide formulas. The idea is to create two Google Sheets documents:

  • In the first one, you will store formulas.
  • In the second one, you will import the values from the first spreadsheet using IMPORTRANGE. 

For example, the original spreadsheet with our formulas is called “Formulas to hide”. It contains three formulas: COUNTIF, FILTER, and SUMIF.

Clone this Google Sheets doc (either make a copy, or simply copy and paste data from it) and replace the formulas with the IMPORTRANGE formulas that will reference the original document.

For COUNTIF:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!A2:A")

For FILTER:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!C2:C")

For SUMIF:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!E2:E")

Then connect these sheets, of course. Your spreadsheet will look the same as the original doc. However, instead of the original formulas, you and those you share the spreadsheet with will only see the IMPORTRANGE formulas:

Functions you can use with IMPORTRANGE

IMPORTRANGE with QUERY

QUERY+IMPORTRANGE allows you to import data based on certain conditions. 

QUERY IMPORTRANGE Syntax:

=QUERY(IMPORTRANGE("spreadsheet", "data_range"), "query_string")
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • query_string – a string made using clauses of the Google API Query Language.

This topic has been mentioned above and described in full in our dedicated blog post: QUERY + IMPORTRANGE in Google Sheets

IMPORTRANGE with VLOOKUP 

VLOOKUP + IMPORTRANGE allows you to import data that matches the specified search criteria. 

VLOOKUP IMPORTRANGE Syntax:

=VLOOKUP(search_key,IMPORTRANGE("spreadsheet", "data_range"),index,[sorted_boolean])
  • search_key – the value (or a cell range) to search for. 
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • index – the index (number) of the imported column in the range to return the value.
  • [sorted_boolean] – a TRUE/FALSE boolean indicating whether the specified column is sorted. TRUE (sorted) is set by default, but in most cases FALSE is recommended. 

For example, we have a spreadsheet with search criteria of specific customer names. The other spreadsheet contains a list of orders per customer. The combination of VLOOKUP and IMPORTRANGE will let us match data across these spreadsheets. Here is the formula:

=vlookup(
   A2:A,
importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"),
   3, false)

If you modify the formula above by adding the ARRAYFORMULA function and IF + LEN, you’ll get the following:

=arrayformula(
  if(len(A2:A)=0,,
   vlookup(
    A2:A,
  importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"),
    3, false
   )
)

IMPORTRANGE with FILTER 

FILTER + IMPORTRANGE allows you to filter the imported data by a certain value. 

FILTER IMPORTRANGE Syntax:

=FILTER(IMPORTRANGE("spreadsheet", "data_range"),[condition_1, condition_2,...])
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • condition – a range that contains the filter criteria.

For example, we need to import a data range from a spreadsheet and filter it by a specific product name: “Denver sandwich“. Here is how it may look:

=filter(
importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"),
importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!E2:E")="Denver sandwich"
)
  • importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I") – data range to import
  • importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!E2:E") – a column from the imported data range where the filter criteria is
  • "Denver sandwich" – filter criteria  

IMPORTRANGE with SUM

SUM + IMPORTRANGE allows you to sum the imported data range. 

SUM IMPORTRANGE Syntax:

=SUM(IMPORTRANGE("spreadsheet", "data_range"))
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 

For example, we need to import a column from a spreadsheet and return the sum of its values. Here is the formula:

=sum(importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!I2:I"))

IMPORTRANGE with SUMIF

You can’t use the IMPORTRANGE function within a SUMIF formula. If you need to sum a range from a spreadsheet according to a set criteria, you can do one of the following:

  • Use QUERY+IMPORTRANGE
  • Import the data with IMPORTRANGE, then apply SUMIF to the already imported data. 

These are the most popular inquiries of Google Sheets functions to combine with IMPORTRANGE. If you know other in-demand cases, mention them in the comments section below, so we may include them in the blog post. 

To wrap up

So, to import data from Google Sheets documents, you have two options: 

  1. Use IMPORTRANGE: It works well if you need to import smaller amounts of data. However, this option is limited in functionality, requires a lot of manual work, and can slow the productivity of your spreadsheet if many IMPORTRANGE formulas are applied.
  2. Use a Google Sheets add-on, such as Coupler.io: It is a stable solution that can handle large amounts of data and multiple calculations in a single document. Moreover, you can automate data import across spreadsheets on schedule. 

Choose the best solution for your project, and good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free