google-spreadsheetQuery Function

Remarks

Official Documentation

Google Docs editors Help

Google Charts on Google Developers

Introduction into queries

Source table

RowABCD
1CodeProductColourPrice
21penred500
32penblue-50
43penred0
54pencilblue17
65pencilgreen-1.5

to select all:

= QUERY(A1:D5, "select *")

or

= QUERY(A1:D5, "select A, B, C, D")

or convert data range into array and use this formula:

= QUERY({A1:D5}, "select Col1, Col2, Col3, Col4")

Sorting with QUERY()

ABCD
1penred500
2penblue-50
3penred0
4pencilblue17
5pencilgreen-1.5

To sort by column D with "order by":

=QUERY("A1:D6","select * order by D desc",1)

Filtering with QUERY()

ABCD
1penred500
2penblue-50
3penred0
4pencilblue17
5pencilgreen-1.5

To only return "pencil" data:

=QUERY("A1:D6","select * where B='pencil' ",1)

To only return rows that contain "pen" (all rows):

=QUERY("A1:D6","select * where B contains 'pen' ",1)

To only return rows where the price is greater than 0:

=QUERY("A1:D6","select * where D>0 ",1)

Note that text strings require apostrophes while numerical values do not.

Filter a query by an aggregation result

=QUERY(QUERY(A1:D6,"select C,SUM(D) group by C",1),"select Col2>0",1)