tq= Spreadsheet Table Query


Spreadsheet Table Query

The following of examples of how the spreadsheet table query can be used.

The Spreadsheet Queries can be used internally in Spreadsheet cells or as a standalone URL and also is a core part of the Google Visualization API.

These Queries are based on a subset of MySQL and work very similar to standard PHP Query request supporting URL encoding  where necessary and more recently "+" .

.i.e.

"+"  or  "%20"

for a blank space in between a table query request.

Encoding Example using Plus

https://docs.google.com/spreadsheets/d/14IhHe13KLhitNcmAFUFDmqs0VlPoT0ufi_p0Rzoa0pY/gviz/tq?tqx=out:html&tq=Select+A+,B,C+,D+where+C%3D%27Contacts%27+Order+by+A+asc


Spreadsheet Table Query - Display Bug Fixed.








Output Formats a spreadsheet as HTML



https://spreadsheets1.google.com/spreadsheet/tq?tqx=out:html&key=0AjU_rJcQHTNFdE9Iaml2Y0FzLTVfalZ5LW5mUWJsSkE

Embed the Spreadsheet Html in Google Sites


Embed the html output in an <IFrame> tag.

<iframe src="..."> Loading Spreadsheet!</iframe>




Filter HTML output by Keyword:


Normal Cell Query Function:

=QUERY(  A1:D ; "Select A ,B,C ,D where C='Contacts' Order by A asc"  ; 1)

select columns ABCD in range A1:D,where C value is equal to "Contacts" , Order the sheet by A asc A ascending, display the first row  ; 1)


Encoded function for table query parameter:

Select%20A%20%2CB%2CC%20%2CD%20where%20C%3D'Contacts'%20Order%20by%20A%20asc



https://spreadsheets1.google.com/spreadsheet/tq?tqx=out:html&tq=Select%20A%20%2CB%2CC%20%2CD%20where%20C%3D'Contacts'%20Order%20by%20A%20asc&key=0AjU_rJcQHTNFdE9Iaml2Y0FzLTVfalZ5LW5mUWJsSkE



Filter HTML output by Latest Date:


The latest date for a particular item.

Normal Cell Query Function:

=QUERY(A1:D ; "Select C ,max(A) where C<>'' Group by C"  ; 1)

select columns C  in range A1:D and get the maximum column A value, where C is <>'' not Blank [could also use !=''], Group the sheet by C, display the first row  ; 1)


Encoded function for table query parameter:

Select%20C%2Cmax(A)%20where%20C%3C%3E''%20Group%20by%20C


https://spreadsheets1.google.com/spreadsheet/tq?tqx=out:html&tq=Select%20C%2Cmax(A)%20where%20C%3C%3E''%20Group%20by%20C&key=0AjU_rJcQHTNFdE9Iaml2Y0FzLTVfalZ5LW5mUWJsSkE










Spreadsheet Query Reference


Query Language Reference