如何使用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"可以做到这一点吗?

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?

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

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}

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

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

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

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

谢谢

克雷格

推荐答案

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

如果我的理解是正确的，那么这个答案如何?请认为这只是几个答案之一.

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

不幸的是，无法使用电子表格.getByDataFilter的方法直接检索过滤后的值.如果要使用此功能，请首先放置开发人员元数据.然后，您可以使用sheets.getByDataFilter方法检索值.例如，作为测试用例，它考虑了以下情况.

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.

• 您在问题中使用的样本值用于这种情况.因此，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"}}]}]}]}]}
  • 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.

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

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

    select * where C='Student'
    

    或

    select * where B<40
    

    在上述情况下，当列"C"的job为Student时，将检索行.在以下情况下，当列"B"的Age小于40时，将检索行.在您的样本值中，从两个查询中检索到"Tim","23","Student".

    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.

    通过curl命令运行上述查询时，它如下所示.

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

    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编码.请注意这一点.
      • 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.

        当对问题中的样本值运行上述curl样本时，将检索以下值.

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

        "Tim","23","Student"
        

        参考:

        • 查询语言参考(0.7版)

        Reference:

        • Query Language Reference (Version 0.7)

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