In this tutorial, we'll teach you how to use Query statements in Awesome Table to: display only the columns of interest, reorder your columns, show items that meet the criteria you've set, sort your items, limit the records displayed, and label your column headers without touching the data source.
warningThis article serves as an overview of what Queries can do - with focus on real-world and practical examples. To learn more about the particulars, please read the Google Visualization Query Language.
Applying a Query using the Query expression syntax.
Let's start with the basics - Language clauses. The syntax of the Query language is composed of clauses. Each clause starts with one or two keywords. All clauses are optional. Clauses are separated by spaces. And the order of the clauses must be as follows:
Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order (from top to bottom).
Returns only rows that match a condition. If omitted, all rows are returned.
Sorts rows by values in columns.
Limits the number of returned rows.
Rename column headers.
*This is a non-exhaustive list of clauses that are applicable in Awesome Table.
Example using all5 clauses - following the prescribed sequence or order:
select * where C >= 90 order by C desc limit 10 label C 'Top 10'
select * - Use all columns in the data source.
where C >= 90 - Only display entries in column C whose value is greater than or equal to 90.
order by C desc - Sort the numeric entries on column C in descending order (highest to lowest).
limit 10 - Then display only the first 10 items.
label C 'Top 10' - Finally, label column C as Top 10.
2 - How to use Queries.
To enter your Query statement in Awesome Table's sidebar menu: go to ADVANCED PARAMETERS > Query.
3 - Hands-on examples using the Query expression syntax:
We're going to use 3 different AwesomeTable views in explaining how Query clauses work.
info We encourage you to copy the templates and actively participate in the exercises.
Task # 2: Display the search field first, followed by the remaining columns.
Explanation: Select all pertinent columns that we want to show, then display them in the specified order. In this example, column N houses the search field (stringFilter) and we want it to be the first filter displayed.
Output: 8 cards (including 2 blank ones)
info This is useful if you need to quickly reorder your columns without requiring you to manipulate the datasource. There are times that you need to reorder your view's columns, but is impracticalor cumbersometo reorder the underlying datasource columns (e.g. data generated by an external system like Google Forms, database software, ...)
Task # 4: Display everything then sort by Complexity in reverse alphabetical order, then by Complexity Level.
select * order by J desc, I
Explanation: Select all columns and sort the rows by the values in column J (Complexity) in descending order (Tough, Intermediate, Easy), then by the values in column I (Complexity Level) in ascending order.
Task # 5: Show only the Top 5 performers (starting with the highest scorer).
select * order by H desc limit 5
Explanation: Select all columns and only show the Top 5 candidates whose Final Score (column H) is sorted from highest to lowest (descending order). Or you can opt to reference the actual ranking (column I) and sort it in ascending order using this alternative statement: select * order by I limit 5.
Use-case / scenarios: Your Google Sheets datasource is linked to Google Forms. You need to rename some headers because some of the the linked-questions from Google Forms are too long. But renaming the datasource headers manually is not recommended. Doing so will create problems with:
the synchronization of submitted form responses or
have issues with other Google Sheets add-ons.
Corporate Promotion datasource
Task # 6: Relabel / rename the header of columns B and C as Nom de famille and Prénom, respectively.
select * label B 'Nom de famille', C 'Prénom'
Explanation: Select all columns and relabel column B's header as "Nom de famille" and column C's header as "Prénom".
Output: 34 candidates
info This is useful if you need to quickly relabel your column headers without requiring you to manipulate the datasource. There are times that you need to rename your column headers, but is impracticalor cumbersometo modify the underlying datasource columns (e.g. data generated by an external system like Google Forms, database software, ...)
Task # 8: Display games with Complexity Levels less than 4.
select * where I < 4
Explanation: Let’s say you only want to create a board games view excluding those with difficult / tough levels. So, we select all columns but only show rows where the data in column I (Complexity Level) is less than the numerical value of 4.
Task # 11: Display games belonging to the category "Card Game".
select * where F contains 'Card Game'
Explanation: Select all columns but only show rows where the data in column F contains the words Card Game. Here we used the complex comparison operator contains since column F uses the csvFilter (which can have multiple categories).
Output: 3 cards
warningString matching is case & space sensitive. The text string you use should be verbatim (or the same) as the one entered in your referenced column.
String Matching examples:
select * where F contains 'Card Game' - will yield 3 results.
select * where F contains 'Card game' or select * where F contains 'card game' - will both yield 0 results and show “No data to display.”
Use-case / scenarios: Focus on project deadlines (open / pending) items with applications in:
Project management, etc.
Files Cabinet datasource
warning The deadline for Project D is dynamic and will always be referencing the current date since it uses the today() function. The number of Query output generated will vary because of this (that is, the Query output will either add or subtract 1 file depending on the current month).
Task # 14: Only display project files with deadlines for the current month and year.
select * where month(D) = month(now()) AND year(D) = year(now())
Explanation: Select all columns where data in column D (deadlines) is the same as that of the current month and year.
Task # 18: Only display project files that have only been published within the last 6 months - including projects with deadlines the same as (or later than) the current date. Then order by status, then by deadline in reverse order.
select * where datediff(todate(now()), D) <= (30 * 6) order by J, D desc
Explanation: Select all columns where data in column D (deadline) is less than or equal to 6 months (30 days ✖ 6) - including projects with deadlines greater than or equal to the current date. Then sort by column J (status), then by column D (deadline) in descending order.
Task # 19: Only display project files that have only been published within the last 6 months - excluding projects with deadlines the same as (or later than) the current date. Then order by status, then by deadline in reverse order.
select * where datediff(todate(now()), D) <= (30 * 6) and
D < todate(now()) order by J, D desc
Explanation: Select all columns where data in column D (deadline) is less than or equal to 6 months (30 days ✖ 6) - excludingprojects with deadlines greater than or equal to the current date. Then sort by column J (status), then by column D (deadline) in descending order.