条件格式规则的构建器。
// 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()
copy()
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)
清除条件格式规则的梯度最大值值,改为使用规则范围内的最大值。此外,还将渐变的最大点颜色设置为输入颜色。
// 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
- 用于链接的构建器
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);
参数
名称 | 类型 | 说明 |
---|---|---|
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 | 要设置的最大值颜色。 |
type | InterpolationType | 要设置的最大点插值类型。 |
value | String | 要设置的最大值。 |
弃踢回攻
ConditionalFormatRuleBuilder
- 用于链接的构建器。
setGradientMaxpointWithValue(color, type, value)
设置条件格式规则的渐变最大值字段。
// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their // background color somewhere from red green to blue, based on their values in comparison to // the values 0, 50, and 100. 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
- 用于链接的构建器
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 | 在满足格式条件时,是否应为文本添加删除线;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
- 用于链接的构建器