Google Sheets API memungkinkan Anda membuat dan memperbarui aturan format bersyarat di spreadsheet. Hanya jenis pemformatan tertentu (tebal, miring, coretan, warna latar depan, dan warna latar belakang) yang dapat dikontrol melalui pemformatan kondisional. Contoh di halaman ini mengilustrasikan cara melakukan operasi pemformatan bersyarat yang umum dengan Sheets API.
Contoh ini disajikan sebagai permintaan HTTP agar tidak menggunakan bahasa yang netral. Untuk mempelajari cara menerapkan update 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
rentangnya adalah Sheet1!A1:D5.
Menambahkan gradasi warna bersyarat pada baris
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan format bersyarat gradien baru untuk baris 10 dan 11
sheet. Aturan pertama menyatakan bahwa sel di baris tersebut memiliki warna latar belakang
yang ditetapkan sesuai dengan nilainya. Nilai terendah pada baris berwarna merah tua,
sedangkan nilai tertinggi berwarna hijau cerah. Warna nilai lain
diinterpolasikan. Aturan kedua melakukan hal yang sama, tetapi dengan nilai numerik tertentu yang menentukan endpoint gradien (dan warna yang berbeda). Permintaan tersebut 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 dibuat, aturan format yang diterapkan akan memperbarui sheet. Karena gradien di baris 11 memiliki maxpoint yang ditetapkan ke 256, nilai apa pun di atasnya memiliki warna maxpoint:
Menambahkan aturan format bersyarat ke kumpulan rentang
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan format bersyarat baru untuk kolom A dan C pada sheet.
Aturan tersebut menyatakan bahwa sel dengan nilai 10 atau kurang memiliki warna latar belakang
yang berubah menjadi merah tua. Aturan disisipkan pada indeks 0 sehingga lebih diprioritaskan daripada aturan pemformatan lainnya. Permintaan tersebut 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 tersebut, aturan format yang diterapkan akan memperbarui sheet:
Menambahkan aturan format bersyarat tanggal dan teks ke rentang
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
AddConditionalFormatRuleRequest
untuk menetapkan aturan format bersyarat baru untuk rentang A1:D5 dalam sheet,
berdasarkan nilai tanggal dan teks dalam sel tersebut. Jika teks berisi string "Biaya" (tidak peka huruf besar/kecil), aturan pertama akan menetapkan teks sel sebagai tebal. Jika sel berisi tanggal yang terjadi sebelum seminggu terakhir, aturan kedua akan menetapkan teks sel sebagai cetak miring dan mewarnainya dengan warna biru. Permintaan tersebut 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 dibuat, 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 format bersyarat baru untuk rentang B5:B8 dalam sheet,
berdasarkan formula kustom. Aturan tersebut menghitung produk sel di
kolom A dan B. Jika produk lebih besar dari 120, teks sel disetel ke
tebal dan miring. Permintaan tersebut 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 tersebut, aturan format yang diterapkan akan memperbarui sheet:
Menghapus aturan format bersyarat
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
DeleteConditionalFormatRuleRequest
untuk menghapus aturan format bersyarat dengan indeks 0 dalam 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
}
}
]
}
Baca daftar aturan format bersyarat
Contoh kode metode
spreadsheets.get
berikut menunjukkan cara mendapatkan judul, SHEET_ID, dan daftar semua
aturan format 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 tersebut 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
}
}
}
},
...
]
}
]
}
Memperbarui aturan format bersyarat atau prioritasnya
Contoh kode metode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateConditionalFormatRuleRequest
dengan beberapa permintaan. Permintaan pertama memindahkan aturan format bersyarat
yang sudah ada ke indeks yang lebih tinggi (dari 0 ke 2, yang akan mengurangi prioritasnya). Permintaan kedua mengganti aturan format bersyarat di indeks 0 dengan aturan baru yang memformat sel yang berisi teks yang sama persis dengan yang ditentukan ("Total Biaya") dalam rentang A1:D5. Pemindahan permintaan pertama selesai sebelum permintaan kedua dimulai, sehingga permintaan kedua menggantikan aturan yang awalnya 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
}
}
}
}
}
}
]
}