Data validation with One of range rule in Google Sheets API

Question

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

Error means that a required field or parameter has not been provided. Make sure that the value supplied is valid, or the combination of provided fields is valid. This can be also because the request was malformed. In other words, the data stream sent by the client to the server didn't follow the rules. Check this link. — abielita
Yes, I think the error comes from the combination of the rule condition type and values. I changed the code to 'type':'ONE_OF_LIST', 'values': [ { "userEnteredValue" : "A1:B2" } ]. And it works. But I'd still like to figure out what the correct combination for one_of_range as the type. — Robin Lee
Does it work if you put an '=' before the sheet name, e.g, "=dropdown!A1:B2"? — Sam Berlin
Yes, that works now thank you! You should add an aswer. Also it might be worth adding that to the documentation here developers.google.com/sheets/reference/rest/v4/… — Robin Lee

Robin Lee Robin Lee · Accepted Answer · 2016-08-25T14:43:18

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

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

Data validation with One of range rule in Google Sheets API

2 Answers