Overview
The Programmatic Query Builder (PQB) is a Google Sheets template that brings a unique perspective to learning and using the Google Query function. I initially developed the template to simplify my own process for constructing queries in Google Sheets, but realized that it might also be interesting to users that are new to the QUERY function because it affords an experimental learning approach. I personally find that troubleshooting query formulas is also visually easier for me with this tool. It does not cover every query functionality in the Google universe, but it is intended as an aide that anyone could use to get started.
The basic operation is that you (Step 1) first provide the template with a target sheet name. The template then automatically lays out the columns contained therein as a menu for picking and choosing components that build up the QUERY select statement. Next (Step 2), you pick the fields that you’d like to display, adjust the data filtering and layout, and review the output. Finally, (Step 3) you iterate as necessary to refine the data output to your liking. You can also get as creative as you’d like - for example, lock in one query and build a second one to drill further into your data (example case demonstrated in the walk-thru video linked below).
The tool supports much of the functionality of seven of the main Google QUERY language clauses:
Select, Where, Group By, Pivot, Order By, Limit, and Label.
It’s comprised of 3 major sections/panels:
- Selections
- Query Text (+ Overrides)
- Query Result
The Selections panel is the main actionable area where you make the selections necessary to build the query. The Query Text panel displays the text of the query along with the individual language clause components and allows overrides of certain clauses and clause components. It also displays a “natural language” version of the query select statement for easier readability. The query results are displayed in the Query Result panel.
The following YouTube video provides a detailed walk-through.
Installation
To install the sheet, simply copy it into your Tiller workbook.
A read-only version is available at the following link: Programmatic Query Builder for Google Sheets
Once opened, right-click the tab “QUERY Builder” and choose “Copy To”–>”Existing Spreadsheet” and choose your Tiller workbook. Once copied to your workbook, you may rename the tab and reposition it wherever you like.
The solution contains no scripts.
Setup & Usage
All cells with possible selections are presented in light blue.
Once copied to your workbook, begin by entering the sheet name that contains the source data for your query. Enter it in the Selections panel cell labeled “Sheet:”.
Take note that the Limit field is pre-filled with a value of 150. This value restricts the number of rows displayed in the output query and can be cleared or adjusted at any time. The purpose of a default value is to prevent the possibility of a massive number of transactions flooding into the sheet before you are prepared for it. The adjacent cell tells you how many rows are expected from the current query. The value is displayed in red to alert you that the output has been restricted.
Select
If you check the box labeled as “Select ALL”, you should see your entire dataset in the Query Result section (except for any rows that are held back by the Limit value). This gives you a chance to look at the data and its structure to confirm that it looks correct for what you want to query against.
Pick a few of the boxes for individual fields and then untoggle the “Select ALL” box. You will see the choices reflected in the Query Text (+ Overrides) panel and the Query Result panel. Use the rank column to enter numeric values to change the order in which the columns appear in the output. The field named “Dbl” is further explained in the walk-thru video, but it basically allows you to pick both the regular column output plus an agg/scalar of that same field. Agg/scalar stands for aggregation and scalar functions. For example, you can choose to show the Average or Sum of numerical data.
Because only one agg/scalar is available per line, you use an additional field called “Adder” to free type additional agg/scalar operations (ex. avg(Col5) when Col5 is a numerical value like Price). The template appends them to the end of the select statement.
Where
The “where” language section is like a filter function. There’s room for two operators, which supports the critical task of filtering between two numerical values or two dates. An Adder input once again supports entry of additional filtering.
Above the “where” section, you will find a “Date presets” selection. It contains a dropdown with some date presets like “This Year”, “Last Calendar Year”, or my personal favorite “Last x Days”. For the “Last x Days” an additional cell will come to life for you to enter the number of days that you desire. After making the choice, a pre-formatted date range appears in a gray row just above the “where” block. You copy and paste it to the relevant row below. Another approach if you have a Date field is to reference these values directly by setting your Date row to equal the gray row. That way when you make a change to the date preset in the dropdown, the values and the query will automatically update.
When making certain selections such as “Single Date” or “Custom Dates”, more entry fields appear to allow custom date entries. There are a couple of entry options. You can type in a date manually, use Ctrl + ; (Windows) or Cmd + ; (Mac) to quickly insert today’s date, or (if a date is already present) double-click the cell and use Google’s in-built date picker. Once again, these dates get pre-formatted and placed into the gray section.
Group By
Next, the “group by” column contains checkboxes for each field. If you wish to group something, you need an aggregation. And if you aggregate something, you need to either group or aggregate. These things go hand and hand. If you select an agg/scalar and get a #VALUE error, or select a group by and get a #VALUE error, a missing grouping or aggregation is most likely the culprit. The PQB template includes common error messages and suggested remedies in the header rows.
Pivot
The “pivot” column also contains checkboxes for each field. Pivoting within the Google query function implies aggregation. It is going to transform unique values in the pivoted column into new columns. It can also be combined with the “group by” language.
Order By
The “order by” language allows you to sort the data rows either ascending (asc) or descending (desc) by the selected columns.
Query Text (+ Overrides)
In this section, there are three fields to consider. There is an “order by” override. If you use this field, you must completely type out your order by clause. This is useful if you need to order by fields that were entered as Adders – for example those extra agg/scalar selections.
Label overrides is the next possible entry to consider. You will find an automatically generated list of the relevant output fields in Column B. You have an opportunity to change what they say to whatever you desire in Column C.
And last but not least, you have a complete Query override available towards the top of Column B. This is especially helpful if you have something really close to what you want displayed in the programmatically produced query text and you want to try out just a slight edit. You can copy and paste the query that you’ve developed so far and adjust it here. You can also take your queries “to-go” from this area of the template.
At the top of column B there is a box called “Query Text – Natural”. This replaces the Col references with the actual Column names so that you can read off the query without having to juggle the cross references and it allows you to focus straight away on what the query is intending to accomplish. This query cannot be pasted into a function and expected to work, but it gives a good readable reference.
Permissions
Ok for personal use.
Notes
-
Note that this template/tool does not produce unbreakable queries. If the underlying source data columns move, the query will likely break and need to be repaired by updating the checkboxes and associated configuration selections at the respective new field(s) location(s). This is something that I might address in a future version. In the meantime, another solution for that issue would be to “take-out” your query and fortify it by replacing the Col references with lookups by column name. I may create a tutorial explaining this in more detail in the future.
-
The template can and probably will be broken by certain configurations. It will not accommodate every advanced query. Please let me know if you need any help and I will do my best to provide some support.
-
There will likely be enhancements made in the future. For example, the “and/or” selection in the where clause might be better served on a field-by-field basis. Another possible approach to selecting fields would be to use drop downs instead of checkboxes. This might be made to cure the brittleness problem mentioned above, where the query breaks if columns move in the source sheet. If you have any comments or suggestions, please let me know.
-
Be aware that some inputs are hidden until a drop down selection is made (ex. Date preselects) or until the query is built out with additional fields (label overrides). Just be careful not to overwrite or delete cells without ensuring that they are empty first.
-
The use of the “Adder” fields will also accommodate special selections like mathematical operations among columns (ex. Col1/Col2 or Col1+Col2, as long as the data supports such an operation). The “Adder” fields cannot be ranked at this time without manual update to the respective clause(s).
FAQ
None at this time.
