The QUERY function is a built-in function that allows you to perform a query over an array of values using the Google Visualization API Query Language.
The QUERY input
Range - A range of cells. For examples: A1:B6, data!B2:H6, ImportRange(spreadsheet_key, [sheet!]range)
The QUERY's input is a list of rows where the first zero or more rows are headers. Each column of the input can only hold values of the following supported types:
- 'boolean' - boolean value ('true' or 'false'). Example value: v:'true'
- 'number' - number value. Example values: v:7 , v:3.14, v:-55
- 'string' - string value. Example value: v:'hello'
- 'date' - date value excluding the time. Example value: v: '08/21/2009'
- 'datetime' - date value including the time. Example value: v: '08/21/2009 12:23'
- 'timeofday' - time of day value. Example values: v: '12:23'
The type of each column is determined by the majority of cells. Values of the wrong type are considered as null values.
Query String - A query string for applying data operations. The query operates on column IDs directly from the input range and uses a subset of the SQL language. For example, "select E," "select A , B," "sum(B),C group by C," "select D where D < 'Nick' ."
Notes
- Spaces are allowed.
- A list of identifiers should be separated by commas.
- If you need to refer to a column that shares a name with a SQL keyword, like BY, surround the column title with the ` character (the key next to the '1' key). For example, [ "select `BY`" ].
For more information, read on the Google Visualization API Query Language.
Headers (optional) - A number specifying the number of header rows in the input range. If omitted, or set to -1, the number of header rows is guessed from the input range. This parameter enables transformation of multi-header rows range input to be transformed to a single row header input which the QUERY supports.
For example:
|
|
The QUERY output
It's an array of values. These values are the result of the query execution on the array of values. The array structure consists of a single column labels row followed by a list of data rows. Labels may be blank, and if all are blank, the labels row is omitted.
In case of error, you'll see an error in the formula cell and a message will be added in the cell annotation. For example:
#VALUE! - error: Invalid query: Cannot use PIVOT when no aggregations are defined in SELECT.
#VALUE! - error: Invalid query: Column [K] does not exist in table.
Column IDs - the column ids from the underlying sheet range. For example: A,B,...,AA,..
Full example: =QUERY(A1:E6, "select avg(A) pivot B")
Here's an example: