Combine QUERY with IMPORTRANGE in Google Sheets (2024)

How does this combination work?

Whenever you combine functions, things can get a bit confusing. To avoid that, let’s look at what’s going on in this syntax.

First, let’s focus on QUERY. This function can manipulate data in many ways, but 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, we will use the IMPORTRANGE function to feed data into QUERY.

IMPORTRANGE is a simple function: just input the link to the spreadsheet and the range you want to import. To learn more about IMPORTRANGE check out our in-depth article.

This imported data can be plugged into the QUERY function. After all you need to input are the query parameters, which dictate how the data will be manipulated and displayed. The examples below illustrate this.

Just remember, the structure of the syntax remains similar, no matter which function you nest inside Google Sheets’ QUERY function.

Example 1: Import and Query

In the following examples, I’ll use this geographical data as the source data:

First, 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 data from countries in the Americas. To display only 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. To import data from countries in the Americas, we used this query: Select * Where Col1=’Americas’. In plain English that translates to “select all the data that has “Americas” in column 1”.

This formula comes in especially handy in situations where you want to merge data. If you work in online marketing for example, you might want 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

In this next example, I added search criteria after IMPORTRANGE. In this case, where Col3 > date ‘2019-07-01’ and Col3 < date ‘2019-12-30’ imports the same data but only for items in the list that were published between those dates.

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.

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:

You need to use IMPORTRANGE for every spreadsheet 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. To avoid importing the header three times, 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 one.
• 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.

Example 4: Import and Query Average Values

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 look up 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

In this final example, the formula filters by continent, sums up each country’s GDP and imports data from a separate spreadsheet.

This is specifically helpful to create dashboards and reports of all kinds.

Get the same results with Sheetgo connections

Sheetgo offers an intuitive and powerful alternative to the combination of QUERY and IMPORTRANGE in Google Sheets. With Sheetgo, you can seamlessly import and process data to simplify your data management process.

Coincidentally, one of the key data processing capabilities of Sheetgo offers QUERY filter. This feature enables you to perform advanced data queries similar to the QUERY function in Google Sheets. You can filter, sort, and manipulate data as it flows between your spreadsheets, all without the need for complex formulas.

Why use Sheetgo over IMPORTRANGE? Here are just three reasons:

• Enhanced Data Management: Unlike IMPORTRANGE, Sheetgo provides a centralized dashboard where you can manage all your data connections.
• Scalability: Sheetgo can handle large datasets and multiple connections without the performance issues that IMPORTRANGE might encounter. This makes it ideal for businesses with complex data requirements.
• Advanced Filtering: With Sheetgo’s QUERY filter, you can perform sophisticated data manipulations directly within your workflows. This allows for more advanced data processing capabilities compared to IMPORTRANGE.

Find out all the reasons why Sheetgo is the best IMPORTRANGE alternative.

Conclusion

Now you’ve got the lowdown on how to use QUERY with IMPORTRANGE. Now you can merge the power of these two functions, for another great combination check out How to use VLOOKUP and IMPORTRANGE.