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. However, if you work with multiple files and are having trouble with IMPORTRANGE, Sheetgo is a great alternative.

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.

• How To Use IMPORTRANGE Function In Google Sheets
• VLOOKUP with IMPORTRANGE in Google Sheets

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 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. However, there are limitations to using IMPORTRANGE, such as the complexity of formulas, file and size limits, loss of formatting, slower processing time, and privacy concerns. Fortunately, there is an alternative that allows you to save time and avoid these limitations: Sheetgo.

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.

1. 2. From the source file, copy the URL.

1. 3. Paste the URL into the 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.

1. 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. 1. Start typing the formula for QUERY.

1. 2. For the ‘data’ argument, use import range by referencing the cells with the arguments.

1. 3. For the ‘query’ argument, I will use "Select * Where Col5='Southteam'". ‘Col5’ is the column “sales_team”, which should match the “Southteam”.

1. 4. Press “Enter” to see the 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.

1. 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.

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.

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.  Finally, you know about a great alternative that automates your processes and avoids the limitations of IMPORTRANGE. Sign up for Sheetgo for free and start saving time today.

To learn more about different methods used to look up or query data in Microsoft Excel and Google Sheets, check out the articles below.

• How to VLOOKUP in Excel with Two Spreadsheets
• How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)
• How To Use IMPORTRANGE Function In Google Sheets
• VLOOKUP with IMPORTRANGE in Google Sheets