# Google Sheets Query Function Explained [2024 Update]

## How to use the QUERY Function in Google Sheets

For our example, we have the small table below stored in Google Sheets:

We want to list all Type A names whose values are more than 15. The column containing the type is the 2nd column while the column containing the values is the 3rd column. For the first condition we can write that as

B = ‘A’

The formula means that you check the 2nd column (B) for cells that contain the value “A” (enclosed in single quotes). This is how you check whether a cell has a specific strings in Google Sheets.

While the second condition we can write as

C>15

We can combine them using the AND command to indicate that we need both conditions fulfilled:

B=’A’ AND C>15

Finally, we add the SELECT and WHERE commands to specify the columns of the range we query. For our example, we include all the columns. Thus,

SELECT * WHERE B=’A’ AND C>15

### 2. Use Formula =QUERY(data, query)

The syntax of the QUERY function is

=QUERY(data,query)

Where data is the range of the table you want to process and query is the command you want to use to process the data. The query must be enclosed in double quotes.

In our example, the range of the table we query is A1:C6. Noting the query as

SELECT * WHERE B=’A’ AND C>15

The formula becomes

=QUERY(A1:C6,"SELECT * WHERE C>15 AND B='A'")

We place it in cell E1 in the same sheet.

### 3. Press Enter

Press Enter once you enter the formula in the cell. Depending on the size of the table, the loading of the output may take some time.

Quite straightforward, right? As long as you know how to make queries, it is easy to construct the formula.

## Sample Queries in Google Sheets

### Select certain columns from a table

SELECT column1, column2, …

Where column1 and column2 are the columns of the table you want to output, written in letters. For example, if you have a 5-column table and you want to output the 2nd and 3rd column, the query is

SELECT B, C

### Filter entries using a condition

If you want to use QUERY function to filter entries using a condition applied to a column, then the query is

SELECT * where condition

Where the condition uses the column letter to identify the column it is applied. The asterisk means all the columns are selected by the query. This means that the output will display all columns of the entries filtered. For example, we want to filter the table by only listing all entries whose values in the third column is more than 15, then the query becomes

SELECT * where C>15

### Filter entries using two or more conditions

Almost the same as the query above, but you can use AND and OR. For example we have a 3-column table with the following info each column:

Column A: Name

Column B: Type

Column C: Value

We want to get all entries whose values in the third column is more than 15 and the type is type A. The resulting query is

SELECT * where C>15 AND B=’A’

## FAQs

### What are the commands you can use with the QUERY function?

Below is a table of a list of commands you can use with the QUERY function. To learn more about each function, click the specific command.

Selects the column/s to analyze. Examples:

To select the first two columns in the range: SELECT A,B

To select all columns from the source: SELECT *

Returns rows that match the conditions defined after selecting the columns.

AND:  Returns rows where both conditions are met. Syntax: Condition1 AND condition2

OR returns rows that match at least one declared condition. Syntax: Condition1 OR condition2

NOT returns rows that do not match the given condition or conditions. Syntax: NOT Condition1

ORDER BY

Arranges the rows using the values stored in a selected column. Example:

Arrange the rows by the values in the third column: ORDER BY C

GROUP BY

Aggregates rows by the values in the specified column. Example:

To aggregate the rows using the values in column B: GROUP BY B

Limits the results to the first N rows. Example:

Display only the first 10 entries LIMIT 10

OFFSET

Skips the first N rows in the output. Example:

Display the rows, skipping the first 10 entries: OFFSET 10

MIN, MAX

Finds the minimum or maximum value in the selected column. Works as a function, with the column enclosed in parenthesis. Example:

Display the maximum and minimum value in the third column: MAX(C), MIN(C)

The values are displayed into separate cells

COUNT, AVG, SUM

COUNT(): Calculates the total number of rows with values in the selected column

AVG(): Calculates the average value of the selected column

SUM(): Calculates the total value of the selected column.

Example:

Calculate the total number of entries, the average value, and the sum of the values in column A

COUNT(A), AVG(A), SUM(A)

The values are displayed into separate cells

Lists rows whose cell in the selected column contains the string. The string can be described using wildcards. Example:

List rows whose first column contains a given string

WHERE A LIKE ‘string’

STARTS WITH, ENDS WITH

Lists rows whose cell in the selected column either starts with or ends with the specified string, respectively. Example:

List rows whose first column starts with a given string

WHERE A STARTS WITH ‘string’

MATCHES

Lists rows whose cell in the selected column has the exact string specified. Example:

List rows whose first column has the exact given string

WHERE A MATCHES ‘string’

CONTAINS

Lists rows whose cell in the selected column contains the string specified as part of its value. Example:

List rows whose first column contains a given string

WHERE A CONTAINS ‘string’

LABEL

Inserts a header row containing the label/s for the selected columns. Example:

LABEL A ‘string’

DATE, DATETIME

Used to convert string input to the command to date and datetime format used in Google Sheets. Example:

List all entries whose date stored in the first column is date:

WHERE A CONTAINS DATE ‘date’

PIVOT

Used to transform distinct values in selected columns into new columns, behaves much like Pivot Table in Google Sheets. Usually combined with GROUP BY. Example:

List the sum of the values stored at the second column per unique entry in the first column:

SELECT A, SUM(B) GROUP BY A

FORMAT

Sets the format of the cells in the selected row. Only modifies the output. Example:

Formats the dates stored in the first column to yyyy-mm-dd:

FORMAT A ‘YYYY-MM-DD’

### Can We Combine QUERY with Import Functions in Google Sheets?

Absolutely! For example, we can combine QUERY with IMPORTRANGE, a powerful function that can be used to consolidate data from different spreadsheets to a single one. The formula is

=QUERY(IMPORTRANGE(sheet_url,data_range), query)

Where

Sheet_url is the URL of the Google Sheets spreadsheet you want to import and query through QUERY function;

Data_range is the range of the table stored in the sheet_url. This includes the sheet name; and

query is the command you want to use to analyze the table.

All these three inputs must be enclosed in double quotes.

For example, we have the following table stored in Spreadsheet A, on the sheet named Sheet2:

We want to query only the entries where the total amount is more than 14. The formula becomes:

=QUERY(IMPORTRANGE(sheet_url,”Sheet2”), "SELECT * WHERE D>14")

You can combine two or more sources to the same QUERY function. Here is the formula, for the case of three ranges:

=QUERY({IMPORTRANGE(sheet1_url,data_range1),IMPORTRANGE(sheet2_url,data_range2),IMPORTRANGE(sheet3_url,data_range3)}, query)

Where

Sheet1_url, sheet2_url, and sheet3_url are the URLs of the Google Sheets spreadsheets you want to import and query through QUERY function;

Data_range1, data_range2, and data_range3 are the ranges of the tables stored in the Sheet1_url, sheet2_url, and sheet3_url, respectively. This includes the sheet name; and

query is the command you want to use to query the combined tables.

You can also use it to import several sheets from the same spreadsheet URL by specifying the same URL for each time.  Learn more about these here.

# Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL