Class ConditionalFormatRuleBuilder
Page Summary
The
ConditionalFormatRuleBuilderclass in Google Apps Script provides methods to set conditional formatting rules based on date, formula, number, and text conditions.Date-based methods like
whenDateBeforeandwhenDateEqualTocan use either specific dates or relative dates such asRelativeDate.TODAY.whenFormulaSatisfiedallows applying formatting based on a custom formula that evaluates to true.A comprehensive set of methods starting with
whenNumberenables formatting based on numerical comparisons like equality, inequality, greater than/less than, and being within or outside a range.Text-based conditions using methods like
whenTextContainsandwhenTextEqualToallow formatting based on whether cells contain, do not contain, start with, end with, or are exactly equal to a specific text string.
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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberBetween(1,10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Methods
| Method | Return type | Brief description |
|---|---|---|
build() | Conditional | Constructs a conditional format rule from the settings applied to the builder. |
copy() | Conditional | Returns a rule builder preset with this rule's settings. |
get | Boolean | Retrieves the rule'sBoolean information if this rule usesboolean condition criteria. |
get | Gradient | Retrieves the rule'sGradient information, if this ruleuses gradient condition criteria. |
get | Range[] | Retrieves the ranges to which this conditional format rule is applied. |
set | Conditional | Sets the background color for the conditional format rule's format. |
set | Conditional | Sets the background color for the conditional format rule's format. |
set | Conditional | Sets text bolding for the conditional format rule's format. |
set | Conditional | Sets the font color for the conditional format rule's format. |
set | Conditional | Sets the font color for the conditional format rule's format. |
set | Conditional | Clears the conditional format rule's gradient maxpoint value, and instead uses the maximumvalue in the rule's ranges. |
set | Conditional | Clears the conditional format rule's gradient maxpoint value, and instead uses the maximumvalue in the rule's ranges. |
set | Conditional | Sets the conditional format rule's gradient maxpoint fields. |
set | Conditional | Sets the conditional format rule's gradient maxpoint fields. |
set | Conditional | Sets the conditional format rule's gradient midpoint fields. |
set | Conditional | Sets the conditional format rule's gradient midpoint fields. |
set | Conditional | Clears the conditional format rule's gradient minpoint value, and instead uses the minimumvalue in the rule's ranges. |
set | Conditional | Clears the conditional format rule's gradient minpoint value, and instead uses the minimumvalue in the rule's ranges. |
set | Conditional | Sets the conditional format rule's gradient minpoint fields. |
set | Conditional | Sets the conditional format rule's gradient minpoint fields. |
set | Conditional | Sets text italics for the conditional format rule's format. |
set | Conditional | Sets one or more ranges to which this conditional format rule is applied. |
set | Conditional | Sets text strikethrough for the conditional format rule's format. |
set | Conditional | Sets text underlining for the conditional format rule's format. |
when | Conditional | Sets the conditional format rule to trigger when the cell is empty. |
when | Conditional | Sets the conditional format rule to trigger when the cell is not empty. |
when | Conditional | Sets the conditional format rule to trigger when a date is after the given value. |
when | Conditional | Sets the conditional format rule to trigger when a date is after the given relative date. |
when | Conditional | Sets the conditional format rule to trigger when a date is before the given date. |
when | Conditional | Sets the conditional format rule to trigger when a date is before the given relative date. |
when | Conditional | Sets the conditional format rule to trigger when a date is equal to the given date. |
when | Conditional | Sets the conditional format rule to trigger when a date is equal to the given relative date. |
when | Conditional | Sets the conditional format rule to trigger when that the given formula evaluates totrue. |
when | Conditional | Sets the conditional format rule to trigger when a number falls between, or is either of, twospecified values. |
when | Conditional | Sets the conditional format rule to trigger when a number is equal to the given value. |
when | Conditional | Sets the conditional format rule to trigger when a number is greater than the given value. |
when | Conditional | Sets the conditional format rule to trigger when a number is greater than or equal to the givenvalue. |
when | Conditional | Sets the conditional conditional format rule to trigger when a number less than the givenvalue. |
when | Conditional | Sets the conditional format rule to trigger when a number less than or equal to the givenvalue. |
when | Conditional | Sets the conditional format rule to trigger when a number does not fall between, and is neitherof, two specified values. |
when | Conditional | Sets the conditional format rule to trigger when a number is not equal to the given value. |
when | Conditional | Sets the conditional format rule to trigger when that the input contains the given value. |
when | Conditional | Sets the conditional format rule to trigger when that the input does not contain the givenvalue. |
when | Conditional | Sets the conditional format rule to trigger when that the input ends with the given value. |
when | Conditional | Sets the conditional format rule to trigger when that the input is equal to the given value. |
when | Conditional | Sets the conditional format rule to trigger when that the input starts with the given value. |
with | Conditional | Sets the conditional format rule to criteria defined byBoolean values,typically taken from thecriteria andarguments of anexisting rule. |
Detailed documentation
build()
Constructs a conditional format rule from the settings applied to the builder.
Return
Conditional — A representation of the conditional format rule.
copy()
Returns a rule builder preset with this rule's settings.
Return
Conditional — A builder based on this rule's settings.
getBooleanCondition()
Retrieves the rule'sBoolean information if this rule usesboolean condition criteria. Otherwise returnsnull.
// Log the boolean criteria type of the first conditional format rules of a// sheet.construle=SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];constbooleanCondition=rule.getBooleanCondition();if(booleanCondition!=null){Logger.log(booleanCondition.getCriteriaType());}
Return
Boolean — The boolean condition object, ornull if the rule does not use a boolean condition.
getGradientCondition()
Retrieves the rule'sGradient information, if this ruleuses gradient condition criteria. Otherwise returnsnull.
// Log the gradient minimum color of the first conditional format rule of a// sheet.construle=SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];constgradientCondition=rule.getGradientCondition();if(gradientCondition!=null){// Assume the color has ColorType.RGB.Logger.log(gradientCondition.getMinColorObject().asRgbColor().asHexString());}
Return
Gradient — The gradient condition object, ornull 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.construle=SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];constranges=rule.getRanges();for(leti=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 innullremoves 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The desired color ornull to clear. |
Return
Conditional — The builder, for chaining.
setBackgroundObject(color)
Sets the background color for the conditional format rule's format. Passing innullremoves 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');constcolor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build();construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setBackground(color).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The desired color object ornull to clear. |
Return
Conditional — The builder, for chaining.
setBold(bold)
Sets text bolding for the conditional format rule's format. Ifbold istrue,the rule bolds text if the condition is met; iffalse, the rule removes any existingbolding if the condition is met. Passing innull removes the bold format setting fromthe 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setBold(true).setRanges([range]).build();construles=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
Conditional — The builder, for chaining.
setFontColor(color)
Sets the font color for the conditional format rule's format. Passing innull removesthe 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setFontColor('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The desired color ornull to clear. |
Return
Conditional — The builder, for chaining.
setFontColorObject(color)
Sets the font color for the conditional format rule's format. Passing innull removesthe 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');constcolor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.TEXT).build();construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setFontColor(color).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The desired color object ornull to clear. |
Return
Conditional — The builder, for chaining.
setGradientMaxpoint(color)
Clears the conditional format rule's gradient maxpoint value, and instead uses the maximumvalue 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpoint('#FF0000').setGradientMinpoint('#FFFFFF').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The maxpoint color to set. |
Return
Conditional — The builder, for chaining.
setGradientMaxpointObject(color)
Clears the conditional format rule's gradient maxpoint value, and instead uses the maximumvalue 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');consttextColor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.TEXT).build();constbackgroundColor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build();construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpoint(textColor).setGradientMinpoint(backgroundColor).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The maxpoint color object to set. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');constcolor1=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1).build();constcolor2=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2).build();constcolor3=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3).build();construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue(color1,SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue(color2,SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue(color3,SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The maxpoint color to set. |
type | Interpolation | The maxpoint interpolation type to set. |
value | String | The maxpoint value to set. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue('#0000FF',SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue('#00FF00',SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue('#FF0000',SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The maxpoint color to set. |
type | Interpolation | The maxpoint interpolation type to set. |
value | String | The maxpoint value to set. |
Return
Conditional — The builder, for chaining.
setGradientMidpointObjectWithValue(color, type, value)
Sets the conditional format rule's gradient midpoint fields. Clears all of the midpoint fieldsif the passed in interpolation type isnull.
// 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');constcolor1=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1).build();constcolor2=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2).build();constcolor3=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3).build();construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue(color1,SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue(color2,SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue(color3,SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The midpoint color to set. |
type | Interpolation | The midpoint interpolation type to set ornull to clear. |
value | String | The midpoint value to set. |
Return
Conditional — The builder, for chaining.
setGradientMidpointWithValue(color, type, value)
Sets the conditional format rule's gradient midpoint fields. Clears all of the midpoint fieldsif the passed in interpolation type isnull.
// 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue('#0000FF',SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue('#00FF00',SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue('#FF0000',SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The midpoint color to set. |
type | Interpolation | The midpoint interpolation type to set ornull to clear. |
value | String | The midpoint value to set. |
Return
Conditional — The builder, for chaining.
setGradientMinpoint(color)
Clears the conditional format rule's gradient minpoint value, and instead uses the minimumvalue 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpoint('#FF0000').setGradientMinpoint('#FFFFFF').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The minpoint color to set. |
Return
Conditional — The builder, for chaining.
setGradientMinpointObject(color)
Clears the conditional format rule's gradient minpoint value, and instead uses the minimumvalue 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');consttextColor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.TEXT).build();constbackgroundColor=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build();construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpoint(textColor).setGradientMinpoint(backgroundColor).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The minpoint color object to set. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');constcolor1=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1).build();constcolor2=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2).build();constcolor3=SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3).build();construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue(color1,SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue(color2,SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue(color3,SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | Color | The minpoint color to set. |
type | Interpolation | The minpoint interpolation type to set. |
value | String | The minpoint value to set. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().setGradientMaxpointWithValue('#0000FF',SpreadsheetApp.InterpolationType.NUMBER,'100',).setGradientMidpointWithValue('#00FF00',SpreadsheetApp.InterpolationType.NUMBER,'50',).setGradientMinpointWithValue('#FF0000',SpreadsheetApp.InterpolationType.NUMBER,'0',).setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
color | String | The minpoint color to set. |
type | Interpolation | The minpoint interpolation type to set. |
value | String | The minpoint value to set. |
Return
Conditional — The builder, for chaining.
setItalic(italic)
Sets text italics for the conditional format rule's format. Ifitalic istrue,the rule italicises text if the condition is met; iffalse, the rule removes anyexisting italicization if the condition is met. Passing innull removes the italicformat 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setItalic(true).setRanges([range]).build();construles=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
Conditional — The builder, for chaining.
setRanges(ranges)
Sets one or more ranges to which this conditional format rule is applied. This operationreplaces any existing ranges. Setting an empty array clears any existing ranges. A rule musthave 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.constsheet=SpreadsheetApp.getActiveSheet();constrangeOne=sheet.getRange('A1:B3');constrangeTwo=sheet.getRange('D4:F6');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberBetween(1,10).setBackground('#FF0000').setRanges([rangeOne,rangeTwo]).build();construles=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
Conditional — The builder, for chaining.
setStrikethrough(strikethrough)
Sets text strikethrough for the conditional format rule's format. Ifstrikethrough istrue, the rule strikesthrough text if the condition is met; iffalse, the ruleremoves any existing strikethrough formatting if the condition is met. Passing innullremoves 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setStrikethrough(true).setRanges([range]).build();construles=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
Conditional — The builder, for chaining.
setUnderline(underline)
Sets text underlining for the conditional format rule's format. Ifunderline istrue, the rule underlines text if the condition is met; iffalse, the rule removes anyexisting underlines if the condition is met. Passing innull removes the underlineformat 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setUnderline(true).setRanges([range]).build();construles=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
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenCellEmpty().setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenCellNotEmpty().setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateAfter(newDate('11/4/1993')).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The latest date. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateAfter(SpreadsheetApp.RelativeDate.TODAY).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Relative | The latest date relative to the date type selected. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateBefore(newDate('11/4/1993')).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The earliest unacceptable date. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateBefore(SpreadsheetApp.RelativeDate.TODAY).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Relative | The latest date relative to the date type selected. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateEqualTo(newDate('11/4/1993')).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The sole acceptable date. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenDateEqualTo(SpreadsheetApp.RelativeDate.TODAY).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
date | Relative | The latest date relative to the date type selected. |
Return
Conditional — The builder, for chaining.
whenFormulaSatisfied(formula)
Sets the conditional format rule to trigger when that the given formula evaluates totrue.
// 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)".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=EQ(B4, C3)').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
formula | String | A custom formula that evaluates totrue if the input is valid. |
Return
Conditional — The builder, for chaining.
whenNumberBetween(start, end)
Sets the conditional format rule to trigger when a number falls between, or is either of, twospecified 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberBetween(1,10).setBackground('#FF0000').setRanges([range]).build();construles=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
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberEqualTo(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The sole acceptable value. |
Return
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThan(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The highest unacceptable value. |
Return
Conditional — The builder, for chaining.
whenNumberGreaterThanOrEqualTo(number)
Sets the conditional format rule to trigger when a number is greater than or equal to the givenvalue.
// 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThanOrEqualTo(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The lowest acceptable value. |
Return
Conditional — The builder, for chaining.
whenNumberLessThan(number)
Sets the conditional conditional format rule to trigger when a number less than the givenvalue.
// 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The lowest unacceptable value. |
Return
Conditional — The builder, for chaining.
whenNumberLessThanOrEqualTo(number)
Sets the conditional format rule to trigger when a number less than or equal to the givenvalue.
// 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberLessThanOrEqualTo(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The highest acceptable value. |
Return
Conditional — The builder, for chaining.
whenNumberNotBetween(start, end)
Sets the conditional format rule to trigger when a number does not fall between, and is neitherof, 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberNotBetween(1,10).setBackground('#FF0000').setRanges([range]).build();construles=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
Conditional — 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.constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenNumberNotEqualTo(10).setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The sole unacceptable value. |
Return
Conditional — 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextContains('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The value that the input must contain. |
Return
Conditional — The builder, for chaining.
whenTextDoesNotContain(text)
Sets the conditional format rule to trigger when that the input does not contain the givenvalue.
// 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextDoesNotContain('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The value that the input must not contain. |
Return
Conditional — 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEndsWith('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | Text to compare against the end of the string. |
Return
Conditional — 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The sole acceptable value. |
Return
Conditional — 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".constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange('A1:B3');construle=SpreadsheetApp.newConditionalFormatRule().whenTextStartsWith('hello').setBackground('#FF0000').setRanges([range]).build();construles=sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | Text to compare against the beginning of the string. |
Return
Conditional — The builder, for chaining.
withCriteria(criteria, args)
Sets the conditional format rule to criteria defined byBoolean values,typically taken from thecriteria andarguments of anexisting 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.constsheet=SpreadsheetApp.getActiveSheet();construles=sheet.getConditionalFormatRules();constbooleanCondition=rules[0].getBooleanCondition();if(booleanCondition!=null){construle=SpreadsheetApp.newConditionalFormatRule().withCriteria(booleanCondition.getCriteriaType(),booleanCondition.getCriteriaValues(),).setBackground('#000000').setRanges(rules[0].getRanges()).build();rules.push(rule);}sheet.setConditionalFormatRules(rules);
Parameters
| Name | Type | Description |
|---|---|---|
criteria | Boolean | 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 correspondingwhen...() method above. |
Return
Conditional — The builder, for chaining.
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-11 UTC.