Google Sheets’ Query Language

Many of us at Atomic Object leverage spreadsheets for various purposes (estimates, hours tracking, finances, etc.), and since we have strong technical backgrounds, we tend to leverage a lot the functions that spreadsheets provide (avg, max, min, ceiling, sum, etc.). We also tend to push the boundaries of spreadsheets by leveraging multiple functions in one cell and doing some complex filtering.

Effectively, many of us are trying to analyze data in the same way we would analyze data in a database leveraging SQL. Historically spreadsheet applications did not have a query language to support easier data analysis. But recently, I stumbled across Google Sheets’ “QUERY”:https://support.google.com/docs/answer/3093343?hl=en function, which supports querying a spreadsheet dataset using SQL.

h2. When to Use QUERY

The query language provides a lot of the same functionality that already exists via spreadsheet functions like AVG, MAX, MIN, SUM, etc. In many cases it won’t provide any more value than those functions and is more verbose than the built-in spreadsheet functions. However, when the analysis requires filtering, grouping, pivoting, and ordering, the query language is a more effective and succinct approach than spreadsheet functions.

h2. The QUERY Function

The QUERY function takes 2 required parameters: the dataset to query (A2:E6, for example), and a text (string) to represent the query itself. The query language is analogous to SQL, but with a limited function set and no need to use the “FROM” keyword since the data set is already defined. You can learn more about the QUERY language “on the Google Developers site”:https://developers.google.com/chart/interactive/docs/querylanguage#Options.

h2. QUERY Example

Below is an example spreadsheet I put together with a dataset that represents salaries across different ages and professions. To the right of the dataset there are the the following statistics that leverage the QUERY function:

* Average salary of individuals between the age of 20 and 30
* Average salary of individuals between the age of 30 and 40
* Average salary by profession
* Average age by profession
* Ranking of individuals by salary