/**
* #### 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
}
