Class ConditionalFormatRuleBuilder

条件格式规则构建器

用于条件格式规则的构建器。

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

方法

方法	返回类型	简介
build()	ConditionalFormatRule	根据应用于构建器的设置构造条件格式规则。
copy()	ConditionalFormatRuleBuilder	返回包含相应规则设置的规则构建器预设。
getBooleanCondition()	BooleanCondition	如果相应规则使用布尔值条件标准，则检索该规则的 BooleanCondition 信息。
getGradientCondition()	GradientCondition	如果相应规则使用梯度条件标准，则检索该规则的 GradientCondition 信息。
getRanges()	Range[]	检索应用了相应条件格式规则的范围。
setBackground(color)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置背景颜色。
setBackgroundObject(color)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置背景颜色。
setBold(bold)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置文本粗体。
setFontColor(color)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置字体颜色。
setFontColorObject(color)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置字体颜色。
setGradientMaxpoint(color)	ConditionalFormatRuleBuilder	清除条件格式规则的梯度最大点值，改为使用规则范围内的最大值。
setGradientMaxpointObject(color)	ConditionalFormatRuleBuilder	清除条件格式规则的梯度最大点值，改为使用规则范围内的最大值。
setGradientMaxpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度最大点字段。
setGradientMaxpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度最大点字段。
setGradientMidpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度中点字段。
setGradientMidpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度中点字段。
setGradientMinpoint(color)	ConditionalFormatRuleBuilder	清除条件格式规则的梯度最小值点值，改为使用规则范围内的最小值。
setGradientMinpointObject(color)	ConditionalFormatRuleBuilder	清除条件格式规则的梯度最小值点值，改为使用规则范围内的最小值。
setGradientMinpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度最小值点字段。
setGradientMinpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的梯度最小值点字段。
setItalic(italic)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置文本斜体。
setRanges(ranges)	ConditionalFormatRuleBuilder	设置应用相应条件格式规则的一个或多个范围。
setStrikethrough(strikethrough)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置删除线。
setUnderline(underline)	ConditionalFormatRuleBuilder	为条件格式规则的格式设置文本下划线。
whenCellEmpty()	ConditionalFormatRuleBuilder	设置在单元格为空时触发的条件格式规则。
whenCellNotEmpty()	ConditionalFormatRuleBuilder	设置条件格式规则，以在单元格不为空时触发。
whenDateAfter(date)	ConditionalFormatRuleBuilder	设置条件格式规则，以在日期晚于指定值时触发。
whenDateAfter(date)	ConditionalFormatRuleBuilder	设置条件格式规则，以在日期晚于给定的相对日期时触发。
whenDateBefore(date)	ConditionalFormatRuleBuilder	设置条件格式规则，以在日期早于指定日期时触发。
whenDateBefore(date)	ConditionalFormatRuleBuilder	将条件格式规则设置为在日期早于指定相对日期时触发。
whenDateEqualTo(date)	ConditionalFormatRuleBuilder	将条件格式规则设置为在日期等于指定日期时触发。
whenDateEqualTo(date)	ConditionalFormatRuleBuilder	将条件格式规则设置为在日期等于指定相对日期时触发。
whenFormulaSatisfied(formula)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在给定公式的计算结果为 true 时触发该规则。
whenNumberBetween(start, end)	ConditionalFormatRuleBuilder	将条件格式规则设置为在数字介于两个指定值之间或等于其中一个指定值时触发。
whenNumberEqualTo(number)	ConditionalFormatRuleBuilder	将条件格式规则设置为在数字等于给定值时触发。
whenNumberGreaterThan(number)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在数字大于指定值时触发该规则。
whenNumberGreaterThanOrEqualTo(number)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在数字大于或等于指定值时触发该规则。
whenNumberLessThan(number)	ConditionalFormatRuleBuilder	设置条件格式规则，当数字小于给定值时触发。
whenNumberLessThanOrEqualTo(number)	ConditionalFormatRuleBuilder	将条件格式规则设置为在数字小于或等于给定值时触发。
whenNumberNotBetween(start, end)	ConditionalFormatRuleBuilder	设置条件格式规则，以在数字不介于两个指定值之间且不等于这两个指定值时触发该规则。
whenNumberNotEqualTo(number)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在数字不等于指定值时触发该规则。
whenTextContains(text)	ConditionalFormatRuleBuilder	设置条件格式规则，以在输入包含指定值时触发。
whenTextDoesNotContain(text)	ConditionalFormatRuleBuilder	将条件格式规则设置为在输入不包含给定值时触发。
whenTextEndsWith(text)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在输入内容以指定值结尾时触发该规则。
whenTextEqualTo(text)	ConditionalFormatRuleBuilder	设置条件格式规则，以便在输入等于给定值时触发该规则。
whenTextStartsWith(text)	ConditionalFormatRuleBuilder	将条件格式规则设置为在输入内容以指定值开头时触发。
withCriteria(criteria, args)	ConditionalFormatRuleBuilder	将条件格式规则设置为由 BooleanCriteria 值（通常取自现有规则的 criteria 和 arguments）定义的条件。

详细文档

build()

根据应用于构建器的设置构造条件格式规则。

返回

ConditionalFormatRule - 条件格式规则的表示形式。

---

copy()

返回包含相应规则设置的规则构建器预设。

返回

ConditionalFormatRuleBuilder - 基于相应规则设置的构建器。

---

getBooleanCondition()

如果相应规则使用布尔值条件标准，则检索该规则的 BooleanCondition 信息。否则返回 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());
}

返回

BooleanCondition - 布尔值条件对象；如果规则不使用布尔值条件，则为 null。

---

getGradientCondition()

如果相应规则使用梯度条件标准，则检索该规则的 GradientCondition 信息。否则返回 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());
}

返回

GradientCondition - 梯度条件对象，如果规则不使用梯度条件，则为 null。

---

getRanges()

检索应用了相应条件格式规则的范围。

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

返回

Range[] - 应用相应条件格式规则的范围。

---

setBackground(color)

为条件格式规则的格式设置背景颜色。传入 null 会从规则中移除背景颜色格式设置。

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

参数

名称	类型	说明
color	String	所需的颜色或 null（用于清除）。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setBackgroundObject(color)

为条件格式规则的格式设置背景颜色。传入 null 会从规则中移除背景颜色格式设置。

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

参数

名称	类型	说明
color	Color	所需颜色对象或 null（用于清除）。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setBold(bold)

为条件格式规则的格式设置文本粗体。如果 bold 为 true，则当满足条件时，规则会将文本加粗；如果为 false，则当满足条件时，规则会移除任何现有的加粗效果。传入 null 会从规则中移除粗体格式设置。

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

参数

名称	类型	说明
bold	Boolean	如果满足格式条件，文本是否应加粗；null 会移除此设置。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setFontColor(color)

为条件格式规则的格式设置字体颜色。传入 null 会从规则中移除字体颜色格式设置。

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

参数

名称	类型	说明
color	String	所需的颜色或 null（用于清除）。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setFontColorObject(color)

为条件格式规则的格式设置字体颜色。传入 null 会从规则中移除字体颜色格式设置。

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

参数

名称	类型	说明
color	Color	所需颜色对象或 null（用于清除）。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpoint(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);

参数

名称	类型	说明
color	String	要设置的最大点颜色。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpointObject(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);

参数

名称	类型	说明
color	Color	要设置的最大点颜色对象。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpointObjectWithValue(color, type, value)

设置条件格式规则的梯度最大点字段。

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

参数

名称	类型	说明
color	Color	要设置的最大点颜色。
type	InterpolationType	要设置的最大点插值类型。
value	String	要设置的最大点数值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpointWithValue(color, type, value)

设置条件格式规则的梯度最大点字段。

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

参数

名称	类型	说明
color	String	要设置的最大点颜色。
type	InterpolationType	要设置的最大点插值类型。
value	String	要设置的最大点数值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMidpointObjectWithValue(color, type, value)

设置条件格式规则的梯度中点字段。如果传入的插值类型为 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);

参数

名称	类型	说明
color	Color	要设置的中间点颜色。
type	InterpolationType	要设置的中点插值类型，或 null 表示清除。
value	String	要设置的中点值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMidpointWithValue(color, type, value)

设置条件格式规则的梯度中点字段。如果传入的插值类型为 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);

参数

名称	类型	说明
color	String	要设置的中间点颜色。
type	InterpolationType	要设置的中点插值类型，或 null 表示清除。
value	String	要设置的中点值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMinpoint(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);

参数

名称	类型	说明
color	String	要设置的最小点颜色。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMinpointObject(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);

参数

名称	类型	说明
color	Color	要设置的最小点颜色对象。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMinpointObjectWithValue(color, type, value)

设置条件格式规则的梯度最小值点字段。

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

参数

名称	类型	说明
color	Color	要设置的最小点颜色。
type	InterpolationType	要设置的 minpoint 插值类型。
value	String	要设置的 minpoint 值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMinpointWithValue(color, type, value)

设置条件格式规则的梯度最小值点字段。

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

参数

名称	类型	说明
color	String	要设置的最小点颜色。
type	InterpolationType	要设置的 minpoint 插值类型。
value	String	要设置的 minpoint 值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setItalic(italic)

为条件格式规则的格式设置文本斜体。如果 italic 为 true，则当满足条件时，规则会将文本设为斜体；如果为 false，则当满足条件时，规则会移除任何现有的斜体格式。传入 null 会从规则中移除斜体格式设置。

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

参数

名称	类型	说明
italic	Boolean	如果满足格式条件，文本是否应采用斜体；null 会移除此设置。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setRanges(ranges)

设置应用相应条件格式规则的一个或多个范围。此操作会替换所有现有范围。设置空数组会清除所有现有范围。规则必须至少包含一个范围。

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

参数

名称	类型	说明
ranges	Range[]	应用相应条件格式规则的范围。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setStrikethrough(strikethrough)

为条件格式规则的格式设置删除线。如果值为 true，则在满足条件时，规则会为文本添加删除线；如果值为 false，则在满足条件时，规则会移除任何现有的删除线格式。strikethrough传入 null 会从规则中移除删除线格式设置。

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

参数

名称	类型	说明
strikethrough	Boolean	如果满足格式条件，文本是否应添加删除线；null 会移除此设置。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setUnderline(underline)

为条件格式规则的格式设置文本下划线。如果 underline 为 true，则当满足条件时，规则会为文本添加下划线；如果为 false，则当满足条件时，规则会移除所有现有的下划线。传入 null 会从规则中移除下划线格式设置。

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

参数

名称	类型	说明
underline	Boolean	如果满足格式条件，文本是否应加下划线；null 会移除此设置。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenCellEmpty()

设置条件格式规则，以便在单元格为空时触发该规则。

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

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenCellNotEmpty()

设置条件格式规则，以在单元格不为空时触发。

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

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateAfter(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 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);

参数

名称	类型	说明
date	Date	最新日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateAfter(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);

参数

名称	类型	说明
date	RelativeDate	相对于所选日期类型的最新日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateBefore(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);

参数

名称	类型	说明
date	Date	最早的不可接受日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateBefore(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);

参数

名称	类型	说明
date	RelativeDate	相对于所选日期类型的最新日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateEqualTo(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);

参数

名称	类型	说明
date	Date	唯一可接受的日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenDateEqualTo(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);

参数

名称	类型	说明
date	RelativeDate	相对于所选日期类型的最新日期。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenFormulaSatisfied(formula)

设置条件格式规则，以便在给定公式的计算结果为 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);

参数

名称	类型	说明
formula	String	一种自定义公式，如果输入有效，则计算结果为 true。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberBetween(start, end)

将条件格式规则设置为在数字介于两个指定值之间或等于其中一个指定值时触发。

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

参数

名称	类型	说明
start	Number	可接受的最低值。
end	Number	可接受的最高值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberEqualTo(number)

将条件格式规则设置为在数字等于给定值时触发。

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

参数

名称	类型	说明
number	Number	唯一可接受的值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberGreaterThan(number)

设置条件格式规则，以便在数字大于指定值时触发该规则。

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

参数

名称	类型	说明
number	Number	最高不接受值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberGreaterThanOrEqualTo(number)

设置条件格式规则，以便在数字大于或等于指定值时触发该规则。

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

参数

名称	类型	说明
number	Number	可接受的最低值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberLessThan(number)

设置条件格式规则，当数字小于给定值时触发该规则。

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

参数

名称	类型	说明
number	Number	最低不可接受值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberLessThanOrEqualTo(number)

将条件格式规则设置为在数字小于或等于给定值时触发。

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

参数

名称	类型	说明
number	Number	可接受的最高值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberNotBetween(start, end)

将条件格式规则设置为在数字不介于两个指定值之间且不等于这两个指定值时触发。

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

参数

名称	类型	说明
start	Number	最低不可接受值。
end	Number	最高不接受值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenNumberNotEqualTo(number)

设置条件格式规则，以便在数字不等于指定值时触发该规则。

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

参数

名称	类型	说明
number	Number	唯一不接受的值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenTextContains(text)

将条件格式规则设置为在输入包含指定值时触发。

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

参数

名称	类型	说明
text	String	输入必须包含的值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenTextDoesNotContain(text)

将条件格式规则设置为在输入不包含给定值时触发。

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

参数

名称	类型	说明
text	String	输入不得包含的值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenTextEndsWith(text)

设置条件格式规则，以便在输入内容以指定值结尾时触发该规则。

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

参数

名称	类型	说明
text	String	要与字符串末尾进行比较的文本。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenTextEqualTo(text)

设置条件格式规则，以在输入等于给定值时触发。

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

参数

名称	类型	说明
text	String	唯一可接受的值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

whenTextStartsWith(text)

将条件格式规则设置为在输入内容以指定值开头时触发。

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

参数

名称	类型	说明
text	String	要与字符串开头进行比较的文本。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

withCriteria(criteria, args)

将条件格式规则设置为由 BooleanCriteria 值（通常取自现有规则的 criteria 和 arguments）定义的条件。

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

参数

名称	类型	说明
criteria	BooleanCriteria	条件格式条件的类型。
args	Object[]	与条件类型相对应的实参数组；实参的数量及其类型与上述相应的 when...() 方法相匹配。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。