Run a “highlight overdue rows” script three times and you get three identical conditional-format rules, then thirty. setHighlightRule(range, { tag, formula, background }) adds a custom-formula rule and first removes any earlier rule it created with the same tag, so re-running it updates the rule instead of stacking duplicates. Rules you or other scripts made are left alone.
When to use
- Setup scripts that people (or Co-Pilot) run more than once
- Overdue, duplicate or “needs review” highlights driven by a formula
- Replacing hand-made rules with ones you can version in code
How to use this snippet
/**
* Add (or replace) a tagged custom-formula conditional-format rule.
* The tag is stored inside the formula as N("tag:...") , which evaluates to 0,
* so the rule behaves exactly like your formula.
*
* @param {Range} range
* @param {{tag: string, formula: string, background: string, fontColor: (string|undefined)}} opts
* @return {number} how many earlier rules with this tag were replaced
*/
function setHighlightRule(range, opts) {
if (!opts || !opts.tag || !opts.formula) throw new Error('setHighlightRule: tag and formula are required');
var sh = range.getSheet();
var marker = 'N("tag:' + opts.tag + '")';
var body = String(opts.formula).replace(/^=/, '');
var tagged = '=(' + body + ')+' + marker; // TRUE + 0 is still truthy
var kept = [], replaced = 0;
sh.getConditionalFormatRules().forEach(function (rule) {
var cond = rule.getBooleanCondition();
var vals = cond ? cond.getCriteriaValues() : [];
var f = vals.length ? String(vals[0]) : '';
if (f.indexOf(marker) !== -1) { replaced++; return; }
kept.push(rule);
});
var builder = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(tagged)
.setRanges([range]);
if (opts.background) builder.setBackground(opts.background);
if (opts.fontColor) builder.setFontColor(opts.fontColor);
kept.push(builder.build());
sh.setConditionalFormatRules(kept);
return replaced;
}
Example
function highlightOverdue() {
var sh = SpreadsheetApp.getActive().getSheetByName('Invoices');
setHighlightRule(sh.getRange('A2:H'), {
tag: 'overdue',
formula: '=AND($G2<>"Paid", $F2<TODAY())',
background: '#fde2e1'
});
}
Run it once or fifty times: the sheet ends up with exactly one “overdue” rule.
Tips:
- Write the formula for the top-left cell of the range, with
$on the columns you want fixed, just as you would in the Sheets UI. - Change the colour or formula and re-run: the old tagged rule is replaced.
- The tag shows up inside the formula in Format → Conditional formatting. Leave it there; it's how the script finds its own rule.
Tip: NitroGAS Co-Pilot can convert your hand-made highlight rules into tagged setHighlightRule calls. Describe each rule in a sentence.
Happy Coding!
