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
|
|
Comments
17 comments
Hi,
I have a little problem using LABEL and ORDER clauses at the same time.
If I write:
SELECT A, C, D ORDER BY D --> the query works fine
but with:
SELECT A, C, D LABEL A ' Name', C 'Phone' ORDER BY D --> the query sends an error message!!
Is there a little bug or I'm missing anything?
Thanks in advance for your help!
Gavp
BTW... great web site, product and ideas, congratulations!!
Hello Gustavoavalerop,
Actually, when you use SELECT and ORDER, ORDER must be just after SELECT. I suggest you use:
It should work with this method.
Kind regards.
Thomas
Great Article! it helped me on some of the things I have been working on.
I have one that I'm asking it to essentially filter more. But once I get to a certain point, Then the rest stops working. What am I doing wrong? In the below formula, A is bringing me Active and inactive. Manager sometimes shows up and sometimes doesn't depending on if I add the Active one or not. Is there a specific way this order should be to make it work?
=QUERY(Lookup!A:Y, "select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' ")
Sean
Hello Sean Gillard,
Actually, your syntax is not correct. You don't need to use"=QUERY(Lookup!A:Y...".
Your query must be like this:
In this way, it will work because that's the correct syntax.
Thank you.
Thomas
Hello
Is it possible to SELECT rows that are not duplicates (based on value in a specified column)?
I can deduplicate in Google Sheets, but was wondering if I could do it in an Awesome Table view.
Best regards
Dave
Hello Dave HUDDART,
It is definitely possible to select rows depending on specific term or criteria. You should consult this part of this article to know how to achieve that.
Kind regards.
Thomas
🙌🙌
The SORT option was VERY helpful; I needed newest data at the top, and doing this through Google Sheets is tedious. This saved me SO much headache!!! ❤❤
Hello Brandon Carraway,
Thank you for your message!
We are really glad you like our product and you find it is useful.
Kind regards.
Thomas
Hi,
Is it possible to add a button to sort the results in diferent ways ? I'd like to have the possibility to sort by one colums (alphebeticaly) or a diferent way by clicking on a button.
Thanks !
Hello Luis Esteves,
There are no ways to change the sorting or query with a button.
Nicolas
How to sort two columns (L, I)
I need to sort by column L desc, then by I desc I tried this: "select * order by L desc and order by I desc" even using a "," instead of "and" as I was reading through the above guide, but I can't seem to figure it out. 🤦♂️
Hi @Brandon.
Instead of select * order by L desc and order by I desc, use this statement:
Please reference Task # 4 above for more info.
Hope this helps.
- Randy
Hello, is it possible to use a group by statement?
Thank you
Hi @Florentin.
The group by clause can and will work when entered in the Awesome Table editor's Query field.
But I don't recommend using this.
Please note that since the group by clause can only be used with an aggregation function, it will perform the specified aggregation (avg, count, max, min, sum) and in effect:
An alternative would be to use the order by clause to cluster items based on a specified category. More about this on Tasks # 3 - 4 above.
Hope this helps.
- Randy
I have built an awesome table that has topic (COL A), grade (COL B), and curriculum standards met in different subject areas (COL C-K).
I filtered as follow:
COL A CategoryFilter(B) - Hidden
COL B CategoryFilter
The rest of the columns have no filter
I want the results to only show columns with data in them.
Right now a search might yield this:
but I want all the columns with blank entries (in this case COL C,D, F,G, H, etc) removed. Is there a query for this?
Jen
Hi @Jen.
To avoid displaying blank cells (and their corresponding rows) on your view, simply follow the instructions detailed in Task # 13 above. Since you need to specify multiple non-blank columns, you need to use the logical AND operator. For example, the Query statement below will specify that it will only show or display rows where (all 3) columns A, B and F doesn't have blank entries.
> Just replace and/or add the relevant columns in this statement for your use case.
* Don't forget to click the UPDATE VIEW button for the Query statement to take effect.
- Randy
Hello everyone! is there a way to sort the row data randomly with the queries?
Please sign in to leave a comment.