Định dạng có điều kiện

API Google Trang tính cho phép bạn tạo và cập nhật các quy tắc định dạng có điều kiện trong bảng tính. Bạn chỉ có thể kiểm soát một số loại định dạng (in đậm, in nghiêng, gạch ngang chữ, màu nền trước và màu nền) thông qua tính năng định dạng có điều kiện. Các ví dụ trên trang này minh hoạ cách thực hiện các thao tác định dạng có điều kiện phổ biến bằng API Trang tính.

Những ví dụ này được trình bày dưới dạng yêu cầu HTTP để trung lập về ngôn ngữ. Để tìm hiểu cách triển khai quá trình cập nhật hàng loạt bằng nhiều ngôn ngữ bằng cách sử dụng thư viện ứng dụng API của Google, hãy xem phần Cập nhật bảng tính.

Trong những ví dụ này, phần giữ chỗ SPREADSHEET_IDSHEET_ID cho biết nơi bạn sẽ cung cấp các mã nhận dạng đó. Bạn có thể tìm thấy mã nhận dạng bảng tính trong URL của bảng tính. Bạn có thể lấy mã trang tính bằng cách sử dụng phương thức spreadsheets.get. Các dải ô được chỉ định bằng ký hiệu A1. Một dải ô ví dụ là Sheet1!A1:D5.

Thêm độ dốc màu có điều kiện trên một hàng

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng AddConditionalFormatRuleRequest để thiết lập các quy tắc mới về định dạng chuyển màu có điều kiện cho hàng 10 và 11 của trang tính. Quy tắc đầu tiên cho biết rằng các ô trong hàng đó được đặt màu nền theo giá trị. Giá trị thấp nhất trong hàng có màu đỏ đậm, trong khi giá trị cao nhất có màu xanh lục sáng. Màu của các giá trị khác sẽ được nội suy. Quy tắc thứ hai cũng thực hiện tương tự, nhưng với các giá trị số cụ thể xác định điểm cuối của độ dốc (và các màu khác nhau). Yêu cầu này sử dụng sheets.InterpolationPointType làm type.

Giao thức yêu cầu được hiển thị dưới đây.

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

Sau khi yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tính. Vì độ dốc trong hàng 11 có điểm tối đa được đặt thành 256, nên mọi giá trị phía trên đều có màu điểm tối đa:

Thêm kết quả công thức ở định dạng chuyển màu

Thêm quy tắc định dạng có điều kiện vào một tập hợp dải ô

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng AddConditionalFormatRuleRequest để thiết lập quy tắc định dạng có điều kiện mới cho cột A và C của trang tính. Quy tắc này chỉ định rằng các ô có giá trị từ 10 trở xuống sẽ có màu nền chuyển thành màu đỏ đậm. Quy tắc này được chèn tại chỉ mục 0 nên sẽ được ưu tiên hơn các quy tắc định dạng khác. Yêu cầu này sử dụng ConditionType làm type cho BooleanRule.

Giao thức yêu cầu được hiển thị dưới đây.

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": "NUMBER_LESS_THAN_EQ",
              "values": [
                {
                  "userEnteredValue": "10"
                }
              ]
            },
            "format": {
              "backgroundColor": {
                "green": 0.2,
                "red": 0.8,
              }
            }
          }
        },
        "index": 0
      }
    }
  ]
}

Sau khi yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tính:

Thêm kết quả công thức nấu ăn ở định dạng có điều kiện

Thêm quy tắc định dạng có điều kiện ngày và văn bản vào một dải ô

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng AddConditionalFormatRuleRequest để thiết lập các quy tắc định dạng có điều kiện mới cho dải ô A1:D5 trong một trang tính, dựa trên ngày và giá trị văn bản trong các ô đó. Nếu văn bản chứa chuỗi "Cost" (không phân biệt chữ hoa chữ thường), thì quy tắc đầu tiên sẽ đặt văn bản trong ô là in đậm. Nếu ô chứa một ngày xảy ra trước tuần trước, thì quy tắc thứ hai sẽ đặt văn bản ô ở dạng in nghiêng và tô màu xanh dương. Yêu cầu này sử dụng ConditionType làm type cho BooleanRule.

Giao thức yêu cầu được hiển thị dưới đây.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "addConditionalFormatRule": {
        "rule": {
          "ranges": [
            {
              "sheetId": SHEET_ID,
              "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": SHEET_ID,
              "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
      }
    }
  ]
}

Sau khi yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tính. Trong ví dụ này, ngày hiện tại là 26/9/2016:

Kết quả công thức nấu ăn ở định dạng có điều kiện ở dạng văn bản và ngày

Thêm quy tắc công thức tuỳ chỉnh vào một dải ô

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng AddConditionalFormatRuleRequest để thiết lập quy tắc định dạng có điều kiện mới cho dải ô B5:B8 trong một trang tính, dựa trên công thức tuỳ chỉnh. Quy tắc này sẽ tính tích của ô trong các cột A và B. Nếu sản phẩm lớn hơn 120 thì văn bản trong ô sẽ được đặt thành kiểu in đậm và in nghiêng. Yêu cầu này sử dụng ConditionType làm type cho BooleanRule.

Giao thức yêu cầu được hiển thị dưới đây.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "addConditionalFormatRule": {
        "rule": {
          "ranges": [
            {
              "sheetId": SHEET_ID,
              "startColumnIndex": 2,
              "endColumnIndex": 3,
              "startRowIndex": 4,
              "endRowIndex": 8
            }
          ],
          "booleanRule": {
            "condition": {
              "type": "CUSTOM_FORMULA",
              "values": [
                {
                  "userEnteredValue": "=GT(A5*B5,120)"
                }
              ]
            },
            "format": {
              "textFormat": {
                "bold": true,
                "italic": true
              }
            }
          }
        },
        "index": 0
      }
    }
  ]
}

Sau khi yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tính:

Kết quả công thức nấu ăn ở định dạng có điều kiện tuỳ chỉnh

Xoá quy tắc định dạng có điều kiện

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng DeleteConditionalFormatRuleRequest để xoá quy tắc định dạng có điều kiện với chỉ mục 0 trong trang tính do SHEET_ID chỉ định.

Giao thức yêu cầu được hiển thị dưới đây.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "deleteConditionalFormatRule": {
        "sheetId": SHEET_ID,
        "index": 0
      }
    }
  ]
}

Đọc danh sách quy tắc định dạng có điều kiện

Mẫu mã phương thức spreadsheets.get sau đây cho biết cách lấy tiêu đề, SHEET_ID và danh sách tất cả các quy tắc định dạng có điều kiện cho từng trang tính trong bảng tính. Tham số truy vấn fields xác định dữ liệu cần trả về.

Giao thức yêu cầu được hiển thị dưới đây.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties(title,sheetId),conditionalFormats)

Phản hồi bao gồm một tài nguyên Spreadsheet, trong đó chứa một mảng các đối tượng Sheet, mỗi đối tượng có một phần tử SheetProperties và một mảng phần tử ConditionalFormatRule. Nếu một trường phản hồi nhất định được đặt thành giá trị mặc định, thì trường đó sẽ bị loại khỏi phản hồi. Yêu cầu này sử dụng ConditionType làm type cho BooleanRule.

{
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1"
      },
      "conditionalFormats": [
        {
          "ranges": [
            {
              "startRowIndex": 4,
              "endRowIndex": 8,
              "startColumnIndex": 2,
              "endColumnIndex": 3
            }
          ],
          "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
              }
            }
          }
        },
        ...
      ]
    }
  ]
}

Cập nhật quy tắc định dạng có điều kiện hoặc mức độ ưu tiên của quy tắc định dạng đó

Mẫu mã phương thức spreadsheets.batchUpdate sau đây cho biết cách sử dụng UpdateConditionalFormatRuleRequest với nhiều yêu cầu. Yêu cầu đầu tiên di chuyển quy tắc định dạng có điều kiện hiện có lên chỉ mục cao hơn (từ 0 sang 2, giảm mức độ ưu tiên của yêu cầu đó). Yêu cầu thứ hai thay thế quy tắc định dạng có điều kiện tại chỉ mục 0 bằng một quy tắc mới để định dạng các ô chứa chính xác văn bản được chỉ định ("Tổng chi phí") trong dải ô A1:D5. Quá trình di chuyển yêu cầu đầu tiên được hoàn tất trước khi yêu cầu thứ hai bắt đầu, vì vậy, yêu cầu thứ hai sẽ thay thế quy tắc ban đầu tại chỉ mục 1. Yêu cầu này sử dụng ConditionType làm type cho BooleanRule.

Giao thức yêu cầu được hiển thị dưới đây.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateConditionalFormatRule": {
        "sheetId": SHEET_ID,
        "index": 0,
        "newIndex": 2
      },
      "updateConditionalFormatRule": {
        "sheetId": SHEET_ID,
        "index": 0,
        "rule": {
          "ranges": [
            {
              "sheetId": SHEET_ID,
              "startRowIndex": 0,
              "endRowIndex": 5,
              "startColumnIndex": 0,
              "endColumnIndex": 4,
            }
          ],
          "booleanRule": {
            "condition": {
              "type": "TEXT_EQ",
              "values": [
                {
                  "userEnteredValue": "Total Cost"
                }
              ]
            },
            "format": {
              "textFormat": {
                "bold": true
              }
            }
          }
        }
      }
    }
  ]
}