Tech > GSheets > Query
The Query function gives a subset of data from a specified range. Generally speaking it is more powerful than the Filter command and it will feel more familiar to DB developers due to the SQL type constructs.
Google ReferenceSyntax
Google ReferenceSyntax
- QUERY(data, query, [headers])
- Queary(A2:E6, "Select * Where A > 6 and B > 2)
- QUERY(A2:E6,"select avg(A) pivot B") ( a strange example from google...)
- QUERY(A2:E6,F2,FALSE) (another strange example from google...)
- =query(indirect(cellX),cellY) -- we use this nomenclature a lot so the query statement itself remains simple. One problem with using this is that the data specification will not change if you add or delete rows or columns as it would if you specify it directly in parameter 1.
- This is by far the most powerful database query type feature offered by GSheets and the help page is not good.
- While powerful, working with this can be tough, if the columns in your data start moving around...
- We have developed a powerful tool that utilizes this feature but enables you to work in plain English.
- AS COMPARED TO QUEARY...
- =query(stHeader0!A4:F,"Select * where B = 'Bob Jones'")
- THE FILTER RESULTS KEEP THEIR FORMATTING AND THEY DON'T WITH QUERY.....