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	清除条件格式规则的渐变 maxpoint 值，改为使用规则范围中的最大值。
setGradientMaxpointObject(color)	ConditionalFormatRuleBuilder	清除条件格式规则的渐变 maxpoint 值，改为使用规则范围中的最大值。
setGradientMaxpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变 maxpoint 字段。
setGradientMaxpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变 maxpoint 字段。
setGradientMidpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变中点字段。
setGradientMidpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变中点字段。
setGradientMinpoint(color)	ConditionalFormatRuleBuilder	清除条件格式规则的渐变 minpoint 值，改为使用规则范围中的最小值。
setGradientMinpointObject(color)	ConditionalFormatRuleBuilder	清除条件格式规则的渐变 minpoint 值，改为使用规则范围中的最小值。
setGradientMinpointObjectWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变 minpoint 字段。
setGradientMinpointWithValue(color, type, value)	ConditionalFormatRuleBuilder	设置条件格式规则的渐变 minpoint 字段。
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)

清除条件格式规则的渐变 maxpoint 值，改为使用规则范围中的最大值。此外，还将渐变的最大值颜色设置为输入颜色。

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

清除条件格式规则的渐变 maxpoint 值，改为使用规则范围中的最大值。此外，还将渐变的最大值颜色设置为输入颜色。

// 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	要设置的 maxpoint 颜色对象。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpointObjectWithValue(color, type, value)

设置条件格式规则的渐变 maxpoint 字段。

// 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	要设置的 maxpoint 插值类型。
value	String	要设置的 maxpoint 值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMaxpointWithValue(color, type, value)

设置条件格式规则的渐变 maxpoint 字段。

// 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	要设置的 maxpoint 插值类型。
value	String	要设置的 maxpoint 值。

返回

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)

清除条件格式规则的渐变 minpoint 值，改为使用规则范围中的最小值。此外，还将渐变色的最小点颜色设置为输入颜色。

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

清除条件格式规则的渐变 minpoint 值，改为使用规则范围中的最小值。此外，还将渐变色的最小点颜色设置为输入颜色。

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

设置条件格式规则的渐变 minpoint 字段。

// 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	要设置的最小点插值类型。
value	String	要设置的 minpoint 值。

返回

ConditionalFormatRuleBuilder - 用于链接的构建器。

---

setGradientMinpointWithValue(color, type, value)

设置条件格式规则的渐变 minpoint 字段。

// 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	要设置的最小点插值类型。
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)

设置条件格式规则格式的文本删除线。如果 strikethrough 为 true，则规则会在满足条件时为文本添加删除线；如果为 false，则规则会在满足条件时移除所有现有的删除线格式。传入 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 - 用于链式调用的构建器