The Google Sheets Query Function A weapon of mass laziness

The Google Sheets Query 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 four videos, with cliff’s notes embedded beneath each:

  • The basics of Google Sheets queries – writing a SELECT statement with conditions
  • Writing date comparisons in queries
  • Querying multiple ranges or sheets at the same time
  • The differences between Google Sheets queries and SQL

Let’s dive in!

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 take a data range and a query string:

=QUERY( ‘data tab’!A:C, “select A where C = ‘twitter’”)

Let’s answer a few questions, all using QUERY functions:

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’”)

For a full cheat sheet on QUERY syntax, grab a copy of the CIFL Google Sheets formula guide or check out the official docs.

Date comparisons in Google Sheets queries: why so complex?

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.

pasted image 0 2

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:

  1. Write the string ‘date’ before the actual date, to declare your intentions
  2. Type your date in the format ‘yyyy-mm-dd’

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:

pasted image 0 6

Notice the syntax:

  1. To combine the string with an outside value, close the query double-quotes, and use ‘&’
  2. Use the TEXT function to convert the date into the correct ‘yyyy-mm-dd’ format

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.

Query multiple Google Sheets: importrange + {} = friends

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:

pasted image 0

  1. Enclose both ranges within { } to combine them, and separate with a ‘;’ to stack one on top of the other
  2. Instead of referencing columns with their letter (A, B), refer to them as Col1, Col2 depending on their order

To query a combined range from different sheets:

  1. First run the importrange function on the outside Sheet you’d like to pull from.  The first element is the spreadsheet ID, which is contained within the URL of the sheet (https://docs.google.com/spreadsheets/d/1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654/edit).pasted image 0 3
  2. You’ll be asked to allow access to the sheet.pasted image 0 7
  3. Embed the range from your importrange function within the query itself.

pasted image 0 4

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.

Google Sheets QUERY vs SQL: what’s the difference?

If you’re used to writing SQL queries, it can be off-putting at first to work with Google Sheets queries (and vice-versa).

Want to get your feet wet with SQL? Take a free 1-hour course covering the basics.

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.

Basic querying

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).

Doing math on columns

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.

Labeling columns

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.

Date comparisons

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.

Fuzzy phrase matching

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.

Joining tables

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!

Match from a list of options

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.

Phew!

That was a lot of Google Sheets querying, but I hope it’s starting to sink in for you.

If you’re hungry for more, make sure to grab a copy of the Sheets formula cheat sheet below – and always feel free to reach out to me at commissioner@codingisforlosers.com if you’re looking for Google Sheets help.

6 Comments

  1. Very nice page! Congratulations!! I love using funcions to create my MVP’s, but this chalange bellow is giving me a hard time. Do you know if there is a limit for query function in google sheets?

    This is working when I request 2220 rows.
    =query(IMPORTRANGE(“Key”;”Nivel 4 Isapa!H1:AB2220″);”SELECT Col1,Col21 WHERE Col21=’A'”)

    This is working when I request 2400 rows it DOES NOT work..
    =query(IMPORTRANGE(“Key”;”Nivel 4 Isapa!H1:AB2220″);”SELECT Col1,Col21 WHERE Col21=’A'”)

    Have you faced something like this?

    • Hey Rubens, I actually have never run into size limitations with queries (beyond the 2mm cell limitation of Google Sheets).

      Have you tried first importing the entire range you’re looking to query into a tab, then running it? Might be a size limitation of the importrange function.

      • You are right, I did found out, the limitation was really on the IMPORTRANGE fuction, it would not bring 47.000 cells. This was exceeding the limits.

        I really love google sheets but I have to find a workaround to reach at least 15.000 rows to work with…

        • One idea – you could nest 3 importrange functions together – place them within { }, and separate the three function calls with ; – ie { importrange1(first 15k rows) ; 2nd 15k ; 3rd 15k }. That’ll achieve the same result of returning ~45000 rows.

  2. Hi there. I’ve been using gsheets for some months and faced how powerful it is. Anyway, surfing around I found your blog and just loved your comparisions and methods.
    I’m one of these guys who like to unlock all resources of a tool before deep into codes.
    I have a suggestion about join tables with query formula. Despite google doesnt support joining tables with query there is actually a way to do a subquery. It is not the same but can help with lots of troubles

    In the sample you gave above …
    ** SQL: SELECT a.column_name, b.column_name FROM table1 a, table2 b WHERE a.column_name = b.column_name
    We can use:
    Sheets: =query( ‘tab’!A:D, “SELECT B WHERE A contains ‘”& query( ‘secondtab’!E:E, “SELECT E WHERE E=1 label E ””) &”‘ “)

    We only have to be careful with spaces, quotation marks, parenthesis and other special caracters.
    I’m pretty sure you will do a better job describing and deploying the function.

    • That’s an interesting approach Allan, thanks for sharing – joining in Sheets queries to me has been the holy grail, the cup of gold of Sheets functions. Your idea doesn’t **exactly** work as you scoped it – you’d have to concatenate a long string of ‘OR A contains X or A contains Y or A contains Z’ – rather than referencing the entire range X:Z in one shot.

      If I can make it work, I’ll share a video about it – thanks for the inspiration.

Submit a comment