Script: How to copy and reapply conditional formatting rules to a range on edit?

Question

In Google Sheets, the range of cells that conditional formatting applies to can change when you paste in new values. I'm looking for a script that will copy the conditional formatting present in the sheet and reapply it to new cells as people paste in data.

I don't actually do much scripting, but I steal other people's scripts and Frankenstein them together until they're vaguely functional. Here's what I've stolen so far:

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

  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  for(var r = 0; r < rules.length; r++) {
    var booleanCondition = rules[r].getBooleanCondition();
    if(booleanCondition != null) {
      var rule = SpreadsheetApp.newConditionalFormatRule()
      .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
      .setBackground(booleanCondition.getBackground())
      .setRanges([sheet.getRange("A:A"),sheet.getRange("C:C")])
      .build();
      newRules.push(rule);
      sheet.setConditionalFormatRules(newRules);
    }
  }
}

The problem, of course, is that I'm getting ALL of the conditional formatting rules for that sheet and applying them all to two columns (A:A and C:C). These rules are applied regardless of what columns they used to apply to.

Can someone suggest a way to copy the pre-existing Conditional Formatting rules and reapply them to the columns I copied them from?

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-12T16:28:43

The setRanges() function [1] is where you need to set up the range to which you want the formatting to be applied. In this case, I used the edited range which you're getting from the event object of the onEdit trigger [2]:

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

I also put this line outside the for loop because inserting all the formatting rules in the sheet is needed only once:

sheet.setConditionalFormatRules(newRules);

[1] https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder#setRanges(Range)

[2] https://developers.google.com/apps-script/guides/triggers/events

Script: How to copy and reapply conditional formatting rules to a range on edit?

1 Answers