使用URL参数查询Google Spreadsheet [英] Query Google Spreadsheet with URL Parameters

问题描述

我最近打开了一个新的电子表格: https://docs.google.com/spreadsheets/d/1yapaaaFn0mtJF0CiY4 >

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

我想找到url = http://www.ettoday的标题" .net/news/20140327/339912.htm

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

我阅读了Google api文档并尝试了此操作:

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%2F298b645-8af45af-4fa-8365 ％27

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

但是没有用.

我也尝试过:

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

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'

但它也不起作用.

有什么方法可以进行这种查询吗?

are there any way to do this kind of query?

推荐答案

我知道这很旧，但是我只是解决了一个类似的问题.

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

通过URL参数查询Google电子表格需要使用其数据可视化查询语言(与SQL几乎相同).

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

必须对查询进行编码，然后将其作为参数添加到URL的末尾(Google在此

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).

使用您的示例网址(请注意，没有"/pubhtml"):

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

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

要查询此工作表，必须在此URL后面附加/gviz/tq?tq=YOUR_ENCODED_QUERY_STRING

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

YOUR_ENCODED_QUERY_STRING对于您的情况将是:

SELECT * where B contains "ettoday"

注释#1-我使用"B"而不是"url".这是因为您必须基于电子表格单元格标识符(A-Z)而不是标签/内容进行查询.

注意#2-当我使用完全完全限定的URL查询时无法使用它，因此我改用contains.

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

将其粘贴到您的URL(使用/gviz/tq?tq=)上，您可以:

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

哪个为我工作:)

这篇关于使用URL参数查询Google Spreadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！