How to avoid exception while setting more number of characters in list validation?
Maximum number of characters accepted in ListOfValues property forDataValidation is 256 (as per MS Excel). If the array list which is assigned to ListOfValuesproperty holds more than 256 characters, then it may lead to an exception.
This issue can be resolved by assigning the list of valuesto the unused ranges in Spreadsheet and refer that range as validation range(DataRange)
Note: you can assign the array list of values to an unused range and hide that range.