Pemformatan bersyarat

Google Spreadsheet API memungkinkan Anda membuat dan memperbarui aturan pemformatan bersyarat dalam spreadsheet. Hanya jenis pemformatan tertentu (tebal, miring, coret, warna latar depan, dan warna latar belakang) yang dapat dikontrol melalui pemformatan bersyarat. Contoh di halaman ini menggambarkan cara melakukan operasi pemformatan bersyarat umum dengan Sheets API.

Contoh ini ditampilkan sebagai permintaan HTTP agar netral terhadap bahasa. Untuk mempelajari cara menerapkan update batch dalam berbagai bahasa menggunakan library klien Google API, lihat Memperbarui 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 gradasi warna bersyarat di seluruh baris

Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan format bersyarat gradien baru untuk baris 10 dan 11 di sheet. Aturan pertama menyatakan bahwa 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 diinterpolasi. Aturan kedua melakukan hal yang sama, tetapi dengan nilai numerik tertentu yang menentukan titik akhir gradien (dan warna yang berbeda). Permintaan menggunakan sheets.InterpolationPointType sebagai type.

Protokol permintaan ditampilkan di bawah.

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 hasil resep format gradien

Menambahkan aturan format bersyarat ke sekumpulan rentang

Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan format bersyarat baru untuk kolom A dan C pada sheet. Aturan menyatakan bahwa sel dengan nilai 10 atau kurang akan diubah warna latarnya menjadi merah tua. Aturan disisipkan pada indeks 0, sehingga diprioritaskan daripada aturan pemformatan lainnya. Permintaan menggunakan ConditionType sebagai type untuk BooleanRule.

Protokol permintaan ditampilkan di bawah.

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 hasil resep format bersyarat

Menambahkan aturan format bersyarat tanggal dan teks ke rentang

Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan pemformatan bersyarat baru untuk rentang A1:D5 dalam sheet, berdasarkan nilai tanggal dan teks dalam sel tersebut. Jika teks berisi string "Cost" (tidak peka 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 miring dan mewarnainya dengan warna biru. Permintaan menggunakan ConditionType sebagai type untuk BooleanRule.

Protokol permintaan ditampilkan di bawah.

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:

Hasil resep format bersyarat teks dan tanggal

Menambahkan aturan formula kustom ke rentang

Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan format bersyarat baru untuk rentang B5:B8 dalam sheet, berdasarkan formula kustom. Aturan ini menghitung hasil perkalian sel di kolom A dan B. Jika produk lebih besar dari 120, teks sel akan disetel ke tebal dan miring. Permintaan menggunakan ConditionType sebagai type untuk BooleanRule.

Protokol permintaan ditampilkan di bawah.

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:

Hasil resep format bersyarat kustom

Menghapus aturan format bersyarat

Contoh kode metode spreadsheets.batchUpdate berikut menunjukkan cara menggunakan DeleteConditionalFormatRuleRequest untuk menghapus aturan format bersyarat dengan indeks 0 di sheet yang ditentukan oleh SHEET_ID.

Protokol permintaan ditampilkan di bawah.

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

Membaca 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.

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

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 ada ke indeks yang lebih tinggi (dari 0 ke 2, sehingga mengurangi prioritasnya). Permintaan kedua menggantikan aturan format bersyarat pada indeks 0 dengan aturan baru yang memformat sel yang berisi teks persis yang ditentukan ("Total Biaya") dalam rentang A1:D5. Perpindahan 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.

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