Bulk Copy Conditional Formatting to other ranges

Question

I have multiple conditional formatting rules applied to a range with custom formulas and specific colors. I need to copy and apply those rules to the other 24 ranges in the same sheet.

The thing is that even after copying the rules to other ranges, I'd still need to edit each formula depending on its position in the cells to reference. But I know that can't be achieved automatically, so I just want to take those rules and put them into other ranges, and then edit each formula manually. That would still save some time.

I tried to create a script with the help of ChatGPT, but the code it gave me kept returning errors.

Here are the data details:

The range that contains custom rules: B14:H19

Ranges that I want to apply the rules to:

K14:Q19 T14:Z19 AC14:AI19 AL14:AR19 AB23:H28 K23:Q28 T23:Z28 AC23:AI28 AL23:AR28 B32:H37 K32:Q37 T32:Z37 AC32:AI37 AL32:AR37 B41:H46 K41:Q46 T41:Z46 AC41:AI46 AL41:AR46 B50:H55 K50:Q55 T50:Z55 AC50:AI55 AL50:AR55

Here's the code suggested by ChatGPT but it didn't work:

 function copyConditionalFormatting() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangesToApply = [
    // Replace with the ranges where you want to apply the conditional formatting
    sheet.getRange("K14:Q19"),
    sheet.getRange("T14:Z19"),
    sheet.getRange("AC14:AI19"),
    sheet.getRange("AL14:AR19"),
    sheet.getRange("AB23:H28"),
    sheet.getRange("K23:Q28"),
    sheet.getRange("T23:Z28"),
    sheet.getRange("AC23:AI28"),
    sheet.getRange("AL23:AR28"),
    sheet.getRange("B32:H37"),
    sheet.getRange("K32:Q37"),
    sheet.getRange("T32:Z37"),
    sheet.getRange("AC32:AI37"),
    sheet.getRange("AL32:AR37"),
    sheet.getRange("B41:H46"),
    sheet.getRange("K41:Q46"),
    sheet.getRange("T41:Z46"),
    sheet.getRange("AC41:AI46"),
    sheet.getRange("AL41:AR46"),
    sheet.getRange("B50:H55"),
    sheet.getRange("K50:Q55"),
    sheet.getRange("T50:Z55"),
    sheet.getRange("AC50:AI55"),
    sheet.getRange("AL50:AR55")
  ];
  // Loop through each range and apply the conditional formatting
  for (var i = 0; i < rangesToApply.length; i++) {
    var newRange = rangesToApply[i];
    var rules = range1.getConditionalFormatRules();
    newRange.setConditionalFormatRules(rules);
  }
} 

Here's the error:

TypeError: newRange.setConditionalFormatRules is not a function
copyConditionalFormatting   @ Code.gs:39

I'm not sure If I can share a Demo of this, because it's connected to other sheets and the conditional formatting rules don't copy with data. So here's screenshot of the custom formulas in Formatting rules (there's 15 rules).

I hope someone would be able to help me here.

DEMO LINK

P.S: please note that you'll see N/A errors in cells because that sheet is part of a more extensive system and linked to other sheets that I can't share.

Answer 1

Here's the solution that worked for me:

    function copyConditionalFormattingRules() {
  // Define the spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // Define the source range from which to copy the rules
  var sourceRange = 'B17:H22';

  // Define the target ranges to which to apply the rules
  var targetRanges = [
    'AC17:AI22',
    'AL17:AR22',
    'AB26:H31',
    'K26:Q31',
    'T26:Z31',
    'AC26:AI31',
    'AL26:AR31',
    'B35:H40',
    'K35:Q40',
    'T35:Z40',
    'AC35:AI40',
    'AL35:AR40',
    'B44:H49',
    'K44:Q49',
    'T44:Z49',
    'AC44:AI49',
    'AL44:AR49',
    'B53:H58',
    'K53:Q58',
    'T53:Z58',
    'AC53:AI58',
    'AL53:AR58'
  ];

  // Get the conditional formatting rules from the source range
  var rules = sheet.getConditionalFormatRules();
  var sourceRules = rules.filter(rule => rule.getRanges().some(range => range.getA1Notation() === sourceRange));

  // Apply the rules to each of the target ranges
  for (var i = 0; i < targetRanges.length; i++) {
    var targetRange = sheet.getRange(targetRanges[i]);

    sourceRules.forEach(rule => {
      var newRule = rule.copy();
      newRule.setRanges([targetRange]);
      rules.push(newRule);
    });
  }

  // Update the sheet with the new rules
  sheet.setConditionalFormatRules(rules);
}

It copied all the custom rules from the source range and pasted them into the other ranges, and of course, I had to edit the formulas manually, but even though, it saved me a lot of time.

Answer 2

As mentioned in the comments, the methods getConditionalFormatRules() and setConditionalFormatRules() are not for Class Range.

The getConditionalFormatRules() method retrieves all the rules from the whole sheet. To retrieve the rule from a specific range then you have to iterate each rule and retrieve the one that matches with the range you want, in this case you want to copy the rule from range B10:B15.

Just to give you an example, I have this script that worked for me. Make sure to add the ID of your spreadsheet in the spreadSheet variable and list all the ranges in the a1Notations variable.

function copyConditionalFormat() {
  var spreadSheet = SpreadsheetApp.openById("XXX");
  var sheet = spreadSheet.getSheetByName("Monthly Calendar");

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

  //List of the ranges where you want to apply the conditional format rule
  var a1Notations = ["AC10:AI15", "AC19:AI24", "AL10:AR15", "AL19:AR24"];  

  for (let i = 0 ; i < rules.length; i++){
    var ranges = rules[i].getRanges();
    var booleanCondition = rules[i].getBooleanCondition();

    for (let j = 0; j < ranges.length; j++) {
      if(ranges[j].getA1Notation() == "B10:H15"){
        for (let k = 0; k<a1Notations.length; k++){
          var newRule = SpreadsheetApp.newConditionalFormatRule()
          .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
          .setBackground(booleanCondition.getBackground())
          .setRanges([sheet.getRange(a1Notations[k])])
          .build();
          newRules.push(newRule)
        }
      }
    }
  }
  sheet.setConditionalFormatRules(newRules)
}

Then you will have to edit each formula as it seems it can't be done automatically.

References:

• Class ConditionalFormatRule
• Class ConditionalFormatRuleBuilder