Google Sheets' conditional formatting UI covers simple cases like a single cell being greater than a number, but highlighting an entire task row as overdue based on a due date in one column and a status in another needs a formula-based rule, which the UI supports but is easier to manage reliably from a script.
This tutorial builds two formula-based conditional format rules on a Tasks sheet: one highlighting rows whose due date has passed and are not marked Done, and another highlighting rows due within the next two days as a softer warning.
Both rules use relative row references starting from row two, so the same formula logic applies correctly to every row in the range even though only one formula string is written for the whole rule.
The script appends its new rules to the array returned by getConditionalFormatRules rather than replacing it outright, which preserves any formatting rules already configured on the sheet from other sources.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Range | Rule |
|---|---|---|
| KPI | D2:D | whenFormulaSatisfied =$D2<0 |
| Format | Red background | Negative variance |
| Clear first | clearConditionalFormatRules optional | Avoid duplicates |
What it does
addOverdueConditionalFormat builds an overdue rule and a due-soon rule with whenFormulaSatisfied, applies both to the same task range, and adds them to the sheet's existing conditional format rules instead of overwriting them.
Prerequisites
whenFormulaSatisfied takes a formula string that Sheets evaluates for every cell in the target range, using relative references so a formula anchored at row two automatically adjusts for row three, row four, and beyond.
Walkthrough
No special authorization is required beyond normal spreadsheet edit access; this pattern applies formatting the same way a user could through the Format menu, just expressed as code.
Edge cases
The formula locks the column references with a dollar sign, as in $D2 and $C2, so the rule always reads column D for the due date and column C for the status regardless of which column in the range is being evaluated, while the row number stays relative.
Testing
Calling setConditionalFormatRules with an incomplete array, rather than the existing rules plus the new ones, silently deletes every other conditional format on the sheet, which is why this function reads getConditionalFormatRules first and pushes onto that array.
Hardening
Add a handful of test rows with due dates in the past, in the next two days, and further in the future, then run addOverdueConditionalFormat and confirm each group highlights with the correct color before applying it to real task data.
Variations
Formula-based rules are evaluated for every cell in their range on every recalculation, so applying them to an extremely large range on a sheet with heavy formula use elsewhere can add noticeably to recalculation time, and narrowing the range to only rows currently in use helps.
Full code: addOverdueConditionalFormat()
Add test rows spanning past, near-future, and far-future due dates, then run addOverdueConditionalFormat and confirm each group highlights with the intended color.
function addOverdueConditionalFormat() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
var range = sheet.getRange('A2:D500');
var existingRules = sheet.getConditionalFormatRules();
var overdueRule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND($D2<TODAY(), $D2<>"", $C2<>"Done")')
.setBackground('#f4c7c3')
.setFontColor('#a50e0e')
.setRanges([range])
.build();
var dueSoonRule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND($D2>=TODAY(), $D2<=TODAY()+2, $C2<>"Done")')
.setBackground('#fce8b2')
.setFontColor('#7f6000')
.setRanges([range])
.build();
existingRules.push(overdueRule);
existingRules.push(dueSoonRule);
sheet.setConditionalFormatRules(existingRules);
}- Line 4: Reads the sheet's existing rules before adding new ones.
- Line 6: Builds a formula-based rule for overdue, undone rows.
- Line 7: Locks column references while leaving the row reference relative.
- Line 12: Builds a second, softer rule for rows due within two days.
- Line 19: Appends both new rules to the existing array instead of replacing it.
- Line 20: Applies the combined rule set back to the sheet in one call.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: add formula-based conditional formatting
- 1Column references in each formula locked with a dollar sign, row references left relative
- 2Existing conditional format rules read before adding new ones, not overwritten
- 3Overdue and due-soon ranges match the actual Tasks data range
- 4Rule colors distinct enough to tell overdue apart from due-soon at a glance
- 5Test rows covering past, near-future, and far-future due dates checked
- 6Rule priority order considered if a row could match more than one rule
- 7Rule range kept reasonably sized to limit recalculation overhead
Frequently asked questions
The dollar sign before D locks the column so the rule always compares against the due date column, while leaving the row number without a dollar sign lets it shift relatively for each row the rule is applied to.
Any previously configured conditional format rules on that sheet are removed, since setConditionalFormatRules replaces the entire rule set rather than appending to it, which is why this tutorial reads the existing rules first.
Yes, whenFormulaSatisfied accepts any formula that evaluates to true or false, including one built from AND, OR, or nested logic combining several conditions the way this tutorial's overdue check does.
Whichever rule appears first in the array returned by getConditionalFormatRules generally takes formatting priority, so ordering matters if a row could technically satisfy more than one rule's formula.
Yes, conditional formatting is evaluated per cell based on the sheet's underlying data, so it continues to apply correctly regardless of how the rows are currently sorted or filtered.
Read getConditionalFormatRules, filter out the rule you want to remove based on its criteria or range, and call setConditionalFormatRules with the filtered array to apply the change.