CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheets
dan
dan

Reputation: 359

Set Data Validation Display style in Apps Script

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

enter image description here

Upvotes: 5

Views: 3352

Answers (3)

Atiq Zabinski
Atiq Zabinski

Reputation: 123

I have added my +1 to the issue tracker. Appalling that as of 1/2025 they still have not implemented this.

Since Comments on the issue tracker have been turned off, I'll use this space to offer my personal McGyver, which has worked for me in some projects, but of course is no substitute for actually having this feature in GAS:

Instead of setting your chip choices programmatically, set them manually to offer choices from a range. This range may be on a hidden Tab or Range. Then use your Script (or a formula) to set the values in this range.

If the number of entries in the range does not vary, you may choose colors for each choice from the range, and those colors will be preserved even when the values in the range change. However, if your Script (or formula) adds more values are to the range than colors you have set in your chip options, those new values will come in colored gray. Avoid this by declaring your Chip color choices when your range is fully populated, and if you need to remove values from anywhere but the bottom, preserve the colors with the use of blank cells.

As an example, here are three screenshots from a project of mine, with columns not relevant to this discussion hidden. Shown here are only 3 columns: the first headed "dayStart", the second headed "dayEnd". The third is normally hidden, but I am showing in the third screen shot.

In A2 and B2 are data validation menus, chip style. The choices in A2 are the 7 days of the week as 2-letter strings. The choices in B2 are the same, but only include the day chosen in A2 and the days that follow. Note that the colors in B2 do not change as the options do.

When the first day, "Su" is chosen in A2, the 2 menus appear the same.

But here, as "We" has been chosen in A2, the options in B2 are limited to "We" ... "Sa". Note that though the first 3 options are missing, the colors of the remaining 4 have not changed.

I have accomplished this by creating B2's dropdown as based on a range; specifically 7 cells in a hidden column. Its values are set by a formula. In order to preserve the colors associated with the days of the week, the formula inserts blank cells where days of the week are removed from B2's options.The hidden range; note use of blank cells to achieve this particular effect. In other projects I have made using this technique, the values of the options may also change. The key is to always associate each cell in the range with a color in your drop-down chip. If any of your cells may turn blank, be sure to set the colors when all have values.

As you can see, no GAS is needed for this solution. I actually am using an onEdit() function here to automatically set the value of B2 to the value of A2 when the User changes A2, so that when the User changes the Start Day, the first result covers a single-day range of time, and then the User may change the End day to include more days if they like. The key point is that I am only using GAS to set Values; whenever I want Chips I resolve myself to setting the Data Validation manually when I first design the project.

Upvotes: 0

Adriano_Pinaffo
Adriano_Pinaffo

Reputation: 1699

This feature does not exist as of today. I created an "issue item" in Google's issue tracker. Let's see if they implement it.

Upvotes: 5

Daniel
Daniel

Reputation: 3725

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.

Upvotes: 8

Related Questions