I'm trying to implement data validation where the rule is one of the range using Google Sheets API.
In sheet1, I have a master list where one column needs to be in one of the values. The possible dropdown values are in a separate sheet called dropdown.
What is the error in my conditional value for one_of_range?
dropdown_action = {
'setDataValidation':{
    'range':{
        'startRowIndex':1,
        'startColumnIndex':4, 
        'endColumnIndex':5
    },
    'rule':{
        'condition':{
            'type':'ONE_OF_RANGE', 
            'values': [
                { "userEnteredValue" : "dropdown!A1:B2"
                }
            ],
        },
        'inputMessage' : 'Choose one from dropdown',
        'strict':True,
        'showCustomUi': True
    }
}
}
request = [dropdown_action]
batchUpdateRequest = {'requests': request}
SHEETS.spreadsheets().batchUpdate(spreadsheetId = id, 
                             body = batchUpdateRequest).execute()
However, I encountered into http error. I was able to get it working if I choose one of list instead of one_of_range. But I prefer to use one_of_range so that I can maintain the possible values in the same spreadsheet.
HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/id:batchUpdate?alt=json returned "Invalid requests[1].setDataValidation: Invalid ConditionValue.userEnteredValue: dropdown!A1:B2">
As Sam Berlin suggested, the solution is to add '=' in the range.
"=dropdown!A1:B2" will work with one_in_range data validation rule.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With