I'm trying to update a Google Sheet using sheets API batchUpdate function. What I want to do is to add data validation (drop-downs) to certain columns in my sheet. I'm sending a list of requests where each request has the parameters needed for each drop-down. However, as I'm adding requests to the list, the conditions in all the previously added requests get replaced with the new condition. Why does this happen? (Code snippets are attached below)
My method: 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; } Here's what the list of requests should look like (converted to JSON) before executing: [ { "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 } } } ] Even though the individual requests are constructed correctly, the value of "values" parameter in the condition in all requests are replaced by the "values" in the latest added request. So that all three drop-downs end up having the same values. The actual list of requests look like this: [ { "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 } } } ] -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to google-spreadsheets-api+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/google-spreadsheets-api/26b95bd4-47f1-490a-bb0b-8162eaffb263%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.