Script: How to retrieve conditional formatting rules from specified cells and selectively reapply them to ranges?

Question

In Google Sheets, when pasting cut values into a sheet, it can disrupt the range of cells that conditional formatting applies to in that sheet. My sheet is edited frequently by coworkers and it mangles the conditional formatting. I'm attempting to create a script that - on edit - copies the conditional formatting rules from each column of a sheet and then reapplies those rules to any cells pasted into each of those columns.

I'm new to scripting, so much of this was compiled by a helpful user in a previous question I had posed.

function onEdit(e) {
  var range = e.range;
  var column = range.getColumn();
  var sheet = range.getSheet();
  range.clearFormat();
  //clears formatting just on data that is pasted into the sheet

  //Get all Sheet rules and iterate through them
  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  newRules = newRules.concat(rules);

  for(var r = 0; r < rules.length; r++) {
    var rule = rules[r];
    //Get condition for each rule
    var booleanCondition = rule.getBooleanCondition();

    //Get the ranges to which each rule applies and iterate through
    var ranges = rule.getRanges();
    for (var i = 0; i < ranges.length; i++) {
      var ruleColumn = ranges[i].getColumn();  

      //If condition isn't null and edited column is the same as the one in the range, add rule
      if((ruleColumn == column) && (booleanCondition != null)) {
        var newRule = SpreadsheetApp.newConditionalFormatRule()
        .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
        .setBackground(booleanCondition.getBackground())
        .setRanges([range])
        .build();
        newRules.push(newRule);
     }
   }
 }
 sheet.setConditionalFormatRules(newRules);
}

This takes the conditional formatting rules present in the sheet and reapplies them to any edited range, selectively applying rules only to the columns they were copied from. The only issue is that it copies every rule in each column, including iterations of the same rule. Every time the sheet is edited, the number of rules applied to edited cells grows exponentially and I'm scared that my computer is about to explode.

Is there a way to only pull the conditional formatting rules from the first row of the sheet and apply them (by column) to the edited range? This would stop my problem of perpetual proliferation of rules and I would be very thankful for that.

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-22T13:05:01

The only way to retrieve conditional formatting rules is with getConditionalFormatRules function [1] which retrieves all the rules from the sheet. You can get the row for each rule range and add a condition to filter the ones that are applied only to the first row.

function onEdit(e) {
  var range = e.range;
  var column = range.getColumn();
  var sheet = range.getSheet();
  range.clearFormat();
  //clears formatting just on data that is pasted into the sheet

  //Get all Sheet rules and iterate through them
  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  newRules = newRules.concat(rules);

  for(var r = 0; r < rules.length; r++) {
    var rule = rules[r];
    //Get condition for each rule
    var booleanCondition = rule.getBooleanCondition();

    //Get the ranges to which each rule applies and iterate through
    var ranges = rule.getRanges();
    for (var i = 0; i < ranges.length; i++) {
      var ruleColumn = ranges[i].getColumn();  
      var ruleRow = ranges[i].getRow();  

      //If condition isn't null and edited column is the same as the one in the range, add rule
      if((ruleColumn == column) && (ruleRow == 1) && (booleanCondition != null)) {
        var newRule = SpreadsheetApp.newConditionalFormatRule()
        .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
        .setBackground(booleanCondition.getBackground())
        .setRanges([range])
        .build();
        newRules.push(newRule);
     }
   }
 }
 sheet.setConditionalFormatRules(newRules);
}

This will copy the rule if the rule range starts in the first row (ie: 'A1', 'A1:A4', 'D1:D10', etc).

[1] https://developers.google.com/apps-script/reference/spreadsheet/sheet#getconditionalformatrules

Script: How to retrieve conditional formatting rules from specified cells and selectively reapply them to ranges?

1 Answers