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

Question

我正在尝试使用 Sheets API BatchUpdate 函数更新 Google 表格。我想要做的是将数据验证(下拉菜单)添加到工作表中的某些列。我正在发送一个请求列表，其中每个请求都有每个下拉列表所需的参数。但是，当我将请求添加到列表中时，所有先前添加的请求中的条件都会被新条件替换。

我的方法如下:

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
      }
    }
  }
]

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

Answer 1

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