Just a Query (Basics of Google Query)

One of the most underrated functions in Google Sheets, it has the potential to not only import data, but filter and sort it as well. All in one formula!

Covering the Basics

Function Syntax:

=QUERY(data, query, [headers])

• = (Functions start with =)

• Query (Keyword to start the Query Function, press tab to auto complete)

• data (Contains the data range to be imported. It can even be used using importrange or vstack function)

• headers(Place a number. Number of imported top rows considered to be headers. Its optional and can be set to even -1 for the system to identify it)

Basic Keywords:

Google sheet queries are written using some of the SQL keywords and if you're interested, you may review it from HERE

• Select: Selects the rows and columns from the data range shared

  • =QUERY(A2:D10, "SELECT A, B, C", 1)

    • Gets the Columns A,B and C from the data range with the first row as header

• Where: Filters the data based on the conditions shared

  • =QUERY(A2:D10, "SELECT * WHERE Col1 = ''", 1)

    • Retrieves the rows where Column 1 or A in this instance has no value in it with the first row as header

• Group By: Used to transform specified columns based on values and conditions shared

  • =QUERY(A2:D10, "SELECT Col1, SUM(Col4) GROUP BY Col1", 1)

    • Get the sum of Column 4 or D in this instance and places it with unique values from Column 1

• Order By: Used to sort the data range ascending(default) or descending(DESC)

  • =QUERY(A2:D10, "SELECT * ORDER BY Col1 DESC", 1)

    • Sorts the data in descending order in Column 1. Do note the entire table gets sorted according to the values in the column specified in Order By.

• Distinct: Gets unique values from the specified columns

  • =QUERY(A2:D10, "SELECT DISTINCT Col1", 1)

    • Gets unique values from Column 1 or A

  While these Five words should cover most of your requirements, there are other keywords, which you may find in the official documentation HERE

Some Constraints and Wildcards:

SQL has some constraints which you may consider as ways to keep data safe. While the query function doesn't help in setting constraints to columns, it does use the constraints to filter out data.

Constraints:

• NULL: A cell without value is considered as NULL. You may use it with WHERE to get the specified columns having or not having NULL values.

  • =QUERY(A2:B10, "SELECT A, B WHERE A IS NULL", 1)

  • =QUERY(A2:B10, "SELECT A, B WHERE A IS NOT NULL", 1)

• LIKE: Matches patterns, used for regex (supports ? for a single character and * for multiple characters)

  • =QUERY(A2:B10, "SELECT A, B WHERE A LIKE 'Jo?'", 1)

• CONTAINS: Checks if a text string contains a substring in that specified column

  • =QUERY(A2:B10, "SELECT A, B WHERE A CONTAINS 'apple'", 1)

• STARTS WITH: Matches if a string starts with a given substring and returns the values

  • =QUERY(A2:B10, "SELECT A, B WHERE A STARTS WITH 'A'", 1)

• ENDS WITH: Matches if a string ends with a given substring and returns the values

  • =QUERY(A2:B10, "SELECT A, B WHERE A ENDS WITH 'end'", 1)

• AND: Combines multiple conditions

  • =QUERY(A2:B10, "SELECT A, B WHERE A > 10 AND B = 'Completed'", 1)

• OR: Combines multiple conditions

  • =QUERY(A2:B10, "SELECT A, B WHERE A > 10 OR B = 'Pending'", 1)

• NOT: Negates a condition

  • =QUERY(A2:B10, "SELECT A, B WHERE NOT A = 5", 1)

Wildcards:

• ? : Single Character Wildcard, mostly used for text

• =QUERY(A2:B10, "SELECT A WHERE A LIKE 'J?n'", 1)

  • Finds text values such as Jon,Jan,Jen,Jin but not John as it contains more than one character after J and before n

• * : Multiple Characters Wildcard, used for selecting multiple columns

  • =QUERY(A2:B10, "SELECT A WHERE A LIKE 'J*n'", 1)

    • Finds text values such as John,Jonathan,Jan,Jen as * mean multiple characters

  • =QUERY(A2:D10, "SELECT *", 1)

    • Imports all the columns within the data range