Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulaconditional-formatting

Apps script conditional formatting for value not in array


enter image description here

I have a column containing US state abbreviations. I'd like to have an array of all 50 states:

const states = ['DE','AK','TX','CA' .........]

that the value of each cell in the column is compared against. If its not one of the states I'd like to format it red.

I've looked at conditional formatting (https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder#whenTextDoesNotContain(String)) where they have sample code like:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenTextDoesNotContain("hello")
.setBackground("#FF0000")
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Is there a conditional rule that would allow (in pseudocode):

.whenTextDoesNotContain(states)

Solution

  • Here is an example of using a custom formula in conditional formatting.

    I create a data set in A1:A5.

    Then I put conditional formatting on A7:A10. The custom formula is =ISNA(MATCH(A7,A$1:A$5,0))

    enter image description here