Search code examples
validationgoogle-apps-scriptgoogle-sheets

Set Data Validation Display style in Apps Script

Is it possible to set the dropdown display style in apps script?

enter image description here

Solution

  • After checking the documentation it looks like the API only allows you to choose between "Arrow" and "Plain Text".

    The Apps Script documentation explains how to create data validation rules with a DataValidationBuilder. Most of the methods just set different DataValidationCriteria. Among those, the methods requireValueInList() and requireValueInRange() are the only ones that have a showDropdown parameter to set a dropdown, and the parameter's values can only be true or false. The default is true, which is equivalent to "Arrow" and false is equivalent to "Plain Text". As a boolean there's no third option for "Chip". Example:

    // Set the data validation for cell A1 to require "Yes" or "No", with a dropdown menu.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], true).build();
cell.setDataValidation(rule);

    Looking at the Sheets REST API, which Apps Script is built on, the DataValidationRule works in a similar way, but this uses showCustomUi instead of showDropDown. Still, the limitation is the same to show only the basic arrow and plain text.

    It just seems like a feature that hasn't been implemented yet. Maybe the "Chip" was added a while after the basic dropdown. You can try to request it in Google's issue tracker.