How To Use The QUERY Function With IMPORTRANGE In Google Sheets

How to use the QUERY function with IMPORTRANGE in Google Sheets
Reading Time: 7 minutes

Objective – To understand various use cases of QUERY function with IMPORTRANGE in Google Sheets and how their combination can be used to achieve wonderful results.

=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

Example usage
//Retrieves column 1 from the sheet ALL ARTICLES

Sample Google Sheets template with formula here.


Google Sheets QUERY and IMPORTRANGE functions are two 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. But it can be made a great deal more useful by combining the QUERY function with IMPORTRANGE.

Why use the QUERY function with IMPORTRANGE in Google Sheets?

As you must have already guessed, they both have their limitations. The QUERY function with 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 QUERY with IMPORTRANGE 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 in the QUERY function. The QUERY-IMPORTRANGE combination will provide us with flexibility and range. Pretty awesome. Now let’s get to see how we can use the QUERY and IMPORTRANGE functions together. 

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 QUERY function with IMPORTRANGE 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 and IMPORTRANGE functions in Google Sheets.

Example 1: Retrieve data from an external spreadsheet using the QUERY function with IMPORTRANGE in Google Sheets 

In this example, we will learn how to retrieve data from a different spreadsheet using the IMPORTRANGE in the QUERY function.

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 the spreadsheet containing the data used in this article. Make your own copy of it to use in the examples.

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, enter the QUERY function by typing =QUERY
  • Select the QUERY function from the dialogue (you can press Enter or Tab to select the suggested function).
  • 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.

  • Enter the complete formula and press Enter and fetch the data.
Use the QUERY function with IMPORTRANGE  in Google Sheets to retrieve all data from another spreadsheet

The table corresponds with what we have in the original spreadsheet. And thanks to IMPORTRANGE, any updates or changes on the original table will result in a corresponding change in the new sheet.

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 the QUERY and IMPORTRANGE 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 with 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:


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. And “Col1” stands for the first column–which is what we want to retrieve.

On pressing Enter, we’ll get the data as shown below.

Retrieving specific column data from another spreadsheet using the query function with importrange in Google Sheets

The QUERY function with IMPORTRANGE returned the articles 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. 

The formula for which is:

=QUERY(IMPORTRANGE(A1,"ALL ARTICLES!F6:N25"),"Select Col2, Count(Col2) GROUP BY Col2 Order by Count(Col2) desc")

Let us deconstruct this formula. The IMPORTRANGE function retrieves the data from the URL in cell A1; the QUERY function, from the data imported by the IMPORTRANGE function, selects the data in column 2, counts the items in column 2, classifies the items in column 2, and arranges these grouped items in descending order, ie, largest to the smallest.

This may seem a little complicated but if you study the formula carefully or use the QUERY function more regularly, it should become less knotty.

QUERY Function and IMPORTRANGE arranging authors by the number of articles

To illustrate the next set of applications for QUERY with IMPORTRANGE in Google Sheets we will be making use of the employee data of a certain imaginary company. The table contains information about the various employees which includes their names, department, age, salary gender, and finally ethnicity. 

Sample employee data for query with importrange in Google Sheets

Example 4: Sort data in descending or ascending order

Let’s consider an instance where we want the QUERY with IMPORTRANGE to return data which are sorted in 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 Function with IMPORTRANGE arranging data by descending order

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 in place of Desc.

QUERY Function and IMPORTRANGE arranging data by ascending order

Example 5: Return the highest values by department using the QUERY function and IMPORTRANGE in Google Sheets

If the company wants to see the highest salary levels in the various departments, it can do this using a combination of MAX and GROUP BY.

The IMPORTRANGE function will 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")
QUERY Function with IMPORTRANGE max earnings in each department

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 use CONTAINS to indicate that we are interested in retrieving the salary for the female employees.

The QUERY formula with IMPORTRANGE to return the total salary of female employees is as under.

=QUERY(IMPORTRANGE(A1,"EMPLOYEES!B4:G15"),"Select sum (Col4) Where (Col5) contains 'Female'")
QUERY Function and IMPORTRANGE returning total earnings for females

The table above shows the total amount earned by the female employees.

There are so many ways to combine the QUERY function with IMPORTRANGE in Google Sheets 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. And thus together, the QUERY and IMPORTRANGE functions work together to perform wonderful functions.

See also

If you enjoyed reading this then you might want to check out similar articles and general Google Sheets tips on our blog:

Reference data from other sheets in Google Sheets

VLOOKUP from another sheet in Google Sheets

Easy Way To Scrape Google Search Results And Auto-update The Data

Leave a Reply