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?

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

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.

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

}

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.

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;

}

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