从Google Sheets API获取过滤的数据 [英] Get filtered data from Google Sheets API

问题描述

我正在使用nodeJS从Google表格中获取数据，URL如下:

I am using nodeJS for fetching the data from google sheet and the URL looks like :

var url = `https://sheets.googleapis.com/v4/spreadsheets/${sheet_key}/values/Sheet2!A1:J20?key=${google_API_key}`

由此，我只能获取A1到J20之间的数据，所以现在我有两个问题:

From this, I am only getting the data between A1 to J20 So now I have two questions :

1. 如何从工作表或最后10条中获取所有行.
2. 如何应用结构化查询过滤器，例如:where name =="Himanshu"

已

对于问题2:我所做的是使用查询语言参考(0.7版)/结构化查询，该查询由@Tanaike引用.这是我的网址的样子 就像现在一样，它也可以正常工作.
https://docs.google.com/a/google.com/spreadsheets/d/${sheet_key}/gviz/tq?tq=select%20*%20where%20B%20%3D%20'Himanshu'&key=${google_API_key}

For question 2: what I have done is using Query Language Reference (Version 0.7) / structured queries, which is referenced by @Tanaike. This is how my URL looks like now and its working as well.
https://docs.google.com/a/google.com/spreadsheets/d/${sheet_key}/gviz/tq?tq=select%20*%20where%20B%20%3D%20'Himanshu'&key=${google_API_key}

但是问题是它向我返回了我无法解析的类似这样的字符串.

But the issue is it's returning me the string something like this which I cant able to parse.

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"509770406","table":{"cols":[{"id":"A","label":"Response Path","type":"string"},{"id":"B","label":"Name","type":"string"}]}})

推荐答案

此答案如何?

您可以使用工作表名称作为以下范围来检索所有行.

You can retrieve all rows using sheet name as the range as follows.

var url = `https://sheets.googleapis.com/v4/spreadsheets/${sheet_key}/values/Sheet2?key=${google_API_key}`

A2

您可以使用查询语言使用与where name == "Himanshu"类似的查询.为了使用此功能，请按以下方式共享电子表格.

A2

You can use the query like where name == "Himanshu" using Query Language. In order to use this, please share the Spreadsheet as follows.

• 在Google云端硬盘上
  • 在电子表格文件上
  • 右键单击->共享->高级->在私人-只有您可以访问"上单击更改"
  • 选中在具有链接的任何人上"
  • 点击保存"
  • 在共享链接"中，复制URL.
    • 从https://docs.google.com/spreadsheets/d/### file ID ###/edit?usp=sharing
    检索文件ID
    • On Google Drive
      • On the Spreadsheet file
      • right-click -> Share -> Advanced -> Click "change" at "Private - Only you can access"
      • Check "On Anyone with the link"
      • Click "Save"
      • At "Link to share", copy URL.
        • Retrieve file ID from https://docs.google.com/spreadsheets/d/### file ID ###/edit?usp=sharing

        关于查询语言，您可以在查询语言参考.

        About Query Language, you can see the detail information at Query Language Reference.

        如果我误解了你的问题，对不起.

        If I misunderstand your question, I'm sorry.

        要检索格式化的数据时，请使用tqx=out:.在您的情况下，使用tqx=out:json.似乎是默认设置.例如，您要使用csv，它是tqx=out:csv.您也可以使用tqx=out:html.我认为tqx=out:csv可能对您的情况有用.

        When you want to retrieve the formatted data, please use tqx=out:. In your case, tqx=out:json is used. It seems that it's default. For example, you want csv, it's tqx=out:csv. Also you can use tqx=out:html. I think that tqx=out:csv may be useful for your situation.

        https://docs.google.com/a/google.com/spreadsheets/d/${sheet_key}/gviz/tq?tqx=out:csv&tq=select%20*%20where%20B%20%3D%20'Himanshu'&key=${google_API_key}
        

        要检索电子表格的JSON数据，请执行以下操作.

        Edit 2 :

        In order to retrieve JSON data of spreadsheet, please do as follows.

        • 在电子表格上
          • 点击文件->发布到网络上
          • 发布为网页.
          • On Spreadsheet
            • Click File -> Publish to the web
            • Publish as web page.

            您可以使用以下URL将电子表格的值检索为JSON.

            You can retrieve the values of spreadsheet as JSON using the following URL.

            https://spreadsheets.google.com/feeds/cells/${sheet_key}/od6/public/values?alt=json
            

            od6表示电子表格的第一页.

            od6 means 1st page of spreadsheet.

            如果要检索其他页面，请使用以下URL进行确认.

            If you want to retrieve other pages, please confirm using the following URL.

            https://spreadsheets.google.com/feeds/worksheets/${sheet_key}/public/basic?alt=json
            

            注意:

            • 如果访问URL时发生错误，请确认是否再次发布电子表格.

            这篇关于从Google Sheets API获取过滤的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！