Don’t forget to share this post

In addition to its spreadsheet features, Google Sheets provides a diverse range of functions that allows you to use spreadsheets to store and retrieve data. While it’s not recommended for very large databases or ones with complex architectures, you can easily manage a simple database in Google Sheets. The VLOOKUP function is useful when you’re searching for specific values, but it’s not designed to find data based on conditions or filter it. The QUERY function, on the other hand, is designed to find, filter, and manipulate data based on multiple criteria. The IMPORTRANGE function allows you to work with data stored in a different Google Sheet. By combining these two versatile functions, you can access and query your data, even if it’s stored in multiple spreadsheets.

In this article, you will learn how to use the QUERY function with the IMPORTRANGE function to query and import data from other Google Sheets files. You have a review of the syntax for both functions and step-by-step instructions on how to use them together. Finally, you have examples showing how to use the most common query clauses.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

QUERY and IMPORTRANGE Syntax

Before combining the two, it’s a good idea to review the syntax and arguments for each function.

=IMPORTRANGE(“spreadsheet_url”, “range_string”)
  • spreadsheet_url: the link to the spreadsheet containing the data you want to import. It should be enclosed in quotation marks or be a reference to a cell containing the URL.
  • range_string: the format is “[sheet_name!]range", where sheet name is optional. The function will import from the first sheet if no sheet name is provided.
=QUERY(data, query, [headers])
  • data: range of cells to be queried. This is where you can insert IMPORTRANGE to query data contained in a different spreadsheet.
  • query: written in the Google Visualization API Query language. Enclosed in quotations or a cell reference.
  • headers*: optional argument (when header consists of or is split over more than one row

Common Query Clauses

The language used for queries is similar to SQL and just as flexible. You can create anything from extremely simple to extremely complex queries. Below, you have some of the most common clauses, which you will learn to use in the following sections.

  • select: selects which columns to return and in what order. If left blank, the function will return all columns in their default order.
  • where: when used, the function will only return rows that match the condition.
  • order by: sorts the rows by the values in the column.
  • group by: aggregates the row values and returns the result.

How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)

Sometimes our data may be spread out among different Google sheets or workbooks. Here's how to VLOOKUP from another sheet or workbook

READ MORE
How to VLOOKUP From Another Google Sheet or Workbook Examples

How to Use QUERY with IMPORTRANGE?

Now that you know the syntax for these functions, it’s time to learn how to use them together. Below, you have step-by-step instructions on how to combine the QUERY and IMPORTRANGE formulas to query data in another spreadsheet.

The example below is based on two files. The source file contains data on sales for all agents and products. The destination file is where I want to import and analyze data based on specific conditions.

Copy Source URL and Cell Range Reference

  1. 1. Open both the source and destination files in Sheets.
QUERY with IMPORTRANGE in Google Sheets Source and Destination Files
QUERY with IMPORTRANGE in Google Sheets - Source and Destination Files
  1. 2. From the source file, copy the URL.
QUERY with IMPORTRANGE in Google Sheets Copy Source File URL
QUERY with IMPORTRANGE in Google Sheets - Copy Source File URL
  1. 3. Paste the URL into the destination file.
QUERY with IMPORTRANGE in Google Sheets Paste Source URL to Destination File
QUERY with IMPORTRANGE in Google Sheets - Paste Source URL to Destination File
  1. 4. Go back to the source file and select the cell range. You can copy the cell range reference from the top-left corner, as shown below.
QUERY with IMPORTRANGE in Google Sheets Select and Copy Source Cell Range
QUERY with IMPORTRANGE in Google Sheets - Select and Copy Source Cell Range
  1. 5. Paste the cell range reference into the destination file. I don’t need to specify the sheet because it is on “Sheet1”.
QUERY with IMPORTRANGE in Google Sheets Paste Cell Range Reference to Destination File
QUERY with IMPORTRANGE in Google Sheets - Paste Cell Range Reference to Destination File

Add QUERY Function

I’ll use a simple query with ‘select’ and ‘where’ clauses to import the rows where the “sales_team” is the ‘Southteam’.

  1. 1. Start typing the formula for QUERY.
QUERY with IMPORTRANGE in Google Sheets Add QUERY Function
QUERY with IMPORTRANGE in Google Sheets - Add QUERY Function
  1. 2. For the ‘data’ argument, use import range by referencing the cells with the arguments.
QUERY with IMPORTRANGE in Google Sheets Add IMPORTRANGE Function
QUERY with IMPORTRANGE in Google Sheets - Add IMPORTRANGE Function
  1. 3. For the ‘query’ argument, I will use "Select * Where Col5='Southteam'". ‘Col5’ is the column “sales_team”, which should match the “Southteam”.
QUERY with IMPORTRANGE in Google Sheets Add Query
QUERY with IMPORTRANGE in Google Sheets - Add Query
  1. 4. Press “Enter” to see the results.
QUERY with IMPORTRANGE in Google Sheets QUERY Results
QUERY with IMPORTRANGE in Google Sheets - QUERY Results
  1. 5. You can easily add another condition by using the ‘OR’ operator. For instance, I can import the results for two different teams by adjusting the query as shown below.
QUERY with IMPORTRANGE in Google Sheets Modify Query with OR
QUERY with IMPORTRANGE in Google Sheets - Modify Query with OR
  1. 6. As you can see in the screenshot below, the results now also contain the data for ‘Westteam’.
QUERY with IMPORTRANGE in Google Sheets Modified Query Results
QUERY with IMPORTRANGE in Google Sheets - Modified Query Results

Approve Access Between Sheets

If you’re working with files that have never been connected before, you’ll see an error message instead of the value when you press ‘Enter’. To see the actual value, you will have to approve access between sheets.

QUERY with IMPORTRANGE in Google Sheets Approve Access Between Sheets
QUERY with IMPORTRANGE in Google Sheets - Approve Access Between Sheets

How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google Sheets

READ MORE
How To Use IMPORTRANGE Function In Google Sheets

Example: QUERY + IMPORTRANGE in Google Sheets

For the sake of simplicity, I’ll use the data from the previous section. For my analysis, I want to see sales totals by team, as well as sales totals by sales agent.

QUERY with IMPORTRANGE in Google Sheets Replace Query
QUERY with IMPORTRANGE in Google Sheets - Replace Query

Group by Sales Team Totals

To group the results, I need to select the ‘sales_team’ column (5) and the sum of the ‘sales_amount’ column, then group by ‘sales_team’.

The query looks like this: "Select Col5,sum(Col2) Group by Col5")”.

QUERY with IMPORTRANGE in Google Sheets Group By Query
QUERY with IMPORTRANGE in Google Sheets - Group By Query

Press ‘Enter’ to see the results.

QUERY with IMPORTRANGE in Google Sheets Group By Results
QUERY with IMPORTRANGE in Google Sheets - Group By Results

Group by Sales Agent Totals

As the name of the sales agent is in column 3, I need to modify the query and replace references to ‘Col5’ with ‘Col3’.

QUERY with IMPORTRANGE in Google Sheets New Group By Query
QUERY with IMPORTRANGE in Google Sheets - New Group By Query

Press ‘Enter’ to see the results.

QUERY with IMPORTRANGE in Google Sheets New Group By Results
QUERY with IMPORTRANGE in Google Sheets - New Group By Results

Order by Sales Amount

As you can see in the previous screenshot, the function automatically sorts the column alphabetically by the name of the sales agent. However, I want to see the results by sales amount, in descending order.

I can modify the previous query by adding the following “Order by sum(Col2) DESC".

QUERY with IMPORTRANGE in Google Sheets Order By Query
QUERY with IMPORTRANGE in Google Sheets - Order By Query

Press ‘Enter’ to see your results ordered by sales amount.

QUERY with IMPORTRANGE in Google Sheets Order By Results
QUERY with IMPORTRANGE in Google Sheets - Order By Results

How to Import HubSpot Data Into Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

As you have seen, QUERY is a versatile function. In addition to using it for traditional lookups, you can use complex sets of conditions and criteria to filter data and perform calculations. In other words, you can use it not only to find data based on conditions but also to manipulate and format data. QUERY can also be combined with IMPORTRANGE to query and retrieve data contained in other spreadsheets.

You now know about the QUERY function and that you can use it to select, filter, and group based on conditions. You also know how to combine QUERY and IMPORTRANGE to query data in other spreadsheets. To learn more about different methods used to look up or query data in Microsoft Excel and Google Sheets, check out the articles below.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 29 2022, Updated Dec 2 2022