The Google Sheets Query function replaces so many other spreadsheet functions it’s not even funny – FILTERs, AVERAGEs, and SUMs all go out the window when it enters the picture.
There are a few nuances to queries though, so let’s go step-by-step. This post contains eight videos, with cliff’s notes embedded beneath each:
Let’s dive in!
Btw…if you’re looking to make the jump from Sheets to BigQuery, check out our BigQuery quickstart Recipes over at query.recipes.
QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER.
In this example, we’ll walk through a few QUERY examples, to analyze a sampling of Twitter data.
The basic structure of a QUERY is to input:
We combine them like so:
=query( ‘data tab’!A:C, “select A where C = ‘twitter’”)
Q: What was the average number of retweets from the sample?
A: =query(D1:G, “select avg(G)”)
Q: What was the average number of retweets on Tweets that contained an @mention but weren’t a retweet?
A: =query(D1:G, “select avg(G) where E contains ‘@’ and not E contains ‘RT’”)
Q: How many retweets did each user in the list receive?
A: =query(D1:G, “select F, sum(G) where E != ‘’ group by F label sum(G) ‘total retweets’”)
Why so complex in Sheets queries – shouldn’t this be easier?
When I first started writing date comparisons within Google queries (pulling data before / after a certain date), it was a pain to figure out the correct syntax.
Unfortunately it’s not really mentioned anywhere in the Google Sheets documentation, so I had to figure it out for myself.
You have to do two things correctly:
If you want to compare a date dynamically from another cell, it’s a bit of a different story. I like to keep date ranges (last 7 days, last 30 days, year-to-date, etc), then reference them in a query like this:
Notice the syntax:
Once you get the hang of it, you’ll never think about it again. But if you do ever forget the syntax, the CIFL cheat sheet has your back.
On debugging common Query errors:
Errors are never fun, but Sheets’ error messages will always point you in the right direction.
There’s data in cell D6 blocking your query from expanding – remove it!
The sheet that you’re referencing in your query doesn’t exist – probably due to a typo or having deleted it :(.
Caused by a misinformed query string – it will always tell you exactly where to look:
Would mean there’s a syntax error somewhere surround my ‘where’ clause – a hanging comma or a typo:
=query(tweets!A1:D,‘select C, sum(D), where D >= 0 group by C limit 2’,0)
Caused by a mismatch between columns in your ‘select’ clause and your ‘label’ clause:
=query(tweets!A1:D,‘select C, sum(D) where D >= 0 group by C limit 2 label sum© “,0)
Here sum(D) is mislabeled as sum©.
When you’re selecting a column that’s outside of your range:
=query(tweets!A1:C,‘select C, sum(D) where D >= 0 group by C limit 2’,0)
Remedied by expanding your data range to include the column (tweets!A1:D in this case).
Pops up when you’re selecting a column (B in this case) that isn’t present in your ‘group by’ clause:
=query(tweets!A1:D,‘select C, B, sum(D) where D >= 0 group by C limit 2’,0)
If you’re not summing or aggregating column B, then you have to group by it.
Simply means there’s no results from your query as currently written – potentially due to a too-restrictive condition:
=query(tweets!A2:D,‘select C, D where D = 100 limit 2’,0)
Try simplifying your query to be less restrictive (removing the D = 100 in this case).
On querying multiple Google Sheets, with importrange + {}:
There comes a time in every life, where you want to combine two data ranges within a Google Sheets query.
Let’s talk about how to combine data ranges from within the same spreadsheet (or from two different sheets), to run one query – working through examples using sample Twitter data.
To query a combined range from within the same sheet:
To query a combined range from different sheets:
To be honest, my goal is to never have to combine ranges from within a query itself (I prefer to do that type of aggregation before writing a query), but sometimes it’s unavoidable.
This one will save you major headaches if multiple colleagues work in your Sheets.
Queries are sensitive creatures – they require us to specify exactly the column letters ( A, sum(B) ) or numbers ( Col1, sum(Col2) ) that we want to return.
But given how we use Sheets, those columns may be changing all the time. People add new rows, delete rows, and generally shift things around.
By nesting two queries together, we can future-proof your queries to always match the same column headers:
Put it all together, and it looks like this:
The first line (inside query) finds your column names – the actual query its running is just ‘select Col3, Col4.’
The second line (outside query) runs your actual math and labels your columns. Remember that query column labels will always appear at the end of the statement, and look generally like the SELECT statement (select Col1, sum(Col2) maps out to label Col1 ‘handle’, sum(Col2) ‘total retweets’).
This can take a bit of playing around to get comfortable with – feel free to copy the demo Sheet used in the video to re-use our formula.
Great for building time series reporting in Sheets:
If you’re working with time-series data in Sheets (like most of us do), then pivoting in queries will be your new favorite move.
It lets you build full report in one Query, by grouping values down the left column, and transposing date ranges (or any second column) to the right.
You pivot with a very similar syntax to grouping – this query produces the result above:
=query(tweets!A1:D,‘select C, sum(D) where D >= 0 group by C pivot A’,0)
As you can see, it’s as simple as adding ‘pivot A’ after the ‘group by’ clause in a query.
You can use dropdowns to add input variables within your query:
When you’re building a report in Google Sheets, a solid goal is to not make the people *using* the report think.
Dropdown menus are great for this, since they allow you to present a limited number of options to report users.
Data validations in Sheets automatically create dropdown menus when you create them – pretty slick right?
And blending the selected value from your dropdown menu with a Query is simple:
=query(data!A:B, ‘select A where B = “ & ‘dropdown tab location’!C2 & “‘, 0)
It only takes a few keystrokes:
That’s it! Your report users will thank you.
What’s the difference between the Sheets Query and SQL?
If you’re used to writing SQL queries, it can be off-putting at first to work with Google Sheets queries (and vice-versa).
If you don’t know any SQL yet, learn Query first. It’ll make working with databases *much* easier when you get there.
Let’s walk through a number of ways in which they’re similar & different, and how you can adapt each of them in your work.
SQL: SELECT * FROM table WHERE column_name = ‘xyz’ ORDER BY column_name desc LIMIT 10
Sheets: =query( ‘tab’!A:D, ‘SELECT * WHERE A = ‘xyz’ ORDER BY A desc LIMIT 10’)
The basic query syntax is roughly the same. Google Sheets queries use the same SELECT statement to choose columns, WHERE / AND / OR to set logic, ORDER BY to arrange results, and LIMIT to pull only a certain number of results (see a full list of language clauses in the Google docs).
The key difference is that there’s no ‘FROM’ statement in a Sheets query – instead of referencing a table, you reference a range of cells (‘tab’!A:D in this example).
Also, you’ll notice that, instead of referencing column names (like in SQL), in Sheets queries you reference columns by their letter (A, B, C, etc) or by their order (Col1, Col2, Col3).
SQL: SELECT column_name, sum(other_column_name) FROM table GROUP BY column_name
Sheets: =query( ‘tab’!A:D, ‘SELECT A, sum(B) GROUP BY A’)
The same avg(), count(), sum(), max(), min() aggregations functions that you’re used to using in SQL are available in Sheets queries, as are basic +, -, *, / arithmetic functions.
SQL: SELECT column_name AS ‘label’ FROM table
Sheets: =query( ‘tab’!A:D, ‘SELECT A, B LABEL A ‘label1’, B ‘label2’ ‘)
In SQL, to label a column you simply add an ‘AS’ to your ‘SELECT’ statement: column_name AS “label.”
In Sheets, this is done at the end of a query, with the ‘label’ statement.
Labeling the sum(A) column as blank removes the automatic sum() header from being displayed, returning a nice clean single metric.
SQL: SELECT column_name FROM table WHERE date_column > ‘8/22/2016’
Sheets: =query( ‘tab’!A:D, ‘SELECT A WHERE C > date ‘2016-08-22’ ‘)
Dates in Google queries are tricky to figure out the first time, if you’re used to working in SQL.
Unlike SQL, which can handle multiple date formats (‘20160826’, ‘8/26/2016’, ‘2016-08-26’), Sheets can only take dates in format ‘yyyy-mm-dd’ – so you sometimes have to use the TEXT function to transform a date like 8/26/2016 into 2016-08-26.
In Sheets, you also have to specify that you’re comparing a date, by adding the phrase ‘date’ before your date string.
SQL: SELECT column_name FROM table WHERE column_name LIKE ‘%phrase%’
Sheets: =query( ‘tab’!A:D, ‘SELECT A WHERE A CONTAINS ‘value’ ‘)
To pull text that contains a value, you can simply use ‘contains,’ versus the ‘like’ statement in SQL. Sheets queries do have a ‘like’ statement, but I find myself using ‘contains’ much more frequently because of its simplicity.
One added wrinkle though, if you’re looking to add a ‘not contains’ parameter, you have to place the ‘not’ before the column letter.
SQL: SELECT a.column_name, b.column_name FROM table1 a, table2 b WHERE a.column_name = b.column_name
Sheets: Not supported ????
My biggest pet peeve with Sheets queries is that they don’t support joining two tables together by a unique key. I’ve tried every workaround I can think of, and so far haven’t found a way.
Google Sheets team, if you’re reading this – help!
SQL: SELECT column_name FROM table WHERE column_name IN (‘match1’, ‘match2’, ‘match3’)
Sheets: =query( ‘tab’!A:D, ‘SELECT A WHERE A = ‘match1’ OR A = ‘match2’ OR A = ‘match3’ ‘)
Unfortunately in Google Sheets, there’s no such thing as the ‘IN’ phrase in SQL, so you have to list out values you’re looking to match for one-by-one.
With a bit of a hacky approach, you can approximate SQL’s nesting function in Sheets:
As I mentioned above, not being able to join queries in Sheets is a major drawback.
Say you have data in two different tabs, and you want to pull all of the values from one table that exist in the other.
I thought it wasn’t possible. I was wrong.
It’s a hacky way of imitating the way that SQL queries can nest, but it works.
You combine arrayformula and concatenate, to produce a long string within your query ( OR A = ‘value1’ OR A = ‘value2’, etc).
The final formula looks like this:
=query(tab1!A:Z,‘select A,sum(B) where A != ’ and (B = ‘blah’ ‘ & concatenate(arrayformula(’ OR B = “ & tab2!C2:C & “‘)) & ‘) group by B’,0)
This takes all of the values that are present in Tab 2, column C, and looks for them in column B of Tab 1.
It’s a poor man’s nesting query, but it works :).
That was a lot of Google Sheets querying, but I hope it’s starting to sink in for you.
Make sure to grab a free copy of the Sheets formula cheat sheet here – but if you’ve made it this far, you’re probably ready to master Sheets by investing a few hours in the advanced CIFL Querying course (which includes detailed walkthroughs and quizzes not found here).
Or, hit us up directly – our team of consultants is standing by to help you solve your most complex QUERY problems.
Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.
GET COOKING