The Blog of Someone Who Builds Things on the Internet

I google how to do things, then post the answers here cause I'll probably forget. Maybe someone else finds it useful.

Using SQL with Google Sheets to create Views

Published February 27, 2017

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