Google Sheets QUERY Function

Three of my favorite things are eating QUERY functions and not using commas.

In all seriousness, I am downright obsessed with this function. I realized that I might have a problem when yesterday was the second time in as many weeks that I had a 1:1 with a customer that was entirely around writing QUERY functions. Let’s break this bad boy down.

Google has their official help article here. Today I want to dive in and talk about two of the ways I use this function IRL.

QUERY Syntax

QUERY is… unique. We’ll leave it at that. If you’re an old pro at writing complex functions in Excel or Sheets, QUERY will feel odd at first. It allows you to combine a million functions into one using the “Google Visualization API Query Language.” Say that five times fast.

Basically, instead of using a multi-argument FILTER function, I can write a simple statement that pulls all the data I need.

Formal Syntax: =QUERY(data, query, [headers])

• data: range you’re referencing
• query: statement telling which parts of the data you want
• headers: optional numeric value dictation how many header rows your data has (I usually set my range so this value is 0)

The query portion is where all the heavy lifting is done. This is a single statement that combines every function you’re looking for. It’s basic syntax is:
“select columns where arguments”

For the columns, these are usually the characters (A, B, etc.). If you want to pull all the columns from your data range, you can use the * character instead of typing them out.

For the arguments, the phrases follow somewhat as you would expect. For example, if I want to filter the data for where column B of my range contains the word Yes, I would write “select columns where B contains ‘Yes'”. Common operators include:

• contains for when you’re looking for the MATCH or FIND function
• =, >, < for when you want an exact match, greater than, or less than
• <= for when you want less than or equal to
• <= for when you want greater than or equal to
• != for when something doesn’t equal (same as <> operator)
• not column for when you want a more generic not statement (usually to use with the contains operator to create a “does not contain” statement)
• and for when you want multiple conditions
• or for when you want alternative conditional arguments
• starts with for when you want to check the beginning of a string
• ends with for when you want to check the ending of a string

Syntax analysis is all good and well, but let’s examine this function first with a use case.

Case 1: Survey Response Data

Real talk: I have a thing about touching raw data. Being that I use a decent number of Google Forms for simple surveying, I run into the issue of having all this data come in and not being able to interact with it out of fear of messing it up. Yes, yes, I know all about Revision History, but that doesn’t do anything to abate Excel-embedded fear of messing up raw data.

Using QUERY, I can pull curated lists of data into a separate sheet for analysis.

A sample of this function is located in Drive here if you want to follow along with the breakdown.

In the Summary sheet, there are three QUERY functions:

Book Club Emails in cell A2: QUERY( ‘Form Responses 1’!B:C, “select B where C contains ‘Yes'”)

• ‘Form Responses 1’!B:C – selecting the form data minus the timestamp
• “select B where C contains ‘Yes'” – displaying only the email addresses where the response in column C includes the word “Yes”

Book Delivery Emails in cell C4: QUERY(‘Form Responses 1’!B:C, “select B where C contains ‘physical'”)

This one is the same as A2 except we’re now looking for emails where they responded that they need a physical copy of the book.

Total Books to Order in cell C2: COUNTA(QUERY(‘Form Responses 1’!C:C, “select * where C contains ‘physical'”,-1))

• ‘Form Responses 1’!C:C – selecting the response column of the form data
• “select * where C contains ‘physical'” – displaying only the responses where they asked for a physical copy (same as Delivery Emails above)
• -1 – acknowledging the header row
• COUNTA wrap – instead of displaying the list of people who ordered a physical book, counting the number of responses

Neat, but not necessarily life-changing depending on the work you do. Let’s go deeper by looking at how QUERY can be used to create sub-sheets from a master.

Case 2: Master Sheet & Sub-sheets

One of the biggest uses I find for QUERY is creating sub-sheets (usually printable) that are based off of one master sheet. The most excellent part is that when I update the master, the sub-sheets automatically update. No more copy/paste tedium!

The sample sheet is here. This sheet contains a master volunteer sheet for game day operations. The sub-sheets are then broken out using QUERY.

Carts-Roster sheet: Pulls out all the non-VIP cart volunteers and their complete assignments. This sheet would be printed and given to the supervisor.

VIP-Roster sheet: Pulls out all the VIP cart volunteers (printed sheet for supervisor).

Booth-Roster sheet: Pulls out the booth assignments for volunteers.

Gate-Coverage sheet: This is where things get interesting. We have a stacked array QUERY: {QUERY(…);QUERY(…);…} all enclosed in a UNIQUE function. The queries:

• QUERY(Master!A5:F,”select A,C,D,E,F where B = ‘Booth'”,0) – anyone that has a booth assignment
• QUERY(Master!A5:F,”select A,C,D,E,F where D contains ‘Gate'”,0) – anyone that moves outside of a gate during the game
• QUERY(Master!A5:F,”select A,C,D,E,F where E contains ‘Gate'”,0) – anyone that is outside of a gate after the game

These three queries stack on top of one another creating a comprehensive list of absolutely everyone that has gate duty for this particular game. We wrap the stacked queries in a UNIQUE function to eliminate any duplicate names.

In Short

The QUERY function is top notch. In the next post I will take a look at how to combine QUERY with the IMPORTRANGE function to connect data from one Google Sheet to another. See you then!

Cheers,
Annie