CODEWITHSUNDEEP
javagoogle-sheetsgoogle-apidropdowngoogle-sheets-api
Ruwangi
Ruwangi

Reputation: 243

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.

My method is as follows: 

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

So that all three drop-downs have the same values. Why does this happen?

Upvotes: 0

Views: 1264

Answers (1)

Ruwangi
Ruwangi

Reputation: 243

I found out the reason for this while writing some other code. The problem has been quite simple. In my method I'm using the same list conditionValueList for each of my conditions, clearing it and filling values again each time. As the value of the reference to that list gets passed each time, the lists of ConditionValue items previously set into the booleanCondition variable also get changed accordingly. Hence all the ConditionValue lists in booleanCondition have the same value (the last assigned value) by the end.

Upvotes: 2

Related Questions