通过条件格式,您可以设置单元格的格式,使其外观发生变化 根据单元格中包含的值或其他单元格中的值动态调整。 条件格式设置有多种可能的应用 使用:
- 突出显示超过特定阈值的单元格(例如,对 金额超过 2,000 美元的所有交易)。
- 设置单元格格式,使其颜色随单元格的值而变化(例如,将 随着超过 2,000 美元的金额增加,背景会越来越强烈)。
- 根据其他单元格的内容(例如, 突出显示“上架时间”房源的地址字段是 >90 个 天)。
您甚至可以根据单元格的值和其他单元格的值设置其格式。对于 例如,您可以根据单元格中的值相较于 范围的中值:
图 1. 用于突出显示高于或低于年龄中位数的值的格式。
在本例中,每行中的单元格都根据值的
与所有年龄的中位数值进行比较。age
符合以下条件的行
年龄高于中间值的文字带有红色文字,而低于中间值的文字则带有红色文字
背景。两行的 age
值与年龄中间值一致
(48),且这些单元格不会收到特殊格式。(对于
创建此条件格式,请参阅下面的示例。)
条件格式规则
条件格式使用格式设置规则来表示。每个电子表格 存储这些规则的列表,并按照它们显示的顺序应用它们, 。借助 Google Sheets API,您可以添加、更新和删除 格式规则
每条规则均应指定一个目标范围、规则类型以及触发 规则和要应用的任何格式。
目标范围 - 可以是单个单元格、一系列单元格,或多个 范围。
规则类型 - 规则分为两类:
评估的条件和您可以应用的格式如下: 各不相同,如以下部分所述。
布尔规则
BooleanRule
定义是否根据
BooleanCondition
求得的值为 true
或 false
。布尔规则采用以下形式:
{
"condition": {
object(BooleanCondition)
},
"format": {
object(CellFormat)
},
}
条件可以使用内置的
ConditionType
、
也可以使用自定义公式进行更复杂的求值。
借助内置类型,您可以根据数值阈值应用格式,
文本比较或是否填充了单元格。例如:NUMBER_GREATER
表示该单元格的值必须大于条件的值。规则是
始终针对目标单元格进行评估。
自定义公式是一种特殊的条件类型,可让您应用格式
任意表达式也允许对任何单元格求值,
而不仅仅是目标单元格。条件公式的计算结果必须为 true
。
要定义由布尔规则应用的格式,您可以使用
CellFormat
类型
以定义:
- 单元格中的文本是粗体、斜体还是带删除线。
- 单元格中的文本颜色。
- 单元格的背景颜色。
渐变规则
答
GradientRule
定义了与一系列值相对应的颜色范围。渐变规则
采用以下形式:
{
"minpoint": {
object(InterpolationPoint)
},
"midpoint": {
object(InterpolationPoint)
},
"maxpoint": {
object(InterpolationPoint)
},
}
每个
InterpolationPoint
定义了颜色及其对应的值。一组三个点可定义
颜色渐变。
管理条件格式规则
要创建、修改或删除条件格式规则,请使用
spreadsheets.batchUpdate
方法:
示例
以下示例展示了如何创建如 如本页面顶部的屏幕截图所示如需查看更多示例,请参阅 条件格式示例 页面。
Apps 脚本
/** * conditional formatting * @param {string} spreadsheetId spreadsheet ID * @returns {*} spreadsheet */ Snippets.prototype.conditionalFormatting = (spreadsheetId) => { try { let myRange = Sheets.newGridRange(); myRange.sheetId = 0; myRange.startRowIndex = 0; myRange.endRowIndex = 11; myRange.startColumnIndex = 0; myRange.endColumnIndex = 4; // Request 1 let rule1ConditionalValue = Sheets.newConditionValue(); rule1ConditionalValue.userEnteredValue = '=GT($D2,median($D$2:$D$11))'; let rule1ConditionFormat = Sheets.newCellFormat(); rule1ConditionFormat.textFormat = Sheets.newTextFormat(); rule1ConditionFormat.textFormat.foregroundColor = Sheets.newColor(); rule1ConditionFormat.textFormat.foregroundColor.red = 0.8; let rule1Condition = Sheets.newBooleanCondition(); rule1Condition.type = 'CUSTOM_FORMULA'; rule1Condition.values = [rule1ConditionalValue]; let rule1BooleanRule = Sheets.newBooleanRule(); rule1BooleanRule.condition = rule1Condition; rule1BooleanRule.format = rule1ConditionFormat; let rule1 = Sheets.newConditionalFormatRule(); rule1.ranges = [myRange]; rule1.booleanRule = rule1BooleanRule; let request1 = Sheets.newRequest(); let addConditionalFormatRuleRequest1 = Sheets.newAddConditionalFormatRuleRequest(); addConditionalFormatRuleRequest1.rule = rule1; addConditionalFormatRuleRequest1.index = 0; request1.addConditionalFormatRule = addConditionalFormatRuleRequest1; // Request 2 let rule2ConditionalValue = Sheets.newConditionValue(); rule2ConditionalValue.userEnteredValue = '=LT($D2,median($D$2:$D$11))'; let rule2ConditionFormat = Sheets.newCellFormat(); rule2ConditionFormat.textFormat = Sheets.newTextFormat(); rule2ConditionFormat.textFormat.foregroundColor = Sheets.newColor(); rule2ConditionFormat.textFormat.foregroundColor.red = 1; rule2ConditionFormat.textFormat.foregroundColor.green = 0.4; rule2ConditionFormat.textFormat.foregroundColor.blue = 0.4; let rule2Condition = Sheets.newBooleanCondition(); rule2Condition.type = 'CUSTOM_FORMULA'; rule2Condition.values = [rule2ConditionalValue]; let rule2BooleanRule = Sheets.newBooleanRule(); rule2BooleanRule.condition = rule2Condition; rule2BooleanRule.format = rule2ConditionFormat; let rule2 = Sheets.newConditionalFormatRule(); rule2.ranges = [myRange]; rule2.booleanRule = rule2BooleanRule; let request2 = Sheets.newRequest(); let addConditionalFormatRuleRequest2 = Sheets.newAddConditionalFormatRuleRequest(); addConditionalFormatRuleRequest2.rule = rule2; addConditionalFormatRuleRequest2.index = 0; request2.addConditionalFormatRule = addConditionalFormatRuleRequest2; // Batch send the requests const requests = [request1, request2]; let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest(); batchUpdate.requests = requests; const response = Sheets.Spreadsheets.batchUpdate(batchUpdate, spreadsheetId); return response; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
Java
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.AddConditionalFormatRuleRequest; import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest; import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse; import com.google.api.services.sheets.v4.model.BooleanCondition; import com.google.api.services.sheets.v4.model.BooleanRule; import com.google.api.services.sheets.v4.model.CellFormat; import com.google.api.services.sheets.v4.model.Color; import com.google.api.services.sheets.v4.model.ConditionValue; import com.google.api.services.sheets.v4.model.ConditionalFormatRule; import com.google.api.services.sheets.v4.model.GridRange; import com.google.api.services.sheets.v4.model.Request; import com.google.api.services.sheets.v4.model.TextFormat; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.Arrays; import java.util.Collections; import java.util.List; /* Class to demonstrate the use of Spreadsheet Conditional Formatting API */ public class ConditionalFormatting { /** * Create conditional formatting. * * @param spreadsheetId - Id of the spreadsheet. * @return updated changes count. * @throws IOException - if credentials file not found. */ public static BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetResponse result = null; try { // Execute the requests. BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size()); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
JavaScript
function conditionalFormatting(spreadsheetId, callback) { const myRange = { sheetId: 0, startRowIndex: 1, endRowIndex: 11, startColumnIndex: 0, endColumnIndex: 4, }; const requests = [{ addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}], }, format: { textFormat: {foregroundColor: {red: 0.8}}, }, }, }, index: 0, }, }, { addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}], }, format: { backgroundColor: {red: 1, green: 0.4, blue: 0.4}, }, }, }, index: 0, }, }]; const body = { requests, }; try { gapi.client.sheets.spreadsheets.batchUpdate({ spreadsheetId: spreadsheetId, resource: body, }).then((response) => { const result = response.result; console.log(`${result.replies.length} cells updated.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Conditionally formats a Spreadsheet. * @param {string} spreadsheetId A Spreadsheet ID. * @return {obj} spreadsheet information */ async function conditionalFormatting(spreadsheetId) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); const myRange = { sheetId: 0, startRowIndex: 1, endRowIndex: 11, startColumnIndex: 0, endColumnIndex: 4, }; const requests = [ { addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}], }, format: { textFormat: {foregroundColor: {red: 0.8}}, }, }, }, index: 0, }, }, { addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}], }, format: { backgroundColor: {red: 1, green: 0.4, blue: 0.4}, }, }, }, index: 0, }, }, ]; const resource = { requests, }; try { const response = await service.spreadsheets.batchUpdate({ spreadsheetId, resource, }); console.log(`${response.data.replies.length} cells updated.`); return response; } catch (err) { // TODO (developer) - Handle exception throw err; } }
PHP
use Google\Client; use Google\Service\Drive; use Google\Service\Sheets\BatchUpdateSpreadsheetRequest; use Google\Service\Sheets\Request; function conditionalFormatting($spreadsheetId) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope(Google\Service\Drive::DRIVE); $service = new Google_Service_Sheets($client); try{ $myRange = [ 'sheetId' => 0, 'startRowIndex' => 1, 'endRowIndex' => 11, 'startColumnIndex' => 0, 'endColumnIndex' => 4, ]; //execute the request $requests = [ new Google_Service_Sheets_Request([ 'addConditionalFormatRule' => [ 'rule' => [ 'ranges' => [ $myRange ], 'booleanRule' => [ 'condition' => [ 'type' => 'CUSTOM_FORMULA', 'values' => [ [ 'userEnteredValue' => '=GT($D2,median($D$2:$D$11))' ] ] ], 'format' => [ 'textFormat' => [ 'foregroundColor' => [ 'red' => 0.8 ] ] ] ] ], 'index' => 0 ] ]), new Google_Service_Sheets_Request([ 'addConditionalFormatRule' => [ 'rule' => [ 'ranges' => [ $myRange ], 'booleanRule' => [ 'condition' => [ 'type' => 'CUSTOM_FORMULA', 'values' => [ [ 'userEnteredValue' => '=LT($D2,median($D$2:$D$11))' ] ] ], 'format' => [ 'backgroundColor' => [ 'red' => 1, 'green' => 0.4, 'blue' => 0.4 ] ] ] ], 'index' => 0 ] ]) ]; $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([ 'requests' => $requests ]); $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest); printf("%d cells updated.", count($response->getReplies())); return $response; } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def conditional_formatting(spreadsheet_id): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) my_range = { "sheetId": 0, "startRowIndex": 1, "endRowIndex": 11, "startColumnIndex": 0, "endColumnIndex": 4, } requests = [ { "addConditionalFormatRule": { "rule": { "ranges": [my_range], "booleanRule": { "condition": { "type": "CUSTOM_FORMULA", "values": [ { "userEnteredValue": ( "=GT($D2,median($D$2:$D$11))" ) } ], }, "format": { "textFormat": {"foregroundColor": {"red": 0.8}} }, }, }, "index": 0, } }, { "addConditionalFormatRule": { "rule": { "ranges": [my_range], "booleanRule": { "condition": { "type": "CUSTOM_FORMULA", "values": [ { "userEnteredValue": ( "=LT($D2,median($D$2:$D$11))" ) } ], }, "format": { "backgroundColor": { "red": 1, "green": 0.4, "blue": 0.4, } }, }, }, "index": 0, } }, ] body = {"requests": requests} response = ( service.spreadsheets() .batchUpdate(spreadsheetId=spreadsheet_id, body=body) .execute() ) print(f"{(len(response.get('replies')))} cells updated.") return response except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id conditional_formatting("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")
Ruby
my_range = { sheet_id: 0, start_row_index: 1, end_row_index: 11, start_column_index: 0, end_column_index: 4 } requests = [{ add_conditional_format_rule: { rule: { ranges: [my_range], boolean_rule: { condition: { type: 'CUSTOM_FORMULA', values: [{ user_entered_value: '=GT($D2,median($D$2:$D$11))' }] }, format: { text_format: { foreground_color: { red: 0.8 } } } } }, index: 0 } }, { add_conditional_format_rule: { rule: { ranges: [my_range], boolean_rule: { condition: { type: 'CUSTOM_FORMULA', values: [{ user_entered_value: '=LT($D2,median($D$2:$D$11))' }] }, format: { background_color: { red: 1, green: 0.4, blue: 0.4 } } } }, index: 0 } }] body = { requests: requests } batch_update = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new batch_update.requests = requests result = service.batch_update_spreadsheet(spreadsheet_id, batch_update) puts "#{result.replies.length} cells updated."