Using SQL with Google Sheets to create Views

I’m not a huge fan of Google Apps or whatever they are calling them these days, as I prefer the power of Office. But 365 is not free and I really do appreciate the power of realtime collaboration you get when working on a Google Apps file.

Something I just discovered was that you can use SQL in Sheets. You can query up any table (set of data) you have using SQL. Being a programming, and always wanting to see data in different views, I love this.

The format is:

=QUERY(table, sql, headers)

where:

  • table: the reference of the table you want to use. Example: SheetName!A1:X100
  • sql: Straight up SQL code
  • headers: Does your table have a row of headers on it? Default is 1

Use example

  1. Setup: You have a sheet named Sheet1 when a bunch of data in it from column A to E, and 100 rows. Headers for the data are in Row 1
  2. Make a new sheet. Call it view or whatever.
  3. Select cell A1. (This is the part it took me a few minutes to wrap my head around. You enter your code in a cell, then it just uses cells around it to show the results)
  4. Enter the following
=QUERY(Sheet1!A1:E101, "SELECT A, B WHERE C <> '' AND D = 'NO'")

You now will get a result of data having the info in columns A & B where C is not blank and D is NO.

Awesome right?

Note: when selecting Sheet1, if you just wanted to select all rows in columns A through E, you could just do:

Sheet1!A:E

Source

Leave a Reply