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.
info This article is only for Google users.
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
warning This article provides an overview of what Queries can do - with focus on real-world and practical examples. To learn more about the particulars, read Google Visualization Query Language.
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.
info This is a non-exhaustive list of clauses that are applicable in Awesome Table.
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 fiveclauses 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.
info We encourage you to copy the templates and actively participate in the exercises.
warning The use of lowercase, uppercase, camelCase, or Proper on the clauses is a matter of personal preference. What’s important is that the column IDs (identifiers) should always be in uppercase.
Task 1: Display everything contained in the datasource.
select *
Explanation: All columns in the Board Games datasource are displayed, following the default sequence of records (from top to bottom).
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
Explanation: Select all pertinent columns that we want to show, then display them in the specified order. In this example, columnN 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 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 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.
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.
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.)
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 app 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 csvFilterAnd (which can have multiple categories).
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:
select * where F contains 'Card Game' - will yield 3 results.
However,
select * where F contains 'Card game' or select * where F contains 'card game' - will both yield 0 results and show 'No data to display'.
Task 12: Display game(s) tagged under 2 categories: 'Card Game' and 'Ancient'.
select * where F contains 'Card Game'andF contains 'Ancient'
Explanation: Select all columns where the data in column F contains the words Card Game and Ancient. This will yield 1 result (7 Wonders) since that’s the only boardgame that meets both conditions.
The comparison operator contains is ideal for use in csvFilterAnd where each item can have multiple categories.
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 != ''
Explanation: To avoid displaying blank rows or blank cards in your app, you need to exclude them based on the specified column’s data-type.
Output: 6 cards
Any of the alternative statements below will yield the same result.
select * where A <> ''
select * where not A = ''
select * where A is not null
(For columns with numeric values)
select * where A != 0
Output: 6 cards
Any of the alternative statements below will yield the same result.
Use-case / scenarios: Focus on project deadlines (open / pending items) with applications in:
Education
Training
Publishing
Software development
Project management, etc.
Files Cabinet datasource
warning The deadline for Project D is dynamic and will always reference 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 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
Explanation: Select all columns where data in column D (deadline) is 2017 and column J (status) is tagged as delayed. Then sort by column D (deadline) in descending order.
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
Explanation: Select all columns where data in column D (deadline) is greater than or equal to the current date. Then order by column J (status) in descending order, then by column D (deadline).
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.
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' ")
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.
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.
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!!! ❤❤
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.
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. 🤦♂️
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:
Bypass your original datasource (negating the parameters [2nd] row containing filters and keywords)
And will use the aggregated output you specified, instead (only the columns you specified in the Query field will be displayed
This will also render your template useless.
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.
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.
select * where A != '' and B != '' and F != ''
> 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.
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.