How to use OR with "=A1=1"; in google sheets formula?

165 views Asked by At

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

0

There are 0 answers