Google Sheets API:如何按值查找行并更新其内容 [英] Google Sheets API: How to find a row by value and update it's content
问题描述
我正在开发一个使用 Google 电子表格作为数据库的 Android 应用程序.应用程序应使用 Sheets API v4 在电子表格中获取、追加和更新值.前两个函数工作正常，但我在更新特定行时遇到困难.我需要在第一列(批次 ID")中找到具有特定值的行，并更新该行中的所有单元格.
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.
我已经阅读了 Sheets API 文档，并且熟悉了诸如 batchUpdateByDataFilter、DataFilterValueRange 或 DeveloperMetadata 等概念，我觉得我应该将这些功能用于我想要实现的目标，但我无法将它们组合在一起，也找不到任何示例.
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.
有人可以向我展示或帮助我了解如何使用这些 Sheets V4 功能吗?
Can someone show me or help me understand how to use these Sheets V4 features?
谢谢.
推荐答案
我有完全相同的问题，似乎到目前为止(2018 年 3 月)Sheets v4 API 不允许按值搜索，返回单元格地址.我在 StackOverflow 某处找到的解决方案是使用公式.每次您想按值查找地址时，都可以在任意工作表中创建公式，然后删除公式.如果您不想每次都删除公式，您可能更喜欢在更安全的地方创建，例如隐藏的工作表.
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.
- 创建隐藏的工作表 LOOKUP_SHEET(spreadsheetId 是您的电子表格 ID):
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addSheet": {
"properties": {
"hidden": true,
"title": "LOOKUP_SHEET"
}
}
}
]
}
- 在隐藏工作表的A1单元格中创建一个公式，在MySheet1工作表中搜索搜索值"，并取回该行:
{
"range": "LOOKUP_SHEET!A1",
"values": [
[
"=MATCH("Search value", MySheet1!A:A, 0)"
]
]
}
响应将如下所示:
{
"updatedData": {
"range": "LOOKUP_SHEET!A1",
"majorDimension": "ROWS",
"values": [
[
3
]
]
}
}
默认情况下，主要维度是 ROWS.MATCH() 返回 A 列中的相对行，如果未提供行 ID，则此位置实际上是绝对的.或者，您可能希望使用更可靠的调用，例如 =ROW(INDIRECT(ADDRESS(MATCH("Search value",A:A,0),1))).如果工作表中有空格，请将其括在单引号中.如果您要搜索数字，请确保不要将其括在引号中.
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 Sheets API:如何按值查找行并更新其内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
