The Sheets API allows you to create and update the conditional formatting rules within spreadsheets. Only certain formatting types (bold, italic, strikethough, foreground color and background color) can be controlled through conditional formatting. The examples on this page illustrate how to achieve common conditional formatting operations with the API.
In these examples, the placeholders spreadsheetId and
sheetId are used to indicate where you would provide those IDs.
You can find the spreadsheet ID in
the spreadsheet URL. You can get the sheet
ID can by using the
spreadsheets.get method.
Add a conditional color gradient across a row
The following spreadsheets.batchUpdate request establishes new gradient conditional formatting rules for row 10 and 11 of a sheet. The first rule states that cells in that row have their backgrounds colored according to their value. The lowest value in the row will be colored dark red, while the highest value will be colored bright green. The color of other values will be determined by interpolation. The second rule does the same, but with specific numeric values determining the gradient endpoints (and different colors).
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startRowIndex": 9,
"endRowIndex": 10,
}
],
"gradientRule": {
"minpoint": {
"color": {
"green": 0.2,
"red": 0.8
},
"type": "MIN"
},
"maxpoint": {
"color": {
"green": 0.9
},
"type": "MAX"
},
}
},
"index": 0
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startRowIndex": 10,
"endRowIndex": 11,
}
],
"gradientRule": {
"minpoint": {
"color": {
"green": 0.8,
"red": 0.8
},
"type": "NUMBER",
"value": "0"
},
"maxpoint": {
"color": {
"blue": 0.9,
"green": 0.5,
"red": 0.5
},
"type": "NUMBER",
"value": "256"
},
}
},
"index": 1
}
},
]
}
Here is an example of how the format rules applied by this request may look.
Since the gradient in row 11 has its maxpoint set to 256, any values above
that have the maxpoint color:
Add a conditional formatting rule to a set of ranges
The following spreadsheets.batchUpdate request establishes a new conditional formatting rule for columns A and C of a sheet. The rule states that cells with values of 10 or less will have their background colors changed to a dark red. The rule is inserted at index 0, so it will take priority over other formatting rules.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startColumnIndex": 0,
"endColumnIndex": 1,
},
{
"sheetId": sheetId,
"startColumnIndex": 2,
"endColumnIndex": 3,
},
],
"booleanRule": {
"condition": {
"type": "NUMBER_LESS_THAN_EQ",
"values": [
{
"userEnteredValue": "10"
}
]
},
"format": {
"backgroundColor": {
"green": 0.2,
"red": 0.8,
}
}
}
},
"index": 0
}
}
]
}
Here is an example of how the format rule applied by this request might look:
Add date and text conditional formatting rules to a range
The following spreadsheets.batchUpdate requests establish new conditional formatting rules for the range A1:D5 in a a sheet, based on date and text values in those cells. The first rule makes the cell text bold if the text contains the string "Cost" (case-insensitive). The second rule italicizes the cell text and colors it blue if the cell contains a date occurring before the past week.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "TEXT_CONTAINS",
"values": [
{
"userEnteredValue": "Cost"
}
]
},
"format": {
"textFormat": {
"bold": true
}
}
}
},
"index": 0
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "DATE_BEFORE",
"values": [
{
"relativeDate": "PAST_WEEK"
}
]
},
"format": {
"textFormat": {
"italic": true,
"foregroundColor": {
"blue": 1
}
}
}
}
},
"index": 1
}
}
]
}
Here is an example of how the format rule applied by this request might look (the current date is 9/26/2016 in this example):
Add a custom formula rule to a range
The following spreadsheets.batchUpdate request establishes a new conditional formatting rule for the range B5:B8 in a sheet, based on a custom formula. The rule calculates the product of the cell and the previous column and if that product is greater than 120 the cell text is bolded and italicized.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startColumnIndex": 1,
"endColumnIndex": 2,
"startRowIndex": 4,
"endRowIndex": 8
}
],
"booleanRule": {
"condition": {
"type": "CUSTOM_FORMULA",
"values": [
{
"userEnteredValue": "=GT(A5*B5,120)"
}
]
},
"format": {
"textFormat": {
"bold": true,
"italic": true
}
}
}
},
"index": 0
}
}
]
}
Here is an example of how the format rule applied by this request might look:
Delete a conditional formatting rule
The following spreadsheets.batchUpdate
request deletes the conditional formatting rule having index 0 in the sheet
specified by sheetId.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"deleteConditionalFormatRule": {
"sheetId": sheetId,
"index": 0
}
}
]
}
Read the list of conditional formatting rules
The following spreadsheets.get
request gets the title, sheetId and list of all conditional
formatting rules for each sheet in a spreadsheet. The fields query parameter
determines what data to return.
The response to this method call is a Spreadsheet
object, which contains an array of Sheet
objects each having a SheetProperties
element and an array of ConditionalFormatRule
elements. If a given response field is currently set to the default value, it is
omitted from the response.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?fields=sheets(properties(title,sheetId),conditionalFormats)
{
"sheets": [
{
"properties": {
"sheetId": 0,
"title": "Sheet1"
},
"conditionalFormats": [
{
"ranges": [
{
"startRowIndex": 4,
"endRowIndex": 8,
"startColumnIndex": 1,
"endColumnIndex": 2
}
],
"booleanRule": {
"condition": {
"type": "CUSTOM_FORMULA",
"values": [
{
"userEnteredValue": "=GT(A5*B5,120)"
}
]
},
"format": {
"textFormat": {
"bold": true,
"italic": true
}
}
}
},
{
"ranges": [
{
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4
}
],
"booleanRule": {
"condition": {
"type": "DATE_BEFORE",
"values": [
{
"relativeDate": "PAST_WEEK"
}
]
},
"format": {
"textFormat": {
"foregroundColor": {
"blue": 1
},
"italic": true
}
}
}
},
...
]
}
]
}
Update a conditional formatting rule or its priority
The following spreadsheets.batchUpdate
request first moves an existing conditional format rule to a higher index (from
0 to 2, decreasing its priority). The second request replaces the
conditional formatting rule at index 0 with a new rule that formats cells
containing the exact text specified ("Total Cost") in the A1:D5 range. The
first request's move is completed before the second begins, so the second
request is replacing the rule that was originally at index 1.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"updateConditionalFormatRule": {
"sheetId": sheetId,
"index": 0,
"newIndex": 2
},
"updateConditionalFormatRule": {
"sheetId": sheetId,
"index": 0,
"rule": {
"ranges": [
{
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "TEXT_EQ",
"values": [
{
"userEnteredValue": "Total Cost"
}
]
},
"format": {
"textFormat": {
"bold": true
}
}
}
}
}
}
]
}