Working with Google Sheets’ QUERY Function

          So far we’ve covered some ground when it comes to using spreadsheet functions within MS Excel ecosystem. Perhaps it’s time to turn to one of its leading competitors: Google Sheets ? When GoogleSheets was launched noone, but perhaps its creators would seriously consider it to be a real threat the mighty, ubiquitous Excel spreadsheet package. Over the years this product went through a plethora of incremental, as well as break-through enhancements. By now GoogleSheets achieved an admirable user base backing it’s strong functionality matching and in some instances surpassing the gold standard continuously maintained by the much beloved Excel program.

          One of most versatile Sheets’ functions is the QUERY function. Its SQL-like syntax allows us to retrieve specific information from our worksheet, enabling various filtering, sorting, lookup, and aggregation capabilities. While this function is more powerful than INDEX/MATCH, VLOOKUP, SUMPRODUCT, SUMIFS and others, its syntax is rather simple: QUERY(Data, Query, [Headers]) .

Data – range of cells we want to query from

Query – actual query code, to be enclosed in quotation marks or be a reference to a cell containing query text.

Headers – OPTIONAL, number of header rows at the top of data, when omitted value is set to -1.

          If you can comfortably compose basic SQL queries, you will find this function rather intuitive and easy to use. However, you might also find yourself wanting to throw your keyboard across the room when some of the SQL functionality we take for granted is either not found or not implemented properly in this function. Working with aliases has definitely topped my personal list of annoyances.
Continue reading