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](https://developers.google.com/drive/v3/web/handle-errors#400_bad_request) 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](http://stackoverflow.com/questions/19671317/400-bad-request-http-error-code-meaning). In other words, the data stream sent by the client to the server didn't follow the rules. Check this [link](http://stackoverflow.com/questions/37893515/google-spreadsheet-api-400-error-bad-request-unable-to-parse-range). — abielita, Aug 09 '16 at 09:53
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, Aug 09 '16 at 18:09
Does it work if you put an '=' before the sheet name, e.g, `"=dropdown!A1:B2"`? — Sam Berlin, Aug 09 '16 at 19:25
Yes, that works now thank you! You should add an aswer. Also it might be worth adding that to the documentation here https://developers.google.com/sheets/reference/rest/v4/spreadsheets#ConditionType — Robin Lee, Aug 15 '16 at 15:15

score 6 · Answer 1 · answered Aug 25 '16 at 14:43

6

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

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

answered Aug 25 '16 at 14:43

Robin Lee

101
5

This needs to be marked as the correct answer. Please mark it as resolved. – Jason Jurotich Aug 06 '22 at 16:23

score 0 · Answer 2 · answered Mar 27 '20 at 14:14

0

Nothing could work for me, and then I saw this subject Google Spreadsheet API setDataValidation with Regex , where ZektorH mark that "Your expression needs to be escaped".

answered Mar 27 '20 at 14:14

user1859295

63
1
10

*Please always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline.* See https://stackoverflow.com/help/how-to-answer – colidyre Mar 28 '20 at 00:16

Data validation with One of range rule in Google Sheets API

2 Answers2