Google Sheets API 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, 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 Sheets API.
Các ví dụ này được trình bày dưới dạng yêu cầu HTTP để không phụ thuộc vào ngôn ngữ. Để tìm hiểu cách triển khai một bản cập nhật hàng loạt bằng nhiều ngôn ngữ bằng cách sử dụng các thư viện ứng dụng Google API, hãy xem phần Cập nhật bảng tính.
Trong các ví dụ này, phần giữ chỗ SPREADSHEET_ID và SHEET_ID cho biết vị trí mà 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ã nhận dạng 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. Ví dụ về một dải ô là Sheet1!A1:D5.
Thêm dải chuyển màu có điều kiện trên một hàng
Mã mẫu 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 theo dải màu mới cho hàng 10 và 11 của một trang tính. Quy tắc đầu tiên quy định rằng các ô trong hàng đó có màu nền được đặt theo giá trị của ô. Giá trị thấp nhất trong hàng có màu đỏ đậm, còn giá trị cao nhất có màu xanh lục tươi. Màu của các giá trị khác được nội suy. Quy tắc thứ hai cũng làm như vậy, nhưng với các giá trị số cụ thể xác định các điểm cuối của chuyển màu (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 trình bày bên dưới.
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 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 ở hàng 11 có maxpoint được đặt thành 256, nên mọi giá trị phía trên đều có màu maxpoint:
Thêm quy tắc định dạng có điều kiện vào một nhóm dải ô
Mã mẫu phương thức spreadsheets.batchUpdate sau đây cho thấy cách sử dụng AddConditionalFormatRuleRequest để thiết lập một quy tắc định dạng có điều kiện mới cho các cột A và C của một trang tính.
Quy tắc này quy định rằng các ô có giá trị từ 10 trở xuống sẽ có màu nền thay đổi thành màu đỏ sẫm. Quy tắc này được chèn ở chỉ mục 0, vì vậy, quy tắc này 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 trình bày bên dưới.
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 yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tính:
Thêm quy tắc định dạng có điều kiện cho ngày và văn bản vào một dải ô
Mã mẫu phương thức spreadsheets.batchUpdate sau đây cho thấy 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 giá trị ngày và văn bản trong các ô đó. Nếu văn bản chứa chuỗi "Chi phí" (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 ô thành chữ đậm. Nếu ô chứa một ngày trước tuần trước, quy tắc thứ hai sẽ đặt văn bản trong ô thành chữ in nghiêng và có 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 trình bày bên dưới.
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 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:
Thêm quy tắc công thức tuỳ chỉnh vào một dải ô
Mã mẫu phương thức spreadsheets.batchUpdate sau đây cho biết cách dùng AddConditionalFormatRuleRequest để thiết lập một 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 một công thức tuỳ chỉnh. Quy tắc này 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, văn bản trong ô sẽ được đặt thành chữ in đậm và chữ 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 trình bày bên dưới.
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 yêu cầu, quy tắc định dạng được áp dụng sẽ cập nhật trang tí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 có chỉ mục 0 trong trang tính do SHEET_ID chỉ định.
Giao thức yêu cầu được trình bày bên dưới.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"deleteConditionalFormatRule": {
"sheetId": SHEET_ID,
"index": 0
}
}
]
}Đọc danh sách các quy tắc định dạng có điều kiện
Đoạn mã mẫu 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 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 trình bày bên dưới.
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, 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 các 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ị bỏ qua trong 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
Mã mẫu 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 một quy tắc định dạng có điều kiện hiện có đến một chỉ mục cao hơn (từ 0 đến 2, giảm mức độ ưu tiên của quy tắc đó). Yêu cầu thứ hai sẽ 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 văn bản chính xác được chỉ định ("Tổng chi phí") trong dải ô A1:D5. Yêu cầu đầu tiên 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 ở 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 trình bày bên dưới.
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 } } } } } } ] }