Clear conditional formatting using Advanced Sheet Service in Google Apps script

253 views
Skip to first unread message

Valorx User

unread,
May 22, 2020, 10:19:09 AM5/22/20
to Google Apps Script Community

I am trying to delete conditional formatting 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.
Reply all
Reply to author
Forward
0 new messages