Class ConditionalFormatRuleBuilder

// 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()); }

// 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()); }

// 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()); }

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);

// 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);