INTRODUCTION
GOOGLE SHEETS is the spreadsheet application offered by Google which is one of the best spreadsheet application.
One of the functionalities is the presence of FUNCTIONS which return the result after calculation.
Whenever we try to create smart sheets, we always try to automate it up to the maximum extent possible.
To automate anything, we need to cover as many as possibilities to make the program error free.
For the different requirements we have different available functions.
One special function about which we are going to learn in this article is the QUERY FUNCTION which lets you use the very powerful GOOGLE VISUALIZATION API QUERY LANGUAGE or simply GOOGLE QUERY LANGUAGE.
Query Function in Google Sheets lets you use the Query on any data present in your sheet. You can extract the data, the way you want, manipulate it with the specific conditions very easily.
So, in this article, we’ll learn about the QUERY FUNCTION of GOOGLE SHEETS and learn its usage.
PURPOSE OF QUERY FUNCTION IN GOOGLE SHEETS
QUERY FUNCTION lets you make use of the QUERIES from the GOOGLE VISUALIZATION API QUERY LANGUAGE in Google Sheets on the specific data.
LEARN ABOUT QUERY LANGUAGE HERE.
Query language, which we’ll be using this function on our data, helps us to find a tailored result from the complete selected data within seconds.
The result will be based upon the conditions we asked the Google Sheets using this language or statements.
PREREQUISITES TO LEARN QUERY FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- If you have any basic understanding or usage information about the SQL QUERIES , it’ll be super easy for you to learn GOOGLE QUERY LANGUAGE.
- But even if you don’t have any knowledge about SQL, we are still good to go. It is still easy!.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel? [ Although this article is for EXCEL but it’ll help you understand the basic working of any spreadsheet application including EXCEL]
GOOGLE QUERY LANGUAGE
Click the link to get more information about Query Language.
SYNTAX: QUERY FUNCTION
The Syntax or the way to phrase the function for the QUERY FUNCTION is
=QUERY( LOOKUP DATA , QUERY IN THE DOUBLE QUOTES, NUMBER OF HEADER ROWS )
LOOKUP DATA is simply the table on which we’ll apply the query. It can be of a few rows or hundreds of rows or columns. For Example, A1:A1000 or A1:B234 and so on.
QUERY is the STATEMENT which will perform on the selected data. It will be inside the DOUBLE QUOTES [ ” ” ].
NUMBER OF HEADERS is the number of header rows within the data. If -1 is selected, the Google Sheets will try to Guess. If 1 is selected, top one row will be considered as the Header. [ We’ll clarify it more in the example ].
LET US NOW LEARN THE PRACTICAL USAGE OF QUERY FUNCTIONS TRYING A FEW EXAMPLES.
EXAMPLES:
SAMPLE DATA:
GOOGLE QUERY LANGUAGE is all about extracting the data from the given table. [ Max of the times ].
Let us create a table with fictitious data and try a few examples on the same.
The following table shows the EMPLOYEE DETAILS of GYANKOSH.NET
The COPY OF THE SHEET IS HERE for your personal use and practice. [ The link is not present yet.]
We’ll take a couple of examples to understand the usage of Query functions.
EXAMPLE 1: FIND OUT THE EMPLOYEES WHO ARE OLDER THAN 26 YEARS
SOLUTION:
For this problem, the traditional way is to create a new table and filter the data or put an IF Function and try to get the status if the employee is more than 26 or less than 26 and then make some tabular changes.
but we are very lucky to have Queries.
In a single shot, we’ll get the result as we want.
FOLLOW THE STEPS TO FIND OUT THE EMPLOYEES OLDER THAN 26 YEARS.
- Select the cell by clicking it where you want the result.
- Enter the function as =QUERY(B6:F18,”SELECT C WHERE D>26″,-1)
QUERY FOR THE SOLUTION: SELECT C WHERE D>26 Isn't it simple. C contains the employee names. D contains the employees' ages. We are simply asking the QUERY FUNCTION to return the names of the employees whose age is more than 26.
- Press Enter.
- The result will appear as shown in the picture below.
SELECT
So, you can see that we didn’t make use of any other option or deletions or manipulation to get this data.
Simply add the query and get the solution.
Let us try to get one more data.
Suppose, we want the age too with the names.
Edit the query as shown in the function below.
=QUERY(B6:F18,”SELECT C,D WHERE D>26″,-1)
We simply added D with the C which will return both the columns.
The result will be as shown in the picture below.
EXAMPLE 2: FIND OUT THE EMPLOYEES OF THE OPERATIONS DEPARTMENT
SOLUTION:
This is again a simple usage of the QUERY FUNCTION.
FOLLOW THE STEPS TO FIND THE NAMES OF THE OPERATIONS DEPARTMENT EMPLOYEES
- Click the cell to select it where you want the result to start from. [ Always keep ample number of rows and columns for the result , if not, it’ll overwrite everything in the result cells ]
- Enter the Query as =QUERY(B6:F18,”SELECT C WHERE F = ‘OPERATIONS'”,-1)
- Press Enter.
- The result will appear as shown in the picture below. [ The formula used can be seen in the Formula Bar.
So, we can see the ease of getting the data from the table using the QUERY FUNCTION.
So, in this article we learnt about the Query function, its syntax formula, purpose and a few examples.
Let us discuss a few more things about this function.
HOW TO SET THE NUMBER OF HEADERS IN QUERY FUNCTION IN GOOGLE SHEETS ?
So, the parameter number 3 in the syntax of the QUERY FUNCTION asks about the Number of rows at the top which will act as the Header in the result.
It simply means if the Top Two rows of the selected data are headers, it’ll be returned as the header only in the result too.
Let us take an example.
Let us create the name of the first two employees as the HEADERS and check out the result.
For this, the function will be
So, we saw the effect of setting the number of header rows in the Google Sheets Query Function.
WOULD THE RESULT OF QUERY FUNCTION CHANGE WITH THE CHANGE IN THE ORIGINAL DATA ?
THE ANSWER IS YES !!!
The QUERY FUNCTION is very alive.
As soon as there is change in the original data i.e. the data where we are looking up, the query will be refreshed and the result will be updated.
