What it does – To understand various use cases of QUERY function in combination with IMPORTRANGE function
=QUERY (IMPORTRANGE(spreadsheet_url, range_string), query, [headers])
spreadsheet_url – It refers to the URL of the spreadsheet we want to retrieve data from
range_string – It refers to the particular table/range in the spreadsheet
=QUERY(IMPORTRANGE(A1,"ALL ARTICLES!F6:N25"),"Select Col1")
//Retrieves column 1 from the sheet ALL ARTICLES
Sample Google Sheets template with formula here.
- Why use the QUERY function with IMPORTRANGE
- How to use the QUERY function with IMPORTRANGE
- #1: Retrieve data from an external spreadsheet
- #2: Retrieve a specific column from an external sheet
- #3: Retrieve a list of items
- #4: Retrieve and sort items in descending or ascending order
- #5: Return the maximum value by department
- #6: Total salary by gender
Google Sheets QUERY and IMPORTRANGE functions are some of the most flexible and powerful functions on the platform.
The QUERY function is useful for its flexibility and ability to tackle a wide range of problems. It is similar to SQL query and makes database-oriented operations a breeze in Google Sheets.
On the other hand, the IMPORTRANGE function has one primary utility—to retrieve data from an entirely different spreadsheet.
While that is an amazing function to use, it is a bit limited in terms of what you can do with the data you import.
Why use the QUERY function with IMPORTRANGE in Google Sheets?
As you must have already guessed, they both have their limitations. The QUERY function in all its flexibility can only be useful in one sheet. On the other hand, the IMPORTRANGE function, while being an excellent long-range formula, is limited in terms of manipulating data.
Therefore, combining the two functions strategically mitigates these limitations. QUERY function offers its amazing query properties and IMPORTRANGE takes that into data located in other sheets. Great teamwork!
Combining the QUERY function and IMPORTRANGE: How it works
We will combine the IMPORTRANGE and Google Sheets QUERY functions by passing the entire IMPORTRANGE function as a parameter into the QUERY function. This will provide us with flexibility and range. Pretty awesome. Now let’s get to see how this is done.
Just to recap, the QUERY function has the syntax below:
=QUERY(data, query, [headers])
- The first parameter is the dataset(usually a table in a spreadsheet)
- Query refers to the data operation we want to perform
- The final parameter is an optional header
Therefore, when we combine the two functions as above, the IMPORTRANGE function replaces the dataset parameter in the query function. The IMPORTRANGE function still does all it is designed to do. And the QUERY function becomes more sophisticated by relying on a dynamic dataset that it can utilize for its operations.
Note: IMPORTRANGE updates changes in the new spreadsheet when the source of its data is modified. This is handy because having to manually update changes in our spreadsheet can quickly become tedious.
How to use the QUERY function with IMPORTRANGE in Google Sheets
Now, let us with the aid of examples examine multiple ways to utilize this powerful combination of the QUERY function with IMPORTRANGE in Google Sheets.
Example 1: Retrieve data from an external spreadsheet using the QUERY function and IMPORTRANGE in Google Sheets
In this example, we will learn how to retrieve data from a different spreadsheet.
Step 1: Set up the data
We are going to use the data from a recent article. The dataset is a table containing the recently published articles on our blog, as well as other information such as the author, the title of the article, the date created, etc.
Here’s a copy of the spreadsheet containing the data sets. Make your own copy of it to use in the example.
Step 2: Enter the function and the parameters
Copy the URL given above and head over to a brand new spreadsheet.
- In a cell of your preference, type in =QUERY
- Select the QUERY function from the dialogue
- Paste the URL as the first parameter of the QUERY function. For now, we won’t be doing any manipulation of data; we just want to retrieve the dataset
- So, we will set the query parameter of the QUERY function to Select* which means select all.
The complete syntax of the QUERY function with IMPORTRANGE in Google Sheets is given below.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AA2JE6bi10V4ps17JrYzzHcluRbNZNwDhfOc_7mZHi8/edit","ALL ARTICLES!F6:N25"),"Select *")
- Enter the formula above and press Enter
The table corresponds with what we have in our original spreadsheet. And thanks to IMPORTRANGE, any updates or changes on the original table will result in a corresponding change here.
Now, if retrieving every column in a spreadsheet was the only thing we wanted to do, there wouldn’t be a need to go as far as combining two formulas, the IMPORTRANGE function can take care of that.
The usefulness of this combination lies in utilizing the best each function has to offer. So let us look at more examples.
Example 2: Retrieve a specific column from an external spreadsheet using the QUERY function and IMPORTRANGE in Google Sheets
Using the QUERY function with IMPORTRANGE in Google Sheets we can retrieve a particular column in a different spreadsheet. We will do this using the Select command.
Assuming we want to only see the articles in the previous table we retrieved, we can do this using the below command :
=QUERY(IMPORTRANGE(A1,"ALL ARTICLES!F6:N25"),"Select Col1")
Here, “ALL ARTICLES” is the name of the spreadsheet, the exclamatory mark indicates that we’re referencing another spreadsheet, and F6:N25 is the range of the data.
On pressing Enter, we’ll get the data as shown below.
The function returned the article column from the external sheet successfully.
Tip: instead of putting the long URL into the function every time, you can just store it in a cell, say A5. Then you can reference it in the formula.
Example 3: Retrieve the list of authors in order of most published articles using the QUERY function with IMPORTRANGE in Google Sheets
If we want to find out how many articles each author has published and to return a list showing that information in order of most published. We can do that using a combination of commands.
Enter the formula below
=QUERY(IMPORTRANGE(A1,"ALL ARTICLES!F6:N25"),"Select Col2, Count(Col2) GROUP BY Col2 Order by Count(Col2) desc")
We used select to pick the column then we used Group by to classify them, finally, we ask for the data to be arranged in ascending order.
To illustrate the next set of applications for functions we will be making use of the employee data of a company. The table contains information about the various employees which includes their names, department, age, salary gender, and finally ethnicity.
Example 4: Sort data by descending or ascending order
Let’s consider an instance where we want the QUERY function and IMPORTRANGE to return data which is sorted by descending order of salary, i.e from the highest earner to the lowest.
This can be helpful to an organization that wants to review its salary structure.
For descending order, enter the following command
=QUERY(IMPORTRANGE(A1,"EMPLOYEES!B4:G15"),"Select* ORDER BY Col4 Desc")
On the other hand, if we need to return the data in ascending order of salary i.e from the least to the highest, use the Asc command.
Example 5: Return MAX values by the department using the QUERY function and IMPORTRANGE in Google Sheets
If the company wants to see the highest salary levels in the various departments. They can do this using a combination of MAX and GROUP BY.
The IMPORTRANGE function would retrieve the data from the original table in another spreadsheet. And the QUERY function will perform operations on it using MAX and GROUP BY.
We are going to see how.
Enter the formula below in a cell.
=QUERY(IMPORTRANGE(A1,"EMPLOYEES!B4:G15"),"Select Col2, MAX(Col4) group by Col2")
The right column shows the maximum salary corresponding to the various departments in the left column.
Example 6: Total salary by gender
We could also be interested in seeing how much a gender earns in the company
We will be using SUM to aggregate the salary then we will use CONTAINS to indicate that we are interested in retrieving data for the Females.
Enter the formula
=QUERY(IMPORTRANGE(A1,"EMPLOYEES!B4:G15"),"Select sum (Col4) Where (Col5) contains 'Female'")
The table above shows the total amount earned by the female employees.
There are so many ways to combine queries and achieve different results. Also, note that all our operation so far has been on data in another spreadsheet. That is why combining the query function with IMPORTRANGE is very effective.
The QUERY function can be combined with IMPORTRANGE to achieve amazing results. Because while the QUERY function isn’t designed to operate outside a spreadsheet, the IMPORTRANGE does this primarily.
If you enjoyed reading this then you might want to check out similar articles and general Google Sheets tips on our blog: