0

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

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Lod
  • 657
  • 1
  • 9
  • 30
  • Hi @TheMaster hope you're doing well and thanks for the hint. I tested the second answer's 1st comment solution as ``var customFormulaString = '=or(A1="1";A1="2")';`` but it's not working for the script. I also tested with the pipe as ``var customFormulaString = '=A1="1" | =A1="2"';`` but still no formatting. What am I doing wrong? Thanks a lot! – Lod Dec 12 '21 at 20:13
  • 1
    Doing well. `var customFormulaString = '=or(A1="1";A1="2")';` should work. But try `var customFormulaString = '=or(A1="1",A1="2")';` - For this to work, `1` and `2` should be strings. If they're number type, `var customFormulaString = '=or(A1=1,A1=2)';` should work. – TheMaster Dec 12 '21 at 20:36
  • 1
    @TheMaster thanks a lot for the number type reminder, I had forgotten about that! The 2nd one with the comma is working. Very nice, I just learned to use the OR in apps script (I thought it wasn't possible before). But is it possible to do the same with the pipe symbol (would make more concise code with lots of conditions). If you prefer I'd glagly ask a new question (that way also it could benefit others and would be accessible for later reminder if need be). Thanks again! – Lod Dec 12 '21 at 20:49
  • 2
    This isn't apps script. That's why I removed all apps script tags. This is Google sheets formula. The formula is set as a string through apps script. Whatever is between `''` is plain formula as a plain string. Pipe isn't supported in formulas. It is supported in apps script. OR in apps script is `||`. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for free resources and more details. – TheMaster Dec 12 '21 at 20:56
  • 1
    You might also want to research how custom formatting operates in a Google sheet by testing it manually in a Google sheet. https://stackoverflow.com/a/69635326/ – TheMaster Dec 12 '21 at 21:08
  • @TheMaster oh ok, thanks that makes sense for the formula use in apps script. Many thanks for the ``||`` observation, I just learned about that now as well! Final question: I just tested ``var customFormulaString = "=A1=1 || =A1=2";`` and ``var customFormulaString = "=A1=1||2";`` but no formatting. How is it possible to use the ``||`` for the same purpose? Thanks again. Ok thanks for the reference on custom formatting, I didn't find it before! – Lod Dec 12 '21 at 21:12
  • 1
    You can't. `You might also want to research how custom formatting operates in a Google sheet by testing it manually in a Google sheet.` Once again, try to learn custom formatting first. https://stackoverflow.com/a/69635326 – TheMaster Dec 12 '21 at 21:16
  • @TheMaster Ok I'll research and test. Be well! – Lod Dec 12 '21 at 21:18
  • 2
    To be clear, there's no other way. You can't use pipe in the way you desired. – TheMaster Dec 12 '21 at 21:19
  • 1
    Having said that, you may just be able to add the true falses: `=(A1=1)+(A1=2)`. If you count number of characters, this isn't any smaller though, but just a another way. – TheMaster Dec 13 '21 at 11:39

0 Answers0