Google Script Conditional Formatting with multiple conditions

Question

I am writing a script to conditionally format my spreadsheet, but I do not know how to create multiple conditions. I have tried several edits to fix my problem, which is that when I run this function, every blank cell in the column will turn red, or every cell that meets the conditions of formula2 will turn red. I want only the cells that both meet formula2 conditions and are blank to turn red.

When I use the notation .whenCellEmpty().whenFormulaSatisfied(formula2)... All of the cells that meet formula2 conditions turn red, even if they are not blank.

When I use the notation .whenFormulaSatisfied(formula2).whenCellEmpty()... All of the blank cells turn red, even when they don't meet formula2 conditions.

Is there a way to write both of these conditions into the same rule?

var range2a = sheet.getRange(3, 16, sheet.getLastRow()-2, 1);
var range2b = sheet.getRange(3, 17, sheet.getLastRow()-2, 1);
var formula2 = '=OR(D3:D="Sale Escrow", D3:D="Sold", D3:D="Subsequent 
       Issue")';
var rule2a = SpreadsheetApp.newConditionalFormatRule()
    .whenCellEmpty().whenFormulaSatisfied(formula2)
    .setBackground("#FF0000")
    .setRanges([range2a])
    .build();
var rules2a = sheet.getConditionalFormatRules();
rules2a.push(rule2a);
sheet.setConditionalFormatRules(rules2a);


var rule2b = SpreadsheetApp.newConditionalFormatRule()
    .whenCellEmpty()
    .setBackground("#FF0000")
    .setRanges([range2b])
    .build();
var rules2b = sheet.getConditionalFormatRules();
rules2b.push(rule2b);
sheet.setConditionalFormatRules(rules2b);

tehhowch tehhowch · Accepted Answer · 2018-05-23T15:15:00

It isn't explicitly stated in the Apps Script documentation for ConditionalFormatRuleBuilder, but the when____ methods serve as one-off shortcuts that define when the rule activates, by creating a BooleanCondition for it.

The limitation is, however, noted in the BooleanCondition documentation:

Each conditional format rule may contain a single boolean condition. The boolean condition itself contains a boolean criteria (with values) and formatting settings. The criteria is evaluated against the content of a cell resulting in either a true or false value. If the criteria evaluates to true, the condition's formatting settings are applied to the cell.

Thus, the various when____ methods do not combine with other when___ methods: whichever is used last on a rule, is the one that is active for that rule, as it has replaced the previous criteria that rule was using.

Note that the BooleanCondition is a read-only class in Apps Script - there are no methods available to the programmer to manually construct or modify a BooleanCondition. If one could create or modify a BooleanCondition, one could in theory create the evaluable boolean condition that evaluates a cell based on multiple criteria.

For now, however, the closest approach is with the whenFormulaSatisfied method, as your formula can be as complex as you desire, and perform the same calculations you would be doing if you could manually edit the BooleanCondition.

=OR(D1="A", D1="B", D1="C")*NOT(A1="")

The multiplication operator is the equivalent of a Boolean AND, while addition would be the equivalent of a Boolean OR.

Google Script Conditional Formatting with multiple conditions

1 Answers