I wonder how to add several contents as conditions with an OR/pipe operator in the code below.
I tried that way var customFormulaString = "=A1=1|2";
and that way var customFormulaString = "=A1=1 | =A1=2";
with var cellsInA1Notation = "A1:C9";
and 1
in A1
and 2
in C3
but no formatting.
Any hint on how to handle multiple conditions with OR/pipe?
Thanks for your help very much appreciated!
function yourScript() {
// ...
var cellsInA1Notation = "A1"; // could also have been e.g. "C3:D4"
setRangeToRedBackground(cellsInA1Notation);
// ...
}
// This is a custom convenience function I made which is not provided directly by the Google Sheets API.
function addConditionalFormatRule(sheet, rule) {
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules)
}
// Adds a conditional format rule to a sheet that causes a cell to have red background color if it contains a 1.
// To illustrate that conditional formatting rules do not need to be spread out across (and hidden) in the spreadsheet GUI,
// but can be manipulated entirely in your script.
function setRangeToRedBackground(cellsInA1Notation) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(cellsInA1Notation);
var customFormulaString = "=A1=1";
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(customFormulaString)
.setBackground("red")
.setRanges([range])
.build();
addConditionalFormatRule(sheet, rule);
}
Original answer by @Magne: How to format cells through code in Google Sheets