时间戳

首页 > 解决方案 > Google Sheets API 批量更新中的数据验证问题

java - Google Sheets API 批量更新中的数据验证问题

问题描述

我正在尝试使用 sheet API batchUpdate 函数更新 Google Sheet。我想要做的是将数据验证（下拉菜单）添加到工作表中的某些列。我正在发送一个请求列表，其中每个请求都有每个下拉列表所需的参数。但是，当我向列表中添加请求时，之前添加的所有请求中的条件都将替换为新条件。

我的方法如下：

public BatchUpdateSpreadsheetResponse setDropdownForPriceTest(String spreadsheetId) throws IOException, GeneralSecurityException {

    Sheets service = GoogleDriveConnection.getSheetsService();
    List<Request> requests = new ArrayList<>();
    List<ConditionValue> conditionValueList = new ArrayList<>();
    BooleanCondition booleanCondition;
    DataValidationRule dataValidationRule;
    GridRange range;

    conditionValueList.clear();
    String[] tripType = PriceBatchTestCase.TRIPTYPE;
    for (String str: tripType) {
        conditionValueList.add(new ConditionValue().setUserEnteredValue(str));
    }
    booleanCondition = new BooleanCondition().setType("ONE_OF_LIST").setValues(conditionValueList);
    dataValidationRule = new DataValidationRule().setCondition(booleanCondition).setShowCustomUi(true).setStrict(true);
    range = new GridRange().setSheetId(0).setStartRowIndex(1).setStartColumnIndex(1).setEndColumnIndex(2);
    requests.add(new Request().setSetDataValidation(new SetDataValidationRequest().setRule(dataValidationRule).setRange(range)));

    conditionValueList.clear();
    String[] policyType = policyPackageService.getArrayPolicyPackageCode();
    for (String str: policyType) {
        conditionValueList.add(new ConditionValue().setUserEnteredValue(str));
    }
    booleanCondition = new BooleanCondition().setType("ONE_OF_LIST").setValues(conditionValueList);
    dataValidationRule = new DataValidationRule().setCondition(booleanCondition).setShowCustomUi(true).setStrict(true);
    range = new GridRange().setSheetId(0).setStartRowIndex(1).setStartColumnIndex(2).setEndColumnIndex(3);
    requests.add(new Request().setSetDataValidation(new SetDataValidationRequest().setRule(dataValidationRule).setRange(range)));

    conditionValueList.clear();
    String[] area = PriceBatchTestCase.AREA;
    for (String str: area) {
        conditionValueList.add(new ConditionValue().setUserEnteredValue(str));
    }
    booleanCondition = new BooleanCondition().setType("ONE_OF_LIST").setValues(conditionValueList);
    dataValidationRule = new DataValidationRule().setCondition(booleanCondition).setShowCustomUi(true).setStrict(true);
    range = new GridRange().setSheetId(0).setStartRowIndex(1).setStartColumnIndex(15).setEndColumnIndex(16);
    requests.add(new Request().setSetDataValidation(new SetDataValidationRequest().setRule(dataValidationRule).setRange(range)));

    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);
    BatchUpdateSpreadsheetResponse response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
    return response;
}

以下是请求列表在执行之前的样子（转换为 JSON）：

[
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 2,
        "sheetId": 0,
        "startColumnIndex": 1,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "SINGLE_TRIP"
            },
            {
              "userEnteredValue": "ANNUAL_MULTI_TRIP"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  },
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 3,
        "sheetId": 0,
        "startColumnIndex": 2,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "ESSENTIALS"
            },
            {
              "userEnteredValue": "CLASSIC"
            },
            {
              "userEnteredValue": "DELUXE"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  },
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 16,
        "sheetId": 0,
        "startColumnIndex": 15,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "EUROPE_LR"
            },
            {
              "userEnteredValue": "EUROPE_HR"
            },
            {
              "userEnteredValue": "WORLD_HR"
            },
            {
              "userEnteredValue": "WORLD_LR"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  }
]

即使单个请求构造正确，实际的请求列表如下所示：

[
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 2,
        "sheetId": 0,
        "startColumnIndex": 1,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "EUROPE_LR"
            },
            {
              "userEnteredValue": "EUROPE_HR"
            },
            {
              "userEnteredValue": "WORLD_HR"
            },
            {
              "userEnteredValue": "WORLD_LR"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  },
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 3,
        "sheetId": 0,
        "startColumnIndex": 2,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "EUROPE_LR"
            },
            {
              "userEnteredValue": "EUROPE_HR"
            },
            {
              "userEnteredValue": "WORLD_HR"
            },
            {
              "userEnteredValue": "WORLD_LR"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  },
  {
    "setDataValidation": {
      "range": {
        "endColumnIndex": 16,
        "sheetId": 0,
        "startColumnIndex": 15,
        "startRowIndex": 1
      },
      "rule": {
        "condition": {
          "type": "ONE_OF_LIST",
          "values": [
            {
              "userEnteredValue": "EUROPE_LR"
            },
            {
              "userEnteredValue": "EUROPE_HR"
            },
            {
              "userEnteredValue": "WORLD_HR"
            },
            {
              "userEnteredValue": "WORLD_LR"
            }
          ]
        },
        "showCustomUi": true,
        "strict": true
      }
    }
  }
]

这样所有三个下拉菜单都具有相同的值。为什么会这样？

标签： javagoogle-sheetsgoogle-apidropdowngoogle-sheets-api

解决方案

我在编写其他一些代码时发现了这个原因。问题很简单。在我的方法中conditionValueList，我对每个条件都使用相同的列表，每次都清除它并再次填充值。由于每次都传递对该列表的引用值，因此ConditionValue先前设置到booleanCondition变量中的项目列表也会相应地更改。因此，到最后，所有ConditionValue列表booleanCondition都具有相同的值（最后分配的值）。

推荐阅读