Google Sheets + Query = Hell YEAH!

A few weeks ago I stumbled upon something that has got my a little excited. I mean did you see the exclamation mark in the title? That is not to be trifled with people.

At any rate, I have stumbled upon the QUERY function from Google Sheets and man this is awesome! To be simply, this will allow you to have database like functionality to a Google Sheet. That may not mean too much to you, but it will.

At my school we have a list of approved parental chaperones by grade level. Basically these are family members. This list is made up of interested family members who have had a background check. No background check, means parents cannot attend. Simple as that.

Field trips are organized by the teachers in the classroom. Therefore, they are responsible for not just the students attending but making sure that parents who have been “approved” by the school can go. The teachers should be referring to a list and that is where the problem enters.

It was a mess (organizationally speaking). It was a Google spreadsheet but everything was entered manually. There were tabs for each grade level, but man it was a pain to update.

Here is what the IT team came up with. We will put all the data on one sheet. Then make a tab for each grade level. Finally on each tab we would use the QUERY function to auto populate the tab with the appropriate information for that grade level.

That way the original data didn’t need to be “organized.”

Make sense? It will. I’ve created a new project to show as an example. I have students in different grade levels attending workshops on different platforms. Worry not folks, these names came from a website that generates random names. Any coincidences that may have occurred are just that.

So here is my data.

Now what I want to do is separate it by workshop. That way I know exactly who is going to attend the Apple, the Google, the Linux and the Microsoft workshops. There are plenty of ways to do this using the vlookup function and so on, but this way is much faster and much simpler.

First, I will make tabs for each workshop.

Before I start the QUERY magic I will want to name the range of the data. On the tab that is called All Data I will select all of it by hitting CTRL+A (Windows) or by hitting CMD+A (Mac).

Now I will select Data –> Named Ranges… from the Google Sheets menu.

A new side window will appear asking you to name that information. I named mine workshop. You can name yours whatever you want, but when referencing back to this data the name must be perfect. If you capitalize the first letter, you have to capitalize the first letter in your QUERY.

Now in the Apple tab in cell A1 I will type this formula.

=query(workshop,“SELECT A,B,C WHERE C=’APPLE’”,1)

This is what should happen in that tab.

Now let’s take a look at that formula and break it down.

• =query – This is you telling Google Sheets you will be using the Query function
• (workshop – Remember when we selected all that data and named it workshop,? Well this is telling Google Sheets that we want to look at that pool of data. The comma is there to signify that we are ready to move onto the action.
• Select A,B,C – This is here to say that of all that data we want to look at these three columns. If you only wanted a list of names you could but Select A and call it a day.
• WHERE C=’APPLE’” – This tells the query to display only the information associated with the word Apple is found in column to C.
• 1 – The one at the end corresponds to how many header rows there are (I think – I’m still learning here too

It’s awesome! One function in one cell is populating the worksheet! It also updates dynamically so when you add another student to the worksheet it will automatically update the correct page with that information.

Awesome!

If you want to see my example click the Google Image below.
It is only view only but you can make a copy and look at the formulas which are all in cell A1 of each respective cell.

Now, I did not just stumble upon this and figure it out all on my lonesome. Oh no, I had a little help from two very good websites that helped break this down for me.

One is from Ben Collins, a spreadsheet Developer and much more. Check out his excellent introduction to the query function on his website.

The other website is Coding is for Losers. This site has a tremendous treasure trove of resources and will take your spreadsheet to the next level! You can find the information on query right here. They also have great YouTube videos. Check out their channel here.

If you have any tips or tricks feel free to leave them in the comments!

Source: IT Babble Blog and Podcast