Re: Problem in Data Validation in Google Sheets API Batch Update
My best guess is that the problem is that you are
reusing conditionValueList, clearing it out between rules. I would think
that the request doesn't get converted to JSON until the very end, where
the list will have the last set of values. Try instead creating a new list
for each rule.
- Eric
On Friday, May 31, 2019 at 1:13:31 AM UTC-4, Ruwangika Gunawardana wrote:
>
> 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 requests = new ArrayList<>();
> List 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,
>
Problem in Data Validation in Google Sheets API Batch Update
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 requests = new ArrayList<>();
List 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
