如何处理使用Google Sheets API v4检索行的结构化查询 [英] How to handle structured queries for retrieving rows with Google Sheets API v4

问题描述

我想开始将依赖Google Spreadsheet API（v3）的应用程序迁移到Google Sheets API v4。然而，在开始之前，我仍然有一个问题需要解决。

在Google Sheets API v3中，我可以使用<$ c请求URL中的$ c> sq 参数。如果工作表有10000行，并且我只需要在特定列中获取具有特定值的行，这将非常有用。它在速度方面也非常有效。

在Google的迁移指南v4中，我在检索行数据下找到了这条语句：

  Sheets API v4当前没有与Sheets 
 API v3结构化查询直接等价的内容。但是，您可以检索相关数据，并根据需要在应用程序中对
进行排序。 
  

如何从一行10000行高效检索相关数据在v4 API中没有结构化查询。我宁愿不必检索所有10000行来筛选该响应。特别是因为它会减慢我的应用程序，并可能增加通过电线发送的数据量。

使用v4 API，我如何检索在特定列（或列集合）中具有特定值的行？

解决方案

我不需要参考文档，因为您已经意识到了这一点。在Sheetsv4中没有这种功能。您必须手动执行

要获取手机，我打开了JSON响应：

  arrayBuffer = xhr.response; 
 console.log（arrayBuffer+ arrayBuffer）; 
 myArr = JSON.parse（arrayBuffer）; （myArr.values [0] [i] ===
 
 for（var i = 0; i< myArr.values.length; i ++）{
 
电话）{
 console.log（列位置是+（i + 1））; // +1，因为计数从0开始
 
 
 code $ $ $ $ $ $ $ $ $ $ $ $ $ $您可以还应提供功能请求这里。
 
I would like to begin migrating my application that relies on Google Spreadsheet API (v3) to Google Sheets API v4.  However, there is one question I have that remains before I begin.

In Google Sheets API v3 I can get a rows based feed with a structured query using the sq parameter in the request URL.  This is useful if the sheet has 10000 rows and I need to get only rows with a certain value in a specific column(s).  It's also very efficient in terms of speed.

In looking at Google's migration guide to v4, I found this statement under "Retrieve Row Data":
The Sheets API v4 does not currently have a direct equivalent for the Sheets
API v3 structured queries. However, you can retrieve the relevant data and
sort through it as needed in your application.
How do I go about retrieving "relevant data" from a sheet of 10000 rows efficiently given that there is no structured query in v4 API.  I would rather not have to retrieve all 10000 rows only to sift through that response.  Especially since it will slow down my app and probably increase the amount of data being sent over the wire.  

With the v4 API, how can I retrieve rows that have specific values in a specific column (or set of columns)?
解决方案
I don't need to reference the docs because you are already aware of it. There is no such feature in Sheetsv4. You have to manually perform the Basic Reading methods and parse the response, loop over it and sift for the value you're looking for.

This is related to your SO question here. I've already written some code there so I'll just post the results here so you have an idea how I searched for values.



To fetch for phone, I looped through the JSON response:
arrayBuffer = xhr.response;
             console.log("arrayBuffer "+ arrayBuffer);
             myArr = JSON.parse(arrayBuffer);

              for(var i = 0; i < myArr.values.length; i++){

                 if(myArr.values[0][i] === "phone"){
                     console.log("column position is " + (i+1)); // +1 because counting starts at 0
                 }
              }
You can also file a Feature Request here.

                        
这篇关于如何处理使用Google Sheets API v4检索行的结构化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！