条件付き書式

Google Sheets API を使用すると、スプレッドシートの条件付き書式ルールを作成、更新できます。条件付き書式で制御できるのは、特定の書式タイプ(太字、斜体、取り消し線、前景の色、背景色)のみです。このページの例は、Sheets API を使用して一般的な条件付き書式オペレーションを実行する方法を示しています。

これらの例では、言語に依存しない HTTP リクエストとして記載しています。Google API クライアント ライブラリを使用してさまざまな言語でバッチ アップデートを実装する方法については、スプレッドシートを更新するをご覧ください。

これらの例では、プレースホルダ SPREADSHEET_IDSHEET_ID は、これらの ID を指定する場所を示しています。スプレッドシート ID はスプレッドシートの URL で確認できます。シート ID は、spreadsheets.get メソッドを使用して取得できます。範囲は A1 表記で指定します。範囲の例は Sheet1!A1:D5 です。

行全体に条件付きカラー グラデーションを追加する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、AddConditionalFormatRuleRequest を使用して、シートの行 10 と 11 に新しいグラデーションの条件付き書式ルールを設定する方法を示しています。最初のルールは、その行のセルの値に応じて背景色を設定します。行の最小値は濃い赤色、最大値は明るい緑色になります。他の値の色は補間されます。2 番目のルールでも同じことを行いますが、特定の数値を使用して、グラデーションのエンドポイント(および異なる色)を決定します。このリクエストでは、sheets.InterpolationPointTypetype として使用します。

リクエスト プロトコルを以下に示します。

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

リクエストの後に、適用された書式ルールによってシートが更新されます。11 行目のグラデーションでは maxpoint が 256 に設定されているため、それより上の値はすべて maxpoint カラーになります。

グラデーション形式のレシピの結果を追加

一連の範囲に条件付き書式ルールを追加する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、AddConditionalFormatRuleRequest を使用して、シートの A 列と C 列に新しい条件付き書式ルールを設定する方法を示しています。このルールでは、値が 10 以下のセルの背景色を濃い赤色に変えます。このルールはインデックス 0 に挿入されるため、他の書式設定ルールよりも優先されます。このリクエストでは、BooleanRuletype として ConditionType を使用します。

リクエスト プロトコルを以下に示します。

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

リクエストの後に、適用された書式ルールによってシートが更新されます。

条件付き書式レシピの結果を追加

範囲に日付とテキストの条件付き書式ルールを追加する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、AddConditionalFormatRuleRequest を使用して、セルの日付とテキストの値に基づいて、シートの範囲 A1:D5 に新しい条件付き書式ルールを設定する方法を示しています。テキストに文字列「Cost」(大文字と小文字を区別しない)が含まれている場合は、最初のルールによってセルのテキストを太字に設定します。セルに過去 1 週間より前の日付が含まれている場合、2 番目のルールによってセルのテキストが斜体に設定され、青色になります。このリクエストでは、BooleanRuletype として ConditionType を使用します。

リクエスト プロトコルを以下に示します。

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

リクエストの後に、適用された書式ルールによってシートが更新されます。この例では、現在の日付は 2016 年 9 月 26 日です。

テキストと日付の条件付き書式レシピの結果

範囲にカスタム数式ルールを追加する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、AddConditionalFormatRuleRequest を使用して、カスタム数式に基づいてシート内の B5:B8 の範囲に新しい条件付き書式ルールを設定する方法を示しています。このルールでは、列 A と列 B のセルの積を計算します。積が 120 より大きい場合、セルのテキストは太字の斜体に設定されます。このリクエストでは、BooleanRuletype として ConditionType を使用します。

リクエスト プロトコルを以下に示します。

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

リクエストの後に、適用された書式ルールによってシートが更新されます。

カスタム条件付き書式レシピの結果

条件付き書式ルールを削除する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、DeleteConditionalFormatRuleRequest を使用して、SHEET_ID で指定されたシートのインデックス 0 を持つ条件付き書式ルールを削除する方法を示しています。

リクエスト プロトコルを以下に示します。

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

条件付き書式ルールの一覧を読む

次の spreadsheets.get メソッドのコードサンプルは、スプレッドシートの各シートのタイトル、SHEET_ID、すべての条件付き書式ルールのリストを取得する方法を示しています。fields クエリ パラメータは、返されるデータを決定します。

リクエスト プロトコルを以下に示します。

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties(title,sheetId),conditionalFormats)

レスポンスは Spreadsheet リソースで構成されます。このリソースには、SheetProperties 要素を持つ Sheet オブジェクトの配列と、ConditionalFormatRule 要素の配列が含まれます。特定のレスポンス フィールドがデフォルト値に設定されている場合、そのフィールドはレスポンスから除外されます。このリクエストでは、BooleanRuletype として ConditionType を使用します。

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

条件付き書式ルールまたはその優先度を更新する

次の spreadsheets.batchUpdate メソッドのコードサンプルは、複数のリクエストで UpdateConditionalFormatRuleRequest を使用する方法を示しています。最初のリクエストでは、既存の条件付き書式ルールを上位のインデックスに移動します(0 から 2 にし、優先度を下げます)。2 番目のリクエストでは、インデックス 0 の条件付き書式ルールが、A1:D5 の範囲で指定された正確なテキスト(「Total Cost」)を含むセルを書式設定する新しいルールに置き換えられました。最初のリクエストの移動は 2 番目のリクエストが始まる前に完了しているため、2 番目のリクエストでインデックス 1 にあったルールが置き換えられます。このリクエストでは、BooleanRuletype として ConditionType を使用します。

リクエスト プロトコルを以下に示します。

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