Class ConditionalFormatRuleBuilder

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

方法

方法返回类型简介
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 值定义的条件,通常取自现有规则的 criteriaarguments

详细文档

build()

通过应用于构建器的设置构建条件格式规则。

弃踢回攻

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


copy()

返回采用此规则设置的规则生成器预设。

弃踢回攻

ConditionalFormatRuleBuilder - 基于此规则设置的构建器


getBooleanCondition()

如果此规则使用布尔值条件条件,则检索规则的 BooleanCondition 信息。否则,返回 null

// Log the boolean criteria type of the first conditional format rules of a sheet.
var rule = SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];
var booleanCondition = rule.getBooleanCondition();
if (booleanCondition != null) {
  Logger.log(booleanCondition.getCriteriaType());
}

弃踢回攻

BooleanCondition - 布尔条件对象,如果规则不使用布尔条件,则为 null


getGradientCondition()

如果此规则使用梯度条件,则检索规则的 GradientCondition 信息。否则,返回 null

// Log the gradient minimum color of the first conditional format rule of a sheet.
var rule = SpreadsheetApp.getActiveSheet().getConditionalFormatRules()[0];
var gradientCondition = rule.getGradientCondition();
if (gradientCondition != null) {
  // Assume the color has ColorType.RGB.
  Logger.log(gradientCondition.getMinColorObject().asRgbColor().asHexString());
}

弃踢回攻

GradientCondition - 渐变条件对象;如果规则未使用渐变条件,则为 null


getRanges()

检索要应用此条件格式规则的范围。

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

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString所需的颜色或要清除的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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground(color)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要清除的所需颜色对象或 null

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器。


setBold(bold)

为条件格式规则的格式设置文本粗体。如果 boldtrue,则在满足条件时,规则会以粗体显示文本;如果为 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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBold(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
boldBoolean在满足格式条件时是否应将文本加粗;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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setFontColor("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString所需的颜色或要清除的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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setFontColor(color)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要清除的所需颜色对象或 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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint("#FF0000")
    .setGradientMinpoint("#FFFFFF")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString要设置的最大值颜色。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var textColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var backgroundColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint(textColor)
    .setGradientMinpoint(backgroundColor)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要设置的 maxpoint 颜色对象。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要设置的最大值颜色。
typeInterpolationType要设置的最大点插值类型。
valueString要设置的最大值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString要设置的最大值颜色。
typeInterpolationType要设置的最大点插值类型。
valueString要设置的最大值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要设置的中点颜色。
typeInterpolationType要设置的中点插值类型或要清除的 null
valueString要设置的中点值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString要设置的中点颜色。
typeInterpolationType要设置的中点插值类型或要清除的 null
valueString要设置的中点值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint("#FF0000")
    .setGradientMinpoint("#FFFFFF")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var textColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.TEXT)
    .build();
var backgroundColor = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint(textColor)
    .setGradientMinpoint(backgroundColor)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var color1 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT1)
    .build();
var color2 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT2)
    .build();
var color3 = SpreadsheetApp.newColor()
    .setThemeColor(SpreadsheetApp.ThemeColorType.ACCENT3)
    .build();
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue(color1, SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue(color2, SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue(color3, SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorColor要设置的最小点颜色。
typeInterpolationType要设置的最小插值类型。
valueString要设置的最小分值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpointWithValue("#0000FF", SpreadsheetApp.InterpolationType.NUMBER, "100")
    .setGradientMidpointWithValue("#00FF00", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#FF0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
colorString要设置的最小点颜色。
typeInterpolationType要设置的最小插值类型。
valueString要设置的最小分值。

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器


setItalic(italic)

为条件格式规则的格式设置文本斜体。如果 italictrue,在满足条件时,规则会将文本设为斜体;如果为 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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setItalic(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
italicBoolean在满足格式条件时,是否应将文本设为斜体;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.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeOne = sheet.getRange("A1:B3");
var rangeTwo = sheet.getRange("D4:F6");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([rangeOne, rangeTwo])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器


setStrikethrough(strikethrough)

为条件格式规则的格式设置文本删除线。如果 strikethroughtrue,在满足条件时,规则会删除文本;如果为 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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setStrikethrough(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
strikethroughBoolean在满足格式条件时,是否应为文本添加删除线;null 会移除此设置。

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器


setUnderline(underline)

为条件格式规则的格式设置文本下划线。如果 underlinetrue,则在满足条件时,规则会为文本添加下划线;如果为 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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setUnderline(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
underlineBoolean在满足格式条件时,是否应为文本添加下划线;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.
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellEmpty()
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

弃踢回攻

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateAfter(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
dateDate最晚日期。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateAfter(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
dateRelativeDate与所选日期类型相关的最晚日期。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateBefore(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
dateDate不接受的最早日期。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateBefore(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
dateRelativeDate与所选日期类型相关的最晚日期。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateEqualTo(new Date("11/4/1993"))
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateEqualTo(SpreadsheetApp.RelativeDate.TODAY)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
dateRelativeDate与所选日期类型相关的最晚日期。

弃踢回攻

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)".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("=EQ(B4, C3)")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
formulaString在输入有效时计算结果为 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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
startNumber可接受的最低值。
endNumber可接受的最高值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
numberNumber不可接受的最高值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThanOrEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
numberNumber不可接受的最低值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThanOrEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberNotBetween(1, 10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
startNumber不可接受的最低值。
endNumber不可接受的最高值。

弃踢回攻

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.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberNotEqualTo(10)
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextContains("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextDoesNotContain("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
textString输入内容不得包含的值。

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEndsWith("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

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".

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextStartsWith("hello")
    .setBackground("#FF0000")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

参数

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

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器


withCriteria(criteria, args)

将条件格式规则设置为由 BooleanCriteria 值定义的条件,通常取自现有规则的 criteriaarguments

// Adds a new conditional format rule that is a copy of the first active
// conditional format rule, except it instead sets its cells to have a black
// background color.

var sheet = SpreadsheetApp.getActiveSheet();
var rules = sheet.getConditionalFormatRules();
var booleanCondition = rules[0].getBooleanCondition();
if (booleanCondition != null) {
  var rule = SpreadsheetApp.newConditionalFormatRule()
      .withCriteria(booleanCondition.getCriteriaType(),
         booleanCondition.getCriteriaValues())
      .setBackground("#000000")
      .setRanges(rule.getRanges())
      .build();
  rules.push(rule);
}
sheet.setConditionalFormatRules(rules);

参数

名称类型说明
criteriaBooleanCriteria条件格式条件的类型。
argsObject[]适合条件类型的参数数组;参数数量和类型与上述相应的 when...() 方法一致。

弃踢回攻

ConditionalFormatRuleBuilder - 用于链接的构建器