Google Sheets API memungkinkan Anda membuat dan mengupdate aturan pemformatan bersyarat dalam spreadsheet. Hanya beberapa jenis pemformatan tertentu (tebal, miring, coret, warna latar depan, dan warna latar belakang) yang dapat dikontrol melalui pemformatan bersyarat. Contoh pada halaman ini mengilustrasikan cara melakukan operasi pemformatan bersyarat umum dengan Sheets API.
Contoh-contoh ini disajikan sebagai permintaan HTTP yang netral bahasa. Untuk mempelajari cara mengimplementasikan update secara batch dalam berbagai bahasa menggunakan library klien Google API, lihat Mengupdate spreadsheet.
Dalam contoh ini, placeholder SPREADSHEET_ID dan SHEET_ID
menunjukkan tempat Anda akan memberikan ID tersebut. Anda dapat menemukan ID spreadsheet di URL spreadsheet. Anda bisa mendapatkan
ID sheet menggunakan
metode spreadsheets.get. Rentang ditentukan menggunakan notasi A1. Contoh
rentang adalah Sheet1!A1:D5.
Menambahkan gradien warna bersyarat ke sebuah baris
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan pemformatan bersyarat gradien baru untuk baris 10 dan 11
lembar. Aturan pertama menyatakan bahwa sel-sel dalam baris tersebut memiliki warna latar belakang
yang ditetapkan sesuai dengan nilainya. Nilai terendah dalam baris berwarna merah tua,
sedangkan nilai tertinggi berwarna hijau cerah. Warna nilai lainnya
di-interpolasi. Aturan kedua melakukan hal yang sama, tetapi dengan nilai numerik tertentu
yang menentukan endpoint gradien (dan warna yang berbeda). Permintaan menggunakan
sheets.InterpolationPointType
sebagai type.
Protokol permintaan ditampilkan di bawah ini.
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
}
},
]
}
Setelah permintaan, aturan format yang diterapkan akan memperbarui sheet. Karena gradien
di baris 11 memiliki titik maksimum yang ditetapkan ke 256, nilai apa pun di atasnya memiliki warna
titik maksimum:
Menambahkan aturan pemformatan bersyarat ke serangkaian rentang
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan pemformatan bersyarat baru untuk kolom A dan C pada sheet.
Aturan tersebut menyatakan bahwa sel-sel dengan nilai 10 atau kurang akan memiliki warna latar belakang
yang berubah menjadi merah tua. Aturan ini disisipkan pada indeks 0, sehingga lebih diprioritaskan daripada aturan pemformatan lainnya. Permintaan menggunakan
ConditionType
sebagai type untuk
BooleanRule.
Protokol permintaan ditampilkan di bawah ini.
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
}
}
]
}
Setelah permintaan, aturan format yang diterapkan akan memperbarui sheet:
Menambahkan aturan pemformatan bersyarat tanggal dan teks ke sebuah rentang
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan pemformatan bersyarat baru untuk rentang A1:D5 dalam sheet,
berdasarkan nilai tanggal dan teks di sel tersebut. Jika teks berisi string
"Cost" (tidak membedakan huruf besar-kecil), aturan pertama akan menetapkan teks sel sebagai tebal. Jika
sel berisi tanggal yang terjadi sebelum minggu lalu, aturan kedua akan menetapkan
teks sel sebagai italik dan mewarnainya dengan warna biru. Permintaan menggunakan
ConditionType
sebagai type untuk
BooleanRule.
Protokol permintaan ditampilkan di bawah ini.
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
}
}
]
}
Setelah permintaan, aturan format yang diterapkan akan memperbarui sheet. Dalam contoh ini, tanggal saat ini adalah 26/9/2016:
Menambahkan aturan formula kustom ke rentang
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan pemformatan bersyarat baru untuk rentang B5:B8 dalam sheet,
berdasarkan formula kustom. Aturan ini menghitung produk sel di
kolom A dan B. Jika produk lebih besar dari 120, teks sel akan ditetapkan ke
tebal dan miring. Permintaan menggunakan
ConditionType
sebagai type untuk
BooleanRule.
Protokol permintaan ditampilkan di bawah ini.
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
}
}
]
}
Setelah permintaan, aturan format yang diterapkan akan memperbarui sheet:
Menghapus aturan format bersyarat
Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan DeleteConditionalFormatRuleRequest untuk menghapus aturan pemformatan bersyarat dengan indeks 0 di sheet yang ditentukan oleh SHEET_ID.
Protokol permintaan ditampilkan di bawah ini.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"deleteConditionalFormatRule": {
"sheetId": SHEET_ID,
"index": 0
}
}
]
}
Membaca daftar aturan pemformatan bersyarat
Contoh kode metode spreadsheets.get berikut menunjukkan cara mendapatkan judul, SHEET_ID, dan daftar semua aturan pemformatan bersyarat untuk setiap sheet dalam spreadsheet. Parameter kueri fields menentukan data yang akan ditampilkan.
Protokol permintaan ditampilkan di bawah ini.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties(title,sheetId),conditionalFormats)
Respons terdiri dari resource Spreadsheet, yang berisi array objek Sheet yang masing-masing memiliki elemen SheetProperties dan array elemen ConditionalFormatRule. Jika kolom respons tertentu ditetapkan ke nilai default, kolom tersebut akan dihilangkan
dari respons. Permintaan menggunakan
ConditionType
sebagai type untuk
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
}
}
}
},
...
]
}
]
}
Mengupdate aturan pemformatan bersyarat atau prioritasnya
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateConditionalFormatRuleRequest
dengan beberapa permintaan. Permintaan pertama memindahkan aturan format bersyarat yang ada ke indeks yang lebih tinggi (dari 0 ke 2, yang akan mengurangi prioritasnya). Permintaan kedua
mengganti aturan pemformatan bersyarat pada indeks 0 dengan aturan baru
yang memformat sel yang berisi teks persis yang ditentukan ("Total Cost") dalam
rentang A1:D5. Pemindahan permintaan pertama diselesaikan sebelum permintaan kedua dimulai, sehingga
permintaan kedua mengganti aturan yang semula berada di indeks 1. Permintaan
menggunakan
ConditionType
sebagai type untuk
BooleanRule.
Protokol permintaan ditampilkan di bawah ini.
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
}
}
}
}
}
}
]
}