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. With SQL-like query language, you can look up, sum, count, average, filter and sort data.
How to use QUERY with IMPORTRANGE
For more advanced and efficient data manipulation, combine the two formulas. This applies QUERY directly to the data you move with IMPORTRANGE to filter specific columns, rows, and ranges.
It’s a real time-saver when working with multiple spreadsheets, as you only pull in the exact data you need. It also allows you to format the data within your query instead of afterwards, which saves you an additional step!
Here’s how to use the two formulas in tandem:
Syntax
- Spreadsheet_url – the link to the spreadsheet where the cells are imported from
- Range_string – defines the range of cells to be imported. This includes two components: sheet_name is the name of the sheet where the data comes from, and range_string is 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 language functions (Select, Group by, etc.) and data manipulation functions (like Avg, Sum, Count, and Multiply)
- Headers – the number of header rows in the imported data. Fill with 1 if the spreadsheet from which you want to import data includes one row of headers
How does it work?
Whenever you combine functions, things possibly get a bit confusing. To avoid that, I’ll give a quick explanation of what’s going on in this syntax.
Let’s focus on the QUERY function first. QUERY can manipulate data in a wide variety of ways, but one thing stays the same: it always needs to start with a data source. In many cases this is data from another tab on the same spreadsheet. But as you know, this article deals with importing data from another spreadsheet. As a result, you use the IMPORTRANGE function to feed the data into the QUERY.
IMPORTRANGE is a simple function: just input the link to the spreadsheet and the range you want to import. It then places the data into the QUERY function.
The last step is to finish the query. Input the query parameters, which dictate how the data will be manipulated and displayed. I’ll demonstrate more of that in the examples below.
Just remember, the structure of the syntax remains similar, no matter which function you wrap up inside Google Sheets’ QUERY function.
Example 1: Import and Query
In the following examples, I’ll use this geographical data as the source data:
Firstly, I import country data from 186 countries into a business spreadsheet. I’ve merged items from a separate dataset based on certain criteria.
Say I only want to import the country data from countries in the Americas. To filter those specific cells, you simply need to apply the Select and Where clauses inside the QUERY function:
In this formula, QUERY searches data from a predefined range/array according to specific criteria. Simultaneously, 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. This way, you only import the data you want to see in your spreadsheet.
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. The * specifies all the data, so this query is saying “select all the data that has “Americas” in column 1”.
It’s possible to do the same for any other search criteria. Just make sure to check spaces, quotation marks and parentheses.
This formula especially 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
Usage
In this example, I added search criteria 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 between July 1 and December 30 2019.
Note that when you input a date into your query, you need to type the word date before it. Also don’t forget to put the date in quotation marks. That’s how the function knows you’re referring to a date.
You might apply this formula in logistics or even for a small business cash register. You can add up rows, columns and data ranges with changes in the data over time. If you’re interested in changes to product lines, sales, and other KPIs over the course of the year, this formula can carry out monthly or quarterly comparative analysis.
Example 3: Import and combine 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:
Most importantly, you need to include IMPORTRANGE in front of every spreadsheet that you want to merge. Within the first IMPORTRANGE formula, I added the headers in A1:P. The other spreadsheets also contain the headers. However, they should not be imported multiple times.
This is why I’ve changed the range to A2:P on the second and third sheets.
Lastly, the Where clause (where Col1<>’’) simply states that all cells that are not blank should be imported.
Here are a few tips for importing multiple sheets:
- The list of imports needs to be enclosed in curly brackets and a semicolon must separate each IMPORTRANGE
- The imported sheets must all have the same layout. For example, if the first column of all the sheets wasn’t Country Name, the data wouldn’t combine properly
- Don’t import the headers on every sheet – you only need them once
- 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). Likewise for educational purposes. When you hand out similar exercises to students or when you collect grades from various professors, for example.
Example 4: Import and Query Average Values
Syntax
Usage
With this formula, I import the average population of each continent. As you can see, I add “avg” after the ‘Select’ clause and I put the column in parentheses. Next, add the query (the data you are looking for) after the Where clause. In this case, the query is ‘Europe’.
Notice how on the rest of this spreadsheet I use multiple QUERY/IMPORTRANGE functions to get average populations and total GDPs for different regions. Since QUERY is highly customizable, you can get all sorts of different outputs depending on your objective!
QUERY function allows you to extract all arithmetic functions. This is a very helpful tool for traders and financial analysts. With it, you can 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.
In addition: it’s a more intuitive, natural language version of the complex INDEX-MATCH formulas in Excel.
Example 5: Import and Query Sum values
Take a look at the fifth example below. Here, I filter (the continent), sum (the GDP of each country) and import data from a separate spreadsheet.
This is specifically helpful for creating dashboards and reports of all kinds.
Now you’ve got the lowdown on how to use QUERY with IMPORTRANGE. As a result, you can combine 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.
