Back to Blog

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

To import a data range from another spreadsheet (Google Sheets document), there is a Google Sheets function – IMPORTRANGE. Let’s check out how it works and discover the IMPORTRANGE alternative, which lets you automate data import across spreadsheets.

Is there a worth watching video tutorial on IMPORTRANGE?

Some users may prefer watching than reading. In this case, check out this step-by-step IMPORTRANGE function tutorial for Google Sheets by Railsware Product Academy.

Understanding IMPORTRANGE Google Sheets

IMPORTRANGE allows you to import a data range from one spreadsheet to another. It’s a pure Google Sheets function – i.e. there is no Excel IMPORTRANGE.

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. 

IMPORTRANGE alternative to automate data import on a schedule

Coupler.io is a tool that lets you import raw data from third-party apps and sources, including Airtable, Pipedrive, CSV, and others. Coupler.io connects each data source to any of the several destinations: Google Sheets, Excel, or BigQuery.

Coupler.io is available as a web app and a Google Sheet add-on. Each of them has a simple flow to work.

The integration you set up between a source and a destination is called an importer. In our case, we need to set up a Google Sheets importer. The Google Sheets importer is free of the IMPORTRANGE drawbacks mentioned above, and allows you to automate data import across Google Sheets.

Google Sheets importer

Read or watch our YouTube video about how to get it on board:

Sign up to Coupler.io and start your data integration journey by creating your first importer. Click the “Add new importer” button and give a title to your importer. Next, you need to set up the Google Sheets importer by completing the following steps:

Source 

  • Select Google Sheets as a source app from the list.
  • Connect your Google account or choose one from the drop-down list if you’ve already connected some.
  • Select a Google Sheets file on your Google Drive to transfer data from. 
  • Select one or several sheets to export data.
  • Click “Jump to Destination Settings” if you’re okay that the entire data set will be imported from your sheet (s). However, you can choose a range in the spreadsheet you want to export data from, i.e. A1:Z9. To do this, click “Continue” and specify the range in the optional field.

Destination

  • Pick Google Sheets as the destination application you want to save data to.
  • Connect to your Google account. 
  • Choose a Google Sheets file on your Google Drive to transfer data to. 
  • Type a name to create a new sheet or pick an existing one. 
  • If you want to change the first cell where to import your data range, specify your value in the Cell address field. The A1 cell is set by default. 
  • Choose the import mode for your data: you can replace your previous information or append new rows under the last imported entries

Schedule  

You can import your data range immediately by clicking “Save and Run“. At the same time, you can also automate imports every hour, every day, or on another frequency. Toggle on the Automatic data refresh and set up a schedule:

  • Select Interval (from 15 minutes to every month)
  • Select Days of the week
  • Select Time preferences
  • Schedule Time zone

For more about the Google Sheets importer setup flow, refer to Coupler.io knowledge base.

You can also use Coupler.io as a Google Sheets add-on to have faster access to the tool in your Google Sheets. For this install it from the Google Workspace Marketplace and set it up as we described above.

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

If you’re importing a limited data range like A1:E21, you can use an array (curly brackets) and IMPORTRANGE formulas separated 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 as follows:  

={FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-first-column})>0);
FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-first-column})>0);...}
  • {spreadsheet-ID} – the ID or URL of the Google Sheets document, you’re importing data from
  • {sheet#1-name} – the name of the first sheet
  • {sheet#1-name} – the name of the second sheet 
  • {sheet#2-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 
  • {sheet#1-first-column} – the first column of the data range from the first sheet.
  • {sheet#2-first-column} – the first column (without the title row) of the data range from the second sheet.

In our case, the formula will 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")))}

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, replace A1:E with A2:E for the second and the third sheets to merge:

={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")))}

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")}

For more on this, read our blog post Why IMPORTRANGE Is Not Working: Errors and Fixes.

Import data from multiple sheets with the IMPORTRANGE alternative – Google Sheets importer

With the Google Sheets importer by Coupler.io, you can import data from multiple sheets more easily. You need to specify the following parameters:

  • the names of the sheets to merge 
  • the data range 

Once you run the importer, it will import and merge data vertically. 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 nested with FILTER and LEN:

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

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. Read more in our blog post VLOOKUP Explained: How to Search Data Vertically in Spreadsheets.

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
   )
)

Read our Guide of Using ARRAYFORMULA in Google Sheets For All.

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  

Read the FILTER Function Tutorial to learn more about filtering in Google Sheets.

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. 

IMPORTRANGE or Coupler.io?

You should select the option to import data from Google Sheets documents based on your requirements:

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

Check out the following comparison table to make the final decision:

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

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

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free