Class ConditionalFormatRuleBuilder

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

MethodReturn typeBrief description
build()ConditionalFormatRuleConstructs a conditional format rule from the settings applied to the builder.
copy()ConditionalFormatRuleBuilderReturns a rule builder preset with this rule's settings.
getBooleanCondition()BooleanConditionRetrieves the rule's BooleanCondition information if this rule uses boolean condition criteria.
getGradientCondition()GradientConditionRetrieves 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)ConditionalFormatRuleBuilderSets the background color for the conditional format rule's format.
setBold(bold)ConditionalFormatRuleBuilderSets text bolding for the conditional format rule's format.
setFontColor(color)ConditionalFormatRuleBuilderSets the font color for the conditional format rule's format.
setGradientMaxpoint(color)ConditionalFormatRuleBuilderClears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges.
setGradientMaxpointWithValue(color, type, value)ConditionalFormatRuleBuilderSets the conditional format rule's gradient maxpoint fields.
setGradientMidpointWithValue(color, type, value)ConditionalFormatRuleBuilderSets the conditional format rule's gradient midpoint fields.
setGradientMinpoint(color)ConditionalFormatRuleBuilderClears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges.
setGradientMinpointWithValue(color, type, value)ConditionalFormatRuleBuilderSets the conditional format rule's gradient minpoint fields.
setItalic(italic)ConditionalFormatRuleBuilderSets text italics for the conditional format rule's format.
setRanges(ranges)ConditionalFormatRuleBuilderSets one or more ranges to which this conditional format rule is applied.
setStrikethrough(strikethrough)ConditionalFormatRuleBuilderSets text strikethrough for the conditional format rule's format.
setUnderline(underline)ConditionalFormatRuleBuilderSets text underlining for the conditional format rule's format.
whenCellEmpty()ConditionalFormatRuleBuilderSets the conditional format rule to trigger when the cell is empty.
whenCellNotEmpty()ConditionalFormatRuleBuilderSets the conditional format rule to trigger when the cell is not empty.
whenDateAfter(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is after the given value.
whenDateAfter(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is after the given relative date.
whenDateBefore(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is before the given date.
whenDateBefore(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is before the given relative date.
whenDateEqualTo(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is equal to the given date.
whenDateEqualTo(date)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a date is equal to the given relative date.
whenFormulaSatisfied(formula)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the given formula evaluates to true.
whenNumberBetween(start, end)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number falls between, or is either of, two specified values.
whenNumberEqualTo(number)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number is equal to the given value.
whenNumberGreaterThan(number)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number is greater than the given value.
whenNumberGreaterThanOrEqualTo(number)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number is greater than or equal to the given value.
whenNumberLessThan(number)ConditionalFormatRuleBuilderSets the conditional conditional format rule to trigger when a number less than the given value.
whenNumberLessThanOrEqualTo(number)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number less than or equal to the given value.
whenNumberNotBetween(start, end)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values.
whenNumberNotEqualTo(number)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when a number is not equal to the given value.
whenTextContains(text)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the input contains the given value.
whenTextDoesNotContain(text)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the input does not contain the given value.
whenTextEndsWith(text)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the input ends with the given value.
whenTextEqualTo(text)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the input is equal to the given value.
whenTextStartsWith(text)ConditionalFormatRuleBuilderSets the conditional format rule to trigger when that the input starts with the given value.
withCriteria(criteria, args)ConditionalFormatRuleBuilderSets 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) {
  Logger.log(gradientCondition.getMinColor());
}

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 (int 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

NameTypeDescription
colorStringThe desired color 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

NameTypeDescription
boldBooleanWhether 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

NameTypeDescription
colorStringThe desired color 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

NameTypeDescription
colorStringThe maxpoint color 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

NameTypeDescription
colorStringThe maxpoint color to set.
typeInterpolationTypeThe maxpoint interpolation type to set.
valueStringThe maxpoint 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

NameTypeDescription
colorStringThe midpoint color to set.
typeInterpolationTypeThe midpoint interpolation type to set or null to clear.
valueStringThe 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

NameTypeDescription
colorStringThe minpoint color 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

NameTypeDescription
colorStringThe minpoint color to set.
typeInterpolationTypeThe minpoint interpolation type to set.
valueStringThe 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

NameTypeDescription
italicBooleanWhether 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

NameTypeDescription
rangesRange[]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

NameTypeDescription
strikethroughBooleanWhether 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

NameTypeDescription
underlineBooleanWhether 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

NameTypeDescription
dateDateThe 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

NameTypeDescription
dateRelativeDateThe 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

NameTypeDescription
dateDateThe 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

NameTypeDescription
dateRelativeDateThe 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

NameTypeDescription
dateDateThe 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

NameTypeDescription
dateRelativeDateThe 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

NameTypeDescription
formulaStringA 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

NameTypeDescription
startNumberThe lowest acceptable value.
endNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
startNumberThe lowest unacceptable value.
endNumberThe 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

NameTypeDescription
numberNumberThe 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

NameTypeDescription
textStringThe 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

NameTypeDescription
textStringThe 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

NameTypeDescription
textStringText 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

NameTypeDescription
textStringThe 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

NameTypeDescription
textStringText 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")
      .build();
  rules.push(rule);
}
sheet.setConditionalFormatRules(rules);

Parameters

NameTypeDescription
criteriaBooleanCriteriaThe type of conditional format criteria.
argsObject[]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

Send feedback about...

Apps Script
Apps Script
Need help? Visit our support page.