How to create multiple Conditional Formatting Rules

[Juan David Paladines]

In this code, I'm trying to create conditional formatting rules depending on the condition sent in another function.

var rule

var rango

var a = []

switch (condition) {

    case "Tipo Equipo":

      rango = sheet.getRange("K2:K" + fila)

      rule = SpreadsheetApp.newConditionalFormatRule()

        .whenTextEqualTo("Kit Pediátrico")

        .setBackground("#BDE1F2")

        .setRanges([rango])

        .build()

      a.push(rule)

      sheet.setConditionalFormatRules(a)

      break

    case "Tipo Servicio":

      rango = sheet.getRange("L2:L" + fila)

      rule = SpreadsheetApp.newConditionalFormatRule()

        .whenTextEqualTo("Cambio")

        .setBackground("#42ECFF")

        .setRanges([rango])

        .build()

      a.push(rule)

      sheet.setConditionalFormatRules(a)

      break

The problem is that, when I run the function, it only creates the last rule, and the others are deleted by default... What do you suggest? 

[Chanel Greco]

What happens when you run the code in debugging mode with a breakpoint? Does it ever enter the first case? 

[Clark Lind]

As for the second part of your question, you can avoid deleting the existing rules like this:

var rule

var rango

//var a = []

var rules = sheet.getConditionalFormatRules(); //get existing rules

switch (condition) {

    case "Tipo Equipo":

      rango = sheet.getRange("K2:K" + fila)

      rule = SpreadsheetApp.newConditionalFormatRule()

        .whenTextEqualTo("Kit Pediátrico")

        .setBackground("#BDE1F2")

        .setRanges([rango])

        .build()

      rules.push(rule)  //add to existing rules without deleting them

      sheet.setConditionalFormatRules(rules)

      break

    case "Tipo Servicio":

      rango = sheet.getRange("L2:L" + fila)

      rule = SpreadsheetApp.newConditionalFormatRule()

        .whenTextEqualTo("Cambio")

        .setBackground("#42ECFF")

        .setRanges([rango])

        .build()

      rules.push(rule)  //add to existing rules without deleting them

      sheet.setConditionalFormatRules(rules)

      break

[Juan David Paladines]

To answer both solutions:

1. When I run the code in debugging, it creates the first rule correctly, but then it delete it and create the second rule. Finally it only creates the last rule in the code.
2. If I don't delete the existing rules, it start creating multiple times the rule, and if I need to run the code for line 100, I'll have more than 100 rule for a single column