Tutorial - QUERY: "The most powerful function" in Google Sheets (part 1)

This topic was inspired by our last community meetup, in which we talked about some formulas in Google Sheets and some people want to know more about QUERY, which was branded “The most powerful function in Google Sheets” by famous GSheets teacher Ben Collins.

So, what is QUERY, and why is it so powerful. Here’s a writeup on it, which hopefully would help many of you here in this community. Trust me, it would change so much how many of you “clean” your data in the future from raw data, in a very easily achievable way.

1/Introduction

QUERY was introduced alongside the Google Visualization API Query Language, and it was a lifesaver for many people, but more so for those who are familiar with SQL (Structured Query Language).

If you are not familiar with SQL, no problem, you can grab most of the basics thing about its structure in this W3School link, which includes many examples for you to work with.

So, what does the general syntax look like?

QUERY(data, query, [headers])


  • Data: The range of data that you want to perform the query function on.
  • Query: The query to perform.
  • Headers (Optional): The number of header rows at the top of the data. You can just choose the appropriate data range to not have headers, or include them in and provide the right number here. Official Google documentation says if this value “is omitted or set to -1 , the value is guessed based on the content of data”. Specifically they want to mention the cases where we have aggregation functions like SUM, AVG, …

2/Select all

So what does it do exactly? In short, it helps you combine many functions into one, and I will provide some specific cases for this. Starting from the easiest one - Select all.

This is the easiest of them all, in case you want to move the data from a sheet to another one in the same file (you can also use FILTER, ARRAYFORMULA or a simple array setup, whatever makes you feel comfortable for this case).

=QUERY(Sheet1!A1:A,"SELECT *",1)

The formula above select all values from Sheet1’s column A and put them in wherever you have this formula. Remember to put double quotation marks before and after your query sentence, or else it won’t work. I also told the Sheets that there’s a header row at the start by putting the argument 1 at the end.

3/Select specific columns

Same as the above, but you can choose specific columns, and in the order you want it to be, with just a very short query. How cool is that?

=QUERY(Sheet1!A1:D,"SELECT D, A, B, C",1)

The D, A, B, C part represents the name of the column you have in the data range you specified. You can also have the same outcome for SELECT Col4, Col1, Col2, Col3. The 4, 1, 2, 3 is assigned based on the data range. For example if you have a W:Z range it will be Col1 - W, Col2 - X, Col3 - Y, Col4 - Z.

4/Select with specific conditions

Now we come to the fun part, the conditions, which is offered by a WHERE clause.

Normally, before you use QUERY, this can be done with FILTER, INDEX - MATCH combination, and many things more. But QUERY make it much shorter.

An example is shown here where I want to show all people who have a salary more than 300.

image

Or going to a further level, I want to show Facebook employees with salary more than 300.

image

To make it funnier, let’s say I’m bored and want to find all information about people whose names start with D. It can be achieved with the LIKE clause, and a wildcard which I’m sure is familiar to those who know the REGEX function family.

Here the ‘D%’ stands for ‘names start with D’ (% is the wildcard)

image

Or this example where someone has “an” in their name. Notice how it didn’t cover Andrew, as the query is case-sensitive.

image

How do we overcome that? Further magic is applied, with the LOWER function.

image

5/Aggregation functions and labeling column names

This applies to Min, Max, Sum, Average,…

Assuming I want to know the max, min and average of salary column in the same dataset, here’s the formula I used to achieve it.

What ever comes after the LABEL function will be used to name your new columns. If we don’t have that, it will be “guessed” by the system and looks like the image below, but I guess you’d rather have beautified column names based on your own choice.

That brings us to the end of part 1. In the part 2 I will cover the GROUP BY, ORDER, LIMIT and some other useful cases of QUERY for you.

Thank you for reading until this point, and stay safe!

22 Likes

@Krivo @nathanaelb @Manan_Mehta @Naos_Wilbrink @Erni_D This was the post I promised to write after the community meetup, hopefully it would help you in many cases. There will be a part 2 in the upcoming days so stay tuned!

4 Likes

@ThinhDinh very, very nice work. You do really nice tutorials. Thanks for sharing.

2 Likes

My pleasure to help the community. Your posts are very nice as well! Have a nice Sunday yourself.

2 Likes

@ThinhDinh sorry to have missed the MeetUp! QQ I know how to use QUERY but I’m wondering, in the context of a Glide app, why/when would I use this? Or is this just for data cleaning of my spreadsheet (as you note above)?

I would imagine mostly data cleaning, but for some complex cases, for example this:

If the person wants to have a quick solution for their problem then QUERY can be used, before we can think of a solution in Glide editor. That’s the first cause for this. Secondly, I believe this would help people in their normal work as well.

Of course I want to do all things in the Glide editor myself to eliminate the lagging, and Glide editor is getting stronger by time.

1 Like

Thank you so much! I was struggling whit the logic behind this. Thanks to clarify in many ways! Awesome

1 Like

Glad it may help people in various ways. Stay tuned for the next part :smile:

A few cases where I need to use query are the following:

  1. I dynamically build a sheet from a list of multiple lessons for students in a different sheet. A student can have multiple lessons from a coach within a specified date range, but I only only want to create one unique set of student/coach/daterange columns. I could do this with a UNIQUE formula, but I also need to exclude certain rows from the other sheet, so this is where the query comes in with a WHERE clause.

  2. In my lessons sheet, I built a column that joins multiple columns together and formats it with html for a single table row. In the sheet mentioned in #1 above, I then use a query to concatenate all of those matching row columns for a unique student/coach/daterange along with the rest of the html required for the table. This is done with query.

  3. I have a column that calculates all payments prior to the current billing period. This requires comparing a range of dates less than the current billing period date. This is done with a query. I’d love to do this in glide, but they don’t currently handle date comparisons very well and I currently can’t create conditional relations.

There are a lot of use cases where you may hit a wall with glides current functionality or google’s built in functions don’t quite work how you would like, but a query seems to pick up the slack. I think SQL(query) is one of the most powerful and fun languages to work with.

3 Likes

Point 1 is what I’m working now. Are you trying to create a past class sheet? And if so,you created one page for each student?

Yes, I have several levels deep.
First a button to a page that lists all of the billing periods for a coach.

From there I list all of the billing periods for that coach:

Selecting a specific billing period leads me to the sheet that contains the queried information from the lessons sheet in point #1. This is the unique student/coach/datarange data where I can further drill down into specifics about that students lessons and billing total for that particular coach/billing period.

When I drill down to a particular student, then points 2 and 3 come into play to display the table and the prior paid amount.

The magic of queries. I’d eliminate them if I could, but they serve a purpose sometimes.

2 Likes

@ThinhDinh Wow, I’m thoroughly impressed by the way your knowledge/skill grows day by day, and how well you articulate what you have learned to others in the community. Incredible write up on QUERY and I look forward to what comes next!!!

1 Like

Great post @ThinhDinh, definitely you don’t have girlfriend or children to take care in your free time :grinning:

Very useful your examples. I have seen sometimes people using filters and other functions in a cell to get a result (very complex to understand easily) instead of using the Query() function but I understand the cause.
The SQL as language or tool to retrieve and analyze data from a database was something dedicated to programmers in 80s, 90s and 2000s but currently, the basic functions have been “simplified” and incorporated to help users with other needs beyond the basic and this is perfect!!

Months ago, my daughter asked me for helping her with a school homework and when she told me the subject I almost screamed her…

  • what? Did you say SQL? No way…WTF!!! :upside_down_face:

BTW… did you know you can use and run several Query() in the same cell, at the same time and get all results vertical or horizontally??

Saludos!

2 Likes

Short display to show you can do it, difference between vertical and horizontal is in the “,” and “;”.

3 Likes

Yes I’m 23 so I don’t have a child. My girlfriend’s name is Glide, apparently :rofl:

7 Likes

Thanks brother, as I have said in another post (I believe it’s the ARRAYFORMULA tutorial) my aim is to help the community with my knowledge. By helping people here I would enhance my knowledge as well, and make this a better community overally. The better the community, the better ideas we get to learn from each other. Everyone benefits.

4 Likes

So your a dance teacher ?

This is really helpful. Thanks.

1 Like

No, I’m a programmer analyst, but my girlfriend teaches figure skating. I was on her for years to get a better system for tracking lessons and billing, but she was stubborn and kept doing it on paper. Then her club asked for a student database to keep track of student information. That’s when I found Glide and the rest was history.

5 Likes

Really nice! and we all found you! By the way…solved the single row conundrum with a script…not what I wanted but it works…Ive left some messages for you on eChat. ThinH DInH came to the rescue!

1 Like