Conditional formatting

The Google Sheets API lets you create and update the conditional formatting rules in spreadsheets. Only certain formatting types (bold, italic, strikethrough, 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 Sheets API.

These examples are presented as HTTP requests to be language neutral. To learn how to implement a batch update in different languages using the Google API client libraries, see Update spreadsheets.

In these examples, the placeholders SPREADSHEET_ID and SHEET_ID indicates where you would provide those IDs. You can find the spreadsheet ID in the spreadsheet URL. You can get the sheet ID by using the spreadsheets.get method. The ranges are specified using A1 notation. An example range is Sheet1!A1:D5.

Add a conditional color gradient across a row

The following spreadsheets.batchUpdate method code sample shows how to use the AddConditionalFormatRuleRequest to establish new gradient conditional formatting rules for rows 10 and 11 of a sheet. The first rule states that cells in that row have their background colors set according to their value. The lowest value in the row is colored dark red, while the highest value is colored bright green. The color of the other values is interpolated. The second rule does the same, but with specific numeric values determining the gradient endpoints (and different colors). The request uses the sheets.InterpolationPointType as the type.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{
  "requests": [
    {
      "addConditionalFormatRule": {
        "rule": {
          "ranges": [
            {
              "sheetId": SHEET_ID,
              "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": SHEET_ID,
              "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
      }
    },
  ]
}

After the request, the applied format rule updates the sheet. Since the gradient in row 11 has its maxpoint set to 256, any values above it have the maxpoint color:

Add a conditional formatting rule to a set of ranges

The following spreadsheets.batchUpdate method code sample shows how to use the AddConditionalFormatRuleRequest to establish a new conditional formatting rule for columns A and C of a sheet. The rule states that cells with values of 10 or less have their background colors changed to a dark red. The rule is inserted at index 0, so it takes priority over other formatting rules. The request uses the ConditionType as the type for the BooleanRule.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{
  "requests": [
    {
      "addConditionalFormatRule": {
        "rule": {
          "ranges": [
            {
              "sheetId": SHEET_ID,
              "startColumnIndex": 0,
              "endColumnIndex": 1,
            },
            {
              "sheetId": SHEET_ID,
              "startColumnIndex": 2,
              "endColumnIndex": 3,
            },
          ],
          "booleanRule": {
            "condition": {
              "type":