Google Sheets SQL: A Complete Guide

Storing data in an organized way makes it easy for everyone to access and process the data. Google Sheets is one of the most popular tools for data storage and analysis. It allows its users to store data online for free. 

With Google Sheets, one can store data in cells and organize it in the form of columns. It is a great tool for data storage for both individuals and organizations. The good thing is that you can store data of any size in Google Sheets. 

However, when the data becomes too large, searching for a specific data within Google Sheets may become an issue. However, the Google Sheets SQL makes it easy for you. With Google Sheets SQL function, you can perform database-style searching on your spreadsheet. 

This way, you can perform searches quickly and easily. 

In this article, you will be seeing how to perform searches on Google Sheets using the Google Sheets Query function. 

Prerequisites:

You need the following things to perform the Google Sheets SQL function:

• A Google Sheets Account. 

Table of Contents

• What are Google Sheets?
• How to Access Google Sheets
• How to Create a New Spreadsheet 
• How to Add Data to your Spreadsheet
• Understanding the Query Functions
• How to Use the Google Sheets Query Function
• Create a Table
• SELECT all the Data
• SELECT Specific Columns
• WHERE Clause
• ORDER BY Clause
• LIMIT Clause
• COUNT Command
• AND and OR Clauses
• Arithmetic Functions
• Aggregate Function
• Group By Clause
• Working with Columns and Conditions
• Advanced techniques
• Limitations of the Google Sheets Query Function
• Conclusion

What are Google Sheets?

Google Sheets is a spreadsheet application developed by Google. It looks and works just like any other spreadsheet tool, but the fact that it’s an online tool makes it offer more functionalities to its users than other spreadsheet applications. 

Here are some of the factors that make Google Sheets better:

• It’s a web-based spreadsheet application that can be used anywhere. So, you will not be forgetting your spreadsheet files at home. 
• It can be used on different devices. It comes with mobile apps for iOS and Android as well as a web-based core app. 
• It’s a free app, and it’s connected to Google Docs, Drive, and Slides for sharing documents, files, and presentations online. 
• It comes with all spreadsheet functions provided in MS Excel. Most people know how to use MS Excel, hence, they feel at home using Google Sheets. 
• It allows you to use add-ons, create own add-ons, and create custom code. 
• Google Sheets is an online tool, hence, you can use your spreadsheet to gather data automatically and do almost anything you need even when the spreadsheet is not open. 

How to Access Google Sheets

With Google Sheets, you can create and format spreadsheets and collaborate with other people online. 

You can access Google Sheets on your computer by following the steps given below:

Step 1: Open the following URL on your web browser from here.

Step 2: The following web page will be opened:

Step 3: Scroll downwards on the web page to the “Get a head start with templates” section. 

You will see the “Go to Google Sheets” button on the top right corner of the window. Click it. 

Step 4: If you are already logged into your Gmail/Google account on the web browser, you will be taken to the Google Sheets Home screen. If you’re not logged in, you will be prompted to login. Just login using your Gmail/Google account and you will be taken to the Google Sheets Home screen that looks as follows:

You have logged into your Google Sheets account. The window shows a number of sample Google Sheets. If you open any of them, you will know what a Google Sheet looks like. All the spreadsheets that you have created will also be shown on the window. For each spreadsheet, you are given the name, the owner and when it was created or last modified. 

How to Create a New Spreadsheet 

To create a new spreadsheet, follow the steps given below:

Step 1: Click the “+ Blank” button on the Google Sheets home page. 

Step 2: You will be taken to a blank spreadsheet. If you are working on a particular spreadsheet and you need to create a new spreadsheet, click “File” from the menu bar and choose “Spreadsheet”. 

Step 3: A blank spreadsheet will be opened on a new tab. Type its name on the “Untitled spreadsheet” section at the top. 

You now have a blank spreadsheet. The Google Sheets interface is similar to other spreadsheet apps that you know with familiar text editing icons. 

How to Add Data to your Spreadsheet

As you can see on your screen, the Google Spreadsheet is made up of cells. If you begin to type, what you type will be populated into the selected cell, usually the top left cell. You can select any cell that you need and type something in it. 

Once you are done typing, press the ENTER key to save the data. You will be moved to the beginning of the next row. You can also press the TAB key to save the data and move to the right in the same row. Other than the mouse, you can use the arrow keys on your keyboard to move from one cell to another within the spreadsheet. 

In the next section, you will learn about Google Sheets SQL function in detail.

Understanding the Query Functions

To give instructions in SQL fashion in Google Sheets, the QUERY function is used. The format of the function is:

=QUERY(data, query, [headers])

In Google Sheets SQL, the QUERY function has three parameters where only two are required. The parameters are:

• data – Range of cells containing the data.
• query – SQL-like query.
• headers – Number of rows that contain header information.

How to Use the Google Sheets Query Function

The Google Sheets SQL function is a very important function to Google Sheets users. It supports the use of database-type commands to manipulate Google Sheets data. It is a very powerful and versatile function. 

If you have used SQL, you will find the Google Sheets Query function easy to use. In the next few sections, you will be seeing how to use Google Sheets SQL function to perform various tasks on Google Sheets. 

You can read more about Google Sheets SQL function from here.

Create a Table

We will be using the following data:

We need to join the data into a single table so that we can refer and access it easily in our Google Sheets Query function. 

Do this:

Step 1: Select the whole table. 

Step 2: Click “Data” from the menu and choose “Named ranges”. 

Step 3: A new window will pop on the right side of your spreadsheet. Type “countries” in the first input box. You will have named the table so that you can use it easily. Click “Done”. 

Good!

The table is now set up. 

We will be writing our pseudo-SQL code inside the Google Sheets Query function as shown below:

=QUERY(countries,"your SQL code goes here",1)

SELECT all the Data

You can use the SELECT * query to retrieve all the columns of your table. 

Type the following query on cell F1:

=QUERY(countries,"SELECT *",1)

Hit the ENTER key. 

The query will return the entire table:

SELECT Specific Columns

We can use the Google Sheets SQL function to select only specific columns. We want to select only the “Country” and “Rank” columns from the table. 

Modify your QUERY function to read as follows:

=query(countries,"select B, D",1)

This returns the following:

WHERE Clause

The WHERE clause helps you specify a condition that must be met and it’s good for filtering data. It should come after the SELECT clause. We want to see the names of countries that generate a revenue of more than $20,000. 

Modify your QUERY command in cell F1 to the following:

=QUERY(countries,"SELECT A, B WHERE B > 20000",1)

The query will return the following output:

Only three countries satisfied our search criteria. 

Let’s see the list of countries that are in Asia:

=QUERY(countries,"SELECT A, C WHERE C = 'Asia' ",1)

The query returns the following result:

ORDER BY Clause

You can use the ORDER BY clause to sort your data. You have to specify the columns to be sorted and the order of sorting, whether ascending or descending. It should come after SELECT and ORDER BY clauses:

Let’s sort our countries by revenue, from largest to smallest (descending order):

=QUERY(countries,"SELECT A, B  ORDER BY B DESC",1)

The query returns the following result:

To sort the data in ascending order, replace the DESC with ASC in the command. 

LIMIT Clause

You can use the LIMIT clause to restrict the number of results that are returned. It should come after the SELECT, WHERE, and ORDER BY clauses. 

Let’s modify our previous command to return the top 5 countries in terms of revenue:

=QUERY(countries,"SELECT A, B ORDER BY B DESC LIMIT 5",1)

The query returns the following result:

COUNT Command

The COUNT command can help you to count data. Let’s say that you want to count the number of countries per continent. 

Modify the command on cell F1 to the following:

=QUERY(countries,"SELECT C, count(A) GROUP BY C",1)

The query returns the following result:

The number of countries per continent has been shown. 

AND and OR Clauses

These two clauses can help you add multiple search criteria to your formula. For example, we can use the AND clause to search for countries that are located in Europe and generate a revenue of more than $20,000. 

The following query demonstrates this:

=QUERY(countries,"SELECT A, B, C WHERE (B > 20000 AND C='Europe')",1)

This returns the following result:

We can use the OR clause to get the list of countries that belong to either Europe or Asia:

=QUERY(countries,"SELECT A WHERE (C = 'Europe' OR C='Asia')",1)

The query returns the following result:

Arithmetic Functions

You can perform standard arithmetic operation on numeric columns. The following query demonstrates this:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)

The query returns the following output:

Aggregate Function

In Aggregate Function, you use other functions in your calculations. For example, min, max, etc.

The following query will calculate, minimum, maximum and average populations in your dataset.

=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)

The query returns the following result:

Group By Clause

The Group By clause is used with aggregate functions to leverage data in groups. You an use the following code:

=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)

The query returns the following result:

Working with Columns and Conditions

To refer to individual columns in Google Sheets SQL, the following query can be used:

=QUERY(A1:F23, "SELECT B, D, E WHERE C=3")

Instead of using column names from your table, you can use the column letters provided by Google Sheets. In this example query given above, the column Name is located in column B in the spreadsheet, so using SELECT B you can see that in the resulting table. Note that the columns are also separated with commas, but there is no comma after the last column you want to select.

After selecting the columns, you can add the column that you for to select. In the example shows,  WHERE C=3. C refers to the Stars column.

Advanced techniques

There are also a few other advanced techniques in Google Sheets SQL like :

• Adding a total row to your Query formulas
• Using dates as filters in your Query formulas
• Other data manipulation functions

Limitations of the Google Sheets Query Function

The Google Sheets Query function makes it easier for you to perform search tasks when using Google Sheets. 

However, it may become complex in some cases like when you want to filter your data using different factors or select data from different sheets. 

Conclusion

In this article, you’ve learnt how to use Google Sheets. You’ve also learnt how to use the Google Sheets SQL Function. 

For any information on Google Sheets HubSpot Integration, you can visit the former link.

If you’re looking for some alternative, we recommend you to try Hevo Data, a No-code Data Pipeline that helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo, with its secure integrations with 150+ sources & BI tools, allows you to export, load, transform, & enrich your data & make it analysis-ready in a jiffy.

Visit our Website to Explore Hevo

You can now transfer data from sources like Google Sheets to your target destination for Free using Hevo!

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Tell us about your experience of using Google Sheets SQL function in the comment section below.