Combine QUERY with IMPORTRANGE in Google Sheets

Combine query with importrange featured image

Written by Laura Tennyson

Dec 17, 2019

How to use QUERY with IMPORTRANGE

Importrange is one of the most useful and powerful functions in Google Sheets. As the name suggests, it imports, or transfers, a range of cells from one spreadsheet to another. Once you have moved data with Importrange you can apply other formulas to it, just like any other dataset. QUERY, on the other hand, is an incredibly versatile formula, essentially allowing you to use your spreadsheet as a database. Using SQL-like query language, you look up, sum, count, average, filter and sort data.

For more advanced and efficient data manipulation, combine the two formulas. Thus, applying QUERY directly to the data you move with IMPORTRANGE to filter specific columns, rows, and ranges. This can be a real time-saver when working with multiple spreadsheets, as you only pull in the exact data you need.

Here’s how to use the two formulas in tandem:

Syntax

=QUERY(IMPORTRANGE(spreadsheet_url, range_string), query, [headers])

  • Spreadsheet_url – is the link to the spreadsheet where the cells will be imported from
  • Range_string – defines the range of cells to be imported. This includes two components: One is sheet_name, the name of the sheet where the data comes from. The second is range_string: the range of the cells from this sheet.
  • Query – includes the criteria or condition to be used. Here you formulate what you are searching for in the target spreadsheet. Query handles both filter and arithmetic functions like Avg, Sum, Count, Sort, and Countif.
  • Headers – Fill with 1 if the spreadsheet from which you want to import data from includes one row of headers.

The structure of the Syntax remains similar, no matter which function you wrap up inside Google Sheets’ QUERY function.

Example 1

In this example, we import country data from 186 countries into a business spreadsheet. We’ve merged items from two separate datasets based on certain criteria.

Let’s say I am interested in importing the country data only from countries in the Americas. For filtering those specific cells, you need to apply the Select and Where clauses inside the QUERY function.

=QUERY(IMPORTRANGE(“Spreadsheet_url, “Population!B1:F187”), “Select * Where Col1=’Americas’ “)

In this formula, QUERY searches data from a predefined range/array according to specific criteria. And IMPORTRANGE imports the data into the target spreadsheet.

Placing QUERY before IMPORTRANGE allows you to search (query) specific information from the data that is imported with IMPORTRANGE.

After IMPORTRANGE, you need to add search criteria. In this case, we added Select * Where Col1=’Americas’ in order to import the same data from countries in the Americas only. Make sure to check spaces, quotation marks and parentheses.

You can do the same for any other search criteria.

This formula comes in handy in situations where you want to merge data. If you work in online marketing for example, you might want to merge keyword data or traffic from various sources into one spreadsheet. It gives you a better overview of several data sources such as Google Analytics, Ahrefs, SEMrush or Google Search Console.

Example 2: Import data within a date range

Syntax

=QUERY(IMPORTRANGE(“Spreadsheet_url”, “Population!A1:F187”), “where Col3 > date ‘2019-07-01’ and Col3 < date ‘2019-12-30′”, 0)

Use of Formula

Search criteria are added after IMPORTRANGE. In this case, where Col3 > date ‘2019-07-01’ and Col3 < date ‘2019-12-30’ to import the same data but only for items in the list that were published after July 1.

You might apply this formula in logistics or even for a small business cash register. It allows you to add up rows, columns and data ranges with changes in the data through business operations. If you’re a business operator interested in changes to product lines, sales, and other KPIs over the course of the year, this formula allows you to carry out monthly or quarterly comparative analysis.

Example 3: Combine/Import multiple spreadsheets with specific criteria

If you want to extract certain cells or a range from several spreadsheets, use the following formula. Here’s how to merge data from multiple spreadsheets into one.

=QUERY({IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A1:P”);IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A2:P”);IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A2:P”)},”Select * where Col1<>””,1)

You need to include IMPORTRANGE in front of every spreadsheet that you want to merge. Within the first IMPORTRANGE formula, we’ve added the Headers in ‘A1:P’. The other spreadsheets also contain the headers but they should not be imported multiple times, of course.

The ‘Where’ clause (where Coll<>”) simply states that all cells should be imported until there are no more filled cells in the spreadsheet.

Note: Remember to allow access to all the spreadsheets you want to import from.

This formula is really useful for business surveys (when you collect several spreadsheets with the same format) and for educational purposes, when similar exercises are handed out to students or grades are collected from various professors.

Example 4: Import and Query Average Values

Syntax

=QUERY(IMPORTRANGE(“Spreadsheet_url”, “Population!A2:P187”), “Select avg(Col4) where Col2 contains ‘Europe’ “)

Usage

With this formula, you import the average population of each continent. As you can see, “avg” is added after the ‘Select’ clause and the column is put in parentheses. You add the query (the data you are looking for) after the ‘where’ clause. In this case, the query is ‘Europe’.

QUERY function allows you to extract all arithmetic functions. For traders and financial analysts, this is a helpful tool to lookup values from a variety of investment spreadsheets and carry out calculations such as Present Value =PV, Future Value =FV and interest rate functions such as =IRR and =RATE. The big advantage: it’s a more intuitive, natural language version of the complex Index-Match formulas in Excel.

Example 5: Import and Query Sum values

In this example, we’re going to filter (the continent) sum (the GDP of each country) and import data from a separate spreadsheet.

This is really helpful for creating dashboards and reports of all kinds.

Syntax

=QUERY(IMPORTRANGE(“Spreadsheet_url), “Select sum(Col5) where Col2 contains ‘Europe’ “)

So now you’ve got the lowdown on how to use Query with Importrange, combining the power of the two functions to import and filter data from one Google Sheet to another. Looking for more help with each formula? Check out our posts on how to use Importrange and Query functions. Having trouble with Importrange and heavy processing? Read about an alternative solution for moving data from one Google Sheet to another. 

You may also like…

Share This