Conditional Formatting

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. The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.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 gradient format recipe result

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 conditional format recipe result

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):

Text and date conditional format recipe result

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:

Custom conditional format recipe result

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
              }
            }
          }
        }
      }
    }
  ]
}

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.