Conditional Formatting with Script - Highlight Rows based on Custom Formula

Question

I have a Google Sheet that tracks attendance using a rolling calendar. It has multiple sheets and I want each sheet to have the same conditional formats. Initially this seemed easy, I wrote some script, got it to iterate through all of the sheets to copy the format in one cell to the entire sheet. It worked well, until it somehow deleted all of the conditional formats except one, =A$1=TODAY(). I have been trying to determine why it is doing this and how to fix it. I have tried setting up the conditional format in a non-moving cell (A1), but then the formulas don't work properly. They end up highlighting the wrong row. I have been looking at Google Sheets API Sheets as well, but am having trouble implementing it. Is there a way I can write a script to set up these conditional formatting rules and how? Any help is appreciated.

I have provided a picture of what I am trying to accomplish as well the script I had been using and references, all of which is below.

The basic layout of the sheet is shown in the picture. The picture also depicts what I am trying to accomplish with the conditional formats.

The formatting rules are as follows (and in this order):

1. Apply to Range: A1:NH (whole sheet)
   Custom Formula is: =A$1=TODAY()
   Background Color: Default Green Background (#b7e1cd)

2. Apply to Range: C2:NH (everything except row 1 and columns A & B)
   Custom Formula is: =$B2>=10
   Background Color: Dark Red 1 (#cc0000)

3. Apply to Range: C2:NH
   Custom Formula is: =$B2>=8
   Background Color: Light Red 2 (#ea9999)

4. Apply to Range: C2:NH
   Custom Formula is: =$B2>=5
   Background Color: Light Orange 1 (#f6b26b)

5. Apply to Range: C2:NH
   Custom Formula is: =$B2>=2
   Background Color: Light Yellow 2 (#ffe599)

The formula in Column B is =SUM($C2:2) auto-filled to each row.

My current script is

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets();
  var sheetNames = [ 'Screening','Maint - PrePress','Vulcan','Sullivan','Packing','Materials','Shipping','Labels','Embroidery','PadPrint','Quality'];
  
  sh.forEach(sheet=> {
    if(sheetNames.includes(sheet.getName())) {
        var rng = sheet.getRange('F2');
        
        rng.copyFormatToRange(sheet, 3, sheet.getLastColumn(), 2, sheet.getLastRow());
  }
});
}

References:

1. https://developers.google.com/apps-script/reference/spreadsheet/gradient-condition
2. https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule
3. https://developers.google.com/sheets/api/guides/conditional-format#apps-script

Answer 1

Try this code, it works:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets();

  // The sheet from which we take a sample of the rules
  var sheetS = ss.getSheetByName('Screening'); 
  var rng = sheetS.getRange('F2');

  var sheetNames = ['Screening','Maint - PrePress','Vulcan','Sullivan','Packing','Materials','Shipping','Labels','Embroidery','PadPrint','Quality'];
  
  sh.forEach(sheet=> {
             if(sheetNames.includes(sheet.getName())) {    
    rng.copyFormatToRange(sheet, 3, sheet.getLastColumn(), 2, sheet.getLastRow());
  }
});
}