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

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