0

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)

Munawir
  • 3,346
  • 9
  • 33
  • 51

2 Answers2

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"
Munawir
  • 3,346
  • 9
  • 33
  • 51
  • Unfortunately v4 of the G Sheets API no longer supports this pattern. Sigh. See https://developers.google.com/sheets/api/guides/migration#retrieve_row_data – Larry K Jan 03 '18 at 07:52
-1

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.

Karl_S
  • 3,364
  • 2
  • 19
  • 33
  • I need to get spreadsheet data as json object on external html page using the link as shown in question, no need to run function on spreadsheet. – Munawir Aug 15 '17 at 14:23
  • Let me know if the edit works. Your URL is different so you may need to adjust the URL a bit, but this works for me in our Domain's files. – Karl_S Aug 15 '17 at 15:39