Get rows having specific value in google spreadsheet feed

Question

I was able to get complete json feed of google spreadsheet using,

https://spreadsheets.google.com/feeds/list/--KEY--/od6/public/values?alt=json-in-script

Is it possible to get only certain rows from google spreadsheet by comparing values in column, like we do in sql ? (For eg. get rows where age==25)

Answer 1

Finally, got the answer.

You can use query like

&sq=age="25"

If you want to use multiple queries use

&sq=age="25"+and+name="Full%20Name"

So the final url will be like

https://spreadsheets.google.com/feeds/list/--KEY--/od6/public/values?alt=json-in-script&sq=age="25"+and+name="Full%20Name"

Answer 2

To get a URL of a spreadsheet and add a query, add the following to the end of the file's URL, after the ID

/gviz/tq?tqx=out:html&tq=select+B,D,E+where+C+%3C%3E+25&gid=427073627

Change the value after gid to the tab/sheet ID. For one of my spreadsheets the entire URL would be:

https://docs.google.com/spreadsheets/u/1/d/1EA9jh4ztcWCO5gT9LfAQ57oTlTEeUoiGkjmro/gviz/tq?tqx=out:html&tq=select+B,D,E+where+C+%3C%3E+25&gid=427073627

Old response - Misunderstood where the query was required

You can use the query() function to do some queries. If the Age is in column C, you could use this:

=query(A1:E, "select B, E, D where C = 25", 1)

This will query the columns A to E from the first to the last row in the sheet. The 1 at the end says first row is a Header row so it is returned in the row where the formula exists. The current column will show the contents of column B, the next the contents of column E, and then the contents of column D for any row where column C has 25.