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 guide, 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.
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 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. Open both the source and destination files in Sheets.
- 2. From the source file, copy the URL.
- 3. Paste the URL into the destination file.
- 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.
- 5. Paste the cell range reference into the destination file. I don’t need to specify the sheet because it is on “Sheet1”.
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. Start typing the formula for QUERY.
- 2. For the ‘data’ argument, use import range by referencing the cells with the arguments.
- 3. For the ‘query’ argument, I will use "Select * Where Col5='Southteam'". ‘Col5’ is the column “sales_team”, which should match the “Southteam”.
- 4. Press “Enter” to see the results.
- 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.
- 6. As you can see in the screenshot below, the results now also contain the data for ‘Westteam’.
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.
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
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.
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")”.
Press ‘Enter’ to see the 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’.
Press ‘Enter’ to see the 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".
Press ‘Enter’ to see your results ordered by sales amount.
Want to Boost Your Team's Productivity and Efficiency?
Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.
Key Features and Benefits:
- Centralized Knowledge: Access your team’s collective wisdom with ease.
- Collaborative Workspace: Foster engagement with flexible project tools.
- Seamless Communication: Connect your entire organization effortlessly.
- Preserve Ideas: Capture insights without losing them in chats or notifications.
- Comprehensive Platform: Manage all content in one organized location.
- Open Teamwork: Empower employees to contribute, share, and grow.
- Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.
Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.
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.