Условное форматирование

API Google Sheets позволяет создавать и обновлять правила условного форматирования в электронных таблицах. С помощью условного форматирования можно управлять только определенными типами форматирования (полужирный, курсив, зачеркивание, цвет текста и цвет фона). Примеры на этой странице иллюстрируют, как выполнять распространенные операции условного форматирования с помощью API Sheets.

Эти примеры представлены в виде HTTP-запросов, чтобы обеспечить языковую нейтральность. Чтобы узнать, как реализовать пакетное обновление на разных языках с помощью клиентских библиотек Google API, см. раздел «Обновление электронных таблиц» .

В этих примерах заполнители SPREADSHEET_ID и SHEET_ID указывают, где следует указывать эти идентификаторы. Идентификатор электронной таблицы можно найти в URL-адресе электронной таблицы. Идентификатор листа можно получить, используя метод spreadsheets.get . Диапазоны указываются с использованием обозначения A1 . Пример диапазона: Sheet1!A1:D5.

Добавьте условный цветовой градиент по всей строке.

Приведенный ниже пример кода метода spreadsheets.batchUpdate демонстрирует, как использовать AddConditionalFormatRuleRequest для установки новых правил условного форматирования градиента для строк 10 и 11 листа. Первое правило гласит, что цвет фона ячеек в этой строке устанавливается в соответствии с их значением. Самое низкое значение в строке окрашивается в темно-красный цвет, а самое высокое — в ярко-зеленый. Цвет остальных значений интерполируется. Второе правило делает то же самое, но с конкретными числовыми значениями, определяющими конечные точки градиента (и разные цвета). Запрос использует sheets.InterpolationPointType в качестве type .

Протокол запроса представлен ниже.

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, поэтому оно имеет приоритет над другими правилами форматирования. В запросе в качестве type для BooleanRule используется 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" (регистр нечувствителен), первое правило делает текст в ячейке жирным. Если ячейка содержит дату, предшествующую прошлой неделе, второе правило делает текст в ячейке курсивом и окрашивает его в синий цвет. Запрос использует ConditionType в качестве type для BooleanRule .

Протокол запроса представлен ниже.

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

После запроса примененное правило форматирования обновляет таблицу. В этом примере текущая дата — 26.09.2016:

Условное форматирование текста и даты. Результат рецепта.

Добавьте пользовательское правило формулы в диапазон

Приведенный ниже пример кода метода spreadsheets.batchUpdate демонстрирует, как использовать AddConditionalFormatRuleRequest для создания нового правила условного форматирования для диапазона B5:B8 на листе на основе пользовательской формулы. Правило вычисляет произведение ячеек в столбцах A и B. Если произведение больше 120, текст в ячейке становится полужирным и курсивным. В запросе в качестве type для BooleanRule используется 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 для удаления правила условного форматирования с индексом 0 на листе, указанном параметром SHEET_ID .

Протокол запроса представлен ниже.

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 , содержащий массив объектов Sheet , каждый из которых имеет элемент SheetProperties , и массив элементов ConditionalFormatRule . Если для какого-либо поля ответа установлено значение по умолчанию, оно исключается из ответа. В запросе в качестве type для BooleanRule используется 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 , уменьшая его приоритет). Второй запрос заменяет правило условного форматирования с индексом 0 новым правилом, которое форматирует ячейки, содержащие точно указанный текст ("Total Cost") в диапазоне A1:D5. Перемещение, выполненное первым запросом, завершается до начала второго, поэтому второй запрос заменяет правило, которое изначально находилось с индексом 1 В запросе в качестве type для BooleanRule используется 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
              }
            }
          }
        }
      }
    }
  ]
}