Tutorial: Spreadsheet Functions Part 3

This one is going to be all about the QUERY function. I love using this one. You can do a lot of exciting things with it. Make sure to also read Part 1 and Part 2 to get to know the setup as well as some other functions and neat examples.

QUERY is an awesome function because you can basically use your spreadsheet as a database and query it which means you have a lot of possibilities. In some cases it is also a lot easier to use than doing the same thing by using other functions. 

The function

QUERY(data, query, [headers])

• data: this is the range you will query on
• query: the actual query string. I’m going to show you different examples for this one but in case you’re interested in more, you can read about the query language here.
• header: this is optional. It states how many header rows are part of the range (which you can omit by making the range smaller/starting at the next row)

Queries

• Use select to choose which columns you want to get
  Get the complete column of titles (B):
  =QUERY(GR!A2:Z,”select B”)
• Use where to choose which rows to get
  Get the complete rows of books with less than 500 pages (L):
  =QUERY(GR!A:Z,”where L < 500″) (this is equal to “select * where L <500” which means select all columns)

Usually you use a mix of the two:

• Get the title (B) and author (C) of books with less than 500 pages (L):
  =QUERY(GR!A2:Z,”select B,C where L < 500″)

Instead of getting whole columns, you can calculate something across columns:

• Sum up all values in a column
  Sum up the pages of all books:
  =QUERY(GR!A:Z,”select sum(L)”)
• functions: sum, avg, count, max, min

You can also change the values in the columns

• Get the year of all dates (O):
  =QUERY(GR!A2:Z,”select year(O)”
• You can also use them in the where clause
  Get books read this year [use now() to get the current one]:
  =QUERY(GR!A2:Z, “select B,C where year(O) = year(now())”)
• functions: year, month, day,…

There are a ton of options what to put into the where clause:

• Combine multiple conditions with AND or OR
  Get books with less than 500 pages or more than 1000 pages:
  =QUERY(GR!A2:Z,”select B,C where L < 500 or L>1000″)
  Get books with more than 1000 pages (L) and a rating (H) of 5:
  =QUERY(GR!A2:Z,”select B,C where L > 1000 and H=5″)
• Check of a cell contains a certain value
  Get the books from a shelf called “favorites” (Q contains “favorite”):
  =QUERY(GR!A2:Z,”select B,C where Q contains ‘favorite’ “)
• Check if an entry starts with a certain sub string
  Get all authors whose name starts with A:
  =QUERY(GR!A2:Z,”select C where C starts with ‘A’ “)

Order the results according to a certain row

• Order by asc or desc
  Get all titles and authors of books, sorted by number of pages
  =QUERY(GR!A2:Z,”select B,C order by L”) (asc is automatically used)
  Sort them with highest number first:
  =QUERY(GR!A2:Z,”select B,C order by L desc”)

Limit the number of results

• Only return a certain number of rows
  Get 5 books with more than 1000 pages:
  =QUERY(GR!A2:Z,”select B,C where L>1000 limit 5″)

Handle errors

There are different situations where the function might return an error, which never looks pretty in a spreadsheet but can easily be prevented. Possible errors that might arise even though the function is correct:

• N/A: the query couldn’t find any entries that fit the query
• DIV/0: This can for example happen when you try to calculate the average but there aren’t any values

In all cases you can make it look better by using the IFERROR function.

• If there is an error, return “No books founds”:
  =IFERROR(QUERY(GR!A2:Z,”select B,C where L>1000 limit 5″),”No books found”)

Feel free to ask questions in the comments.

For more Code & Design Tutorials see this page.