Class ConditionalFormatRuleBuilder

Builder for conditional format rules.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number between 1 and 10.
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Methods

Method	Return type	Brief description
`build()`	`ConditionalFormatRule`	Constructs a conditional format rule from the settings applied to the builder.
`copy()`	`ConditionalFormatRuleBuilder`	Returns a rule builder preset with this rule's settings.
`getBooleanCondition()`	`BooleanCondition`	Retrieves the rule's `BooleanCondition` information if this rule uses boolean condition criteria.
`getGradientCondition()`	`GradientCondition`	Retrieves the rule's `GradientCondition` information, if this rule uses gradient condition criteria.
`getRanges()`	`Range[]`	Retrieves the ranges to which this conditional format rule is applied.
`setBackground(color)`	`ConditionalFormatRuleBuilder`	Sets the background color for the conditional format rule's format.
`setBackgroundObject(color)`	`ConditionalFormatRuleBuilder`	Sets the background color for the conditional format rule's format.
`setBold(bold)`	`ConditionalFormatRuleBuilder`	Sets text bolding for the conditional format rule's format.
`setFontColor(color)`	`ConditionalFormatRuleBuilder`	Sets the font color for the conditional format rule's format.
`setFontColorObject(color)`	`ConditionalFormatRuleBuilder`	Sets the font color for the conditional format rule's format.
`setGradientMaxpoint(color)`	`ConditionalFormatRuleBuilder`	Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges.
`setGradientMaxpointObject(color)`	`ConditionalFormatRuleBuilder`	Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges.
`setGradientMaxpointObjectWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient maxpoint fields.
`setGradientMaxpointWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient maxpoint fields.
`setGradientMidpointObjectWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient midpoint fields.
`setGradientMidpointWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient midpoint fields.
`setGradientMinpoint(color)`	`ConditionalFormatRuleBuilder`	Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges.
`setGradientMinpointObject(color)`	`ConditionalFormatRuleBuilder`	Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges.
`setGradientMinpointObjectWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient minpoint fields.
`setGradientMinpointWithValue(color, type, value)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule's gradient minpoint fields.
`setItalic(italic)`	`ConditionalFormatRuleBuilder`	Sets text italics for the conditional format rule's format.
`setRanges(ranges)`	`ConditionalFormatRuleBuilder`	Sets one or more ranges to which this conditional format rule is applied.
`setStrikethrough(strikethrough)`	`ConditionalFormatRuleBuilder`	Sets text strikethrough for the conditional format rule's format.
`setUnderline(underline)`	`ConditionalFormatRuleBuilder`	Sets text underlining for the conditional format rule's format.
`whenCellEmpty()`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when the cell is empty.
`whenCellNotEmpty()`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when the cell is not empty.
`whenDateAfter(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is after the given value.
`whenDateAfter(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is after the given relative date.
`whenDateBefore(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is before the given date.
`whenDateBefore(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is before the given relative date.
`whenDateEqualTo(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is equal to the given date.
`whenDateEqualTo(date)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a date is equal to the given relative date.
`whenFormulaSatisfied(formula)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the given formula evaluates to `true`.
`whenNumberBetween(start, end)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number falls between, or is either of, two specified values.
`whenNumberEqualTo(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number is equal to the given value.
`whenNumberGreaterThan(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number is greater than the given value.
`whenNumberGreaterThanOrEqualTo(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number is greater than or equal to the given value.
`whenNumberLessThan(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional conditional format rule to trigger when a number less than the given value.
`whenNumberLessThanOrEqualTo(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number less than or equal to the given value.
`whenNumberNotBetween(start, end)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values.
`whenNumberNotEqualTo(number)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when a number is not equal to the given value.
`whenTextContains(text)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the input contains the given value.
`whenTextDoesNotContain(text)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the input does not contain the given value.
`whenTextEndsWith(text)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the input ends with the given value.
`whenTextEqualTo(text)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the input is equal to the given value.
`whenTextStartsWith(text)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to trigger when that the input starts with the given value.
`withCriteria(criteria, args)`	`ConditionalFormatRuleBuilder`	Sets the conditional format rule to criteria defined by `BooleanCriteria` values, typically taken from the `criteria` and `arguments` of an existing rule.

Detailed documentation

`build()`

Constructs a conditional format rule from the settings applied to the builder.

Return

ConditionalFormatRule — a representation of the conditional format rule

`copy()`

Returns a rule builder preset with this rule's settings.

Return

ConditionalFormatRuleBuilder — a builder based on this rule's settings

`getBooleanCondition()`

Retrieves the rule's BooleanCondition information if this rule uses boolean condition criteria. Otherwise returns null.

// Log the boolean criteria type of the first conditional format rules of a sheet.
var rule = SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];
var booleanCondition = rule.getBooleanCondition();
if (booleanCondition != null) {
  Logger.log(booleanCondition.getCriteriaType());
}

Return

BooleanCondition — the boolean condition object, or null if the rule does not use a boolean condition.

`getGradientCondition()`

Retrieves the rule's GradientCondition information, if this rule uses gradient condition criteria. Otherwise returns null.

// Log the gradient minimum color of the first conditional format rule of a sheet.
var rule = SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];
var gradientCondition = rule.getGradientCondition();
if (gradientCondition != null) {
  // Assume the color has ColorType.RGB.
  Logger.log(gradientCondition.getMinColorObject().asRgbColor().asHexString());
}

Return

GradientCondition — The gradient condition object, or null if the rule does not use a gradient condition.

`getRanges()`

Retrieves the ranges to which this conditional format rule is applied.

// Log each range of the first conditional format rule of a sheet.
var rule = SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];
var ranges = rule.getRanges();
for (var i = 0; i < ranges.length; i++) {
  Logger.log(ranges[i].getA1Notation());
}

Return

Range[] — the ranges to which this conditional format rule is applied.

`setBackground(color)`

Sets the background color for the conditional format rule's format. Passing in null removes the background color format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color to red if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The desired color or `null` to clear.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setBackgroundObject(color)`

Sets the background color for the conditional format rule's format. Passing in null removes the background color format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color to theme background color if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground(color)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The desired color object or `null` to clear.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setBold(bold)`

Sets text bolding for the conditional format rule's format. If bold is true, the rule bolds text if the condition is met; if false, the rule removes any existing bolding if the condition is met. Passing in null removes the bold format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn their
// text bold if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBold(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`bold`	`Boolean`	Whether or not the text should be bolded if the format condition is met; `null` removes this setting.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setFontColor(color)`

Sets the font color for the conditional format rule's format. Passing in null removes the font color format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their font
// color to red if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setFontColor("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The desired color or `null` to clear.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setFontColorObject(color)`

Sets the font color for the conditional format rule's format. Passing in null removes the font color format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their font
// color to theme text color if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setFontColor(color)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The desired color object or `null` to clear.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMaxpoint(color)`

Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges. Also sets the gradient's maxpoint color to the input color.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere between white and red, based on their values in comparison to
// the ranges minimum and maximum values.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint("#FF0000")
    .setGradientMinpoint("#FFFFFF")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The maxpoint color to set.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setGradientMaxpointObject(color)`

Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges. Also sets the gradient's maxpoint color to the input color.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere between theme text and background colors, based on their values
// in comparison to the ranges minimum and maximum values.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var textColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var backgroundColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint(textColor)
    .setGradientMinpoint(backgroundColor)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The maxpoint color object to set.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMaxpointObjectWithValue(color, type, value)`

Sets the conditional format rule's gradient maxpoint fields.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from theme accent 1, accent 2 to accent 3 colors, based on their
// values in comparison to the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The maxpoint color to set.
`type`	`InterpolationType`	The maxpoint interpolation type to set.
`value`	`String`	The maxpoint value to set.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMaxpointWithValue(color, type, value)`

Sets the conditional format rule's gradient maxpoint fields.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from red green to blue, based on their values in comparison to
// the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The maxpoint color to set.
`type`	`InterpolationType`	The maxpoint interpolation type to set.
`value`	`String`	The maxpoint value to set.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setGradientMidpointObjectWithValue(color, type, value)`

Sets the conditional format rule's gradient midpoint fields. Clears all of the midpoint fields if the passed in interpolation type is null.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from theme accent 1 to accent 2 to accent 3 colors, based on
// their values in comparison to the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The midpoint color to set.
`type`	`InterpolationType`	The midpoint interpolation type to set or `null` to clear.
`value`	`String`	The midpoint value to set.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMidpointWithValue(color, type, value)`

Sets the conditional format rule's gradient midpoint fields. Clears all of the midpoint fields if the passed in interpolation type is null.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from red green to blue, based on their values in comparison to
// the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The midpoint color to set.
`type`	`InterpolationType`	The midpoint interpolation type to set or `null` to clear.
`value`	`String`	The midpoint value to set.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setGradientMinpoint(color)`

Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges. Also sets the gradient's minpoint color to the input color.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere between white and red, based on their values in comparison to
// the ranges minimum and maximum values.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint("#FF0000")
    .setGradientMinpoint("#FFFFFF")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The minpoint color to set.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setGradientMinpointObject(color)`

Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges. Also sets the gradient's minpoint color to the input color.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere between theme text and background colors, based on their values
// in comparison to the ranges minimum and maximum values.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var textColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var backgroundColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint(textColor)
    .setGradientMinpoint(backgroundColor)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The minpoint color object to set.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMinpointObjectWithValue(color, type, value)`

Sets the conditional format rule's gradient minpoint fields.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from theme accent 1 to accent 2 to accent 3 colors, based on
// their values in comparison to the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`Color`	The minpoint color to set.
`type`	`InterpolationType`	The minpoint interpolation type to set.
`value`	`String`	The minpoint value to set.

Return

ConditionalFormatRuleBuilder — The builder, for chaining.

`setGradientMinpointWithValue(color, type, value)`

Sets the conditional format rule's gradient minpoint fields.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their
// background color somewhere from red to green to blue, based on their values in comparison to
// the values 0, 50, and 100.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`color`	`String`	The minpoint color to set.
`type`	`InterpolationType`	The minpoint interpolation type to set.
`value`	`String`	The minpoint value to set.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setItalic(italic)`

Sets text italics for the conditional format rule's format. If italic is true, the rule italicises text if the condition is met; if false, the rule removes any existing italicization if the condition is met. Passing in null removes the italic format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn their
// text italic if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setItalic(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`italic`	`Boolean`	Whether or not the text should be italicised if the format condition is met; `null` removes this setting.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setRanges(ranges)`

Sets one or more ranges to which this conditional format rule is applied. This operation replaces any existing ranges. Setting an empty array clears any existing ranges. A rule must have at least one range.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 and range D4:F6
// to turn red if they contain a number between 1 and 10.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeOne = sheet.getRange("A1:B3");
var rangeTwo = sheet.getRange("D4:F6");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([rangeOne, rangeTwo])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`ranges`	`Range[]`	The ranges to which this conditional format rule is applied.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setStrikethrough(strikethrough)`

Sets text strikethrough for the conditional format rule's format. If strikethrough is true, the rule strikesthrough text if the condition is met; if false, the rule removes any existing strikethrough formatting if the condition is met. Passing in null removes the strikethrough format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to strikethrough
// their text if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setStrikethrough(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`strikethrough`	`Boolean`	Whether or not the text should be struckthrough if the format condition is met; `null` removes this setting.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`setUnderline(underline)`

Sets text underlining for the conditional format rule's format. If underline is true, the rule underlines text if the condition is met; if false, the rule removes any existing underlines if the condition is met. Passing in null removes the underline format setting from the rule.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to underline
// their text if the cell has text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setUnderline(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`underline`	`Boolean`	Whether or not the text should be underlined if the format condition is met; `null` removes this setting.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenCellEmpty()`

Sets the conditional format rule to trigger when the cell is empty.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they are empty.
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellEmpty()
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenCellNotEmpty()`

Sets the conditional format rule to trigger when the cell is not empty.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they are not empty.
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellNotEmpty()
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateAfter(date)`

Sets the conditional format rule to trigger when a date is after the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a date after 11/4/1993.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateAfter(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`Date`	The latest date.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateAfter(date)`

Sets the conditional format rule to trigger when a date is after the given relative date.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a date after today.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateAfter(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateBefore(date)`

Sets the conditional format rule to trigger when a date is before the given date.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a date before 11/4/1993.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateBefore(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`Date`	The earliest unacceptable date.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateBefore(date)`

Sets the conditional format rule to trigger when a date is before the given relative date.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a date before today.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateBefore(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateEqualTo(date)`

Sets the conditional format rule to trigger when a date is equal to the given date.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain the date 11/4/1993.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateEqualTo(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`Date`	The sole acceptable date.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenDateEqualTo(date)`

Sets the conditional format rule to trigger when a date is equal to the given relative date.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain todays date.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateEqualTo(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenFormulaSatisfied(formula)`

Sets the conditional format rule to trigger when that the given formula evaluates to true.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they satisfy the condition "=EQ(B4, C3)".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("=EQ(B4, C3)")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`formula`	`String`	A custom formula that evaluates to `true` if the input is valid.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberBetween(start, end)`

Sets the conditional format rule to trigger when a number falls between, or is either of, two specified values.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number between 1 and 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`start`	`Number`	The lowest acceptable value.
`end`	`Number`	The highest acceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberEqualTo(number)`

Sets the conditional format rule to trigger when a number is equal to the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain the number 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The sole acceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberGreaterThan(number)`

Sets the conditional format rule to trigger when a number is greater than the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red
// if they contain a number greater than 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The highest unacceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberGreaterThanOrEqualTo(number)`

Sets the conditional format rule to trigger when a number is greater than or equal to the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number greater than or equal to 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThanOrEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The lowest acceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberLessThan(number)`

Sets the conditional conditional format rule to trigger when a number less than the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number less than 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The lowest unacceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberLessThanOrEqualTo(number)`

Sets the conditional format rule to trigger when a number less than or equal to the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number less than or equal to 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThanOrEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The highest acceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberNotBetween(start, end)`

Sets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number not between 1 and 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberNotBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`start`	`Number`	The lowest unacceptable value.
`end`	`Number`	The highest unacceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenNumberNotEqualTo(number)`

Sets the conditional format rule to trigger when a number is not equal to the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they don't contain the number 10.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberNotEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`number`	`Number`	The sole unacceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenTextContains(text)`

Sets the conditional format rule to trigger when that the input contains the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain the text "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextContains("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`text`	`String`	The value that the input must contain.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenTextDoesNotContain(text)`

Sets the conditional format rule to trigger when that the input does not contain the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they don't contain the text "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextDoesNotContain("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`text`	`String`	The value that the input must not contain.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenTextEndsWith(text)`

Sets the conditional format rule to trigger when that the input ends with the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they end with the text "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEndsWith("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`text`	`String`	Text to compare against the end of the string.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenTextEqualTo(text)`

Sets the conditional format rule to trigger when that the input is equal to the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they have text equal to "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`text`	`String`	The sole acceptable value.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`whenTextStartsWith(text)`

Sets the conditional format rule to trigger when that the input starts with the given value.

// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they start with the text "hello".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextStartsWith("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`text`	`String`	Text to compare against the beginning of the string.

Return

ConditionalFormatRuleBuilder — the builder, for chaining

`withCriteria(criteria, args)`

Sets the conditional format rule to criteria defined by BooleanCriteria values, typically taken from the criteria and arguments of an existing rule.

// Adds a new conditional format rule that is a copy of the first active
// conditional format rule, except it instead sets its cells to have a black
// background color.

var sheet = SpreadsheetApp.getActiveSheet();
var rules = sheet.getConditionalFormatRules();
var booleanCondition = rules[0].getBooleanCondition();
if (booleanCondition != null) {
  var rule = SpreadsheetApp.newConditionalFormatRule()
      .withCriteria(booleanCondition.getCriteriaType(),
         booleanCondition.getCriteriaValues())
      .setBackground("#000000")
      .setRanges(rule.getRanges())
      .build();
  rules.push(rule);
}
sheet.setConditionalFormatRules(rules);

Parameters

Name	Type	Description
`criteria`	`BooleanCriteria`	The type of conditional format criteria.
`args`	`Object[]`	An array of arguments appropriate to the criteria type; the number of arguments and their type match the corresponding `when...()` method above.

Return

ConditionalFormatRuleBuilder — the builder, for chaining