Author: By Raj
Part of our Google Sheets Automation Expert guides. Need this built for your team? Hire a Google Apps Script developer.
Estimated reading time: 10 minutes
Automate Conditional Formatting in Google Sheets with Apps Script
Conditional formatting via Apps Script keeps KPI dashboards consistent when templates are copied weekly or when SLA thresholds change globally.
Use ConditionalFormatRuleBuilder with whenNumberGreater, whenTextContains, or custom formulas (=AND($D2>0,$E2<$F2)) applied through sheet.setConditionalFormatRules([...]).
Combine with Sparkline charts for trends; formatting alone cannot fix bad source data, validate inputs first per /blog/google-apps-script-best-practices.
Programmatic rule builders
Build rules in arrays, then replace all rules at once with setConditionalFormatRules to avoid duplicate conflicting highlights from manual UI edits.
Target ranges with getRange('B2:B') relative to row 2 for column-wide patterns.
SLA and aging highlights
Use custom formula =AND($G2<>"",$G2<TODAY()-3) for tickets open more than three days. Apply red background via setBackground('#f4c7c3').
Refresh rules after timezone changes, use Session.getScriptTimeZone() in helper cells if needed.
Inventory and variance bands
Gradient rules for stock levels: green above safety stock, yellow within 10%, red below minimum. whenNumberBetween helps middle bands.
Archive old rules before importing new product categories to prevent overlapping colors.
Performance on large sheets
Thousands of conditional format rules slow the UI. Prefer one rule per column with relative references instead of per-cell rules in loops.
For very large grids, consider formula-based flag columns plus simple formatting on that column only.
Example code
function applySlaFormatting() {
const sh = SpreadsheetApp.getActive().getSheetByName('Tickets');
const range = sh.getRange('A2:G');
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND($G2<>"", $G2<TODAY()-3)')
.setBackground('#ea9999')
.setRanges([range])
.build();
sh.setConditionalFormatRules([rule]);
}| Approach | Best for | Tradeoff |
|---|---|---|
| Apps Script native | Google Workspace-centric workflows | 6-min limit, quotas |
| Zapier / Make | No-code, many connectors | Per-task cost, vendor lock-in |
| Python + Cloud | Heavy data / ML | Hosting cost, separate auth |
| Google Sheets automation expert | Production custom logic | Build cost, you own code |
FAQ
Can I read existing rules before changing them?
Yes. const rules = sheet.getConditionalFormatRules(); mutate or filter, then setConditionalFormatRules(rules) again.
Do scripted rules appear in the UI?
They show in Format > Conditional formatting like manual rules, which helps analysts understand logic.
Can formatting trigger onEdit loops?
Generally no, but scripts that write values in response to edits can re-fire triggers, use LockService if needed.
How do I match brand hex colors?
setBackground('#hex') and setFontColor accept hex strings. Document palette in a Brand sheet for designers.
Is conditional formatting better than Charts?
Formatting is for at-a-glance ops queues; charts are for trends. Many dashboards use both, see /blog/google-sheets-automation-examples.
Need this done for you? I handle this as part of my consulting work, fixed-price quote within 24 hours.
Book a call with Raj →Get the full Automate Conditional Formatting in Google Sheets with Apps Script script template
I'll email you a production-ready, commented version you can deploy in 10 minutes.
Continue reading
Google Sheets Automation
Google Sheets CRM Automation: Triggers, Pipelines, and Follow-Ups
Google Sheets Automation
Google Sheets Dashboard Automation with Apps Script
Google Sheets Automation
Auto-Generate Reports from Google Sheets with Apps Script
From another topic
How to Automate Google Sheets with Apps Script (Beginner Guide) →Need help with this? I handle this as part of my Google Sheets Automation Expert service.
CRM, dashboards, invoices, inventory, and report automation in Sheets.
See how it works →