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

user_7764067 0
Viewed 8995

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?

gs-conditional-formatting google-apps-script google-sheets
6 Answers
user_33995640

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

}
user_66652960
  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.
user_15859200

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;

}
user_107268100

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