Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

Clear conditional formatting using Advanced Sheet Service in Google Apps script

278 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