Unexpected error while getting the method or property setConditionalFormatRules on object SpreadsheetApp.Sheet.

[Juan David Paladines]

I'm trying to use this code to delete the repeated conditional format rules in the Spreadsheet

function deleteRepeated(sheet) {

  var existingRules = sheet.getConditionalFormatRules()

  for (let index = 0; index < existingRules.length; index++) {

    let ranges = existingRules[index]

    for (let j = 0; j < existingRules.length; j++) {

      if (ranges == existingRules[j]) {

        delete existingRules[j]

      }

    }

  }

  var newRules = [] //skipping the logic to create new rules

  var allRules = existingRules.concat(newRules)

  //clear all rules first and then add again

  sheet.clearConditionalFormatRules()

  sheet.setConditionalFormatRules(allRules)

}

In the last line of code, there's the error in mention... How can I avoid that error?

[cbmserv...@gmail.com]

The only way I can see to remove conditional rules is to set them to an empty set. Here is the example they give on how to clear one rule from the set.

 

// Remove one of the existing conditional format rules.
var sheet = SpreadsheetApp.getActiveSheet();
var rules = sheet.getConditionalFormatRules();
rules.splice(1, 1); // Deletes the 2nd format rule.
sheet.setConditionalFormatRules(rules);

 

So Rules returned is a set, if you do a .setConditionalFormatRules ([]) it should clear all rules.

↓ llamadas  ↓ emails  ↓ whatsapp  ↑ control  ↑ medición  ↑ automatización

Conoce cómo cada vez más empresas están empoderando a sus aliados

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/493726c2-16ff-4fa5-9c9d-f05d1b259dd8n%40googlegroups.com.

[Juan David Paladines]

When I run sheet.setConditionalFormatRules(rules) it creates 3 exact rules...

[cbmserv...@gmail.com]

It will create as many rules as you have inside the array (rules).

 

If you want to remove any rules, you need to remove them from the Array, then call the setConditionalFormatRules for the correct set to be defined.

 

But when you remove the duplicate rules, you need to make sure it is the duplicate you are removing. I added an additional check in the if statement and removed the delete and replaced it with a splice. Try this:

 

 

function deleteRepeated(sheet) {

  var sheet=SpreadsheetApp.getActiveSheet();

  var existingRules = sheet.getConditionalFormatRules()

  for (let index = 0; index < existingRules.length; index++) {

    let ranges = existingRules[index]

    for (let j = 0; j < existingRules.length; j++) {

      if ((ranges == existingRules[j]) & (j != index) ) {

        existingRules.splice(j, 1); // Deletes the format rule.

      }

    }

  }

  var newRules = [] //skipping the logic to create new rules

  var allRules = existingRules.concat(newRules)

  //clear all rules first and then add again

  sheet.clearConditionalFormatRules()

  sheet.setConditionalFormatRules(allRules)

}

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cd80c04b-e7e6-4bc3-9631-47e42d644dfcn%40googlegroups.com.