CODEWITHSUNDEEP
google-sheets-api
Robin Lee
Robin Lee

Reputation: 111

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

Upvotes: 4

Views: 2842

Answers (3)

SeeDSpy
SeeDSpy

Reputation: 1

This option helped me:

"userEnteredValue": "='dropdown'!$A$1:$B$2",

Upvotes: 0

user1859295
user1859295

Reputation: 103

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

Upvotes: 0

Robin Lee
Robin Lee

Reputation: 111

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

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

Upvotes: 7

Related Questions