How to use a script to copy conditional formatting rules from one sheet to another?

Question

I'm trying to copy conditional formatting rules from one of my sheets to others, in Google Sheets. I know, I can just copy and use "paste special" to copy/paste them in, but I specifically want to use a script, because I've set up a script that automatically creates a new sheet, and I want to apply the conditional formatting to all of the sheets I make, because the whole spreadsheet is a sort of log, and the specific column that contains the conditional formatting applies to the entire sheet. Here's what I have:

Answer 1

    function passConditionalFormat(sourceSheetName, targetSheetName) {
      var ss = SpreadsheetApp.getActive();
      //The source of data formatting rules
      var sourceSheet = ss.getSheetByName(sourceSheetName);
      var range = sourceSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns());
      //The target of data formatting rules
      var targetSheet = ss.getSheetByName(targetSheetName);
      
      range.copyFormatToRange(targetSheet, 1, sourceSheet.getMaxColumns(), 1, sourceSheet.getMaxRows());
    }

You can use one of the following options:

1. copyFormatToRange(sheet, column, columnEnd, row, rowEnd)
2. copyFormatToRange(gridId, column, columnEnd, row, rowEnd)

Note that this solution is good only for passing the conditional format from one sheet to another one in the same spreadsheet.

Answer 2

Using the SpreadsheetApp functions and ConditionalFormatRuleBuilder class, you can create new rules from the rules in your sheet and insert them in the new sheet. I create the below code which works for getting all the rules from 'Merkler' sheet and insert them in 'New sheet', only if those rules are applied to the 'F' column.

function copyFormat() {
  //Get the Spreadsheet where the Merkler and new sheet are in
  var spreadSheet = SpreadsheetApp.openById("[SPREADSHEET-ID]");
  //The source sheet with the formatting in column F is called Merkler
  var sheet = spreadSheet.getSheetByName("Merkler");
  //The target sheet is called "New Sheet"
  var newSheet = spreadSheet.getSheetByName("New sheet");
  
  //The formatting will be taken from column F and copy to it in new Sheet
  var range = newSheet.getRange("F2:F1000");
  var column = 6; //F is the 6th column

  //Get all Sheet rules and iterate through them
  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  
  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);
      }
    }
  }
  newSheet.setConditionalFormatRules(newRules);
}

You need to replace the SpreadsheetID.