Back to Blog

Google Sheets Query Function: Everything you Need to Know in One Article

What is Query in Google Sheets? It’s a function that grabs the data based on criteria and, if necessary, amends the formatting, performs extra calculations, changes the order of columns, etc. As a result, your data source stays unchanged, and your working sheet has the selection of columns and rows that you need to complete the task.

The Google Sheets Query function allows users to perform various data manipulations. For instance, it becomes very handy when you need to prepare data in a special format to be able to use it for building certain types of visualizations. Your data source may include too much information or be unsuitable for the specific chart formatting, or column order. 

Many users state that it is very similar to SQL. So, if you already have the knowledge of this language, it will be much easier for you to comprehend this function. If not – don’t worry, we have put lots of effort into this article to turn it into the ultimate beginner guide that covers the majority of Google Sheets Query related questions you may have.

A slight spoiler at the beginning of the article – I’ll be explaining every query string separately and point to this Google Sheets spreadsheet to show how it actually works.

Google Sheets Query function: syntax

So, open your Google Sheets document along with the above-mentioned spreadsheet in the neighbour browser tab and let’s start our journey by looking at the syntax of the Google Sheets Query function.

So, the basic syntax is as follows:

= QUERY(data, query, [headers])

where

  • data – a set of cells that you want to request Google Sheets to perform an inquiry on.
  • query – a string that contains an inquiry composed using the Google API Query Language. Don’t forget to wrap your query into double quotation marks, or just refer to a cell with the inquiry written in the Google Query language. 
  • headers – an optional part of the Query formula to define the number of heading rows in your data set. 

What are Literals in Google Sheets Query Function?

Let’s look at the types of literals that exist in the Google API Query Language.

Literals are the various types of values you input into a spreadsheet. 

They can be:

  • Strings – the text values which are put into single/double quotes. Note that they are case-sensitive.

For example: "first day"  'one person' "Burger"

  • Numbers – numerals used in decimal notation.

For example: 1  2.5 7.15 -20.0   .8

  • Date/time – this type of literal includes: 1) the word DATE and the value in the yyyy-MM-dd format; 2) the word TIMEOFDAY and the value in the HH:mm:ss[.SSS] format; 3) the word TIMESTAMP or DATETIME and the value in the yyyy-MM-dd HH:mm:ss[.SSS] format.

Note: every column can have only one type of literal: string or numeric (which contains numbers and date/time) values. If a given column includes more than one type of literal, then Google Sheets will pick the data type that is used more frequently for this column to execute the Query function on. 

Introduction to the Data Set Used in Examples

To be able to master your Query skills, we are going to use an Airtable database that I imported to Google Sheets. I used Coupler.io add-on to fetch data from Airtable and push it to a spreadsheet. I have chosen this tool because it makes data import easier than the regular approach when you need to download an Airtable table as a CSV, and then manually upload it to Google Sheets. In addition, I set up a schedule for automatic refreshes of the information in my worksheet to run on an hourly basis. Besides Airtable, Coupler.io can help you link Xero, Pipedrive, HubSpot and other systems to Google Sheets.

Airtable importer screen
Adding Airtable Importer screen

Let’s imagine that we have a small online business that sells sandwiches. The below screenshot shows all orders that we have recently had, including the date of order, customer names, customer id, product name and id, price, the number of items sold, and the total price paid by a client.

After applying some magic, I pulled all data from Airtable to Google Sheets.

If you are interested in seeing a step-by-step guide of how I did it, feel free to read this Connect Airtable to Google Sheets article.  

Here is a screenshot of the data set I am going to use to explain the Query function in Google Sheets on real examples.

Google Sheets Query Clauses

The Google API Query Language includes nine clauses, where each of them has a unique intended purpose. They are optional, meaning that you don’t have to include all of them into one query.

One query string may contain several space-separated clauses that have to be written in this order: 1) SELECT, 2) WHERE, 3) GROUP BY, 4) PIVOT, 5) ORDER BY, 6) LIMIT, 7) OFFSET, 8) LABEL, 9) FORMAT.

Keep reading to learn more about these nice clauses and see the examples that accompany them.

Google Sheets Query: Select

The SELECT clause allows defining the columns you want to fetch and the order in which you want to organize them in your new worksheet. If the order is not specified, the data will be returned “as is” in a source spreadsheet.

One can use column IDs (the letters located at the top of every column in a spreadsheet), reference columns as Col1, Col2 and so on in the numerical sequence, or the results of arithmetic operators, scalar or aggregation functions as elements to order in this clause.

Note: if you are planning to embed Query into more complex formulas, we recommend referencing columns as Col1, Col2 and so on in the numerical sequence. If you choose this option, then the data argument from the general Query syntax = QUERY(data, query, [headers]) has to be enclosed in curly brackets {data}. 

Note: navigate to the Data Manipulation with Google Sheets Query section to read about arithmetic operators, scalar and aggregation functions.

Google Sheets Query SELECT All Example

In our case, the ready to use formula will read:  

=query('data from Airtable'!A:L,"select *")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select *" – select all information in the above mentioned data set

I will illustrate every case with an example and place it in a separate sheet of this document. Here is the tab with the Select all example.

Note: if you omit the header element, the returned data will include the heading row; if you type “0”, the header won’t be displayed at all.

The same action may be carried out via Coupler.io, which can pull all data from another sheet or spreadsheet to your current document. Check out How to Reference another Spreadsheet article, which gives an explanation of how you can set up this connection.

Google Sheets Query SELECT Multiple Columns Example

If a user wants to fetch one or multiple columns, one needs to define them by a column ID.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, E, I")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, E, I" – pull all data from the columns C, E, I

Here you may find the tab with the Select multiple columns example.

Google Sheets Query SELECT Multiple Sheets Example

If you need to query multiple sheets in Google Sheets, meaning that you want to select data from several different tabs of a spreadsheet, then feel free to use the below example:

=query({'data from Airtable'!A1:L; Sheet1!A1:L; Sheet2!A1:L}, "select * where Col1 is not null")

where

  • {'data from Airtable'!A1:L; Sheet1!A1:L; Sheet2!A1:L} – an array formula enclosed into curly brackets which includes the list of sheets I want to pull data from, separated by semicolons. 
  • "select * where Col1 is not null" – pull all data where the contents of the rows in column 1 (column A, Order ID) are not empty. Continue reading this article to learn more about the Where clause, as well as “is null” and  “is not null” operators.

Here you may find the tab with the Select multiple sheets (tabs) example.

Note: if you want to query some data from another spreadsheet, then I would recommend you using a combination of QUERY and IMPORTRANGE.

Google Sheets Query: WHERE

Users apply WHERE when they need to pull specific rows from the columns, they have already identified in the SELECT clause, which satisfies one or more conditions.

To compare values across rows, one needs to be aware of these basic operators which accompany the WHERE clause.

OperatorMeaning
<=Less than or equal 
<Less than
>More than
>=More than or equal
=Equal
!= or <>Not equal
Google Sheets Where basic operators

WHERE Basic Operators Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, E, I WHERE I>=40")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, E, I WHERE I>=40" – pull the data from those columns C, E, I, where the value in column I (total price) is more than or equals 40.

Here you may find the tab with the WHERE basic operators example.

Note: if you want to say that the cell is empty or its contents equal 0 – use the is null operator, and if you want to select rows which are not empty – then type is not null.

WHERE Combined Conditions Example

One can combine several conditions using and, or, and not as part of the WHERE clause in the query.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, E, I WHERE I>=40 and not E='Denver sandwich'")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, E, I WHERE I>=40 and not E='Denver sandwich'" – pull the data from those columns C, E, I, where the value in column I (total price) is more than or equals 40 and where the string in column E (product) does not include the Denver sandwich.

Here you may find the tab with the WHERE combined conditions example.

WHERE Advanced Operators Example

Use these advanced operators to run more complex queries:

OperatorMeaning
starts withCompares the value with the condition and searches for full correspondence in the prefix or at the beginning of the string.
ends withCompares the value with the condition and searches for full correspondence in the suffix or at the end of the string.
containsCompares the value with the condition and searches for the presence of it in any part of the string (be it at the beginning, in the middle or at the end of the argument).
matchesThis match is performed via the usage of regular expressions enclosed in single quotation marks.
likeCompares the value with the condition expressed by the usage of two arguments: 1) % – is used when there may be either no characters, one or multiple ones of any type and kind; 2) _ (underscore) – is used when there can be only one single character of any kind.
Google Sheets Where advanced operators

In my case, the ready to use formula will read 

=query('data from Airtable'!A:L,"select C, E, I WHERE E starts with 'C' and C like 'K%'")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, E, I WHERE E starts with 'C' and C like 'K%'" – the string pulls the data from those columns C, E, I, where the value in column E (product) starts with the “C” letter and where the string in column C (customer name) starts with the “K” letter.

Here you may find the tab with the WHERE advanced conditions example.

Google Sheets Query: GROUP BY

This clause is used to group values across the selected data range by a certain condition.

Note: the columns that you mention in the SELECT clause must be present in either GROUP BY clause or as part of the aggregation function (e.g. avg, count, max, min, sum).

SELECT One Column and GROUP BY Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, sum(I) Group by C")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, sum(I) Group by C" – the string sums purchases (column I) and group them by customer names (column C).

Here you may find the tab with the SELECT one column and Group By example.

SELECT Multiple Column and GROUP BY Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, H, sum(I) Group by C,H")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, H, sum(I) Group by C,H" – the string pulls the data from columns C and H, sums purchases (column I) and groups data by customer names (column C).

Note: when using this formula, specify all columns that you defined in the Select clause  in the Group by clause as well. The output will be grouped by the first column ID you mention in the Group by clause.

Here you may find the tab with the SELECT multiple columns and Group By example.

Google Sheets Query: PIVOT

Using this clause one can convert rows into columns, and vice versa, as well as aggregate, transform and group data by any field.

Note: the columns that you mention in the SELECT clause must be present in either GROUP BY clause or as part of the aggregation function (e.g. avg, count, max, min, sum).

PIVOT without GROUP BY Example

If rows of the pivot columns contain the same values, the PIVOT clause will aggregate them. So, if you don’t use GROUP BY as part of the PIVOT clause, in a result you will get a table with one row only.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select sum(G) Pivot E")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select sum(G) Pivot E" – the string sums prices of all burgers sold (column G) and groups them by the product (column E).

Here you may find the tab with the PIVOT without Group By example.

PIVOT with GROUP BY Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, sum(G) Group BY C Pivot E",1)

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, sum(G) Group BY C Pivot E" – the string returns a PIVOT table which has the names of burgers (column E) in the heading row, and the list of customers (column C) as the main column, showing which burgers customers bought and how much they paid (column G). 

Here you may find the tab with the PIVOT with Group By example.

Google Sheets Query: ORDER BY (ascending or descending)

This clause allows one to sort data across columns in ascending (ASC) or descending (DESC) order.

One can use column IDs or the results of arithmetic operators, scalar or aggregation functions as elements to order in this clause. 

ORDER BY Ascending Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select * where A is not null order by A")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select * where A is not null order by A" – the string pulls all data and orders it by order ID (column A) in ascending order.

Note: it is crucial to add is not null to the string to make sure the output does not account for empty cells and bring them all up in your table.

Here you may find the tab with the Order by ASC example.

ORDER BY Descending Example

If rows of the pivot columns contain the same values, the PIVOT clause will aggregate them. So, if you don’t use GROUP BY as part of the PIVOT clause, you will get a table with one row only.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:I=L,"select * order by A DESC")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select * order by A DESC" – the string pulls all data and orders it by order ID (column A) in descending order.

Here you may find the tab with the Order by Descending example.

Google Sheets Query: Limit + Example

The Limit clause reduces the quantity of rows that is pulled from another sheet. 

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select * Limit 5")

where

  • 'data from Airtable'!A:L – the data range to query on
  • select * Limit 5" – the string pulls all data and limits the returned result to the first 5 rows + the header.

Here you may find the tab with the Limit clause example.

Google Sheets Query: OFFSET 

Using this clause you may ask Google Sheets to skip a pre-defined number of rows from the top of your data source spreadsheet. 

OFFSET Only Example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select * Offset 10")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select * Offset 10" – the string pulls all data and skips the first 10 rows excluding the header.

Here you may find the tab with the Offset only example.

OFFSET Accompanied by LIMIT Example

If OFFSET is combined with the LIMIT clause, though it follows LIMIT in the syntax, it will apply first.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select * Limit 5 Offset 10")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select * Limit 5 Offset 10" – the string pulls all data, skips the first 10 rows, and limits the result to 5 rows excluding the header.

Here you may find the tab with the Offset and Limit example.

Google Sheets Query: Label

The LABEL clause allows you to assign a name to a heading field of one or multiple columns. However, you won’t be able to apply it instead of a column ID in a query string.

One can use column IDs or the results of arithmetic operators, scalar or aggregation functions as elements in this clause. 

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select * label C 'customer', E 'Burger', I 'Total paid'")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select * label C 'customer', E 'Burger', I 'Total paid'" – the string pulls all data, and gives columns C, E and I new labels.

Here you may find the tab with the Label example.

Google Sheets Query: Format

Users apply the FORMAT clause to format NUMBER, DATE, TIME, TIMEOFDATE, and DATETIME values for one or multiple columns.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select B, G, I, J label J 'Hour' format B 'dd-mmm-yyyy', G '##.00', I '##.000', J 'HH'")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select B, G, I, J label J 'Hour' format B 'dd-mmm-yyyy', G '##.00', I '##.000', J 'HH'" – the string pulls the data from columns B, G, I, and J, formats the date in B column, the number in G and I columns, and the time in J column, also changing its label to ‘Hours’.

Here you may find the tab with the Format example.

Data Manipulation with Google Sheets Query

The Google Visualization API query language specifies the three core functions and operators which are called to help you manipulate your data:

  1. Arithmetic operators
  2. Aggregation functions
  3. Scalar functions

Arithmetic operators + Example

These operators help users execute basic calculations. They include + (plus), - (minus), / (divide), * (multiply), where the parameters are two numbers and the result the Query function returns is a number as well.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select C, I, G*H label G*H 'Arithmetic multiplication'")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select C, I, G*H label G*H 'Arithmetic multiplication'" – the string pulls the data from columns C, I, multiplies the value in column G by the number in column H, and changes the label of the column with multiplication to ‘Arithmetic multiplication’.
  • Note, that the pulled from the data source sheet value in column B equals the calculated result shown in column C.

Here you may find the tab with the Arithmetic operators example.

Aggregation Functions + Example

These apply to one column ID and execute an operation across data in all rows of this specific column. Usually, aggregation functions appear in the SELECT, ORDER BY, LABEL, and FORMAT clauses. Additionally, they also may refer to a data set formed as part of the PIVOT or GROUP BY clauses.

Note: they cannot be used as part of these clauses: WHERE, GROUP BY, PIVOT, LIMIT, or OFFSET.

The aggregation functions include the following categories:

1) Those where the supported column type is a number and the result is a number as well:
avg()Provides the average of all numbers in a column.
sum()Provides the sum of all numbers in a column.
2) Those where there can be any column type and the result is a number:
count()Provides the quantity of items in a column (rows with empty cells are not calculated).
3) Those where there can be any column type and the result is going to be the same as the column type. In this case, earlier dates will be lesser than the later ones; and the text values are lined up in alphabetical order, where case-sensitivity is considered as well:
max()Provides the maximum value of all in a column.
min()Provides the minimum value of all in a column.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select min(B), count (C), max(I), avg(G), sum(I)")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select min(B), count (C), max(I), avg(G), sum(I)" – the string fetches the minimum value from B column, counts the number of items in C column, pulls the maximum value from I column, calculates the average of the G column contents, and sums up the numbers in I column.

Here you may find the tab with the Aggregation functions example.

Scalar Functions + Examples

Scalar functions are used to convert a given parameter into another value.

Note: if you use one of the Scalar functions, the heading cell of the column will be amended.

One may use these functions as part of the SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LABEL and FORMAT clauses. 

Below I have split the functions into groups by the required parameters and the types of values they return.

  1. These functions support a single parameter of type DATE or DATETIME and return a number as a result:
Function nameWhat it does
year()Fetches the year from a DATETIME/TIMESTAMP or DATE value.
month()Fetches the month from a DATETIME/TIMESTAMP or DATE value on a scale from 0 to 11, where January equals 0, February is 1, and so on finishing with December being 11.
day()Fetches the day of the month from a DATETIME/TIMESTAMP or DATE value.
quarter()Fetches the quarter from a DATETIME/TIMESTAMP or DATE value on a scale from 1 to 4, where 1 corresponds to the first quarter, 2 to the second, and so on.
dayOfWeek()Fetches the day of the week from a DATETIME/TIMESTAMP or DATE value on a scale from 1 to 7, where 1 corresponds to Sunday, 2 to Monday, and so on.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:L,"select year(B), month(B), day(B), quarter(B), dayofweek(B)")

where

  • 'data from Airtable'!A:L – the data range to query on
  • "select year(B), month(B), day(B), quarter(B), dayofweek(B)" – the string fetches the year, month, day, quarter and day of the week from B column (date of order).

Here you may find the tab with the Scalar DATE parameter example.

  1. These functions support a single parameter of type DATETIME or TIMEOFDAY and the result is a number:
Function nameWhat it does
hour()Fetches the hour from a DATETIME/TIMESTAMP or DATE value. 
minute()Fetches the minute from a DATETIME/TIMESTAMP or DATE value.
second()Fetches the second from a DATETIME/TIMESTAMP or DATE value.
millisecond()Fetches the millisecond of the week from a TIMEOFDAY or DATETIME/TIMESTAMP value.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:K,"select hour(K), minute(K), second(K), millisecond(K)")

where

  • 'data from Airtable'!A:K – the data range to query on
  • "select hour(K), minute(K), second(K), millisecond(K)" – the string fetches the hour, minute, second, and millisecond from K column (date/time 2).

Here you may find the tab with the Scalar DATETIME parameter example.

  1. These functions support a single parameter of type String and the result is a String as well:
Function nameWhat it does
upper()Converts the string value by replacing all letters with the uppercase ones.
lower()Converts the string value by replacing all letters with the lowercase ones.

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:I,"select lower(C), upper(C)")

where

  • 'data from Airtable'!A:I – the data range to query on
  • "select lower(C), upper(C)" – the string fetches data from the C column (customer name) and converts all information to lower and upper cases.

Here you may find the tab with the Scalar Lower and Upper parameter example.

  1. This function supports two parameters of type DATE or DATETIME (can be any one of these two) and the result is a number:
Function nameWhat it does
dateDiff()Calculates the difference between the two DATE / DATETIME / TIMESTAMP values and displays the result as a number of days. Please note that time value is not taken into consideration during the calculation.

Difference between two dates example

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:K,"select dateDiff(B,K) label dateDiff(B,K) 'Difference between two dates, days'")

where

  • 'data from Airtable'!A:K – the data range to query on
  • "select dateDiff(B,K) label dateDiff(B,K) 'Difference between two dates, days'" – the query string calculates the difference in days between the dates in column B and K (B-K) and changes the label of the column respectively.

Here you may find the tab with the Calculation of date difference between two dates example.

Difference between the date of order and the current date

In order to calculate the difference between a given date and the present time, we will need to familiarize ourselves with the now() function first.

It does not require any parameter and returns a DATETIME as a result:

Function nameMeaning
now()Displays the current DATETIME value using the GMT time.

Here is a tab in our spreadsheet where you can see the current date and time.

The ready to use formula computing the difference is as follows:

=query('data from Airtable'!A:K,"select dateDiff(B,now()) label dateDiff(B,now()) 'Difference between date and now,days'")

where

  • 'data from Airtable'!A:K – the data range to query on
  • "select dateDiff(B,now()) label dateDiff(B,now()) 'Difference between date and now,days'" – the query string calculates the difference in days between the date in column B and now (current date and time) and changes the label of the column respectively.

Here you may find the tab with the Calculation of date difference between date and now example.

  1. This function supports one of the parameters at a time: a DATE, DATETIME, or a NUMBER and returns a DATE:
Function nameWhat it does
toDate()Converts a DATE, DATETIME, or a NUMBER value into a DATE: If a given parameter is a DATE – the returned value will be the same DATE value If a given parameter is a DATETIME – the returned value will be the DATE only If a given parameter is a NUMBER –  the returned value will be the DATE calculated as the number of milliseconds after 01.01.1970 00:00:00 GMT (the Epoch). 

In my case, the ready to use formula will read: 

=query('data from Airtable'!A:K,"select toDate(K)")

where

  • 'data from Airtable'!A:K – the data range to query on
  • "select toDate(K)" – the query string returns the date value from the DATETIME parameter.

Here you may find the tab with the Scalar toDate example.

It is the beginning, not the end

As you can see, the Google Sheets Query function has lots of capabilities that you can use across multiple documents to cover different use cases. The main aim of this article was to explain the general Query syntax, backing it up with simple examples.

Understanding the broadness of the topic, I encourage you to leave your questions in the comments section below, so our team can answer them, and provide ready-to-use advice for your specific cases.

If you find the article useful, feel free to share it with your friends and colleagues!

Back to Blog

Access your data
in a simple format for free!

Start Free