logo标签列表

谷歌表格API: 如何通过值查找行并更新其内容

androidgoogle-sheetsgoogle-apigoogle-sheets-api
49
我正在开发一款使用 Google 电子表格作为数据库的 Android 应用程序。该应用程序应该使用 Sheets API v4 获取、追加和更新电子表格中的值。前两个功能可以正常工作，但我在更新特定行时遇到了困难。我需要查找具有特定值的行（“批次 ID”在第一列中）并更新此行中的所有单元格。
这是我的电子表格的外观： 现在我正在获取要修改的行：
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.
*/

这其实可以正常工作，但我认为这是一个丑陋的解决方案，我相信还有更好的方法。

我已经阅读了Sheets API文档，并且熟悉了诸如batchUpdateByDataFilter、DataFilterValueRange或DeveloperMetadata等概念，我感觉我应该使用这些功能来实现我的目标，但我无法将它们组合在一起，也找不到任何示例。

有人能向我展示或帮助我理解如何使用这些Sheets V4功能吗？

谢谢。

你解决了这个问题吗？ - M.Yogeshwaran
不，最终我还是采用了我的“丑陋”解决方案，因为它确实符合我的需求。 - ferenckovacsx
@ferenckovacsx，你能提供一些代码吗？ - Noor Hossain
4个回答
42

我遇到了完全相同的问题，截至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"
    }
   }
  }
 ]
}

在隐藏工作表的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
   ]
  ]
 }
}

默认情况下，主要维度是行。如果没有提供行ID，则MATCH()返回列A中的相对行，那么这个位置实际上是绝对的。或者，您可能想使用更可靠的调用，例如=ROW(INDIRECT(ADDRESS(MATCH("搜索值",A:A,0),1)))。如果工作表中有空格，请将其括在单引号中。如果您正在搜索数字，请确保不要将其括在引号中。

1
PUT调用是原子性的吗？如果在第一次调用返回之前第二次调用了不同的查找，那么第一次调用的响应是否有可能反映第二次调用的搜索？ - user1114
它不是原子性的，可能会发生提高条件。解决这个问题的最好方法是使用append，然后使用响应来清除单元格。 - Leandro Zubrezki
有趣！这个在2021年8月还能用吗？ - juniortan
5
在电子表格API中，我们有开发者元数据的概念，允许我们存储对最终用户不可见的信息，以便稍后检索和使用。 在这种情况下，最好的方法是将批次ID分配为特定行的元数据。 我将基于Javascript SDK添加代码。 

const response = await sheets.spreadsheets.developerMetadata.search({
  auth: jwtClient,
  spreadsheetId,
  requestBody: {
    dataFilters: [
      {
        developerMetadataLookup: {
          locationType: 'ROW',
          metadataKey: 'batchId',
          metadataValue: '$BATCH_ID'
        }
      }
    ]
  }
});
if (response.matchedDeveloperMetadata) {
  // There is a row with that id already present.
  const { endIndex } = response.matchedDeveloperMetadata[0].developerMetadata.location.dimensionRange;
  // Use endIndex to create the range to update the values range: `SheetName!A${endIndex}`,
  await sheets.spreadsheets.values.update(
    {
      auth: jwtClient,
      spreadsheetId,
      range: `SheetName!A${endIndex}`,
      valueInputOption: 'USER_ENTERED',
      requestBody: {
        majorDimension: 'ROWS',
        values: [[]]
      },
    },
    {}
  );
} else {
  // Append the value and create the metadata.
  const appendResponse = await sheets.spreadsheets.values.append(
    {
      auth: jwtClient,
      spreadsheetId,
      range: 'SheetName!A1',
      valueInputOption: 'USER_ENTERED',
      requestBody: {
        majorDimension: 'ROWS',
        values: [[]]
      },
    },
    {}
  );
  if (appendResponse.data.updates?.updatedRange) {
    const updatedRange = appendResponse.data.updates?.updatedRange;
    const [, range] = updatedRange.split('!');
    const indexes = convertSheetNotation(range);
    await sheets.spreadsheets.batchUpdate({ auth: jwtClient, spreadsheetId, requestBody: {
      requests: [
        {
          createDeveloperMetadata: {
            developerMetadata: {
              location: {
                dimensionRange: {
                  sheetId: 0,
                  startIndex: indexes[0],
                  endIndex: indexes[0] + 1,
                  dimension: "ROWS"
                }
              },
              metadataKey: 'batchId',
              metadataValue: '$BATCH_ID',
              visibility: "DOCUMENT"
            }
          }
        }
      ]
    }});
  }
}

我们需要注意竞态条件，否则可能会出现重复的行，请告诉我这是否有帮助 :)

2
请注意，如果您打算遵循此解决方案，则电子表格中存储的元数据总量存在限制。 - Long Nguyen
3
我有同样的需求。
首先，创建一个函数，从表格中获取目标对象的索引，例如：
private int getRowIndex(TheObject obj, ValueRange response) {
    List<List<Object>> values = response.getValues();
    int rowIndex = -1;

    int i = 0;
    if (values != null) {
        for (List row : values) {
            i += 1;
            if (row.get(1).equals(obj.getBatchId())) {
                System.out.println("There is a match! i= " + i);
                rowIndex = i;
            }
        }
    }

    return rowIndex;
}

第二步：通过传递目标对象及其所需的值“批处理 ID”和其他字段的新值来创建更新方法。
public void updateObject(Object obj) throws IOException, GeneralSecurityException {    
    sheetsService = getSheetsService();
    ValueRange response = sheetsService.spreadsheets().
            values().get(SPREADSHEET_ID, "Sheet1").execute();
    
    int rowIndex = this.getRowIndex(obj, response);
    
    if (rowIndex != -1) {
        List<ValueRange> oList = new ArrayList<>();
        oList.add(new ValueRange().setRange("B" + rowIndex).setValues(Arrays.asList(
                Arrays.<Object>asList(obj.getSomeProprty()))));
    
        oList.add(new ValueRange().setRange("C" + rowIndex).setValues(Arrays.asList(
                Arrays.<Object>asList(obj.getOtherProprty()))));
    
        //... same for others properties of obj
    
        BatchUpdateValuesRequest body = new BatchUpdateValuesRequest().setValueInputOption("USER_ENTERED").setData(oList);
        BatchUpdateValuesResponse batchResponse;
        batchResponse sheetsService.spreadsheets().values().batchUpdate(SPREADSHEET_ID, body).execute();
    } else {
        System.out.println("the obj dont exist in the sheet!");
    }
}

最后： 在您的应用程序中，您必须将目标对象传递给更新方法：
TheObject obj = new Object();
obj.setBatchId = "some value";

如果您想要，可以使用其他值填充obj。
然后调用该方法：
objectChanger.updateObject(obj);
如何删除在行索引rowIndex中找到的那一行？ - Noor Hossain
0
你所需要做的就是从一个数组的数组中创建一个新的字符串数组，这样你就可以在这个新数组上运行indexOf()方法。
由于我们知道values.get方法返回的是一个数组的数组，例如：
[
  [""],
  [""],
  ...
]

我的方法是稍微扁平化这个结构。

const data = await googleSheetsInstance.spreadsheets.values.get({
  //here u have to insert auth, spreadsheetId and range
});

//here you will get that array of arrays
const allData: any[] = data.data.values; 

//Now you have to find an index in the subarray of Primary Key (such as 
//email or anything like that

const flattenedData = allData.map((someArray: any[]) => {
  return someArray[2]; //My primary key is on the index 2 in the email 
  Array
});

你将得到一个普通的字符串数组，其中包含主键，因此现在您可以轻松地在扁平化的数组上调用indexOf()方法。
const index:number = flattenedData.indexOf("someuniquestring);
console.log(index);

索引值将告诉您行数。不要忘记电子表格从1开始，而Javascript中的索引从0开始。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接