Search
Clear search
Close search
Google apps
Main menu

QUERY function

Runs a Google Visualization API Query Language query across data.

The 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]).

Part Description Notes
data The range of cells to perform the query on.
  • Each column of data can only hold boolean, numeric (including date/time types), or string values.
  • In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.
query The query to perform, written in the Google Visualization API Query Language.
[headers] The number of header rows at the top of data.
  • Headers are optional.
  • If omitted or set to -1, the value is guessed based on the content of data.

Sample formulas

QUERY(A2:E6,"select avg(A) pivot B")

QUERY(A2:E6,F2,FALSE)

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

Issue

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. 

Solution

Set the column number formatting to plain text

Formatting dates in a query

Issue

When a QUERY formula 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. 

Solution

If you use a date in a query, use the YYYY-MM-DD format. For example, 2017-12-31.

Use the 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

There are two ways to reference a column in the QUERY function’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,  =QUERY(A:E,”select B…”)) “select B where C = ‘potato’”
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,  =QUERY({A:C,J:L},”select Col4…”)). “select Col1 where Col2 = ‘banana’”

Using IMPORTRANGE

Nesting an IMPORTRANGE inside a query will generate an error unless the IMPORTRANGE is run alone first to enable it to be authorized.

Examples

Example descriptions:

  • select & where: Returns rows that match the specified condition using Select and Where clauses.
  • group by: Aggregates Salary values across rows using Select and Group by clauses.
  • pivot: Transforms distinct values in columns into new columns.
  • order by: Aggregates Dept values across rows and sorts by the maximum value of Salary.
  • 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.

Was this article helpful?
How can we improve it?