How to Use Basic SQL Commands + Query Another Sheet in Google Sheets

‍

Let’s say you already have troves of data stored in your workbook, and you need to analyze it by querying it. How? Fortunately, Google has a built-in query language that we can use to put our data-crunching skills to the next level! We will first use it by learning how to query another sheet in the same file in Google Sheets.

The QUERY function and some Query Language commands

Google Sheets has its own QUERY function that can be used to pass Query Language commands to Google Sheets. Here are some of the commands:

• ‍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.‍
• order by - Sorts rows by values in columns.
  

You can visit the Google Query Language specifications page to read more about the commands. You can also check out our general introduction to Basic SQL Queries to learn more about each!

‍

‍

Google Sheets identifies the columns by its letter (as labeled on its top), and follows the following syntax:

QUERY(data, query, [headers])

Where data is the range of cells where the query command will be performed, query is the query we will send to be processed, enclosed in double quotation marks, and [headers] is an optional command to identify how many header rows are in the sheet. 

Our sample table is shown in the figure below:

As you can see, the data range is A1:F12 and has six columns: Title, Body Html, Product Type, Handle, Published At, and Variants Price. To understand how to use the query keywords, we will have some example commands below:

List the name of the product and its price

=QUERY(A1:F12,"select A, F")

List the products that cost more than 50 dollars per item

=QUERY(A1:F12,"select A where F>50")

List products with “WonderFoods” in its name

=QUERY(A1:F12,"select A where A contains 'WonderFoods'")

‍

Arrange the products in increasing price

=QUERY(A1:F12,"order by F")

How to query another sheet in Google Sheets

How can we query another sheet in Google Sheets? Simple! We simply insert the name of the source sheet in the command. So if, for example, the example source data we have above is in a sheet named “rawdata” and we want to query it in another sheet, we would simply do it in the same way as we would when we use another command in Google Sheets:

=QUERY(rawdata!A1:F12,"select A, F")

That’s it! 

‍

How to query another sheet from another workbook in Google Sheets

How about if it is from another Google Sheets workbook? Here is one easy way: Import the needed portion of the workbook to selected sheets of your own workbook, and then apply the QUERY command on those sheets. To do so, use the following command:

=IMPORTRANGE(workbook_url, data_range)

Make sure that the workbook is accessible to you and you know how the data looks like. If it is your own workbook and Google Sheets cannot access the workbook, a small box with Allow access button will appear, and the data will be loaded. You then proceed to apply the QUERY command.

Too much hassle?

I get it, this tutorial may be short, but you must be thinking that there are much more details that you will encounter when you start using these… 

Do you want to cut the chase and go straight to actually seeing the metrics that you need to make important decisions for your business? Consider trying Lido instead. 

With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!