Conditional Formatting with Apps Script

Conditional formatting rules automatically change a cell's appearance based on its value. Apps Script lets you create these rules programmatically using SpreadsheetApp.newConditionalFormatRule() — applying them in bulk to an entire sheet, updating them when conditions change, or removing all rules at once.

Highlighting Closed Deals

function highlightClosedDeals() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var range = sheet.getRange("A2:H100");

  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$G2="Closed"')
    .setBackground("#c6efce")
    .setFontColor("#276221")
    .setRanges([range])
    .build();

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

  Logger.log("Conditional format rule applied for Closed.");
}

Applying Multiple Status Rules at Once

This replaces all existing conditional format rules with a clean set for all three statuses:

function applyAllStatusRules() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var range = sheet.getRange("A2:H100");

  var rules = [
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$G2="Closed"')
      .setBackground("#c6efce").setFontColor("#276221")
      .setRanges([range]).build(),

    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$G2="In Progress"')
      .setBackground("#ffeb9c").setFontColor("#7d6608")
      .setRanges([range]).build(),

    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$G2="Pending"')
      .setBackground("#fce4d6").setFontColor("#922b21")
      .setRanges([range]).build()
  ];

  sheet.setConditionalFormatRules(rules);
  Logger.log("All status conditional format rules applied.");
}

With the sample data, Sarah Johnson and Lisa Park's rows turn green, and Mark Chen's row turns yellow automatically.

Highlighting High-Value Deals

Flag Enterprise Plan deals (Amount > 500) in the Amount column:

function highlightHighValueDeals() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var amountRange = sheet.getRange("D2:D100");

  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(500)
    .setBackground("#d0e4ff")
    .setFontColor("#1a73e8")
    .setFontWeight("bold")
    .setRanges([amountRange])
    .build();

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

  Logger.log("High-value deal highlight applied.");
}

Mark Chen's $999 Enterprise Plan amount cell is highlighted.

Highlighting Overdue Deals (Date > 30 Days Ago)

function highlightOverdueDeals() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var range = sheet.getRange("A2:H100");

  // Flag In Progress deals where the date is older than 30 days
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=AND($G2="In Progress", $H2<TODAY()-30)')
    .setBackground("#ffc7ce")
    .setFontColor("#9c0006")
    .setRanges([range])
    .build();

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

  Logger.log("Overdue deal highlight applied.");
}

Removing All Conditional Format Rules

function clearAllConditionalFormatRules() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  sheet.setConditionalFormatRules([]);
  Logger.log("All conditional format rules cleared.");
}

Available Condition Methods

Method	Description
`.whenTextEqualTo(value)`	Cell text equals value
`.whenTextContains(value)`	Cell text contains value
`.whenNumberGreaterThan(n)`	Cell number > n
`.whenNumberLessThan(n)`	Cell number < n
`.whenNumberBetween(a, b)`	Cell number between a and b
`.whenFormulaSatisfied(formula)`	Custom formula evaluates to true
`.whenCellEmpty()`	Cell is empty
`.whenCellNotEmpty()`	Cell is not empty