Conditional Formatting with Script - Highlight Rows based on Custom Formula

0
votes

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.
Example Spreadsheet with Formats

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
2
google-apps-scriptgoogle-sheetsgoogle-sheets-apiconditional-formatting
Can you provide a sanitized copy of the spreadsheet you are working on (not just a screenshot), clearly showing the desired outcome? - Iamblichus
@lamblichus here is the link: docs.google.com/spreadsheets/d/… - Jed Burch
So, your question is how to apply the mentioned conditional formats programmatically? - Iamblichus
Yes, and I want to do it say every 10 times the sheet is edited. The purpose is to prevent people from changing them permanently and thus causing errors. Also to fix errors from adding rows or columns because google sheets adjusts the conditional formatting around those, and doesn't include them. - Jed Burch
I see you got an answer based on copying the formats between ranges. An alternative would be to create the formatting rules from scratch, via script (not copying them from other ranges). Do you think that would be preferrable? If that's the case, I'd consider posting an answer with this. - Iamblichus

2 Answers

1
votes

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());
  }
});
}
0
votes

In order to programmatically apply conditional format, you can do the following:

Code snippet:

function applyConditionalFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets(); // Remove if not all sheets
  //var sheetNames = ['Screening','Quality', ...]; // Uncomment if not all sheets
  //var sheets = sheetNames.map(sheetName => ss.getSheetByName(sheetName)); // Uncomment if not all sheets
  sheets.forEach(sheet => {
    //sheet.clearConditionalFormatRules(); // Uncomment if want to remove previous rules
    var rules = [];
    rules.push(createRule(sheet, "A1:NH", "=A$1=TODAY()", "#b7e1cd"));
    rules.push(createRule(sheet, "C2:NH", "=$B2>=10", "#cc0000"));
    rules.push(createRule(sheet, "C2:NH", "=$B2>=8", "#ea9999"));
    rules.push(createRule(sheet, "C2:NH", "=$B2>=5", "#f6b26b"));
    rules.push(createRule(sheet, "C2:NH", "=$B2>=2", "#ffe599"));
    sheet.setConditionalFormatRules(rules);
  });
}

function createRule(sheet, rangeNotation, formula, color) {
  var range = sheet.getRange(rangeNotation);
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(formula)
    .setBackground(color)
    .setRanges([range])
    .build();
  return rule;
}

Note:

  • I assumed that you want you apply this to all sheets in your spreadsheet. If that's not the case, uncomment lines 4 and 5 of the code snippet, remove line 3, and write all desired sheet names to the array in line 4.
  • Uncomment line sheet.clearConditionalFormatRules(); if you don't want conditions to be piled up every time you run this.