Runs a Google Visualization API Query Language query across data.
QUERY function offers a short, readable way to analyze data. This function uses the principles of Structured Query Language (SQL) to do searches.
Parts of a QUERY formula
A QUERY formula is formatted as
=QUERY(data, query, [headers]).
||The range of cells to perform the query on.||
||The query to perform, written in the Google Visualization API Query Language.||
||The number of header rows at the top of data.||
QUERY(A2:E6,"select avg(A) pivot B")
Troubleshoot problems with your formula
Try following these best practices:
- Don’t include commas between select clause functions. For example,
Select B Where A is not null order by B label B ‘Student’.
- Include the third argument (
headers) in your formula. If you don’t use this argument, you may see unexpected results.
Missing data when you query text and numbers
Use a single data type (text or numbers), when using the
QUERY function. If you have mixed data, the query will miss data.
When there is mixed data in a column:
- The most common data type will be the data type for the query.
- The least common data type will be considered null values and will not be counted in the query.
Set the column number formatting to plain text.
Formatting dates in a query
QUERYformula refers to a date in a cell, the date is taken as a datetime number and won’t work in comparison unless it is formatted as YYYY-MM-DD.
If you use a date in a query, use the YYYY-MM-DD format. For example, 2017-12-31.
TEXT function to format dates like this:
=query(Sheet1!D1:Z, "select D, F, Z where toDate(D) > date '" & text(A1, "yyyy-mm-dd") & "' ", 1)
Referring to columns
QUERYfunction’s second argument. For most cases, you will reference a column by the column letter.
|Reference||When to use||Example|
|By the letter at the top of the column.||When the source data is an explicitly referenced range (for example,
|By the column index (number of columns from the left) within the data.||When the source data is an array that has been generated/computed (for example,
IMPORTRANGEinside a query will generate an error unless the
IMPORTRANGEis run alone first to enable it to be authorized.
- select & where: Returns rows that match the specified condition using
- group by: Aggregates
Salaryvalues across rows using
- pivot: Transforms distinct values in columns into new columns.
- order by: Aggregates
Deptvalues across rows and sorts by the maximum value of
- headers: Specifies the number of header rows in the input range, which enables transformation of multi-header rows range input to be transformed to a single row header input.
Mary is a Docs & Drive expert and author of this help page. Leave her feedback below about the page.