CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Jayakrishnan
Jayakrishnan

Reputation: 4294

Clear conditional formatting using Advanced Sheet Service in Google Apps script

I am trying to delete conditional formating of a range using below code:

var sheet = range.getSheet();
var address = range.getA1Notation();

var conditionalFormatRules = sheet.getConditionalFormatRules();
var conditionalFormatRule = [];
var sheetId = sheet.getSheetId();
for (let index = 0; index < conditionalFormatRules.length; index++) {
    let ranges = conditionalFormatRules[index].getRanges();
    for (let j = 0; j < ranges.length; j++) {
        if (ranges[j].getA1Notation() == address) {
            conditionalFormatRule.push({
                "deleteConditionalFormatRule": {
                    "index": index,
                    "sheetId": sheetId
                }
            });
        }
    }
}

if (conditionalFormatRule.length > 0) {
    var spreadsheet = SpreadsheetApp.getActive();
    var ssId = spreadsheet.getId();
    var format_req = {
        "requests": conditionalFormatRule
    };
    Sheets.Spreadsheets.batchUpdate(format_req, ssId);
}

But it fails with the following exception:

{ [GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid requests[8].deleteConditionalFormatRule: No conditional format on sheet: 1876237346 at index: 8] name: 'GoogleJsonResponseException', details: { message: 'Invalid requests[8].deleteConditionalFormatRule: No conditional format on sheet: .... at index: 8', code: 400 } }

It says there is no conditional formatting at index: 8 but there are 11 conditional format rules for that particular range (confirmed by logging the conditional formatting rules)

I want to delete all the conditional formatting rules for a particular range if there is any better way please suggest.

Thanks in advance.

Upvotes: 1

Views: 1275

Answers (2)

Ricardas
Ricardas

Reputation: 216

If you want to update/add new rules to one range, while preserving old rules in the sheet in different ranges, you can achieve that by changing the condition and skipping splicing in your code, like that:

var existingRules = sheet.getConditionalFormatRules();
var rulesToKeep = [];
var rangeToUpdate ="A1:B10"
 for (let index = 0; index < existingRules.length; index++) {
 let ranges = conditionalFormatRules[index].getRanges();
for (let j = 0; j < ranges.length; j++) {
    if (ranges[j].getA1Notation() != rangeToUpdate.getA1Notation()) {
       rulesToKeep.push(existingRules[index]);
     }
   }
}
var newRules = [] //skipping the logic to create new rules
var allRules = rulesToKeep.concat(newRules);
//clear all rules first and then add again
sheet.clearConditionalFormatRules(); 
sheet.setConditionalFormatRules(allRules);

Very important is to get getA1Notation() for both ranges for comparison, even if your range string is already in A1Notation()

Upvotes: 1

Jayakrishnan
Jayakrishnan

Reputation: 4294

Solved the issue by using getConditionalFormatRules, clearConditionalFormatRules, and setConditionalFormatRules methods of Sheet class.

  1. Get all conditional formatting rules and store it in a variable existingRules.
  2. Remove the rules from existingRules.
  3. Concat new rules newRules and existingRules.
  4. Clear all conditional formatting rules.
  5. Set all conditional formatting rules allRules to the sheet again.
var existingRules = sheet.getConditionalFormatRules();
     var removedRules = [];
     for (let index = 0; index < existingRules.length; index++) {
     let ranges = conditionalFormatRules[index].getRanges();
    for (let j = 0; j < ranges.length; j++) {
        if (ranges[j].getA1Notation() == address) {
           removedRules.push(existingRules[index]);
         }
       }
    }

    for (var i = removedRules.length - 1; i >= 0; i--)
        existingRules.splice(removedRules[i], 1);

    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);

Upvotes: 1

Related Questions