I’ve created a small (pseudo) class that more easily clears and creates conditional formatting rules in a Google Sheet tab with Google Apps Script.
Why?
Well in Google Apps Script, conditional formatting rules are all or nothing.
You can only ever set, get or clear ALL rules in a specified Google Sheet tab.
So if you add a single rule to a sheet tab, all existing rules will be removed.
This means that each time you need to clear a rule or a portion of a rule or add in a new rule, you need to rebuild the entire rule set for that sheet tab.
It is an unpleasant experience.
The script below simplifies this process into some common clear and create processes for your conditional formatting.
Let me know in the comments below if you have a suggestion for another method for the class.
One last thing.
If you want to understand what is going on with the code (You are my kind of peeps), check out the video tutorial series.
Table of Contents
The Code: Range_ConditionalFormatting()
To add this script to your own projects, I recommend that you create a new Google Apps Script file (page) and paste the code in there.
Check out the chapters below on how to use the class.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 |
/** * #### Clear and Set by range conditional formattting. #### * * Clears and sets conditional formatting by range. * This is a sudo class. * @author Scott Donald [scott@yagisanatode.com] * @see {https://yagisanatode.com/clear-and-set-conditional-formatting-rules-to-a-specific-range-in-google-sheets-with-apps-script/} for further informtation. */ /** * @typedef {Range_ConditionalFormattingMethods} * @param {SpreadsheetApp.Sheet} sheet - the target Sheet tab * @property {function} clearRule * @property {function} setRules * @returns Range_ConditionalFormattingMethods */ function Range_ConditionalFormatting(sheet) { /** * @typedef {Object} matrixRange * @property {Number} rs = row start * @property {Number} re = row end * @property {Number} cs = col start * @property {Number} ce = col end */ /** * Checks sample ranges agains target ranges and removes * any overlapping ranges. * @param {matrixRange[]} targets - Selected ranges used to erase * @param {matirxRange[]} erased - Range to erase if it overlaps * @returns {matrixRange[]} */ function checkRangeOverlapAndRebuild_(targets, erased) { console.log("checkRangeOverlapAndRebuild_") console.log(targets) console.log(erased) // On each iteration check the range let newRanges = [] let checkRanges = [...erased] let checkRangesLen = checkRanges.length /** * Checks row overlaps and creates new ranges. * @param {matrixRange} s - sample range. * @param {matrixRange} t - target range. */ function rowCheck(s, t) { if (s.rs < t.rs) { // If s row starts before t start. if (s.re <= t.re) { // If s row ends at or before t end. (add: top) let n1 = { rs: s.rs, re: t.rs - 1, cs: s.cs, ce: s.ce } newRanges.push(n1) s.rs = t.rs colCheck(s, t) } else if (s.re > t.re) { // If s row ends after t end. (add: top, bottom) let n1 = { rs: s.rs, re: t.rs - 1, cs: s.cs, ce: s.ce } let n4 = { rs: t.re + 1, re: s.re, cs: s.cs, ce: s.ce } newRanges.push(n1, n4) s.rs = t.rs s.re = t.re colCheck(s, t) } } else if (s.rs >= t.rs) { // If the s row range starts inside the t. if (s.re > t.re) { // If the s row ends after the t end. (add:bottom) let n4 = { rs: t.re + 1, re: s.re, cs: s.cs, ce: s.ce } newRanges.push(n4) s.re = t.re } colCheck(s, t) } } /** * Checks columns overlaps and creates new ranges. * @param {matrixRange} s - sample range. * @param {matrixRange} t - target range. */ function colCheck(s, t) { if (s.cs < t.cs) { // If s col start before t col start if (s.ce <= t.ce) { //If s col ends at or before t ends. (add: left) let n2 = { rs: s.rs, re: s.re, cs: s.cs, ce: t.cs - 1 } newRanges.push(n2) } else if (s.ce > t.ce) { // If s col ends after t end. (add: left, right) let n2 = { rs: s.rs, re: s.re, cs: s.cs, ce: t.cs - 1 } let n3 = { rs: s.rs, re: s.re, cs: t.ce + 1, ce: s.ce } newRanges.push(n2, n3) } } else if (s.cs >= t.cs && s.ce > t.ce) { let n3 = { rs: s.rs, re: s.re, cs: t.ce + 1, ce: s.ce } newRanges.push(n3) } } // Iterate over each target area to check range overlaps. targets.forEach(t => { // Loop through checkRanges // This allows us to check for any ranges we create when we have // mulitple target ranges. while (checkRangesLen > 0) { const s = checkRanges[checkRangesLen - 1] // If s does not touch t, add range. (do first for quick exit) if ( s.re < t.rs || s.rs > t.re || s.ce < t.cs || s.cs > t.ce ) { newRanges.push(s) // Pushes existing ranges. } else { rowCheck(s, t) } checkRanges.pop() checkRangesLen = checkRanges.length } checkRanges = [...newRanges] // Review the new check range on the next target newRanges = [] checkRangesLen = checkRanges.length }) return checkRanges } const publicAPI = {} /** * @typedef {boolean} clearRule * @param {SpreadsheetApp.Range | SpreadsheetApp.Range[]} rangeOrRanges - target range or ranges. * @param {Number} [clearType] - 0 = Clear exactly matching range. * - 1 = Clear only within range. * - 2 = Clear withing and partial ranges. * the targe range will be removed from the conditional formatting. */ publicAPI.clearRule = function (rangeOrRanges, clearType = 0) { /** @type {SpreadsheetApp.Range[]} */ const ranges = rangeOrRanges.constructor == Array ? rangeOrRanges : [rangeOrRanges] const tgtRanges = clearType == 0 ? ranges.map(r => r.getA1Notation()) // We need A1 if zero. : ranges.map(r => { return { rs: r.getRow(), re: r.getLastRow(), cs: r.getColumn(), ce: r.getLastColumn() } }) const cur_rules = sheet.getConditionalFormatRules() let updated_rules = [] let hasRangeChange = false // interate over the current conditional formatting updated_rules = cur_rules.reduce((acc, crule) => { // - Iterate over current ranges and delete any const cruleRanges = crule.getRanges() // let remainingRuleA1ranges = [] const remainingRuleA1ranges = cruleRanges.reduce((acc2, r) => { switch (clearType) { case 0: // CLEAR EXACT MATCH // - If a range matches, delete. if (tgtRanges.includes(r.getA1Notation())) { hasRangeChange = true return acc2 } break case 1: // CLEAR ALL RANGES WITHIN SPECIFIED RANGE // - If a range fits within any of the target ranges, delete. const hasMatch = tgtRanges.some(tr => { if ( r.getRow() >= tr.rs && r.getLastRow() <= tr.re && r.getColumn() >= tr.cs && r.getLastColumn() <= tr.ce ) { return true } return false }) if (hasMatch) { hasRangeChange = true return acc2 } break case 2: // CLEAR ALL RANGE OVERLAPS WITHIN SPECIFIED RANGE const cruleRangeLoc = [{ rs: r.getRow(), re: r.getLastRow(), cs: r.getColumn(), ce: r.getLastColumn(), }] const cruleNewRanges = checkRangeOverlapAndRebuild_( tgtRanges, cruleRangeLoc ) // If there has been a change if (JSON.stringify(cruleNewRanges) != JSON.stringify(cruleRangeLoc)) { // Convert to A1 Notation cruleNewRangesA1 = cruleNewRanges.map(cruleRange => { return sheet.getRange( cruleRange.rs, cruleRange.cs, cruleRange.re - cruleRange.rs + 1, cruleRange.ce - cruleRange.cs + 1, ) }) hasRangeChange = true return acc2 = acc2.concat(cruleNewRangesA1) } break } // SWITCH ENDS // Nothing deleted. return acc2 = acc2.concat(r) }, []) const remainingRulesA1rangesLength = remainingRuleA1ranges.length // If there are no remaining ranges in the if (remainingRulesA1rangesLength == 0) return acc // If there is a difference in # of ranges after check, rebuild cond fmt rule if (hasRangeChange) { console.log("range has been removed. ") let cruleCopy = crule.copy() cruleCopy.setRanges(remainingRuleA1ranges) cruleCopy.build() acc = acc.concat(cruleCopy) return acc } // If the range was not impacted return the rule. acc = acc.concat(crule) return acc }, []) // Update the conditional formatting rules. sheet.setConditionalFormatRules(updated_rules) } /** * @typedef {SpreadsheetApp.ConditionalFormatRule} setRules * @param {SpreadsheetApp.ConditionalFormatRule[]} rules - array of conitional formatting rules. * @param {Number} [position] - End of rules by default (-1) * - 0 Start of rules. Positive numbers apply position up to max length of rule set. * - -1 End or rules negative numbers position in reverse from last rule to start up to zero. */ publicAPI.setRules = function (rules, position = -1) { let newRules = sheet.getConditionalFormatRules() const ruleLen = newRules.length let pos = -1 if (position > -1) { if (position > ruleLen) { pos = ruleLen } else { pos = position } } else { const posNeg = ruleLen + position + 1 console.log(position, posNeg) if (posNeg < 0) { pos = 0 } else { pos = posNeg } } console.log("POS", pos, "\nCur rules length", sheet.getConditionalFormatRules().length, "\nNew rules length", ruleLen ) // Get current rules newRules.splice(pos, 0, ...rules) sheet.setConditionalFormatRules(newRules) } return publicAPI } |
Note! Square brackets around parameters indicate optional parameters.
Class
The pseudo-class Range_ConditionalFormatting(sheet) tales one argument:
- sheet: This is a Spreadsheet App Sheet class. Notated as
SpreadsheetApp.Sheetin JS Doc.
|
1 2 3 4 5 6 |
function test_Range_ConditionalFormatting() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") Range_ConditionalFormatting(sheet). // .clearRule() or setRule() } |
Methods
| Method | Return Type |
|---|---|
| clearRule() |
Clears a rule or rules by a single range or array or ranges, based on 3 clearing approaches.
|
| setRule() |
Sets a rule or rules while maintaining the existing rules in the Sheet tab.
|
clearRule(rangeOrRanges, [clearType])
Clears the conditional formatting rules in a Google Sheet tab by the selected range or ranges and clear type while maintaining the existing conditional formatting rules in the Google Sheet tab.
Parameters
| Name | Optional | Type | Description |
|---|---|---|---|
| rangeOrRanges | Range or Range Array | A SpreadsheetApp.Range or array of ranges that will be the target locations to clear. | |
| clearType | ✓ | Number | The way the method should clear the range (see below)
Default is clearType = 0 |
Clear types
- 0 – Clears any conditional formatting rule that exactly matches the range.
- 1 – Clears any conditional formatting rule that has a range within the target range.
- 2 – Clears any conditional formatting rule or portions of the range of a rule that overlap the target range.
Examples
clearRule(range) – Single range and no optional clear type
Clears a conditional formatting range that exactly matches the single range provided.
|
1 2 3 4 5 6 7 8 9 10 11 |
function test_Range_ConditionalFormatting_range() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a range of formatting rules. const range = sheet.getRange("B4:B7") console.log("Single Range Clear - exact match.") Range_ConditionalFormatting(sheet).clearRule(range) } |
Video link: clearRule(ranges) – Ranges and no optional clear type
clearRule(ranges) – Multiple ranges and no optional clear type
Clears the conditional formatting that exactly matches multiple ranges.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function test_Range_ConditionalFormatting_ranges() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a list of ranges const ranges = ["B12:B15", "B20:B23", "B28:B31"].map(r => sheet.getRange(r)) console.log(ranges.length) Range_ConditionalFormatting(sheet).clearRule(ranges) } |
clearRule(range, clearType = 0) – Single range with clear type zero- Exact match
Clears a conditional formatting range that exactly matches the array of ranges provided. Clear type zero (0) is the default clear type.
|
1 2 3 4 5 6 7 8 9 10 11 |
function test_Range_ConditionalFormatting_range_clear_exact() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a range of formatting rules. const range = sheet.getRange("B4:B7") console.log("Single Range Clear - exact match.") Range_ConditionalFormatting(sheet).clearRule(range, 0) } |
clearRule(ranges, clearType = 0) – Multiple ranges with clear type zero – Exact match
Clears the conditional formatting that exactly matches multiple ranges.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function test_Range_ConditionalFormatting_ranges_clear_exact() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a list of ranges const ranges = ["B12:B15", "B20:B23", "B28:B31"].map(r => sheet.getRange(r)) console.log(ranges.length) Range_ConditionalFormatting(sheet).clearRule(ranges, 0) } |
clearRule(range, clearType = 1) – Single range with clear type one – Within range
Clears a conditional formatting rule range that has a range equal to or within the target range.
|
1 2 3 4 5 6 7 8 9 10 11 |
function test_Range_ConditionalFormatting_range_clear_within() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a range of formatting rules. const range = sheet.getRange("B4:B7") console.log("Single Range Clear - exact match.") Range_ConditionalFormatting(sheet).clearRule(range, 1) } |
clearRule(ranges, clearType = 1) – Multiple ranges with clear type zero- Within range
Clears all conditional formatting rule ranges equal to or within the target array of ranges.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function test_Range_ConditionalFormatting_ranges_clear_wihin() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a list of ranges const ranges = ["B12:B15", "B20:B23", "B28:B31"].map(r => sheet.getRange(r)) console.log(ranges.length) Range_ConditionalFormatting(sheet).clearRule(ranges, 1) } |
clearRule(range, clearType = 2) – Single range with clear type one – Overlaps
Clears a conditional formatting rule range that has a range equal to or within the target range or rebuilds the range where any range overlaps the target range removing that portion of the range.
|
1 2 3 4 5 6 7 8 9 10 11 |
function test_Range_ConditionalFormatting_range_clear_exact() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("TEST1") // Clear a range of formatting rules. const range = sheet.getRange("B4:B7") console.log("Single Range Clear - exact match.") Range_ConditionalFormatting(sheet).clearRule(range, 2) } |
clearRule(ranges, clearType = 2) – Multiple ranges with clear type zero- Overlaps
Clears all conditional formatting rule ranges equal to or within the target array of ranges or rebuilds the range where any range overlaps the target range removing that portion of the range.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function test_Range_ConditionalFormatting_ranges_clear_exact() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("Sheet1") // Clear a list of ranges const ranges = ["B12:B15", "B20:B23", "B28:B31"].map(r => sheet.getRange(r)) console.log(ranges.length) Range_ConditionalFormatting(sheet).clearRule(ranges, 2) } |
setRule(rules, [position])
The setRule method adds a conditional formatting rule or rules to an existing Google Sheet tab. It does not remove any existing rules in the selected tab.
The method has an optional position parameter that allows the user to customise where they wish to order the rule in relation to the existing rules on the Sheet tab.
Parameters
| Name | Optional | Type | Description |
|---|---|---|---|
| rules | Conditional Formatting Rules | A
array of objects. This is an array of rules built with the Apps Script conditional formatting rule builder. |
|
| position | ✓ | Number | The position of the new rule in the existing set of rules.
Default is position = -1, or the bottom of the rules array. A position of zero (0) sets the rule to the top of the array set. Positive numbers apply position up to max length of the rule set. -1 for end of the rule Rules with negative numbers are position from the last rule in reverse order up to zero. |
Examples
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
function test_Range_ConditionalFormatting_set() { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("TEST4") let range const rule1 = SpreadsheetApp.newConditionalFormatRule() .whenTextEqualTo("1") .setBackground("#f9cb9c") const rule2 = SpreadsheetApp.newConditionalFormatRule() .whenTextEqualTo("2") .setBackground("#b4a7d6") // TEST 1 - ADD RULES - NO POSITION range = sheet.getRange("A3:L8") rule1 .setRanges([range]) .build() rule2 .setRanges([range]) .build() Range_ConditionalFormatting(sheet).setRules([rule1, rule2]) // TEST 2 - ADD RULES - BOTTOM OF RULES ARRAY range = sheet.getRange("A14:L18") rule1 .setRanges([range]) .build() rule2 .setRanges([range]) .build() Range_ConditionalFormatting(sheet).setRules([rule1, rule2], -1) // TEST 3 - ADD RULES - TOP OF RULES ARRAY range = sheet.getRange("A24:L28") rule1 .setRanges([range]) .build() rule2 .setRanges([range]) .build() Range_ConditionalFormatting(sheet).setRules([rule1, rule2], 0) // TEST 4 - ADD RULES - AFTER 1ST RULE range = sheet.getRange("A34:L38") rule1 .setRanges([range]) .build() rule2 .setRanges([range]) .build() Range_ConditionalFormatting(sheet).setRules([rule1, rule2], -6) } |
Video Tutorial Series
You can check out the playlist for Conditional formatting here.
The video tutorials:
- Clear Conditional Formatting Rules by Exact Match in Google Sheets with Apps Script
- Clear Conditional Formatting Rules Within a Range in Google Sheets with Apps Script
- Clear Conditional Formatting Rules that Overlap a Target Range in Google Sheets with Apps Script
- Add Conditional Formatting Rules to a Google Sheet Tab with Apps Script
Conclusion
Being such a short pseudo-class it is hardly worth slowing down your script by making this into a library. I recommend you just add it to your existing project for simplicity and performance.
If you think there are any other methods worth adding, please let me know in the comments.
I would also love to hear how you used this in your own projects.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi.