Google表格API：如何按价值查找行并更新其内容 [英] Google Sheets API: How to find a row by value and update it's content

问题描述

我正在研究一个使用Google Spreadsheet作为数据库的Android应用程序。
应用程序应该使用Sheets API v4在电子表格中进行GET，APPEND和UPDATE值。前两个功能工作正常，但更新特定行时遇到困难。我需要在第一列中找到一个具有特定值的行（批次ID）并更新此行中的所有单元格。

这就是我的电子表格看起来像

现在我正在行被修改如下：

  ValueRange response = this.mySheetsService.spreadsheets（）。 
 values（）。get（spreadsheetId，range）.execute（）; 
 
列表< List< Object>> values = response.getValues（）; 
字符串rangeToUpdate; 
 
 Log.i（TAG，范围内的所有值：+ values.toString（））; 
 
 int i = 0; 
 if（values！= null）{
 for（List row：values）{
 i + = 1; 
 if（row.get（0）.equals（selectedBatchID））{
 Log.i（TAG，IT'S A MATCH！i =+ i）; 
 rangeToUpdate =A+（i + 1）+：E+（i + 1）; // row to be updated 
} 
} 
} 
 / *一旦我有需要更新的行，我构建新的ValueRange requestbody和
 *执行一个values（）。update（spreadsheetId，rangeToUpdate，requestbody）请求。 
 * / 
  

这实际上工作正常，但我认为这是一个丑陋的解决方案，我是我已经阅读了Sheets API文档，并熟悉了诸如 batchUpdateByDataFilter ，等概念。 > DataFilterValueRange 或 DeveloperMetadata ，我感到我应该使用这些功能来实现我想实现的功能，但是我无法将它们放在一起，也找不到任何示例。 / p>

有人可以向我展示或帮助我了解如何使用这些表格V4功能吗？

谢谢。 p>

解决方案

我有完全相同的问题，至今为止（2018年3月）Sheets v4 API不允许按值，返回单元地址。我在StackOverflow上找到的解决方案是使用公式。每次您想要按值查找地址时，都可以在任意表格中创建公式，然后擦除公式。如果您不希望每次都删除公式，那么您很多人都喜欢在更安全的地方创建，比如隐藏工作表。

1. 创建隐藏的工作表LOOKUP_SHEET（spreadsheetId是您的电子表格ID）：
   

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

 $ {
requests：[
 {
addSheet：{
properties：{
hidden ：true，
title：LOOKUP_SHEET
} 
} 
} 
] 
} 
  

2. 在隐藏工作表的A1单元格中创建一个公式，用于在MySheet1中搜索搜索值并返回该行：

PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/LOOKUP_SHEET!A1?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE& ; valueInputOption = USER_ENTERED& fields = updatedData

  {
range：LOOKUP_SHEET！A1 ，
values：[
 [
= MATCH（Search value，MySheet1！A：A，0）
] 
] 
} 
  

回复如下：

  {
updatedData：{
range：LOOKUP_SHEET！A1，
majorDimension：ROWS，
values：[
 [
 3 
] 
] 
} 
} 
  pre> 
 
 
默认情况下，主要维度是ROWS。 MATCH（）返回列A中的相对行，如果没有行ID，则该位置实际上是绝对的。或者，您可能希望使用更可靠的调用，如= ROW（INDIRECT（ADDRESS（MATCH（Search value，A：A，0），1）））。如果工作表中有空格，请将其用单引号括起来。如果您正在搜索号码，请确保您不要将它放在引号中。
 
I am working on an Android application that uses a Google Spreadsheet as a database. 
The application should GET, APPEND and UPDATE values in a spreadsheet, using the Sheets API v4. The first two functions are working fine but I have difficulties updating a specific row. I need to find a row that has a specific value in it's first column ("Batch ID") and update all the cells in this row.

This is how my spreadsheet looks like.

Right now I am getting the row  to be modified like this:
ValueRange response = this.mySheetsService.spreadsheets().
                values().get(spreadsheetId, range).execute();

List<List<Object>> values = response.getValues();
String rangeToUpdate;

Log.i(TAG, "all values in range: " + values.toString());

int i = 0;
if (values != null) {
    for (List row : values) {
        i += 1;
        if (row.get(0).equals(selectedBatchID)) {
            Log.i(TAG, "IT'S A MATCH! i= " + i);
            rangeToUpdate = "A" + (i + 1) + ":E" + (i + 1); //row to be updated
        }
    }
}
/*once I have the row that needs to be updated, I construct my new ValueRange requestbody and
*execute a values().update(spreadsheetId, rangeToUpdate , requestbody) request.
*/
This is actually working fine but I think it's an ugly solution and I am sure there is a better one out there.

I have read the Sheets API documentation and I got familiar with notions such as batchUpdateByDataFilter, DataFilterValueRange or DeveloperMetadata and I sense that I should use these features for what I'm trying to achieve but I couldn't put them together and I couldn't find any examples.

Can someone show me or help me understand how to use these Sheets V4 features?

Thank you.
解决方案
I have exactly the same issue, and it seems that so far (March 2018) Sheets v4 API does not allow to search by value, returning cell address. The solution I found somewhere here on StackOverflow is to use a formula. The formula can be created in an arbitrary sheet each time you want to find the address by value, then you erase the formula. If you do not want to delete the formula every time, you many prefer to create in a safer place, like a hidden worksheet.

Create hidden worksheet LOOKUP_SHEET (spreadsheetId is your spreadsheet ID):
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
 "requests": [
  {
   "addSheet": {
    "properties": {
     "hidden": true,
     "title": "LOOKUP_SHEET"
    }
   }
  }
 ]
}



Create a formula in the A1 cell of the hidden worksheet that searches for "Search value" in MySheet1 sheet, and get back the row:
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/LOOKUP_SHEET!A1?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=USER_ENTERED&fields=updatedData
{
 "range": "LOOKUP_SHEET!A1",
 "values": [
  [
   "=MATCH("Search value", MySheet1!A:A, 0)"
  ]
 ]
}
The response will look like this:
{
 "updatedData": {
  "range": "LOOKUP_SHEET!A1",
  "majorDimension": "ROWS",
  "values": [
   [
    3
   ]
  ]
 }
}
By default, major dimension is ROWS. MATCH() returns relative row within column A, if no row IDs are provided then this position is effectively absolute. Or, you may want to use a more reliable call like =ROW(INDIRECT(ADDRESS(MATCH("Search value",A:A,0),1))). If the sheet has spaces in it, enclose it in single quotes. If you are searching for number, make sure you do not enclose it in quotes.

                        
这篇关于Google表格API：如何按价值查找行并更新其内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！