How to copy conditional formatting between different spreadsheets in Google Apps Script

Question

I have multiple spreadsheets titled: Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. Each spreadsheet has 3 sheets titled: Plan, Class, and Coach. Each spreadsheet has near-identical formatting, the only difference being the height of merged cells in the first and last 8 columns of "Plan!".

I am constantly changing values in my spreadsheets, so while each spreadsheet has the same format, the values are very different. I also don't want to combine all of the sheets into one spreadsheet because I would have nearly 20 tabs to navigate through, making it very confusing and time consuming.

The problem I have is when I need to change something on every sheet. My sheet needs to be something I can update and change throughout the year depending on my needs, and as you would expect, it can be very annoying to have to open each individual sheet and change the same thing in every one. I have found the IMPORTRANGE function to be very useful in keeping my header rows and other constants updated between sheets, but I have not found a way to do so with conditional formatting, which is the most annoying thing to have to change.

I have multiple conditional format rules on each sheet, and I really need a way to have each sheet update automatically whenever I change or add a rule to say, "Monday".

• I only want to copy conditional format rules.
• I need each sheet to update whenever I update conditional format rules for "Monday"

This is what I wrote:

function copyConditional(){
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan");
  var source = SpreadsheetApp.openById("1RJVmCimFh24NbAlENJvH5pT3zRq_KLJmFdEWqUN_Fo8").getSheetByName("Plan");
  var targetR = target.getRange('I3:AN59');
  var sourceR = source.getRange('I3:AN59');
  
  sourceR.copyTo(targetR,SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING)
}

When I run my code, I get an error message stating, "Exception: Target range and source range must be on the same spreadsheet. (line 14, file 'Code')"

Now, I don't know if what I am looking to do is actually possible, but I am hoping there is some workaround to what I am trying to do. My experience with code is small, but I am starting to understand a lot more of it (all thanks to this great community at Stack Overflow!). I would appreciate any help you can give me.

Answer 1

Solution:

• Although the solution of Tanaike might work, here I propose a more straighforward approach.

• The following solution uses the ConditionalFormatRuleBuilder class:

     function copyConditional(){
        var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan");
        var source = SpreadsheetApp.openById("1RJVmCimFh24NbAlENJvH5pT3zRq_KLJmFdEWqUN_Fo8").getSheetByName("Plan");
  
        var rules = source.getConditionalFormatRules();
        target.setConditionalFormatRules(rules);
      }
  

---

Limitations:

• This approach copies all the conditional format rules of the source sheet Plan to target sheet Plan and may not be what you are looking for.
• However, this solution might still work for you if you haven't used multiple conditional format rules within the sheet.

---

Alternative approach:

In order to copy the conditional formatting of particular ranges, then you need to use the ConditionalFormatRule class to get the ranges to which the conditional format rules are applied. Since, I don't have access to your sheet and can't test it myself, I can't tell how many different conditional format rules you use and in which ranges. As a starting point, this is an array of all the rules and the ranges that these rules are applied in the source sheet:

var rules = source.getConditionalFormatRules();

Then you iterate through the ranges to find exactly which ranges contain which rules:

var rule = source.getConditionalFormatRules()[0];
var ranges = rule.getRanges();
for (int i = 0; i < ranges.length; i++) {
  Logger.log(ranges[i].getA1Notation());
}

---

Alternative solution:

Based on the latter, you can determine the rules you would like to copy to the target file, instead of copying all the rules. For example, if range is I3:AN59 then copy the conditional format rules to the target sheet:

function copyConditional(){

var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan");
var source = SpreadsheetApp.openById("1RJVmCimFh24NbAlENJvH5pT3zRq_KLJmFdEWqUN_Fo8").getSheetByName("Plan");

var rules = source.getConditionalFormatRules();

for (let i = 0 ; i < rules.length; i++){

var ranges = rules[i].getRanges();
for (let j = 0; j < ranges.length; j++) {
  if(ranges[j].getA1Notation() == 'I3:AN59'){
   target.setConditionalFormatRules([rules[i]]);
 }
}
}
}

This approach assumes that the conditional format rules are the same for I3:AN59.

I hope my answer was helpful to you.

Answer 2

Just a thought here: I understand you don't want one sheet with a million tabs but you can handle that with a menu and some very basic code.

Prefix each sheet name with the day abbreviation or or a number (eg 3 for Wednesday).

Write some code to show the relevant day and hide everything else

Use the onOpen function to show today's tabs and hide everything else.

And a whole bunch of menu options to show a particular day.