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 值定义的条件， 通常来自 criteria 和 arguments 现有规则

详细文档

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
color	String	要设置的 maxpoint 颜色。

返回

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.

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

参数

名称	类型	说明
color	Color	要设置的 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);

参数

名称	类型	说明
color	Color	要设置的 maxpoint 颜色。
type	InterpolationType	要设置的 maxpoint 插值类型。
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.

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

参数

名称	类型	说明
color	String	要设置的 maxpoint 颜色。
type	InterpolationType	要设置的 maxpoint 插值类型。
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

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

返回

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

参数

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

返回

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

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

参数

名称	类型	说明
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.
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);

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
strikethrough	Boolean	格式条件为 时，是否应给文本加删除线 met;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".

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

参数

名称	类型	说明
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.
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);

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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)".

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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.

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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".

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

参数

名称	类型	说明
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.

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

参数

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

返回

ConditionalFormatRuleBuilder - 用于链接的构建器