2
In this tutorial, you will learn 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 have set, sort your items, limit the records displayed, and label your column headers without touching the data source.
What are Queries?
Queries let you show only a specific subset of your data by using the Google Visualization Query Language. With Query in Awesome Table, you can:
- view data coming only from selected columns
- reorder the columns
- rename or relabel column headers
- show rows (records) based on specified criteria
- sort data
Apply 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. The order of the clauses must be as indicated in the table below.
| Order | Clause | Usage |
|---|---|---|
| 1 | select | 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). |
| 2 | where | Returns only rows that match a condition. If omitted, all rows are returned. |
| 3 | order by | Sorts rows by values in columns. |
| 4 | limit | Limits the number of returned rows. |
| 5 | label | Rename column headers. |
Example using all five clauses listed above, following the prescribed sequence or order:
select * where C >= 90 order by C desc limit 10 label C 'Top 10'
Translation:
- 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' - fnally, label column C as Top 10
How to use Queries
In the Awesome Table editor:
1. In the sidebar, open the Advanced parameters section.
2. Enter your Query in the Query field.
Hands-on examples using the Query expression syntax
We are going to use three different Awesome Table apps to explain how Query clauses work.
| Clause | Demo Template | Description |
|---|---|---|
| select and order by | Board Games | A collection of 6 board games (+ 2 intentionally inserted blank rows). |
| label | Corporate Promotion | A roster of 34 candidates of which only the top 5 will make it through the management training program. |
| limit | Corporate Promotion | A roster of 34 candidates of which only the top 5 will make it through the management training program. |
| where with criteria | Board Games | A collection of 6 board games (+ 2 intentionally inserted blank rows). |
| where with dates | Files Cabinet | A folder containing project files in various formats (Google Docs, Sheets, Slides and graphics). |
Select and Order by
Board Games datasource
|
Task 1: Display everything contained in the datasource.
select *
Output: 8 cards (including 2 blank ones)
|
|
|
Task 2: Display the search field first, followed by the remaining columns.
select N,A,B,C,D,E,F,G,H,I,J,K,L,M
Output: 8 cards (including 2 blank ones)
info This is useful if you need to quickly reorder your columns without having to manipulate the datasource. There are times that you need to reorder your app's columns, but is impractical or cumbersome to reorder the underlying datasource columns (for example, data generated by an external system like Google Forms, database software.)
|
|
|
Task 3: Display everything then sort by Complexity in reverse alphabetical order.
select * order by J desc
Output: 8 cards (including 2 blank ones)
warning You can also specify asc for ascending order.
info To save time, you can omit asc altogether since the Query output is arranged in ascending order by default (low-high or a-z).
|
|
|
Task 4: Display everything then sort by Complexity in reverse alphabetical order, then by Complexity Level.
select * order by J desc, I
Output: 8 cards (including 2 blank ones)
|
|
Limit
Copy the Corporate Promotion template
Use-case / scenarios:
Display only the top-ranked items with applications in:
- Education (Top 10 students, Top 100 applicants, Scholarship recipients, etc.)
- Corporate (Ranking for: Departmental contest, Grants / funding, promotion, etc.
Corporate Promotion datasource
|
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.Output: 5 candidates
|
|
Label
Copy the Corporate Promotion template
Use-case / scenarios:
Your Google Sheets datasource is linked to Google Forms. You need to rename some headers because some of 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 having to manipulate the datasource. There are times that you need to rename your column headers, but is impractical or cumbersome to modify the underlying datasource columns (for example, data generated by an external system like Google Forms, database software.)
|
|
Where with Criteria
Board Games datasource
|
Task 7: Display games with Complexity Levels greater than or equal to 3.
select * where I >= 3
Output: 3 cards
|
|
|
Task 8: Display games with Complexity Levels less than 4.
select * where I < 4
Output: 4 cards (+ 2 blank ones)
|
|
|
Task 9: Display games aimed at our seasoned players (Tough category).
select * where J = 'Tough'
The simple comparison operator equal to (=) is ideal for use with categoryFilter where only one category (per column) can be selected at one time. Output: 2 cards
|
|
|
Task 10: Display games intended for both the easy and intermediate players.
select * where J = 'Easy' or J = 'Intermediate'
Output: 4 cards
|
|
|
Task 11: Display games belonging to the category 'Card Game'.
select * where F contains 'Card Game'
Output: 3 cards
warning String matching is case and space sensitive. The text string you use should be exactly the same as the one entered in your referenced column.
String Matching examples:
|
|
|
Task 12: Display game(s) tagged under 2 categories: 'Card Game' and 'Ancient'.
select * where F contains 'Card Game' and F contains 'Ancient'
The comparison operator contains is ideal for use in csvFilterAnd where each item can have multiple categories. Output: 1 card
|
|
|
Task 13: Your eyes may be getting tired from seeing those red-blank cards. The challenge in this exercise is to prevent blank cards from ever displaying.
(For columns with text strings)
select * where A != ''
Output: 6 cards Any of the alternative statements below will yield the same result.
(For columns with numeric values)
select * where A != 0 Output: 6 cards Any of the alternative statements below will yield the same result.
|
|
Where with Dates
Copy the Files Cabinet template
Use-case / scenarios:
Focus on project deadlines (open / pending items) with applications in:
- Education
- Training
- Publishing
- Software development
- Project management, etc.
Files Cabinet datasource
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())
Output: 2 files
|
|
|
Task 15: Only display delayed project files from the previous year (2017) then sort by deadline in reverse order.
select * where year(D) = 2017 and J = 'Delayed' order by D desc
Output: 6 files
|
|
|
Task 16: Only display the 2018 project files then sort by status, then by deadline.
select * where year(D) = 2018 order by J, D
Output: 8 files
|
|
|
Task 17: Only display project files with deadlines the same as (or later than) the current date. Then sort by status in reverse alphabetical order, then by deadline.
select * where D >= todate(now()) order by J desc, D
Output: 5 files
|
|
|
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)
Output: 12 files
|
|
|
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
Output: 7 files
|
|