Google Sheets SQL: A Complete Guide

• February 1st, 2022

google sheets sql

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?

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:

Google Sheets SQL: Google Sheets - Transfer Data for Free from Google Sheets to your target destination

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. 

Google Sheets SQL: Go to Google Sheets

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:

Google Sheets SQL: Google Sheets Home

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. 

Google Sheets SQL: Google Sheets Home page - Transfer Data for Free from Google Sheets to your target destination

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”. 

Google Sheets SQL: Create new spreadsheet

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

Google Sheets SQL: Blank Google Sheet

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.

Hevo, A Simpler Alternative to Integrate your Data for Analysis

Hevo Data offers a faster way to move data from databases or SaaS applications into your data warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Get Started with Hevo for Free

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 150+ sources that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support call.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Transfer Data from sources like Google Sheets to your target destination for Free!

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:

Google Sheets SQL: Google sheets data - Transfer Data for Free from Google Sheets to your target destination

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. 

Google Sheets SQL: Select data

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

Google Sheets SQL: Click on data and select "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”. 

Google Sheets SQL: Name the table - Transfer Data for Free from Google Sheets to your target destination

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)
Google Sheets SQL: query result

Hit the ENTER key. 

The query will return the entire table:

Google Sheets SQL: query result

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:

Google Sheets SQL: query result

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:

Google Sheets SQL: query result

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:

Google Sheets SQL: query 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:

Google Sheets SQL: query result - Transfer Data for Free from Google Sheets to your target destination

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:

Google Sheets SQL: query 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:

Google Sheets SQL: query 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:

Google Sheets SQL: 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:

Google Sheets SQL: 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:

Google Sheets SQL: result

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:

Google Sheets SQL: 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:

Google Sheets SQL: result - Transfer Data for Free from Google Sheets to your target destination

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.

select three-star hotels only

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.

Free No-Code Data Pipeline for Google Sheets