Why does my script delete all existing conditional formatting when it runs?

Because setConditionalFormatRules replaces the full list. You need to read the existing rules first with getConditionalFormatRules(), append or modify the array, and then pass it back. Calling setConditionalFormatRules([newRule]) with a fresh single-element array wipes everything else.

How do I apply a rule based on a custom formula, like highlighting a row when column C says 'Done'?

Use whenFormulaSatisfied on the builder and pass a formula string: .whenFormulaSatisfied('=$C2="Done"'). Set the range to the full row span you want highlighted (e.g. A2:Z100). The dollar sign on C anchors the column while the row reference shifts per cell, matching how the Sheets UI handles row-highlight formulas.

Can I add a gradient (color scale) rule with Apps Script?

Yes. Instead of chaining boolean condition methods, call setGradientMaxpoint, setGradientMidpointWithValue, and setGradientMinpoint on the builder. These accept InterpolationType enum values (SpreadsheetApp.InterpolationType.NUMBER, PERCENT, etc.) and hex color strings. The builder path is the same — setRanges, build, append, setConditionalFormatRules.

How do I target a dynamic range that changes size each time the script runs?

Get the last row with data first — sheet.getLastRow() — then build the range string in code before passing it to getRange. For example: sheet.getRange('B2:B' + sheet.getLastRow()). Pass that Range object into setRanges as usual. The rule locks to whatever range you passed at build time; it does not auto-expand after the fact.

// Sheets · Apps Script

Apply conditional formatting rules with a script in Google Sheets.

How to build and attach a conditional formatting rule in Google Sheets using Apps Script — without wiping the existing rules already on the sheet.

“I want to add a conditional formatting rule to a Google Sheet from a script without deleting the rules that are already there.”

The script

copy · paste · trigger

applyConditionalFormat.gs

Apps Script

// Highlight cells in B2:B50 red when value is less than 0
function applyNegativeHighlight() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  var range = sheet.getRange('B2:B50');

  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(0)
    .setBackground('#FF9999')
    .setFontColor('#CC0000')
    .setRanges([range])
    .build();

  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Build the rule with the builder API

SpreadsheetApp.newConditionalFormatRule() returns a ConditionalFormatRuleBuilder. You chain condition methods onto it — whenNumberLessThan, whenTextContains, whenFormulaSatisfied, and about a dozen others — then setBackground or setFontColor for the visual output, then setRanges with an array of Range objects, then build() to produce the immutable rule.

The condition and the style are completely separate concerns on the builder. You can call setBackground without setFontColor, or both, or neither if you only want a custom text style. The one thing you cannot omit is setRanges — a rule with no ranges attached will throw when you try to commit it.

For formula-based rules, use whenFormulaSatisfied and pass a string starting with '='. The formula is evaluated relative to the top-left cell of each range, exactly the same way the Sheets UI works. I keep a comment in the script noting which cell the formula anchors to, because it will confuse you six months later.

The append pattern — why you must read before writing

setConditionalFormatRules replaces the entire rule list on the sheet. Pass it an empty array and every rule the user or another script ever set disappears. This is the single most common bug people file about conditional formatting in Apps Script.

The fix is one extra line: call getConditionalFormatRules() first, push your new rule onto that array, then pass the whole array to setConditionalFormatRules. The sheet stores rules in priority order — index 0 wins when two rules conflict — so push appends at the lowest priority. If your rule needs to take precedence, use unshift instead of push.

If you're running a setup function that should be idempotent, check whether a rule for that range already exists before appending. Loop over getConditionalFormatRules(), call getRanges() on each, and compare A1 notation. It's more code but it keeps repeated script runs from stacking duplicate rules.

Clearing or replacing a specific rule without touching the others

To remove one rule, filter it out of the array by index or by matching its condition type and range, then call setConditionalFormatRules with the filtered result. There is no removeConditionalFormatRule singular method — the whole-list replacement is the only write path.

ConditionalFormatRule exposes getBooleanCondition() and getGradientCondition() to inspect what a rule does. getBooleanCondition() returns a BooleanCondition object with getCriteriaType(), getCriteriaValues(), getBackground(), and getFontColor(), which gives you enough to match against a rule you previously wrote. In practice I find it simpler to tag the range — format a named range and match on getRanges()[0].getA1Notation() — rather than inspect the style values.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my script delete all existing conditional formatting when it runs?: Because setConditionalFormatRules replaces the full list. You need to read the existing rules first with getConditionalFormatRules(), append or modify the array, and then pass it back. Calling setConditionalFormatRules([newRule]) with a fresh single-element array wipes everything else.
How do I apply a rule based on a custom formula, like highlighting a row when column C says 'Done'?: Use whenFormulaSatisfied on the builder and pass a formula string: .whenFormulaSatisfied('=$C2="Done"'). Set the range to the full row span you want highlighted (e.g. A2:Z100). The dollar sign on C anchors the column while the row reference shifts per cell, matching how the Sheets UI handles row-highlight formulas.
Can I add a gradient (color scale) rule with Apps Script?: Yes. Instead of chaining boolean condition methods, call setGradientMaxpoint, setGradientMidpointWithValue, and setGradientMinpoint on the builder. These accept InterpolationType enum values (SpreadsheetApp.InterpolationType.NUMBER, PERCENT, etc.) and hex color strings. The builder path is the same — setRanges, build, append, setConditionalFormatRules.
How do I target a dynamic range that changes size each time the script runs?: Get the last row with data first — sheet.getLastRow() — then build the range string in code before passing it to getRange. For example: sheet.getRange('B2:B' + sheet.getLastRow()). Pass that Range object into setRanges as usual. The rule locks to whatever range you passed at build time; it does not auto-expand after the fact.

// one good script a week