Google Sheets Query – a Powerful Tool for Data Analysis and Data Manipulation

Q: What is Google Sheets QUERY UNIQUE () function?

You can use the UNIQUE function in Google Sheets to count only unique non-repetitive rows from the query. To use it, add it around the QUERY function. E.g., =UNIQUE(QUERY(A1:B136, "select A, B")).

03.11.2022

Alexander Demchenko

Table of Contents

Intro to Google Spreadsheet Query Function
Why Do You Need the Understanding of SQL for Google Sheets?
Google Sheets QUERY Syntax
- Data
- Query
- Header
Clauses in Google Sheets QUERY
Google Sheets Query for Multiple Sheets
Google Sheets Query a Workable Tool for Data Analysis

Intro to Advanced QUERY Google Sheets Function

At present, there is a growing need for data analysis and data manipulation in various spheres of our life. If you have multiple datasets to filter, analyze or transform, you may be familiar already with the filtering feature and simple functions like SUM, AVERAGE, COUNT, etc. However, it might not be enough to get all data analysis done neatly, and you risk getting your tables messy with numerous layers of filters and functions all over the place. Being simple, the Google sheets query function is at the same time a powerful tool that may come in handy for your activities, whether they are for research or business purposes. To help you use this powerful instrument right away just go on reading, check some beginner google sheets query examples we’ve prepared, and then proceed to more advanced functions.

Why Do You Need the Understanding of SQL for Google Sheets?

In fact, the query function of google sheets operates similarly to Structured Query Language, so SQL is referred to as the basis in learning how to access, manipulate and use big data.

Rank	Country	Continent	Population
1	China	Asia	1,385,566,537
2	India	Asia	1,385,566,537
3	United States	North America	1,252,139,596
4	Indonesia	Asia	320,050,716
5	Brazil	South America	249,865,631
6	Pakistan	Asia	200,361,925
7	Nigerai	Africa	182,142,594
8	Bangladesh	Asia	173,615,345
9	Russia	Asia	156,594,962
10	Japan	Asia	142,833,689
11	Mexico	North America	127,143,577
12	Philippines	Asia	122,332,339
13	Ethiopia	Africa	98,393,574
14	Vietnam	Asia	94,100,756
15	Germany	Europe	91,679,733
16	Egypt	Africa	82,726,626

The key reason to learn it is the fact that it’s widely used in databases, and chances are high your homegrown and managed database uses it as well. However, the foundational big data language you master should be supplemented with a profound study of google query sheets function. SQL with google sheets combined reveal a ton of opportunities to work with data that are otherwise impossible or complex. Google query tool is powerful and versatile since a single QUERY does the job of many functions at a time and can replicate most features of pivot tables. With the help of a query statement, the function fetches specific data from a spreadsheet. So, it can by right be called a one-stop-shop for all your analytical, lookup, filtering, logical, counting, calculation, averaging, and sorting requirements.

Google Sheets QUERY Syntax

To start learning the query function, it’s necessary to master its components, and the syntax of the query. Google documentation breaks the syntax of the function into 3 parameters. Thus, Google Sheets query contains data, query, and headers (optionally). To get the outcome you need, you should write each parameter in the right place. But, let’s first break the syntax down:

Data

Data – refers to the data set that is used for a certain Query. It’s vital that the data type within the selected column is of the same type, otherwise, Google will determine the type of data prevalent and return the null value for the rest that does not match. By the way, it can only be numeric (figures, dates/time stamps), strings, or a boolean (true/false).

Query

Query — is the SQL part of the overall google sheets query where clauses are applied to define what should be retrieved as a result. We’ll explain the notion of clauses a bit later, but it’s essential to remember that the value of this component needs to be enclosed in quotation marks for Google to recognize the necessary criteria, it can refer to another cell if that one has quotes wrapping it, otherwise, Google will fail to interpret it.

Header

The header is an optional parameter that determines the number of rows being headers in your selected dataset. Keep in mind that if the header value is left blank, or set to -1, Google will either not consider a certain number of rows and recognize them as labels, or scan the information and estimate the number of rows that are headers.

How to query google sheets examples:


=query(data!A2:Z900, “SELECT A, C, E, J, L”, 2)

If we break this down parameter by parameter, we get: the information we need lives in the tab called data, in column A – Z and row 2 – 900. query = “SELECT A, C, E, J, L”, It’s necessary to grab full columns of A, C, E, J, and L from the data. Important: “surrounded in quotes!” headers = 2 It means that the second query row in google sheets of table data is a label for the data we extract, it should not be included with the rest of the data, but needs to be used as a label. Looks quite simple, doesn’t it? So, you may wonder why to use the function of query and not refer to the data tab. As soon as you take up layering in data manipulation functions and clauses, everything shines bright immediately. So, having gotten acquainted with the syntax, you can proceed to the major part of your present study—the clauses. This is the secret ingredient that creates the unique value proposition of the query. So, let’s dig deeper into the clauses and their specifics.

Clauses in Google Sheets QUERY

A query won’t run without clauses, so it’s the core of the query that actually tells it what to do. As a result, you get the data you need. In Google’s documentation, you can find a comprehensive table of all the clauses available:

Clause	Usage
`select`	Selects which columns to return, and in what order. If omitted, all of the table’s columns are returned, in their default order.
`where`	Returns only rows that match a condition. If omitted, all rows are returned.
`group by`	Aggregates values across rows.
`pivot`	Transforms distinct values in columns into new columns.
`order by`	Sorts rows by values in columns.
`limit`	Limits the number of returned rows.
`offset`	Skips a given number of first rows.
`label`	Sets column labels.
`format`	Formats the values in certain columns using given formatting patterns.
`options`	Sets additional options.
`from`	The from clause has been eliminated from the language.

It’s vital to remember that the clauses should be used in the order as they are enumerated in the table above, otherwise, you’ll get an error. It may seem a bit confusing until practiced. Let’s turn back to our example:


=query(data!A2:Z900, “SELECT A, C, E, J, L ORDER BY L”, 2)

The google query like this will first select the data from the indicated columns and then sort it by a specific column value. In our example, the data will be ranked from the lowest to highest (by default) based on L column. In case you need to get your data sorted from the highest to the lowest volumes, you should add DESC to the google sheets query ORDER BY. Your query will then look the following way:


=query(data!A2:Z900, “SELECT A, C, E, J, L ORDER BY L DESC”, 2)

Country	Continent	Population
Vatican City	Europe	799
Tokelau	Australia	1195
Niue	Australia	1344
Falkland Islands	South America	3044
Saint Helena	Africa	4129
Montserrat	North America	5091
Saint Pierre and Michelo	North America	6043
Tuvalu	Australia	9876
Nauru	Australia	10051
Wallis and Futuna	Australia	13272
Anguilla	North America	14300
Caribbean Netherlands	North America	19130
Cook Islands	Australia	20629
Palau	Australia	20918
Virgin Islands, British	North America	28341

Google sheets query where has rather a simple concept as well, it returns the rows of the Google sheet that meet a certain condition. The latter can be mathematical: <, >, >=, etc, or logical, containing string comparison operators: starts with, contains, ends with, and more.


=query(data!A2:Z900, “SELECT A, C, E, J, L, WHERE J CONTAINS “criteria”
ORDER BY L DESC”, 2)

With such a query you’ll get your data filtered as in the previous example, but get only the rows that match the indicated condition/criteria. In case you often use the QUERY function there is a number of logical operations that help set conditions within the function. You can use the following:

Query Limit in Google Sheets

The LIMIT clause speaks for itself, it sets the limit for the rows in Google Sheets when the data is returned. To limit the rows in the Google spreadsheet from our example, you should just put the LIMIT 10/15/25 as you need it.


=query(data!A2:Z900, “SELECT A, C, E, J, L, WHERE J CONTAINS “criteria”
ORDER BY L DESC LIMIT 25”, 2)”

Thus, you’ll get returned the 25 rows as ruled by query LIMIT for Google Sheets you work with after it’s processed as in the previous query: selected, sorted, and ordered.

Aggregation and Arithmetic Functions

The google sheets query GROUP BY aggregates values for unique value combinations in the google sheets GROUP BY clause, it helps to manipulate data more effectively. First, the function of aggregation intakes a specified column of values and performs the required action across all values in each group, if there are any, otherwise in each row. The aggregation functions are sum, count, average, max, and min.

max Population	min Population	avg Population
1385566537	799	30738710.02

Then the scalar function sets in and operates over zero or a certain another parameter to return another value. What is more, you can do the math in your query and use arithmetic operators for the purpose. You probably already guessed they are simple: +, -, *, and /, but you can as well create your own query formula in Google sheets.

In case you feel you need to know more about aggregation functions, it’s reasonable to study Google’s documentation, it provides context and plenty of examples for the purpose.

Complex QUERY functions

As you could already understand, the google spreadsheet query allows for not only simple arithmetic operations like multiplication and addition or aggregation ones – calculation of the average, for instance, but also complex QUERY functions for more complex tasks. They can be like these:

Pivot google sheets query

What is more, there is the pivot google sheets query clause that allows the users to build their own google sheets query pivot table according to the QUERY used. It’s a kind of more advanced usage of the function of query, but the more you use it, the more possibilities you will reveal.

Google Sheets Query for Multiple Sheets

There are cases when the query data in Google Sheets is spread across multiple tabs and sheets, still, the QUERY function can be used.

The key is to have the query data from google spreadsheet in all the sheets in one format, then you should keep in mind to refer to columns not by the letter, but by the number: Col1, Col2, etc. Finally, data ranges should be embraced in curly braces and separated with a semicolon. Tabs or sheets need to be indicated with an exclamation mark following the sheet name, and then the range of cells within the sheet should be identified. Though google sheets query for multiple sheets may be considered one of the most complex functions to master, it’s well worth the time and effort if you need to manipulate data effectively.

Advanced Query Google Sheets FAQ

How to use QUERY in Google Sheets?

A formula that uses the QUERY function has the following format =QUERY(data, query, headers). Replace “data” with the range of required cells (for example, “B5:E54” or “B:E”), and instead of “query” – insert the desired search query. The headings argument sets the number of heading lines to include at the top of the range.

What Is Google Sheets QUERY COUNT() function?

People use the COUNT function in Google Sheets query to count the number of rows according to the given condition. The formula looks as follows =QUERY(range, “select count(A) where B”). The select count function supports logical signs >, <, =, truth-functional operators: and, or. E.g., =QUERY(A1:B136, “select count(A) where A=’YourWord’ AND B>20 AND C<10”).

What is Google Sheets QUERY UNIQUE () function?

You can use the UNIQUE function in Google Sheets to count only unique non-repetitive rows from the query. To use it, add it around the QUERY function. E.g., =UNIQUE(QUERY(A1:B136, “select A, B”)).

Google Sheets Query a Workable Tool for Data Analysis

Google sheets query has proved to be an effective tool for data analysis, sorting, and manipulation. As a data scraping company DataOx, not only knows how to get big data from google but also processes it to meet the requirements and needs of the client. Working with google sheets queries is one of our routines, and we can assure you the possibilities and the potential of this tool is great. You can not only fulfill simple operations but create your own query formula for google sheets and work with big data spread over multiple tabs and sheets. You can master it all yourself or schedule a free consultation with our expert and decide how DataOx can help you with Google Sheets Data processing.