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 [email protected].
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.
