Know All About the QUERY Function in Google Sheets

Introduction

Now, in this blog, you will know all about the QUERY function in Google Sheets.  Knowing all about the Query function in Google Sheets will help you retrieve data from a large dataset. It helps you to visualize the specific range of data from Google Sheets, which you have mentioned in your Query function. As a result, you don’t need to go through the hassle of finding your data from a large dataset. This function will help you to write SQL-like queries using the Google API Query Language. The function sends a request to the Google Sheets API using the Google Sheets Query, and it will receive filtered data as a result. 

Breaking Down the Syntax for the Query Function

The syntax for the Google Sheets Query function is – 

=QUERY(data,query,header)

Before using the QUERY function in Google Sheets, you should understand its syntax in detail. Let’s see how the elements of its syntax are worked. For this, you need to understand the importance of the three parameters – data, query, and headers.

• data

The ‘data’ parameter indicates the cell range for which you want to perform the query. The data columns can hold data types of numeric, string, or boolean. This data can include time, date, etc. However, if the data column contains multiple types of data, then the majority will determine the data type for the entire column. The values of the remaining data types will be considered null. 

• query

This parameter refers to the query, which you want to perform for your data. The value of the query should be written within the quotation marks or there should be a cell reference that contains the appropriate text. The queries have been written on Query Visualization API Language by Google. 

• header

‘Header’ is an optional parameter for Google Sheets Query. It refers to the number of header rows that are presented on the top of the data.  If you omit this parameter or set it as -1, then its value will be approximated based on the content of the ‘data’ parameter. 

Steps for Using the Query Function in Google Sheets

1. First, you have to create your dataset using Google Sheets.
2. Then, you need to write the Google Sheets Query function. Choose a cell where you want to write the function and then start your writing.

Go through these examples to learn how to write the Query functions for Google Sheets easily –

How to Use AND In Query Google Sheets?

From this example, we want to know about those locations where you will get stores in the ‘Finance’ category and where the salary is lower than $60,000.

1. First, click on the cell where you want to enter your Query formula.
2. Now, type the initial part of the formula. So, enter an equal (=) sign. Then, write Query and start an opening in parenthesis.
3. Next, you need to enter the first parameter data. Here, our cell range is A2:D5.    
4. Then, add a comma (,). It will separate your parameters. 
5. Now, enter the second parameter, which is a query. We have entered the query "select A, B, C, D where B contains 'Finance' AND D<60000". Ensure that you have used the quotation marks properly.
6. Finally, add a closing parenthesis to finish the formula and press Enter. 
7. Output for AND operator

This query will retrieve both the data from column B, which contains ‘Finance’, and where the salary range is lower than $60000 in column C. So, the Google Sheets Query function looks like this – 

=QUERY(A2:D5, "select A,B,C,D where B contains 'Finance' AND D<60000")

How to Use OR In Query Google Sheets?

Now, you have to follow the same steps to include the OR operator in a QUERY function. Here, you have to find an employee who works in the IT department or lives in Tokyo.  

 So, the query will be -

=QUERY(A2:C5, “select  B, C where B contains ‘IT’ OR C contains ‘TOKYO’’’)

•  Output for OR operator

This query works like this - 

• select A, B, C - These are the columns you have selected. 
• where B contains ‘IT’ - That means the query will find those employees who are working in the IT department from the B column. It’s the first condition. 
• OR - This is the logical OR operator. It will help to generate the output as per either one of the conditions mentioned here. 
• C contains ‘TOKYO’ - It’s the second condition. As per this condition, the keyword ‘TOKYO’ should be retrieved from the C column. 

Some Significant Clauses for Query Google Sheets

You have to compose your query language with several clauses. These clauses are optional, and they are started by one or two keywords. So, here you will learn some useful clauses for Google Sheets Query –

SELECT

SELECT is the basic clause for starting a query. It returns the rows which match the specific conditions mentioned in the SELECT clause. For example, if you want to select all the columns in a sheet, then the query will be –

=QUERY(A2:D5, “SELECT*”)

Remember that you have to put the * after SELECT. 

• Output for performing QUERY function for selecting all the columns
• Now, if you want to retrieve multiple and specified columns, then use this –

=QUERY(A2:D5, “SELECT A, B, C”)

• Output after selecting multiple columns

WHERE

It returns the rows that fulfill the conditions specified by the WHERE clause. For example, 

=QUERY(A2:D5, “SELECT A, C WHERE A= ‘Jack’”)

• Google Sheets QUERY function Output of the – WHERE clause

ORDER BY 

ORDER BY is used to sort rows in ascending or descending order. For ascending order you have to use the keyword ‘ASC’ and for descending order use ‘DESC’. For example, 

=QUERY(A2:D5, “SELECT  B, C, D WHERE B= ‘IT’ ORDER BY D DESC”)

• Google Sheets QUERY function – Output of the descendingorder.

=QUERY(A2:D5, “SELECT  B, C, D WHERE B= ‘IT’ ORDER BY D ASC”)

• Google Sheets QUERY function – Output of the ascending order

Benefits of Using Query Google Sheets

• Google Sheets Query function saves you time from manually writing formulas in each column. Also, it saves your time from copying and pasting and undue errors.
• Query functions sync in real-time. Thus, it provides fresh data to your Google Sheets every time. 
• When you update the datasets of query language in Google Sheets, it will update the corresponding data across your Google apps without any error. 

Don’t Miss the Workflow Automation Of xFanatical Foresight!

Once you have written the Google Sheets Query function, you can use them multiple times and tweak them to fit different purposes.  

By using the automation solution of Foresight, you can streamline and simplify your regular Google Workspace workflows. Now, you might be wondering how to simplify the mundane tasks of Google Workspace! Well, Foresight makes it possible for you. It has a simple and intuitive user interface that automates Google Workspace workflows. With some built-in templates and drag-and-drop functionalities, it automates your manual work process. So, now the Google Admin and users can automate their repetitive tasks without writing any automation script. 

So, use Foresight to speed up your operations and enhance productivity. Now, you can learn the use cases like - Export Active Users In Google Workspace, etc. to improve your efficiency. 

Conclusion

Now, you have developed a clear concept of Query Google Sheets. The Query function helps to fetch your target data without writing, performing any mathematical operation, or writing any formula. After writing the function, you will view the output within the next few seconds. 

Now, it's time to try out an automation solution from Foresight. At first, you may choose the 14-day free trial to utilize this platform and then go for a paid subscription plan. 

Now, you should learn other contents related to this one - QUERY function, etc.