如何使用 Google Sheets (v4) API getByDataFilter 返回特定行的数据? [英] How can I use the Google Sheets (v4) API getByDataFilter to return a specific row of data?

问题描述

我想找到一种使用 Google API 根据我提供的条件检索特定数据行的方法.我在想getByDataFilter"可能会这样做吗?

我正在编写一个供个人使用的应用.我想使用 Google 表格作为我的后端，以便我可以从中进行邮件合并.在这个阶段，我正在使用 API 资源管理器来查看我可以获得哪些数据.我已经弄清楚如何使用

从一系列单元格中获取数据<前>获取 https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8/values/A%3AL?valueRenderOption=UNFORMATTED_VALUE&fields=values&key={YOUR_API_KEY}

我尝试过使用 getByDataFilter 但似乎无法返回任何过滤值.

我有以下基本返回所有内容.

<前>发布 https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8:getByDataFilter?fields=sheets(data(columnMetadata%2FdeveloperMetadata%2Flocation%2FdimensionStartData%2Flocation%2Fdimensionstart%2Flocation%2Fdimensionstart%2Fange%2Fdimensionstart%Rowkey%2FdimensionStartIndex%2F2Fdimensionstart%2FRAGt2ycwA8)}{数据过滤器":[{developerMetadataLookup":{元数据位置":{}}}]}

我希望能够过滤数据并只返回符合我指定条件的列.即一个简单的例子是如果我有这样的工作表

-------------------|姓名 |年龄 |工作 |----------------------------------|克雷格 |42 |老师 |----------------------------------|蒂姆 |23 |学生 |----------------------------------|杰斯 |45 |会计师 |----------------------------------

我希望能够以某种方式过滤 Tim 的行并返回 ['Tim',23,'Student']

谢谢，

克雷格

解决方案

• 您想从电子表格中检索过滤后的值.
• 您想从外部检索值.
• 您已经能够使用 Sheets API 和访问令牌来请求 API.

如果我的理解是正确的，这个答案怎么样?请将此视为多个答案之一.

问题:

遗憾的是，无法使用电子表格.getByDataFilter 的方法直接检索过滤后的值.当您想使用它时，请首先放置开发人员元数据.然后，您可以使用电子表格.getByDataFilter 的方法检索该值.例如，作为一个测试用例，它考虑了以下情况.

• 您问题中的示例值用于这种情况.所以 Tim 被放在A3"的单元格中.
• 使用 batchUpdate 方法通过createDeveloperMetadata"将开发者元数据创建到单元格A3"中.metadataKey 和 metadataValue 分别是 key1 和 value1.
• 在上述情况下，当请求以下端点时，可以检索值.
  • 端点:POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:getByDataFilter?fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue
  • 请求正文:{"dataFilters":[{"developerMetadataLookup":{"metadataLocation":{"sheetId":{sheetId}}}}]}
  • 当然，也可以使用 metadataKey 和 metadataValue 检索值.
  • 结果:{"sheets":[{"data":[{"rowData":[{"values":[{"userEnteredValue":{"stringValue":"Tim"}},{"userEnteredValue":{"numberValue":23}},{"userEnteredValue":{"stringValue":"Student"}}]}]}]}]}

在这种情况下，需要将开发者元数据设置为单元格.但是从你的提问和回复来看，我觉得这个方法可能和你想要的不一样.所以我想提出直接从电子表格中检索过滤值的解决方法.

解决方法:

在这个解决方法中，我想建议使用查询语言检索过滤后的值.示例查询如下.

查询:

select * where C='Student'

或

select * where B<40

在上面的例子中，当C"列的job是Student时，检索行.在以下情况下，当B"列的 Age 小于 40 时，将检索行.在您的示例值中，"Tim","23","Student" 是从两个查询中检索的.

当上面的查询通过 curl 命令运行时，它变成如下.

卷曲示例:

curl "https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?gid={sheetId}&tqx=out:csv&range=A2:C&tq={query}&access_token={accessToken}"

• 在此示例中，CSV 数据作为结果值返回.
• 使用此脚本时，请设置{spreadsheetId}、{sheetId}、{query} 和{accessToken}.如果要使用其他情况，也请修改range=A2:C.
  • URL 的查询参数的每个值可能都需要进行 URL 编码.请注意这一点.
• 当您使用浏览器访问上述 URL 时，会检索 CSV 数据.

结果:

当针对您问题中的示例值运行上述 curl 示例时，将检索以下值.

"Tim","23","Student"

参考:

• 查询语言参考(0.7 版)

I want to find a way of using the Google API to retrieve a specific row of data based on criteria I supply. I am thinking "getByDataFilter" might do this?

I am writing an app for personal use. I want to use Google Sheets as my backend so that I can mail merge from it. At this stage I am using the API explorer to see what data I can get. I have figured out how to get data from a range of cells using

    GET https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8/values/A%3AL?valueRenderOption=UNFORMATTED_VALUE&fields=values&key={YOUR_API_KEY}

I have tried playing around with getByDataFilter but cannot seems to return any filtered values.

I have the following which basically returns everything.

    POST https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8:getByDataFilter?fields=sheets(data(columnMetadata%2FdeveloperMetadata%2Flocation%2FdimensionRange%2FstartIndex%2CrowData%2Fvalues%2FeffectiveValue%2CstartColumn%2CstartRow))&key={YOUR_API_KEY}

    {
     "dataFilters": [
      {
       "developerMetadataLookup": {
        "metadataLocation": {
        }
       }
      }
     ]
    }

I expect to be able to filter the data and only return the columns that match the criteria I specify. i.e. a simple example would be if I had a sheet like this

----------------------------------
| Name  | Age   | Job            |
----------------------------------
| Craig | 42    | Teacher        |
----------------------------------
| Tim   | 23    | Student        |
----------------------------------
| Jess  | 45    | Accountant     |
----------------------------------

I want to be able to somehow filter for Tim's row and return ['Tim',23,'Student']

Thanks,

Craig

解决方案

• You want to retrieve the filtered values from your Spreadsheet.
• You want to retrieve the values from outside.
• You have already been able to use Sheets API and the access token for requesting the API.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Issue:

Unfortunately, the filtered values cannot be directly retrieved using the method of spreadsheets.getByDataFilter. When you want to use this, at first, please put the developer metadata. Then, you can retrieve the value using the method of spreadsheets.getByDataFilter. For example, as a test case, it thinks of the following situation.

• Your sample values in your question are used for this situation. So Tim is put in a cell of "A3".
• Developer metadata is created to the cell "A3" with "createDeveloperMetadata" using the batchUpdate method. metadataKey and metadataValue are key1 and value1, respectively.
• Under the above situation, when the following endpoint is requested, the values can be retrieved.
  • Endpoint: POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:getByDataFilter?fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue
  • Request body: {"dataFilters":[{"developerMetadataLookup":{"metadataLocation":{"sheetId":{sheetId}}}}]}
  • Of course, the values can be also retrieved with metadataKey and metadataValue.
  • Result: {"sheets":[{"data":[{"rowData":[{"values":[{"userEnteredValue":{"stringValue":"Tim"}},{"userEnteredValue":{"numberValue":23}},{"userEnteredValue":{"stringValue":"Student"}}]}]}]}]}

In this case, it is required to set the developer metadata to the cells. But from your question and replying, I thought that this method might be different from what you want. So I would like to propose the workaround for directly retrieving the filtered values from the Spreadsheet.

Workaround:

In this workaround, I would like to propose to retrieve the filtered values using the query language. The sample query is as follows.

Query:

select * where C='Student'

or

select * where B<40

In above case, when job of the column "C" is Student, the rows are retrieved. In below case, when Age of the column "B" is less than 40, the rows are retrieved. In your sample values, "Tim","23","Student" is retrieved from both query.

When above query is run by the curl command, it becomes as follows.

curl sample:

curl "https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?gid={sheetId}&tqx=out:csv&range=A2:C&tq={query}&access_token={accessToken}"

• In this sample, CSV data is returned as the result value.
• When you use this script, please set {spreadsheetId}, {sheetId}, {query} and {accessToken}. If you want to use other situations, also please modify range=A2:C.
  • Each value of the query parameters of URL might be required to be URL encoded. Please be careful this.
• When you access to above URL using your browser, the CSV data is retrieved.

Result:

When above curl sample is run for your sample values in your question, the following values are retrieved.

"Tim","23","Student"

Reference:

• Query Language Reference (Version 0.7)

这篇关于如何使用 Google Sheets (v4) API getByDataFilter 返回特定行的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！