借助条件格式,您可以设置单元格的格式,使其外观根据单元格中所含的值或其他单元格中的值而动态变化。条件格式有许多可能的应用,包括以下用途:
- 突出显示高于特定阈值的单元格(例如,使用粗体文本突出显示所有金额超过 2,000 美元的交易)。
- 设置单元格格式,使其颜色随值而变化(例如,随着金额超过 2,000 美元,应用更深的红色背景)。
- 根据其他单元格的相应内容动态设置单元格格式(例如,突出显示“在市场上的时间”字段大于 90 天的房源的地址)。
您甚至可以根据单元格及其它单元格的值设置单元格的格式。例如,您可以根据单元格值与范围中位数值的对比情况设置单元格格式:
图 1. 设置格式以突出显示高于或低于中位数年龄的值。
在此示例中,每行的单元格格式取决于其 age
列中的值与所有年龄的中位数值的比较结果。年龄高于中位数的行会显示红色文本,低于中位数的行会显示红色背景。其中两行中的 age
值与平均年龄 (48) 相符,并且这些单元格不会采用特殊格式。(如需查看用于创建此条件格式的源代码,请参阅下面的示例。)
条件格式规则
条件格式是使用格式规则表示的。每个电子表格都会存储这些规则的列表,并按照列表中显示的顺序应用这些规则。借助 Google 表格 API,您可以添加、更新和删除这些格式设置规则。
每条规则都指定了目标范围、规则类型、触发规则的条件以及要应用的任何格式设置。
目标范围 - 可以是单个单元格、单元格范围或多个范围。
规则类型 - 规则分为两类:
这些规则类型的评估条件和可应用的格式各不相同,详见以下部分。
布尔规则
BooleanRule
定义是否根据评估为 true
或 false
的 BooleanCondition
应用特定格式。布尔规则采用以下形式:
{
"condition": {
object(BooleanCondition)
},
"format": {
object(CellFormat)
},
}
条件可以使用内置的 ConditionType
,也可以使用自定义公式进行更复杂的评估。
借助内置类型,您可以根据数值阈值、文本比较或单元格是否已填充来应用格式。例如,NUMBER_GREATER
表示单元格的值必须大于条件的值。系统始终会针对目标单元格评估规则。
自定义公式是一种特殊的条件类型,可让您根据任意表达式应用格式,还允许对任何单元格(而不仅仅是目标单元格)进行评估。条件的公式必须求得的值为 true
。
如需定义布尔值规则应用的格式,您可以使用 CellFormat
类型的一部分来定义:
- 单元格中的文本是粗体、斜体还是带删除线。
- 单元格中的文本颜色。
- 单元格的背景颜色。
渐变规则
GradientRule
用于定义与一系列值对应的一系列颜色。渐变规则采用以下形式:
{
"minpoint": {
object(InterpolationPoint)
},
"midpoint": {
object(InterpolationPoint)
},
"maxpoint": {
object(InterpolationPoint)
},
}
每个 InterpolationPoint
都定义了一种颜色及其对应的值。一组三个点可定义颜色渐变。
管理条件格式规则
如需创建、修改或删除条件格式规则,请将 spreadsheets.batchUpdate
方法与适当的请求类型搭配使用:
使用
AddConditionalFormatRuleRequest
将规则添加到指定索引的列表中。使用
UpdateConditionalFormatRuleRequest
替换或重新排列列表中给定索引处的规则。使用
DeleteConditionalFormatRuleRequest
从列表中移除指定索引处的规则。
示例
以下示例展示了如何创建此页面顶部屏幕截图中显示的条件格式。如需查看更多示例,请参阅条件格式示例页面。
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."