Creating a dropdown using Google Apps Script. Is the method for changing the colours missing or not available yet?

Question

Screenshot

I'm hoping someone could confirm if this functionaility is not yet available. I'm having trouble finding the correct method or technique to apply colours to a dropdown using Google Apps Script as shown in the image attached.

I've been able to create the dropdown using a simple function as shown below. I've been using the Google Apps Script References Help but nothing stands out to me.

function createDropdown() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange("F2");
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(["-", "Go", "Stop"]).build();
  cell.setDataValidation(rule);
}

If anybody could perhaps point me in the right direction or confirm it's simply not possible, that'd be really appreciated! :)

Thank you

I've tried the built in autocomplete to see if other methods appear that don't yet appear in the documentation.

Answer 1

You are right, at this time Google Apps Script doesn't support setting the data validation style and format settings. Keep an eye on the Relese Notes to learn when these new UI features be added (they might appear earlier in the autocomplete but not necesarily will be working for all users even if they are visible for all).

You have to adjust your workings to manually set the data validation to display as a smart chip instead of an arrow as well to manually set the color of each option because, as abovementioned, Google Apps Script doesn't include methods to handle smart chips other that handling link previews, which is done using the Card Service.

References

• Preview links with smart chips

Related

• API access to Smart Chips to create/read/write/etc
• Read/Write Smart Chip values from Google Sheets (API)

Answer 2

Had the same issue. Searched and searched everywhere couldn't find a solution. As of a workaround I did following. But this is not an ideal solution.

Step 1 - I created a conditional formatting for the entire column with setting background colour as the colour I want by giving the the format rule as 'Text is exactly' with the text and the colour I want.

Step 2 - Then I will be creating the data validation from apps script.

Step 3 - After that once the user selected the specific text (mentioned in Step 1) from dropdown the colour will apply to the background.

Note: This won't work well with Chip display style of data validation though. Beacause then only the background will have the colour not the chip.