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<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/c625ebe1-d881-464e-8f89-4af7d977b460%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
