Back to Blog

QUERY + IMPORTRANGE in Google Sheets: Real-Life Formula Examples 2022

Are you an experienced Google Sheets user? Then you probably know how to link data between multiple spreadsheets and use the IMPORTRANGE function for that. Meanwhile, you could become an expert and do better. Professionals combine IMPORTRANGE with QUERY and manipulate the imported dataset at the same time. If you want to join the experts and improve your Google Sheets skills – welcome to this blog post.

IMPORTRANGE function explained 

The IMPORTRANGE function allows you to import a data range from a specified spreadsheet.

IMPORTRANGE syntax

=IMPORTRANGE("spreadsheet_url","range_string")
  • spreadsheet_url – insert the URL of the spreadsheet to import data from. For example, 
https://docs.google.com/spreadsheets/d/1h3pPtbMAsPM_jS2wMLG_GflqyAd

Alternatively, you can use the spreadsheet ID instead of the entire URL

1h3pPtbMAsPM_jS2wMLG_GflqyAd
  • range_string – insert a string that specifies the data range for the import. For example, Sheet1!A1:C13. The string consists of the sheet_name! and the range
    • range – specify the range of cells to import.
    • sheet_name! – specify this parameter if you are not importing data from the first sheet of the document.

IMPORTRANGE formula example

We have a database in the spreadsheet called Orders from Airtable. Let’s import all the values in the columns E to I. Here is the formula for this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!E1:I21")

You can check out the formula example in this tab.

Related Posts

QUERY function explained 

The QUERY function lets you manipulate data while importing it from another sheet. You can select, filter, sort, and do other manipulations.  

QUERY syntax

=QUERY(data_range,"query_string")
  • data_range – insert a range of cells to query. data_range may include columns with boolean, numeric, or string values. If a column contains different data types, QUERY will pick the majority data type as the column data type. 
  • query_string – insert a string made using clauses of the Google API Query Language. Alternatively, you can refer to a cell that contains the query_string (in this case, avoid double quotation marks both in the QUERY formula and the query_string). 

Optionally, you can enhance the QUERY formula with a part that defines the number of headers in your data range. In this case, the QUERY syntax will be the following:

=QUERY(data_range,"query_string",headers_number)

One header row is used by default.

QUERY formula example

Let’s use the data range that we imported using IMPORTRANGE in the previous example and do the following manipulations:

  • Select columns A, D and E.
  • Filter out the products with a total price of more than $50.
  • Order the outcome by quantity in ascending order.
  • Change the A column header from “Product” to “Sandwich name”.

Here is the QUERY formula to do that:

=QUERY('IMPORTRANGE example'!A:E,"select A,D,E where E>50 order by D label A 'Sandwich name'")

You can check out the formula example in this tab.

Though QUERY is a powerful function, it has a drawback: it only works within a spreadsheet. So, you can grab data from one sheet to another, but you can’t query another spreadsheet. The combination of QUERY+IMPORTRANGE is meant to handle this issue. 

We blogged about this function in “Google Sheets Query

Using QUERY and IMPORTRANGE together

When you apply QUERY and IMPORTRANGE together, you can query a data range in another spreadsheet (or multiple spreadsheets).

If you need a quick insight into using QUERY+IMPORTRANGE, check out this part of the IMPORTRANGE video tutorial by Railsware Product Academy. The needed timestamp is already setup:)

QUERY+IMPORTRANGE syntax

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

Optionally, you can enhance the formula with a part that defines the number of headers in your data range. In this case, the syntax will be the following:

=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string", [headers])

One header row is used by default.

QUERY+IMPORTRANGE formula example

Let’s repeat the examples above but using only one formula. So, we need to:

  • import a data set from the spreadsheet called Orders from Airtable.
  • do a few manipulations with it:
    • Select columns A, D and E.
    • Filter out the products with a total price of more than $50.
    • Order the outcome by quantity in ascending order
    • Change the A column header from “Product” to “Sandwich name”.

Here is the formula:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!E1:I21"),
  "select Col1,Col4,Col5 where Col5>50 order by Col4 label Col1 'Sandwich name'")

All orders!E1:I21 is our target data_range. During the import, the column reference will change as follows:

E to A, or simply Column #1 – Col1

F to B, or simply Column #2 – Col2

G to C, or simply Column #3 – Col3

H to D, or simply Column #4 – Col4

I to E, or simply Column #5 – Col5

That’s why we recommend you apply Col reference in your QUERY formulas to avoid errors. 

You can check out the formula example in this tab.

QUERY+IMPORTRANGE alternative

There’s also a simpler option that allows you to avoid cumbersome formulas and save your time. You can easily transfer data from spreadsheets using Google Sheets importer in Coupler.io

One of its important advantages is the automatic data refresh feature. You can set up a schedule, and your information will be regularly updated based on new data from one or several spreadsheets. The syncs can be planned on certain days or just run as often as every 15 minutes. This is very convenient when you work with live reports or build complex dashboards.

In contrast, IMPORTRANGE doesn’t allow you to synchronize data on a schedule. And it won’t even load any updates until you manually open the spreadsheet.

Here’s how to use Coupler.io Google Sheets importer:

  • Sign up for Coupler.io with your Google or Microsoft account.
    • Hit the Add new importer button and type in a title for your first importer.
  • Then, specify the source – where your data should be taken from. In our example, it’s Google Sheets. 
    • After this, connect to your Google account. 
    • Choose a spreadsheet you need to export your data from. Then specify a particular sheet or several sheets in this file for importing. Keep in mind that merging data from several sheets works best when the sheets have the same structure (for example, invoices).
    • Depending on your needs, Coupler.io can export all data from your source, or you can select a specific range for data transferring. For example, A1:Z9.
  • Now, set the destination – where your data should be imported.
    • Select Google Sheets from the dropdown menu, then connect your destination Google account.
    • Specify the Google Sheets document and the sheet where the data needs to be transferred.  
    • Optionally, you can change the cell address and import mode. There are two modes available here, append and replace. The latter option deletes previous information when importing its updated version. The append mode just adds new data below the initial information in the spreadsheet.
  • Turn on the Automatic data refresh and set your schedule if you want to keep your spreadsheet synchronized with the source.
  • Click Save & Run to save your settings and import your data.

Take a look at the data set transferred with the Google Sheets importer from the Orders from Airtable spreadsheet:

Note: apart from the web app, Coupler.io also has an add-on for Google Sheets. If you need to import data from multiple spreadsheets often, this option may be more convenient for you. Install the Coupler.io add-on from the Google Workspace Marketplace.

Coupler.io on the G Suite Marketplace

You can also use Coupler.io importers to extract data from other sources. For example, you can fetch data from Airtable, Jira, Shopify or other systems, and import them to Google Sheets, Excel, or BigQuery.

What you can do with IMPORTRANGE+QUERY functions (real-life formula examples)

Now, let’s discover what a combination of IMPORTRANGE and QUERY can provide. Orders from Airtable will be our data source spreadsheet. All the rest is the magic of QUERY language clauses.

Import a specific range of data with the select QUERY clause

The select QUERY clause allows you to choose exactly which columns you want to pull. Check out more about Google Sheets Query: Select.

Task: 

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.

QUERY+IMPORTRANGE formula example 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9")

You can check out the formula example in this tab.

Import data from multiple spreadsheets and combine it into one

You can transfer data from several Google spreadsheets with the same columns structure and merge them together using QUERY + IMPORTRANGE. For this, you need to wrap several IMPORTRANGE functions in curly braces {} and separate them by either commas (to merge data horizontally) or semicolons (to merge data vertically).

QUERY + IMPORTRANGE formula syntax to merge spreadsheets horizontally:

=QUERY({IMPORTRANGE("spreadsheet1_url", "data_range"),IMPORTRANGE("spreadsheet2_url", "data_range"),IMPORTRANGE("spreadsheet3_url", "data_range"),..}, "query_string")

QUERY + IMPORTRANGE formula syntax to merge spreadsheets vertically:

=QUERY({IMPORTRANGE("spreadsheet1_url"; "data_range"),IMPORTRANGE("spreadsheet2_url"; "data_range"),IMPORTRANGE("spreadsheet3_url"; "data_range");..}, "query_string")

data_range must contain the same column range for each spreadsheet_url, whereas the number of rows may differ.

IMPORTANT: Before importing data from multiple spreadsheets, you’ll have to make data imports from each spreadsheet separately to allow access. Otherwise, you’ll get #VALUE! Error:in ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

The reason is quite simple: when you IMPORTRANGE data from a particular spreadsheet for the first time, the formula requests to allow access: you need to click on a small button that pops up from the cell.

But there is no such button when you do it for several sources. Check out our blog post Why IMPORTRANGE Is Not Working to handle other errors.

Task:

  • Import columns B, E, H and I from the spreadsheet#1, Orders from Airtable
  • Import columns B, E, H and I from the spreadsheet#2, Orders from Airtable#2
  • Import columns B, E, H and I from the spreadsheet#3, Orders from Airtable#3.
  • Skip the first row when exporting data from spreadsheets #2 and #3.

QUERY+IMPORTRANGE formula example

=QUERY({IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A1:I21"); IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A2:I21"); IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A2:I21")},
"select Col2,Col5,Col8,Col9")

Once we allowed access to the final spreadsheet, our formula for multiple sources immediately worked.

You can check out the formula example in this tab.

Import data from multiple spreadsheets with Coupler.io

Coupler.io can automatically combine your data from several spreadsheets in one specified document. In addition, it will keep updating your information, fetching it from multiple sources according to your schedule. This tool is also simpler to use than IMPORTRANGE as you don’t need to deal with formulas.

To start transferring data, you can use the Coupler.io web app or the add-on for Google Sheets. The process will largely be the same as discussed above in the QUERY+IMPORTRANGE alternative section. The only difference is that you’ll need to create several separate importers. Each of them will be transferring information from a different spreadsheet to the same document.

Here’s how to do this:

  • Sign in to Coupler.io and create Importer 1 with the following parameters:
    • Source:
      • Select Google Sheets as a source app
      • Specify the spreadsheet and sheet to fetch the data from
    • Destination
      • Select Google Sheets as a destination app
      • Specify the spreadsheet and sheet to load data to
    • Choose the replace import mode. This will replace all the data in your document with the newly exported rows. If you already have some data in your destination sheet and want to keep it, then it’s better to use the append mode. This will add the new records below the existing data.
    • Set up the schedule for automatic updates
    • Press Save and Run
  • Then, create Importer 2 (you can copy the Importer 1 and edit the parameters so that you don’t need to create a new importer from scratch)
    • Choose another spreadsheet as your data source
    • Add the same destination file as for the previous importer
    • Choose the append import mode, so that your data from the second source doesn’t erase your information from the first one
    • Set up your synchronization schedule, which should have the time preferences later than the schedule for the Importer 1
    • Press Save and Run
  • Create new importers following the steps for Importer 2. You can add as many as you want, one importer for each data source.
  • When all your importers are ready, run them one by one. Meet your data:

You’ll need to go through all these steps just once. And as a result, you’ll get an always up-to-date document that’s being synchronized with multiple spreadsheets.

Cut off unnecessary rows of the imported data with the limit and offset QUERY clauses

The limit QUERY clause allows you to define the total number of rows (excluding the header) to be imported. The offset QUERY clause allows you to define how many rows to cut off from the top of the imported data range. Check out more about Google Sheets Query: Limit and Offset.

Task #1:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.
  • Reduce the number of imported rows to 10 (excluding the header).

QUERY+IMPORTRANGE formula example: 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 Limit 10")

You can check out the formula example in this tab.

Task #2:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.
  • Reduce the number of imported rows to 10 (excluding the header).
  • Skip five rows from the top of the spreadsheet we export data from (excluding the header). 

QUERY+IMPORTRANGE formula example: 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 Limit 10 offset 5")

You can check out the formula example in this tab.

Change names of the imported columns with the label QUERY clause

The label QUERY clause allows you to change columns’ names (headers). Check out more about Google Sheets Query: Label.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Reduce the number of imported rows to 10 (excluding the header).
  • Rename the headers of the imported columns:
    • Date of order => Time
    • Product => Sandwich
    • Quantity => How many
    • Total price => How much

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 Limit 10 label Col2 'Time', Col5 'Sandwich', Col8 'How many', Col9 'How much'")

You can check out the formula example in this tab.

Format values in the imported columns with the format QUERY clause 

The format QUERY clause allows you to apply specific formats to imported columns. Check out more about Google Sheets Query: Format.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Reduce the number of imported rows to 10 (excluding the header).
  • Format values in the B column to display month and year only.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 Limit 10 format Col2 'mmm-yyyy'")

You can check out the formula example in this tab.

Import the filtered data with the where QUERY clause

The where QUERY clause allows you to filter rows in the imported columns based on set conditions. Check out more about Google Sheets Query: Where.

Task:

  • Import columns B, E, H and I from the spreadsheet Orders from Airtable
  • Filter the imported data by the following conditions:
    • the value in column I should be greater than or equal to 50.
    • the value in column E should start with the letter “S”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 where Col5 starts with 'S' and Col9>=50")

You can check out the formula example in this tab.

Sort the imported data with the order by QUERY clause 

The order by QUERY clause allows you to sort data based on the chosen column in ascending/descending order. Check out more about Google Sheets Query: Order By.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Filter the imported data by the column I where the values should be greater than or equal to 50.
  • Sort the imported data by the date of order (column B) in descending order

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,Col9 where Col9>=50 order by Col2 DESC")

You can check out the formula example in this tab.

Arithmetic operations with the imported data

You can add (+), subtract (-), multiply (*), and divide (/) columns from a spreadsheet and import the outcome as a separate column. Check out more about Google Sheets Query: Arithmetic Operators.

Task:

  • Import columns E and I from the spreadsheet Orders from Airtable
  • Multiply the Price (column G) by the Quantity (column H) and import the outcome as a separate column named “Calculated total price”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col5,Col7*Col8,Col9 label Col7*Col8 'Calculated total price'")

You can check out the formula example in this tab.

Aggregation operations with the imported data

Within the select, order by, label, and format QUERY clauses, you can apply the following aggregation functions to the imported columns:

  • avg() – provides the average of all numbers in a column.
  • sum() – provides the sum of all numbers in a column.
  • count() – provides the quantity of items in a column (rows with empty cells are not calculated).
  • max() – provides the maximum value in a column.
  • min() – provides the minimum value in a column.

Aggregation functions are not applicable to where, group by, pivot, limit and offset QUERY clauses.

Check out more about Google Sheets Query: Aggregation Functions.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Provide the minimum value in column B.
  • Count the items in column E.
  • Provide the sum of all numbers in column H.
  • Provide the average of all values in column I.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select min(Col2),count(Col5),sum(Col8),avg(Col9)")

You can check out the formula example in this tab.

Scalar operations with the imported data

You can use scalar functions to convert imported parameters into a single value. For example, if you apply the year() scalar function, it will fetch the year value out of the YYYY-MM-DD format. Check out more about Google Sheets Query: Scalar Functions.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Fetch the day of the week from date values in column B.
  • Replace all the letters with uppercase ones in column E.
  • Calculate the difference between the date of order and current time, and import the values in a separate column named “Time difference in days”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select dayofweek(Col2),upper(Col5),Col8,Col9,dateDiff(Col2,now()) label dateDiff(Col2,now())'Time difference in days'")

You can check out the formula example in this tab.

Aggregate the imported data across rows with the group by QUERY clause

The group by QUERY clause allows you to group values across the selected data range based on a specific condition. The clause can be applied if an aggregation function has been used within the select clause. Check out more about Google Sheets Query: Group By.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Summarize the Total price data (column I).
  • Group the aggregated values by the Product name (column E).
  • Sort the imported data by the grouped values (column E) in ascending order.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select Col2,Col5,Col8,sum(Col9) Group by Col5,Col2,Col8 order by Col5")

You can check out the formula example in this tab.

Aggregate the imported data and rotate rows into columns with the pivot QUERY clause

The pivot QUERY clause allows you to convert rows into columns, and vice versa, as well as aggregate, transform and group data by any field. The clause can be applied if an aggregation function has been used within the select QUERY clause. Check out more about Google Sheets Query: Pivot.

Task:

  • Import columns E and I from the spreadsheet Orders from Airtable
  • Summarize the Total price data (column I).
  • Pivot the aggregated values by the Product name (column E).

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"),
  "select sum(Col9) pivot(Col5)")

You can check out the formula example in this tab.

So QUERY and IMPORTRANGE or Coupler.io?

Well, this was a pretty long journey, but you handled it! Now you know everything you can do with QUERY and IMPORTRANGE in Google Sheets. Also, if you don’t want to sink into formulas, or you need to automate data import, you can go with Coupler.io. Whichever solution you prefer, good luck with your data activity, and here’s hoping you never get #ERROR. 🙂

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free