條件式格式規則的建構工具。
// 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)
設定條件式格式規則的漸層 maxpoint 欄位。
// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their // background color somewhere from theme accent 1, accent 2 to accent 3 colors, based on their // values in comparison to the values 0, 50, and 100. 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)
設定條件式格式規則的漸層 maxpoint 欄位。
// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to set their // background color somewhere from red green to blue, based on their values in comparison to // the values 0, 50, and 100. 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
:建構工具,用於鏈結