App Script Conditional Formatting on multiple Sheets

Question

I have been using this App Script to apply Conditional Formatting on single sheet.

I would like to apply this conditional formatting to multiple sheets. I am striving hard to find a way like this below line where i would add sheet name and conditional formatting will start working on those sheets which have been added in the code.

Your help will be greatly appreciated.

something like this

["Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"];


function formatting() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dec');
  const range = sheet.getRange("C1:AG"+sheet.getLastRow());
  const values = range.getValues();
  const bcolors = range.getBackgrounds();
  const new_bcolors = values.map((r,i)=>r.map((c,j)=>c=='L'?'#ea9999':bcolors[i][j]))
  range.setBackgrounds(new_bcolors)
 }

You want to copy the format or the conditional formatting rules? Because your code tries to copy only the background colors (format) — soMario
Yes the format color if all sheets Range("C1:AG") has this value "L" then it should paste the the format as above code does. — user14807564
Hey there @Strenous, has your issue been resolved? If not - would you mind providing more details about the use of the onEdit trigger? — ale13

soMario soMario · Accepted Answer · 2021-02-10T11:26:36

Explanation:

Your goal is to apply a specific conditional formatting on a selection of sheets.

I believe, constructing the conditional formatting for each sheet would be faster than iterating over each cell for every sheet to check for the value "L" and then set the background color of that cell. Also this approach wouldn't be dynamic because you change the format, not the conditional formatting. Namely, if you change "L" to something else e.g. "M" the cell format will stay until you run the script again.

The idea of the following script is to use the ConditionalFormatRuleBuilder class and the steps are:

The rule is: if the value of the cell is equal to "L" then apply the color #ea9999.
forEach sheet in the document check if the name is included in the selected array of sheet names sheetsNames.

If it does, then construct the following conditional formatting:

SpreadsheetApp.newConditionalFormatRule()
             .whenTextEqualTo("L")
             .setBackground("#ea9999")
             .setRanges([range])
             .build();

Solution:

function copyConditional(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetsNames = ["Sheet1", "Sheet2", "Sheet3", "Sheet4", 
                       "Sheet5", "Sheet6", "Sheet7"];
  const sheets = ss.getSheets();
  sheets.forEach(sh=>{
       if (sheetsNames.includes(sh.getName())){
           console.log(sh.getName())
           let range = sh.getRange("C1:AG"+sh.getMaxRows());  
           let rule = SpreadsheetApp.newConditionalFormatRule()
                   .whenTextEqualTo("L")
                   .setBackground("#ea9999")
                   .setRanges([range])
                   .build();
           let rules = sh.getConditionalFormatRules();
           rules.push(rule);
           sh.setConditionalFormatRules(rules);
       }})                   
}

App Script Conditional Formatting on multiple Sheets

1 Answers

Explanation:

Solution: