CODEWITHSUNDEEP
google-sheets
sweslo17
sweslo17

Reputation: 21

Query Google Spreadsheet with URL Parameters

I have recently opened a new spreadsheet: https://docs.google.com/spreadsheets/d/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo/pubhtml

I want to find 'title' which url=http://www.ettoday.net/news/20140327/339912.htm

I read google api doc and tried this:

spreadsheets.google.com/feeds/list/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo/0/private/full?sq=url%3D%27http%3A%2F%2Fwww.peoplenews.tw%2Fnews%2F29813808-befa-45b6-9123-8dcef851af45%27

but it didn't work.

I also tried:

docs.google.com/spreadsheets/d/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo/gviz/tq?tq=SELECT%20topic20WHERE%20url%3D'http%3A%2F%2Fwww.peoplenews.tw%2Fnews%2F29813808-befa-45b6-9123-8dcef851af45'

but it didn't work either.

are there any way to do this kind of query?

Upvotes: 2

Views: 17178

Answers (3)

Ad Kahn
Ad Kahn

Reputation: 579

try this

https://docs.google.com/spreadsheets/u/0/d/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo/gviz/tq?tqx=out:html&tq=SELECT+*+where+B+contains+"http://www.ettoday.net/news/20140327/339912.htm"

tq=SELECT+*+where+B+contains+"http://www.ettoday.net/news/20140327/339912.htm"

SELECT * where B contains "http://www.ettoday.net/news/20140327/339912.htm"

More info here -> 在這裡閱讀更多

Upvotes: 3

Mac
Mac

Reputation: 1173

I know this is old, but I just worked through a similar issue.

Querying a google spreadsheet via URL params requires the use of their data visualization query language (nearly identical to SQL).

Your query must be encoded then added as a parameter to the end of your URL (google provides an encoder with its document on this here).

Using your example url (notice no "/pubhtml"):

https://docs.google.com/spreadsheets/d/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo

To query this sheet, you must append this URL with /gviz/tq?tq=YOUR_ENCODED_QUERY_STRING

YOUR_ENCODED_QUERY_STRINGfor your case would be:

SELECT * where B contains "ettoday"

Note #1 - I used "B" and not "url". This is because you must query based on the spreadsheet cell identifier (A-Z), not the label/contents.

Note #2 - I could not get it to work when I queried with a fully quallified URL, so I used contains instead.

After encoding that string we get:

SELECT%20*%20where%20B%20contains%20%22ettoday%22

Slap that onto your URL (with /gviz/tq?tq=) and you have:

https://docs.google.com/spreadsheets/d/1yapaaaFn0mtJF0CMiIil4Y1uYCqS97jIWL4iBZMPAKo/gviz/tq?tq=SELECT%20*%20where%20B%20contains%20%22ettoday%22

Which works for me :)

Upvotes: 2

Jereme
Jereme

Reputation: 690

The spreadsheets.google.com query is the old method of accessing the google spreadsheets.

The new method involves the docs.google.com query. Here is a working one: https://docs.google.com/spreadsheets/d/1chFDkz5Fqus1ODgtdEGNt4Mq2nxnkKnuqbEB4LaZF6o/gviz/tq

That was retrieved from: query to new google spreadsheets

Some of the old query parameters still work, such as "?range=A1:B", however not all of them do. Unfortunately, I have not yet found a good reference for the new API. Google claims that all the features of V1 and v2 of the api are available in this new one, but it sure doesn't feel like it to me.

Note: the old query method still works with the old version of google spreadsheets and you should use it if you haven't converted the sheet you are using. The new method is just for sheets that have been converted.

Note2: Google forms no longer seems to work consistently with the old spreadsheets, so you will probably be forced to delete the old sheet and have the form generate a new one which will be the new version and require the new url to query it.

Upvotes: 1

Related Questions