Class ConditionalFormatRuleBuilder

    Conditional Format Rule Builder

    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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberBetween 
 ( 
 1 
 , 
  
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Methods

    Method Return type Brief description
    build()
    		Conditional Format Rule Constructs a conditional format rule from the settings applied to the builder.
    copy()
    		Conditional Format Rule Builder Returns a rule builder preset with this rule's settings.
    get Boolean Condition()
    		Boolean Condition Retrieves the rule's Boolean Condition information if this rule uses boolean condition criteria.
    get Gradient Condition()
    		Gradient Condition Retrieves the rule's Gradient Condition information, if this rule uses gradient condition criteria.
    get Ranges()
    		Range[] Retrieves the ranges to which this conditional format rule is applied.
    set Background(color)
    		Conditional Format Rule Builder Sets the background color for the conditional format rule's format.
    set Background Object(color)
    		Conditional Format Rule Builder Sets the background color for the conditional format rule's format.
    set Bold(bold)
    		Conditional Format Rule Builder Sets text bolding for the conditional format rule's format.
    set Font Color(color)
    		Conditional Format Rule Builder Sets the font color for the conditional format rule's format.
    set Font Color Object(color)
    		Conditional Format Rule Builder Sets the font color for the conditional format rule's format.
    set Gradient Maxpoint(color)
    		Conditional Format Rule Builder Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges.
    set Gradient Maxpoint Object(color)
    		Conditional Format Rule Builder Clears the conditional format rule's gradient maxpoint value, and instead uses the maximum value in the rule's ranges.
    set Gradient Maxpoint Object With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient maxpoint fields.
    set Gradient Maxpoint With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient maxpoint fields.
    set Gradient Midpoint Object With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient midpoint fields.
    set Gradient Midpoint With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient midpoint fields.
    set Gradient Minpoint(color)
    		Conditional Format Rule Builder Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges.
    set Gradient Minpoint Object(color)
    		Conditional Format Rule Builder Clears the conditional format rule's gradient minpoint value, and instead uses the minimum value in the rule's ranges.
    set Gradient Minpoint Object With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient minpoint fields.
    set Gradient Minpoint With Value(color, type, value)
    		Conditional Format Rule Builder Sets the conditional format rule's gradient minpoint fields.
    set Italic(italic)
    		Conditional Format Rule Builder Sets text italics for the conditional format rule's format.
    set Ranges(ranges)
    		Conditional Format Rule Builder Sets one or more ranges to which this conditional format rule is applied.
    set Strikethrough(strikethrough)
    		Conditional Format Rule Builder Sets text strikethrough for the conditional format rule's format.
    set Underline(underline)
    		Conditional Format Rule Builder Sets text underlining for the conditional format rule's format.
    when Cell Empty()
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when the cell is empty.
    when Cell Not Empty()
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when the cell is not empty.
    when Date After(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is after the given value.
    when Date After(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is after the given relative date.
    when Date Before(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is before the given date.
    when Date Before(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is before the given relative date.
    when Date Equal To(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is equal to the given date.
    when Date Equal To(date)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a date is equal to the given relative date.
    when Formula Satisfied(formula)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the given formula evaluates to true .
    when Number Between(start, end)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number falls between, or is either of, two specified values.
    when Number Equal To(number)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number is equal to the given value.
    when Number Greater Than(number)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number is greater than the given value.
    when Number Greater Than Or Equal To(number)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number is greater than or equal to the given value.
    when Number Less Than(number)
    		Conditional Format Rule Builder Sets the conditional conditional format rule to trigger when a number less than the given value.
    when Number Less Than Or Equal To(number)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number less than or equal to the given value.
    when Number Not Between(start, end)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values.
    when Number Not Equal To(number)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when a number is not equal to the given value.
    when Text Contains(text)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the input contains the given value.
    when Text Does Not Contain(text)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the input does not contain the given value.
    when Text Ends With(text)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the input ends with the given value.
    when Text Equal To(text)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the input is equal to the given value.
    when Text Starts With(text)
    		Conditional Format Rule Builder Sets the conditional format rule to trigger when that the input starts with the given value.
    with Criteria(criteria, args)
    		Conditional Format Rule Builder Sets the conditional format rule to criteria defined by Boolean Criteria 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

    Conditional Format Rule — A representation of the conditional format rule.

    copy()

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

    Return

    Conditional Format Rule Builder — A builder based on this rule's settings.

    get Boolean Condition()

    Retrieves the rule's Boolean Condition 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. 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (). 
 getConditionalFormatRules 
 ()[ 
 0 
 ]; 
 const 
  
 booleanCondition 
  
 = 
  
 rule 
 . 
 getBooleanCondition 
 (); 
 if 
  
 ( 
 booleanCondition 
  
 != 
  
 null 
 ) 
  
 { 
  
 Logger 
 . 
 log 
 ( 
 booleanCondition 
 . 
 getCriteriaType 
 ()); 
 }

    Return

    Boolean Condition — The boolean condition object, or null if the rule does not use a boolean condition.

    get Gradient Condition()

    Retrieves the rule's Gradient Condition 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. 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (). 
 getConditionalFormatRules 
 ()[ 
 0 
 ]; 
 const 
  
 gradientCondition 
  
 = 
  
 rule 
 . 
 getGradientCondition 
 (); 
 if 
  
 ( 
 gradientCondition 
  
 != 
  
 null 
 ) 
  
 { 
  
 // Assume the color has ColorType.RGB. 
  
 Logger 
 . 
 log 
 ( 
 gradientCondition 
 . 
 getMinColorObject 
 (). 
 asRgbColor 
 (). 
 asHexString 
 ()); 
 }

    Return

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

    get Ranges()

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

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

    Return

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

    set Background(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Background Object(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 color 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 BACKGROUND 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 color 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Bold(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setBold 
 ( 
 true 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    set Font Color(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setFontColor 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Font Color Object(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 color 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 TEXT 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setFontColor 
 ( 
 color 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Maxpoint(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpoint 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setGradientMinpoint 
 ( 
 '#FFFFFF' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    color
    		String The maxpoint color to set.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Maxpoint Object(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 textColor 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 TEXT 
 ) 
  
 . 
 build 
 (); 
 const 
  
 backgroundColor 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 BACKGROUND 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpoint 
 ( 
 textColor 
 ) 
  
 . 
 setGradientMinpoint 
 ( 
 backgroundColor 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Maxpoint Object With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 color1 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT1 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color2 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT2 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color3 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT3 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 color1 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 color2 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 color3 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Maxpoint With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 '#0000FF' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 '#00FF00' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 '#FF0000' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Midpoint Object With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 color1 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT1 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color2 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT2 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color3 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT3 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 color1 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 color2 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 color3 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Midpoint With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 '#0000FF' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 '#00FF00' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 '#FF0000' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Minpoint(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpoint 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setGradientMinpoint 
 ( 
 '#FFFFFF' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    color
    		String The minpoint color to set.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Minpoint Object(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 textColor 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 TEXT 
 ) 
  
 . 
 build 
 (); 
 const 
  
 backgroundColor 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 BACKGROUND 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpoint 
 ( 
 textColor 
 ) 
  
 . 
 setGradientMinpoint 
 ( 
 backgroundColor 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Minpoint Object With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 color1 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT1 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color2 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT2 
 ) 
  
 . 
 build 
 (); 
 const 
  
 color3 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 () 
  
 . 
 setThemeColor 
 ( 
 SpreadsheetApp 
 . 
 ThemeColorType 
 . 
 ACCENT3 
 ) 
  
 . 
 build 
 (); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 color1 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 color2 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 color3 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Gradient Minpoint With Value(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 setGradientMaxpointWithValue 
 ( 
  
 '#0000FF' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '100' 
 , 
  
 ) 
  
 . 
 setGradientMidpointWithValue 
 ( 
  
 '#00FF00' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '50' 
 , 
  
 ) 
  
 . 
 setGradientMinpointWithValue 
 ( 
  
 '#FF0000' 
 , 
  
 SpreadsheetApp 
 . 
 InterpolationType 
 . 
 NUMBER 
 , 
  
 '0' 
 , 
  
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    set Italic(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setItalic 
 ( 
 true 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    set Ranges(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 rangeOne 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rangeTwo 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'D4:F6' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberBetween 
 ( 
 1 
 , 
  
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 rangeOne 
 , 
  
 rangeTwo 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    set Strikethrough(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setStrikethrough 
 ( 
 true 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    set Underline(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setUnderline 
 ( 
 true 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    when Cell Empty()

    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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenCellEmpty 
 () 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Cell Not Empty()

    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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenCellNotEmpty 
 () 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date After(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateAfter 
 ( 
 new 
  
 Date 
 ( 
 '11/4/1993' 
 )) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Date The latest date.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date After(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateAfter 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 TODAY 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The latest date relative to the date type selected.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date Before(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateBefore 
 ( 
 new 
  
 Date 
 ( 
 '11/4/1993' 
 )) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Date The earliest unacceptable date.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date Before(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateBefore 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 TODAY 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The latest date relative to the date type selected.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateEqualTo 
 ( 
 new 
  
 Date 
 ( 
 '11/4/1993' 
 )) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Date The sole acceptable date.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Date Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenDateEqualTo 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 TODAY 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The latest date relative to the date type selected.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Formula Satisfied(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)". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenFormulaSatisfied 
 ( 
 '=EQ(B4, C3)' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Between(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberBetween 
 ( 
 1 
 , 
  
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberEqualTo 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The sole acceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Greater Than(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberGreaterThan 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The highest unacceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Greater Than Or Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberGreaterThanOrEqualTo 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The lowest acceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Less Than(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberLessThan 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The lowest unacceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Less Than Or Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberLessThanOrEqualTo 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The highest acceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Not Between(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberNotBetween 
 ( 
 1 
 , 
  
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    when Number Not Equal To(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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenNumberNotEqualTo 
 ( 
 10 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    number
    		Number The sole unacceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Text Contains(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextContains 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Text Does Not Contain(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextDoesNotContain 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

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

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Text Ends With(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEndsWith 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    when Text Equal To(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextEqualTo 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 rules 
 . 
 push 
 ( 
 rule 
 ); 
 sheet 
 . 
 setConditionalFormatRules 
 ( 
 rules 
 );

    Parameters

    Name Type Description
    text
    		String The sole acceptable value.

    Return

    Conditional Format Rule Builder — The builder, for chaining.

    when Text Starts With(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". 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 'A1:B3' 
 ); 
 const 
  
 rule 
  
 = 
  
 SpreadsheetApp 
 . 
 newConditionalFormatRule 
 () 
  
 . 
 whenTextStartsWith 
 ( 
 'hello' 
 ) 
  
 . 
 setBackground 
 ( 
 '#FF0000' 
 ) 
  
 . 
 setRanges 
 ([ 
 range 
 ]) 
  
 . 
 build 
 (); 
 const 
  
 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

    Conditional Format Rule Builder — The builder, for chaining.

    with Criteria(criteria, args)

    Sets the conditional format rule to criteria defined by Boolean Criteria 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. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 rules 
  
 = 
  
 sheet 
 . 
 getConditionalFormatRules 
 (); 
 const 
  
 booleanCondition 
  
 = 
  
 rules 
 [ 
 0 
 ]. 
 getBooleanCondition 
 (); 
 if 
  
 ( 
 booleanCondition 
  
 != 
  
 null 
 ) 
  
 { 
  
 const 
  
 rule 
  
 = 
  
 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 Criteria 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

    Conditional Format Rule Builder — The builder, for chaining.
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: