How to clear conditional formatting (not all formatting) in Google Apps script

Question

I need a way to remove all conditional formatting by running a script (my client will be using this and he doesn't want to have to repeat the process of removing conditional formatting for each worksheet in each of a large number of spreadsheet files).

Is there any way to do this via Google Apps script? All I see is .clearFormat(), which unfortunately clears all formatting, of which a lot should not be deleted (eg, font color, bg color, font, font weight, font rotation, cell outlines)

How to do this in such a way that only one button needs to be pressed for each spreadsheet file?

Slouch

Answer 1

Google Apps Scripts now supports removing conditional formatting using clearConditionalFormatRules

var sheet = SpreadsheetApp.getActiveSheet();
sheet.clearConditionalFormatRules();

https://developers.google.com/apps-script/reference/spreadsheet/sheet#clearconditionalformatrules

Rationale

That's great to know. A little late for the project I was working on, but I'm sure this knowledge will come in handy at some point. Wish I'd had that option a year ago... – Slouch

Worked brilliantly. Just go to Tools -> Script editor on the sheet you want to use this on, then paste this code and hit "run". Keeps regular cell formatting in place and only removes conditional formatting, exactly what I needed to avoid manually deleting hundreds of rules one by one! – Nikos

Answer 2

This is possible with Google Sheets API v4, which Apps Script can access via Advanced Sheets Service (note that it must be enabled before use, as the linked page instructs). Here is a script that deletes all conditional formatting rules in Sheet1 of the current spreadsheet (you'll want to loop over sheets, etc).

function clearSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheet = ss.getSheetByName("Sheet1");
  var sheetId = sheet.getSheetId(); 
  var format_req = {
    "requests": [{
      "deleteConditionalFormatRule": { 
        "index": 0,
        "sheetId": sheetId
      }
    }]
  };
  var string_req = JSON.stringify(format_req);
  while (true) {
    try {
      Sheets.Spreadsheets.batchUpdate(string_req, ssId);
    }
    catch(e) {
      break;
    }
  }
}

Each conditional format rule has a 0-based "index". Deleting the rule with index 0 causes other indices to decrease by 1. So the loop continues deleting index = 0 rule until there isn't one, and an error is thrown (and caught, exiting the loop).

This is a weird way of sending requests: I'd much rather send one batch request like this:

  var format_req = {
    "requests": [
    {
      "deleteConditionalFormatRule": { 
        "index": 0,
        "sheetId": sheetId
      }
    },
    {
      "deleteConditionalFormatRule": { 
        "index": 1,
        "sheetId": sheetId
      }
    }]
  };

but to do this, one must know how many conditional formatting rules are there (and I don't see how one would find out). If you ask for more rules to be deleted than exist in the sheet, the entire request fails and nothing is deleted.

Without advanced service

With plain Apps Script, the best one can do is this:

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var backgrounds = range.getBackgrounds(); 
  var fontColors = range.getFontColor(); 
  var fontFamilies = range.getFontFamilies();
  // ... other get methods from https://developers.google.com/apps-script/reference/spreadsheet/range 

  // tricky part: modify the backgrounds, replacing the colors used in conditional formatting by white 

  range.clearFormat();
  range.setBackgrounds(backgrounds)
       .setFontColors(fontColors)
       .setFontFamilies(fontFamilies)
    // .set  other things

Here I am assuming that conditional formatting affects only cell backgrounds. If one is unable to filter the background colors (which requires knowing exactly what colors were used in conditional formatting rules), the effects of conditional formatting will become ordinary background color, which is very undesirable... it may be better to forego setting the background colors at all.

Allen

Thanks, Alex. I was just looking into that myself and almost had it working, but was struggling to find the number of conditional formatting rules, which I guess can't be known. Your solution to deal with not knowing works well, but unfortunately I'm not sure I can use it, since my client will be the one using this script and I don't have access to all the spreadsheets to turn on the Sheets API for him. I take it there's absolutely no way to do this without advanced services? – Slouch

The best one can do without advanced services is to get the parameters that are not used by conditional formatting (getFontWeights, getFontStyles, etc), apply clearFormat and then restore (setFontWeights...). If all your conditional formatting is in cell backgrounds, then the rest will survive. – Allen

That would be a good way to do it, but for my particular use case this unfortunately won't work (the formatting includes text rotated 45degrees, cells with outlines, etc.). I'll accept your answer since I think it will work for most people/situations. Thanks! – Slouch

"but to do this, one must know how many conditional formatting rules are there (and I don't see how one would find out)" According to API reference you can use GET request with fields parametrs fields=sheets(properties(title,sheetId),conditionalFormats) To get the list of all Conditional Format rules on all sheets of the document and parse it to determine the number of rules Fully agree that not having a straight mechanism for clear CF or fully set/replace all CF with a Butch Update requests is a lack( – Abreaction

good point on the index resetting- if you try to delete anything more than half at a time you will get an error because the index is resetting in real time. For example, I was trying to delete 71 rules at once but would get this error on the request: Invalid requests[36].deleteConditionalFormatRule: No conditional format on sheet: 1092661118 at index: 36, so you need to delete the rules in batches. – Ulema

Answer 3

So after my comment to if....

but to do this, one must know how many conditional formatting rules are there (and I don't see how one would find out)

decided to extend his code:

function get_clear_Formatting() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ssId = ss.getId();

// 1 part - get conditional formatting data for all sheets in active spreadsheet

 var params = {
  'fields': "sheets(properties(title,sheetId),conditionalFormats)"
 };

 var getFormatResult = Sheets.Spreadsheets.get(
   ssId,
   params
 );

  var sheets = getFormatResult.sheets;

  var ConditionalFormatIndex = {
      "sheetID" : [],
      "sheetTitle" : [],
      "formatRulesCount" : []
      }

  for (var i = 0; i < sheets.length; i++) {
    ConditionalFormatIndex.sheetID[i]          = sheets[i].properties.sheetId;
    ConditionalFormatIndex.sheetTitle[i]       = sheets[i].properties.title
    ConditionalFormatIndex.formatRulesCount[i] = (sheets[i].conditionalFormats) ?
                                                  sheets[i].conditionalFormats.length :
                                                  0;
  }

// 2 part - clear all conditional formatting in an all sheets in active spreadsheet

  var ClearFormat_req = []

  for (var i = 0; i < ConditionalFormatIndex.sheetID.length; i++) {
    if ( ConditionalFormatIndex.formatRulesCount[i] ) {
      for (var cf = 0; cf < ConditionalFormatIndex.formatRulesCount[i]; cf++) {

        ClearFormat_req.push(
          {
            "deleteConditionalFormatRule": { 
              "index": 0,
              "sheetId": ConditionalFormatIndex.sheetID[i]
            }
          }); 
      }
    };  
  }

  Sheets.Spreadsheets.batchUpdate({'requests': ClearFormat_req}, ssId);

}

Abreaction

This doesn't work as the index of conditional formatting rules on the google side isn't reliably indexed, for me it was breaking at around 6 conditional rules. The only way I've found to reliably do it is as explained in the correct answer. Unfortunately this causes a ton of API calls if you have a complex sheet. Having an API call against the sheet or workbook to delete all conditional formatting would be very useful. – Petitionary

Answer 4

1. Copy a cell that doesn't have any conditional formatting.
2. Select the cell(s) you want to remove the conditional formatting from.
3. Edit -> Paste special -> Paste conditional formatting only.

Acropetal

Answer 5

A bit late, but I found a way, if that may help someone. remove every conditional formatting on ranges intersecting the one in parameters Then leave every other conditional formatting untouched (actually : rebuild it).

function test(){

  var sh=shWork;//define your sheet
  var r= sh.getRange("A3:A6");//example
  clearEveryConditionalFormattingOnRange(sh,r)

}

function clearEveryConditionalFormattingOnRange(sh,r){  
  //build a parallel rules at looping on initial rule, in order to rebuild it without unwanted elements

  //get rules
  var rules=sh.getConditionalFormatRules();

  //create new rules
  var a_newRules= new Array();

  //loop on rules
  for (var i=0;i<rules.length;i++){

    //create new currentRanges
    var a_newCurrentRanges=new Array();

    //loop on ranges from rule
    var currentRule=rules[i];
    var currentRanges=currentRule.getRanges();
    for (var j=0;j<currentRanges.length;j++){

      var currentRange=currentRanges[j];
      var testIfIntersect_OK=RangeIntersect(r,currentRange);

      //add this range to a_newCurrentRanges
      if (!testIfIntersect_OK){        
        a_newCurrentRanges.push(currentRange);        
      }//if (testIfIntersect_OK){

    }//for (var j=0;j<currentRanges.length;j++){

    //create then add new rule to a_newRules
    if (a_newCurrentRanges.length>0){

      var a_newRule = SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied(currentRule.getBooleanCondition().getCriteriaValues())
      .setBackground(currentRule.getBooleanCondition().getBackground())
      .setRanges(a_newCurrentRanges)
      .build();     
      a_newRules.push(a_newRule);

    }//if (a_newCurrentRanges.length>0){

  }//for (var i=0;i<rules.lengthi++){

  sh.setConditionalFormatRules(a_newRules);

}

//returns true if intersection between range1 and range2
function RangeIntersect(R1, R2) {

  var LR1 = R1.getLastRow();
  var Ro2 = R2.getRow();
  if (LR1 < Ro2) return false;


  var LR2 = R2.getLastRow();
  var Ro1 = R1.getRow();
  if (LR2 < Ro1) return false;

  var LC1 = R1.getLastColumn();
  var C2 = R2.getColumn();
  if (LC1 < C2) return false;

  var LC2 = R2.getLastColumn();
  var C1 = R1.getColumn();
  if (LC2 < C1) return false;

  return true;

}

Janinajanine

Answer 6

I ended up finding a solution based on David Friedman's answer. This script successfully removed the conditional format from just one column (D), and left the conditional formats in other columns unchanged.

// clearConditionalFormat
// Data must have header row that does NOT have conditional formatting
// Otherwise you must identify some other cell on the sheet that does not 
//                 have conditional formatting

function test(){
var sheetName = "Sheet13";   // replace with your sheet's name
var rangeText = "D3:D";      // replace with the your range
clearConditionalFormat(rangeText,sheetName);
};


function clearConditionalFormat(rangeText,sheetName){
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var rangeText = rangeText.toString().toUpperCase();
var range =  ss.getRange(rangeText).activate();

var rangeTextSplit = rangeText.split(":");               
     // example: returns  AA22 from AA22:AZ37
var rangeFirstLetter = rangeTextSplit[0].replace(/[0-9]+/g, ""); 
    // example: returns  AA from AA22
var rangeRowNum1 = rangeTextSplit[0].replace(/[A-Z]+/g, "")*1; 
    // example: returns the 22 of AA22:AZ37
var rangeHeaderText = rangeFirstLetter + (rangeRowNum1 - 1);

sheet.getRange(rangeHeaderText)
    .copyTo(range,SpreadsheetApp.CopyPasteType
    .PASTE_CONDITIONAL_FORMATTING, false);
 };

Yoder