1

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) }
Improve this question
3
  • You want to copy the format or the conditional formatting rules? Because your code tries to copy only the background colors (format) Commented Feb 10, 2021 at 10:54
  • 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. Commented Feb 10, 2021 at 10:58
  • Hey there @Strenous, has your issue been resolved? If not - would you mind providing more details about the use of the onEdit trigger? Commented Feb 10, 2021 at 14:57

1 Answer 1

Reset to default
1

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); }}) }
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

You mean i should use the conditional formatting for each sheet separately to make it faster.
@Strenuous Glad your issue was resolved. Improved my explanation a little bit.
@ Yes you are proved as always helpful.
Yes @Marios you were right it is extremely slow is there any way to do this thing via Built in Conditional formatting function using single formula by mentioning each sheet name.
@Strenuous yes add these sheet names to sheetsNames and try it out. It should work.
|