RUNNING QUERIES IN GOOGLE SHEETS

TAKING YOUR SPREADSHEET ANALYSIS TO A NEW LEVEL

As the field of data analytics evolves, so does the number of analysis tools available. By having a diverse set of tools at their disposal, a data analyst can be more efficient and effective in their work. Undoubtedly, one of the most significant tool is a spreadsheet.

A spreadsheet is a file made up of cells that holds data in rows and columns, and performs calculations on datasets using formulas and functions. But have you ever thought about performing database-like queries in a spreadsheet?

Yes, it is possible using the Google Sheets’ QUERY function.

The QUERY function in Google Sheets makes use of a particular language for manipulating data called the Google Visualization API Query Language. A query language that allows us to execute numerous data transformations, manipulating data sets using database-like instructions.

It’s important to keep in mind that in order to fully comprehend the contents of this article, the reader must have a basic understanding of spreadsheets and SQL queries

The syntax for this function =QUERY(data, query, [headers])

• The data specifies the range of the data set to work with
• The query is the significant element of the formula. We use SQL-like statements to query our data range. Two important things to note

1. The column label(ID) is used to reference the column to be used (not the data header). It is case-sensitive, hence, it should be UPPERCASE.
2. The query must be enclosed in double quotes.

• The headers argument is optional. It is used to specify the number of header rows at the top of the data. It is guessed based on the content of the data when omitted.

GETTING TO KNOW THE CLAUSES

The SELECT clause is used to specify the column(s) of data to return. Like SQL, we can also use * to return all columns in our data range.

For example “SELECT B, D, E” returns data in columns B, D and E.

Aggregate functions like AVG(), COUNT(), MIN(), MAX(), SUM() can also be used with the SELECT clause

The WHERE clause is used to return specific rows that matches a condition. It is a crucial part of the query as there are lots of functionalities that comes with it. Criteria are declared with comparison operators (>, <, > =, < =, != or <> ) and/or logical operators (AND, OR)

Some other string operators that can be used alongside the WHERE clause includes
- IS NULL which is used to check for empty/blank cells
- IS NOT NULL which does the opposite of IS NULL, returning non blank cells
- STARTS WITH is a prefix match. It is used to match the beginning of values in the rows of a criteria column
- ENDS WITH is a suffix match. It is used to match the end of values in the rows of a criteria column
- LIKE which is for string matching using wildcard characters % and _

Another clause is GROUP BY. It is used to group rows with same values in a column. GROUP BY is often used when there are aggregated columns in the SELECT statement

The ORDER BY clause is used to sort/arrange the result of the query in ascending or descending order. The ORDER BY can use one or more columns to sort.
By default, ORDER BY column-Id sorts in ascending order. The DESC word is used at the end of the statement to sort in descending order.

At this point, we’re pretty much running SQL queries in Google Sheets right?

There are still lots of clauses that can be used in the QUERY function. The LIMIT clause can be used to specify the number of rows to return or we can skip a certain number of rows from the top using OFFSET.

We can as well rename our columns (aliasing) using the LABEL clause. Although, the syntax is a little bit tricky, it can be useful especially when we’re running aggregates in our SELECT statement.

Using the QUERY function with these clauses gives another level of super power to analyze data efficiently in Google Sheets. It can be used in place of VLOOKUP, FIND, IF statements and more.

Still thinking about using it? It might seems overwhelming at first, but with practice, you’ll be running queries in Google sheets like a true DATA BENDER.

You can read more about the QUERY function on the Google support page .

Have you ever used the query function? Would you now? Are there any other query clause you know about?

Leave a comment - I’d love to hear from you

Have a look at my project portfolio also and let me know what you think

Let’s connect. You can reach out and follow me on LinkedIn or X