Format bersyarat

Google Sheets API memungkinkan Anda membuat dan memperbarui aturan format bersyarat di {i>spreadsheet<i}. Hanya jenis pemformatan tertentu (tebal, miring, dicoret, warna latar depan, dan warna latar belakang) dapat dikontrol melalui kondisional format font. Contoh di halaman ini menggambarkan cara mencapai operasi pemformatan bersyarat dengan Sheets API.

Contoh ini disajikan sebagai permintaan HTTP agar tidak menggunakan bahasa yang netral. Untuk mempelajari cara mengimplementasikan update secara batch dalam berbagai bahasa menggunakan Google API library klien, lihat Memperbarui di spreadsheet.

Dalam contoh ini, placeholder SPREADSHEET_ID dan SHEET_ID menunjukkan di mana Anda akan memberikan ID tersebut. Anda dapat menemukan spreadsheet ID di URL spreadsheet. Anda bisa mendapatkan sheet ID dengan menggunakan Metode spreadsheets.get. Tujuan rentang ditentukan menggunakan notasi A1. Channel contohnya adalah Sheet1!A1:D5.

Menambahkan gradasi warna bersyarat pada baris

Hal berikut spreadsheets.batchUpdate contoh kode metode menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk menetapkan aturan format bersyarat gradien baru untuk baris 10 dan 11 dari {i>sheet<i}. Aturan pertama menyatakan bahwa sel-sel di baris tersebut memiliki warna latar belakang yang ditetapkan sesuai dengan nilainya. Nilai terendah dalam baris berwarna merah tua, sedangkan nilai tertinggi warnanya hijau terang. Warna nilai lain diinterpolasi. Aturan kedua melakukan hal yang sama, tetapi dengan nilai numerik tertentu menentukan endpoint gradien (dan warna yang berbeda). Permintaan menggunakan ID 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 maxpoint warna:

Tambahkan hasil resep format gradien

Menambahkan aturan format bersyarat ke kumpulan rentang

Hal berikut spreadsheets.batchUpdate contoh kode metode menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan {i>conditional formatting<i} baru untuk kolom A dan C pada {i>sheet<i}. Aturan tersebut menyatakan bahwa sel dengan nilai 10 atau kurang memiliki latar belakang warna berubah menjadi merah tua. Aturan dimasukkan pada indeks 0, sehingga diperlukan diprioritaskan dibandingkan aturan pemformatan lainnya. Permintaan menggunakan ID 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:

Tambahkan hasil resep format bersyarat

Menambahkan aturan format bersyarat tanggal dan teks ke rentang

Hal berikut spreadsheets.batchUpdate contoh kode metode menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk menetapkan aturan format bersyarat baru untuk rentang A1:D5 di sheet, berdasarkan nilai tanggal dan teks dalam sel-sel tersebut. Jika teks mengandung {i>string<i} "Biaya" (tidak peka huruf besar/kecil), aturan pertama menetapkan teks sel sebagai tebal. Jika sel berisi tanggal yang terjadi sebelum seminggu terakhir, aturan kedua menyetel teks sel sebagai miring dan memberi warna biru. Permintaan menggunakan ID 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:

Hasil resep format bersyarat teks dan tanggal

Menambahkan aturan formula kustom ke rentang

Hal berikut spreadsheets.batchUpdate contoh kode metode menunjukkan cara menggunakan AddConditionalFormatRuleRequest untuk membuat aturan format bersyarat baru untuk rentang B5:B8 di {i>sheet<i}, berdasarkan formula kustom. Aturan tersebut menghitung produk dari sel dalam kolom A dan B. Jika produk lebih besar dari 120, teks sel diatur ke tebal dan miring. Permintaan menggunakan ID 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:

Hasil urutan langkah format bersyarat kustom

Menghapus aturan format bersyarat

Hal berikut spreadsheets.batchUpdate contoh kode metode 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

Hal berikut Kode metode spreadsheets.get contoh menunjukkan cara mendapatkan judul, SHEET_ID, dan daftar semuanya aturan format bersyarat untuk setiap sheet dalam spreadsheet. Kueri fields menentukan data apa 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 Spreadsheet, yang berisi array Objek Sheet yang masing-masing memiliki suatu SheetProperties dan array ConditionalFormatRule yang kurang penting. Jika kolom respons yang diberikan ditetapkan ke nilai default, kolom tersebut akan dihilangkan dari respons. Permintaan menggunakan ID 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

Hal berikut spreadsheets.batchUpdate contoh kode metode menunjukkan cara menggunakan UpdateConditionalFormatRuleRequest dengan beberapa permintaan. Permintaan pertama memindahkan format bersyarat yang ada aturan ke indeks yang lebih tinggi (dari 0 menjadi 2, yang akan menurunkan prioritasnya). Yang kedua permintaan 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. Pemindahan permintaan pertama selesai sebelum perpindahan permintaan kedua dimulai, jadi permintaan kedua menggantikan aturan yang awalnya ada di indeks 1. Tujuan 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": [
    {
      "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
              }
            }
          }
        }
      }
    }
  ]
}