Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Data validation with One of range rule in Google Sheets API

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">

like image 606
Robin Lee Avatar asked Oct 26 '25 05:10

Robin Lee


1 Answers

As Sam Berlin suggested, the solution is to add '=' in the range.

"=dropdown!A1:B2" will work with one_in_range data validation rule.

like image 181
Robin Lee Avatar answered Oct 29 '25 09:10

Robin Lee



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!