How to remove google apps script "Exception: Conditional format rule cannot reference a different sheet."

Viewed 294

I have several similar spreadsheets I am working with. Each spreadsheet has 3 sheets of the same name: "Plan", "Class", and "Coach". I want the conditional formatting within the sheets of my spreadsheets to be exactly the same. I was helped a little while ago in creating a script that could do so, and it worked perfectly fine in my test spreadsheets (I don't like to put something in my actual spreadsheets until I know it will work properly). The script uses getConditionalFormatRules on all the sheets from my source sheet and then uses setConditionalFormatRules and the other sheet ID's to recreate the source spreadsheet's rules in my other spreadsheets. I have now placed my code into the actual spreadsheet, but I am now experiencing a problem.

Every time I run my script, I get this error: Exception: Conditional format rule cannot reference a different sheet.

I get this message when the script is copying the conditional formatting from the sheet called "class". The other two sheets' formatting gets copied perfectly fine. I have gone over the code many times now, but I can find no error in my code. I have tried creating new sheets and starting over, and then referencing the recreated sheets, but I still get the same message. I have tried changing the sheet name as well, but to no avail (plus, I would rather not change the sheet name from "Class" because many of my other scripts would also have to change). I am very lost.

This is my current script:

function copyConditional(){
  var targetT = SpreadsheetApp.openById("Tuesday_ID").getSheetByName("Plan");
  var targetW = SpreadsheetApp.openById("Wednesday_ID").getSheetByName("Plan");
  var targetTh = SpreadsheetApp.openById("Thursday_ID").getSheetByName("Plan");
  var targetF = SpreadsheetApp.openById("Friday_ID").getSheetByName("Plan");
  var targetS = SpreadsheetApp.openById("Saturday_ID").getSheetByName("Plan");
  var source = SpreadsheetApp.openById("Monday_ID").getSheetByName("Plan");
  var rules = source.getConditionalFormatRules();
    targetT.setConditionalFormatRules(rules);
    targetW.setConditionalFormatRules(rules);
    targetTh.setConditionalFormatRules(rules);
    targetF.setConditionalFormatRules(rules);
    targetS.setConditionalFormatRules(rules);
  

  var targetT2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var targetW2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var targetTh2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var targetF2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var targetS2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var source2 = SpreadsheetApp.openById("").getSheetByName("Coach");
  var rules2 = source2.getConditionalFormatRules();
    targetT2.setConditionalFormatRules(rules2);
    targetW2.setConditionalFormatRules(rules2);
    targetTh2.setConditionalFormatRules(rules2);
    targetF2.setConditionalFormatRules(rules2);
    targetS2.setConditionalFormatRules(rules2);
  
  var targetT1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var targetW1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var targetTh1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var targetF1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var targetS1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var source1 = SpreadsheetApp.openById("").getSheetByName("Class");
  var rules1 = source.getConditionalFormatRules();
    targetT1.setConditionalFormatRules(rules1);
    targetW1.setConditionalFormatRules(rules1);
    targetTh1.setConditionalFormatRules(rules1);
    targetF1.setConditionalFormatRules(rules1);
    targetS1.setConditionalFormatRules(rules1);
}

The only thing I changed in this code was taking out my sheet ID's, but I can guarantee I had all the correct ID's in the right spots.

I would like to know what I can do to remove the exemption from my script, or if there is maybe another workaround to allow the script to function properly. Thank you!

Edit This is an image of part of my sheet. The first two rows have rules that will change the background color and font color of a cell depending on its exact value. There is also one rule for every 6 columns (except for the first) that will change the cell's background color if any information at all is entered.

Sheet "Class"

Edit 2 These are all the rules on sheet "Class" as of the moment. I do add rules pretty frequently, but following the same pattern. I am using my script because it can be very time consuming to have to add the same new rule to all 6 of my sheets individually.

Conditional rules sheet "Class"

My other sheet "Coach" has all the same "text is exactly" rules, except the range changes from A1:EO2 to A1:BF59. The other sheet "Plan", has all the same "text is exactly" rules, but changes the range to A1:AU59. Sheet "Plan" also has some unique rules, which I will add as well.

Sheet "Plan" unique rules

EDIT

I never found out why the script did not work on my original sheets. I had to recreate all of my sheets from scratch, rewrite my script in the new spreadsheets, and paste in the new spreadsheet ID's into my script to get my desired effect. At this point, I have determined there must have been some fundamental flaw in the sheet itself, and that my problem had nothing to do with my code.

I am not posting this as an answer, because creating new spreadsheets from scratch did not exactly solve the problem. I consider it a lengthy and annoying workaround more than a functioning solution. Thanks to everyone who tried to help though!

0 Answers
Related