Google Apps Script: Как Установить Условное Форматирование?

Что такое условное форматирование и зачем оно нужно?

Условное форматирование в Google Таблицах — это мощный инструмент визуализации, позволяющий автоматически изменять внешний вид ячеек (фон, цвет текста, стиль шрифта) на основе заданных условий или критериев. Это помогает быстро выявлять тенденции, аномалии, важные значения или ошибки в больших объемах данных, делая анализ более интуитивным и эффективным.

Саммари статьи

ChatGPT

Google AI

Grok

Perplexity

Использование Google Apps Script для управления условным форматированием открывает двери для динамической и сложной автоматизации, недоступной через стандартный пользовательский интерфейс. С помощью скриптов можно создавать, изменять и удалять правила форматирования программно, реагируя на события, внешние данные или сложные вычисления.

Основные понятия: правила, диапазоны, критерии

При работе с условным форматированием через Apps Script ключевыми являются следующие концепции:

• Правило (Conditional Format Rule): Объект, инкапсулирующий логику форматирования. Каждое правило содержит критерии и спецификации форматирования (цвет фона, текста и т.д.).
• Диапазон (Range): Область ячеек на листе, к которой применяется правило условного форматирования.
• Критерий (Boolean Criteria): Условие, которое должно быть выполнено для применения форматирования. Критерии могут быть основаны на значении ячейки (например, больше 10, содержит текст «Error»), дате, или результатах пользовательской формулы.
• Конструктор правил (ConditionalFormatRuleBuilder): Интерфейс в Apps Script для пошагового создания и настройки нового правила условного форматирования.

Преимущества использования Apps Script для управления условным форматированием

Использование скриптов предоставляет значительные преимущества:

• Автоматизация: Создание и обновление правил без ручного вмешательства, например, при добавлении новых данных или изменении структуры таблицы.
• Сложная логика: Реализация условий, основанных на данных из других листов, внешних источников или сложных вычислениях, которые невозможно задать через стандартный интерфейс.
• Динамичность: Адаптация правил форматирования в реальном времени в ответ на действия пользователя или изменения данных.
• Масштабируемость: Эффективное управление большим количеством правил и диапазонов в сложных таблицах.
• Интеграция: Встраивание логики условного форматирования в более крупные рабочие процессы, управляемые Apps Script.

Установка условного форматирования с помощью Apps Script: Базовый пример

Рассмотрим процесс создания простого правила: выделение ячеек с числовыми значениями больше 50 зеленым фоном.

Получение доступа к электронной таблице и листу

/**
 * Получает активный лист в текущей электронной таблице.
 * @returns {GoogleAppsScript.Spreadsheet.Sheet} Активный лист.
 */
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  if (!sheet) {
    throw new Error("Не удалось получить активный лист.");
  }
  return sheet;
}

Создание правила условного форматирования: цвет фона ячейки

Для создания правила используется ConditionalFormatRuleBuilder, получаемый через SpreadsheetApp.newConditionalFormatRule().

/**
 * Создает базовое правило условного форматирования.
 * @returns {GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder} Конструктор правил.
 */
function createBaseRule(): GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder {
  return SpreadsheetApp.newConditionalFormatRule();
}

Настройка критерия: пример с числовым значением

Используем метод whenNumberGreaterThan() для установки критерия и setBackground() для задания цвета фона.

/**
 * Настраивает правило для выделения ячеек с числами > 50.
 * @param {GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder} ruleBuilder - Конструктор правил.
 * @returns {GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder} Настроенный конструктор.
 */
function setNumericCriteria(ruleBuilder: GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder): GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder {
  return ruleBuilder
    .whenNumberGreaterThan(50) // Критерий: число больше 50
    .setBackground("#d9ead3") // Установить светло-зеленый фон
    .setFontColor("#274e13"); // Установить темно-зеленый цвет текста
}

Применение правила к диапазону ячеек

Правило применяется к одному или нескольким диапазонам с помощью метода setRanges() и финализируется вызовом build(). Затем правило добавляется к листу.

/**
 * Применяет созданное правило условного форматирования к диапазону A1:C10.
 */
function applyBasicConditionalFormatting(): void {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange("A1:C10");

  // 1. Создаем конструктор
  let ruleBuilder: GoogleAppsScript.Spreadsheet.ConditionalFormatRuleBuilder = createBaseRule();

  // 2. Настраиваем критерий и формат
  ruleBuilder = setNumericCriteria(ruleBuilder);

  // 3. Устанавливаем диапазон
  ruleBuilder = ruleBuilder.setRanges([range]);

  // 4. Собираем правило
  const rule: GoogleAppsScript.Spreadsheet.ConditionalFormatRule = ruleBuilder.build();

  // 5. Добавляем правило на лист
  const rules: GoogleAppsScript.Spreadsheet.ConditionalFormatRule[] = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

  Logger.log("Правило условного форматирования успешно добавлено.");
}

Расширенные возможности: Настройка сложных правил

Apps Script позволяет выходить за рамки простых числовых сравнений.

Использование формул для критериев: =A1>10

Метод whenFormulaSatisfied() позволяет использовать пользовательские формулы Google Таблиц в качестве критериев. Формула должна возвращать TRUE или FALSE. Обратите внимание, что ссылка на ячейку в формуле (например, A1) должна соответствовать левой верхней ячейке первого диапазона, к которому применяется правило.

/**
 * Создает правило на основе пользовательской формулы.
 * Выделяет строки, где значение в колонке B больше значения в колонке C.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист для применения правила.
 * @param {string} rangeNotation - Диапазон в формате A1.
 */
function applyFormulaBasedRule(sheet: GoogleAppsScript.Spreadsheet.Sheet, rangeNotation: string): void {
  const range = sheet.getRange(rangeNotation); // Например, "A1:D100"
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("= $B1 > $C1") // Формула для первой строки диапазона
    .setBackground("#fce5cd") // Светло-оранжевый фон
    .setRanges([range])
    .build();

  const rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

Применение нескольких условий к одному диапазону

К одному и тому же диапазону можно применить несколько правил условного форматирования. Порядок применения правил имеет значение: если ячейка удовлетворяет критериям нескольких правил, будет применено форматирование последнего добавленного (или верхнего в списке правил в интерфейсе) правила, которое не конфликтует с предыдущими.

Изменение формата текста, границ и других параметров

ConditionalFormatRuleBuilder предоставляет методы для тонкой настройки внешнего вида:

• setFontColor(cssColor: string): Цвет текста.
• setBold(bold: boolean): Полужирное начертание.
• setItalic(italic: boolean): Курсивное начертание.
• setStrikethrough(strikethrough: boolean): Зачеркнутый текст.
• setUnderline(underline: boolean): Подчеркнутый текст.
• Примечание: Установка границ через Apps Script для условного форматирования на данный момент не поддерживается напрямую через ConditionalFormatRuleBuilder. Для этого может потребоваться отдельная логика форматирования ячеек.

Работа с разными типами данных: текст, даты, логические значения

Конструктор правил предлагает методы для различных типов данных:

• Текст: whenTextContains(text: string), whenTextEqualTo(text: string), whenTextStartsWith(prefix: string), whenTextEndsWith(suffix: string), whenTextDoesNotContain(text: string).
• Даты: whenDateBefore(date: Date | string), whenDateAfter(date: Date | string), whenDateEqualTo(date: Date | string), whenDateBefore(relativeDate: SpreadsheetApp.RelativeDate), whenDateAfter(relativeDate: SpreadsheetApp.RelativeDate), whenDateEqualTo(relativeDate: SpreadsheetApp.RelativeDate). RelativeDate включает опции типа TODAY, TOMORROW, YESTERDAY, PAST_WEEK и т.д.
• Числа: Кроме whenNumberGreaterThan(), есть whenNumberLessThan(), whenNumberEqualTo(), whenNumberBetween(), whenNumberNotBetween(), whenNumberNotEqualTo().
• Пустые/непустые ячейки: whenCellEmpty(), whenCellNotEmpty().

Удаление и изменение существующих правил условного форматирования

Apps Script позволяет не только создавать, но и управлять жизненным циклом правил.

Получение списка всех правил условного форматирования для листа

Метод sheet.getConditionalFormatRules() возвращает массив всех правил, примененных к листу.

/**
 * Получает все правила условного форматирования для указанного листа.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @returns {GoogleAppsScript.Spreadsheet.ConditionalFormatRule[]} Массив правил.
 */
function getAllRules(sheet: GoogleAppsScript.Spreadsheet.Sheet): GoogleAppsScript.Spreadsheet.ConditionalFormatRule[] {
  return sheet.getConditionalFormatRules();
}

Удаление правила по индексу

Правила хранятся в массиве. Зная индекс нужного правила, его можно удалить из массива и обновить правила на листе.

/**
 * Удаляет правило условного форматирования по его индексу.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @param {number} index - Индекс правила для удаления (начиная с 0).
 */
function deleteRuleByIndex(sheet: GoogleAppsScript.Spreadsheet.Sheet, index: number): void {
  const rules: GoogleAppsScript.Spreadsheet.ConditionalFormatRule[] = sheet.getConditionalFormatRules();
  if (index >= 0 && index < rules.length) {
    rules.splice(index, 1); // Удаляем правило из массива
    sheet.setConditionalFormatRules(rules); // Обновляем правила на листе
    Logger.log(`Правило с индексом ${index} удалено.`);
  } else {
    Logger.log(`Правило с индексом ${index} не найдено.`);
  }
}

Изменение критериев и формата существующего правила

Чтобы изменить существующее правило, необходимо:

1. Получить копию правила с помощью метода copy().
2. Использовать copy().copy() для получения ConditionalFormatRuleBuilder из существующего правила.
3. Внести изменения с помощью методов конструктора (whenNumberGreaterThan, setBackground, setRanges и т.д.).
4. Построить новое, измененное правило с помощью build().
5. Заменить старое правило в массиве правил новым.
6. Обновить правила на листе с помощью setConditionalFormatRules().

/**
 * Изменяет фон существующего правила по индексу.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @param {number} ruleIndex - Индекс правила для изменения.
 * @param {string} newBackgroundColor - Новый цвет фона в формате CSS (#RRGGBB).
 */
function modifyRuleBackground(sheet: GoogleAppsScript.Spreadsheet.Sheet, ruleIndex: number, newBackgroundColor: string): void {
  const rules = sheet.getConditionalFormatRules();
  if (ruleIndex < 0 || ruleIndex >= rules.length) {
    Logger.log(`Правило с индексом ${ruleIndex} не найдено.`);
    return;
  }

  const originalRule = rules[ruleIndex];
  // Создаем изменяемую копию правила
  const ruleBuilder = originalRule.copy(); 
  // Вносим изменения
  ruleBuilder.setBackground(newBackgroundColor);
  // Собираем измененное правило
  const modifiedRule = ruleBuilder.build(); 

  // Заменяем старое правило новым в массиве
  rules[ruleIndex] = modifiedRule;
  // Обновляем правила на листе
  sheet.setConditionalFormatRules(rules);
  Logger.log(`Фон правила с индексом ${ruleIndex} изменен на ${newBackgroundColor}.`);
}

Оптимизация работы с большим количеством правил

При работе с множеством правил важно минимизировать количество вызовов getConditionalFormatRules() и setConditionalFormatRules(), так как это относительно ресурсоемкие операции.

• Получите все правила один раз в начале.
• Выполните все необходимые добавления, удаления и модификации в локальном массиве правил.
• Установите обновленный массив правил один раз в конце.
• По возможности объединяйте правила, если они используют одинаковое форматирование, но разные критерии, применяемые к одному диапазону (хотя Apps Script не предоставляет прямого API для «группировки» критериев в одно правило, как в UI, можно стратегически создавать правила).

Практические примеры и решения

Автоматическое выделение строк с просроченными датами

Задача: В колонке ‘D’ указаны сроки выполнения задач. Необходимо выделить красным строки, где срок уже прошел.

/**
 * Выделяет строки с просроченными датами в колонке D.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @param {string} rangeNotation - Диапазон для проверки (например, "A2:E100").
 */
function highlightOverdueTasks(sheet: GoogleAppsScript.Spreadsheet.Sheet, rangeNotation: string): void {
  const range = sheet.getRange(rangeNotation);
  const rule = SpreadsheetApp.newConditionalFormatRule()
    // Критерий: дата в колонке D меньше СЕГОДНЯ и ячейка D не пуста
    .whenFormulaSatisfied("=AND($D1<TODAY(), $D1<>"")") 
    .setBackground("#f4cccc") // Светло-красный фон
    .setFontColor("#cc0000") // Темно-красный текст
    .setRanges([range])
    .build();

  const rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

Подсветка ячеек, содержащих определенный текст

Задача: В отчете о рекламной кампании выделить ячейки в колонке ‘B’, содержащие слово «Error» или «Warning».

/**
 * Выделяет ячейки в диапазоне, содержащие "Error" или "Warning".
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @param {string} rangeNotation - Диапазон для проверки (например, "B2:B500").
 */
function highlightKeywords(sheet: GoogleAppsScript.Spreadsheet.Sheet, rangeNotation: string): void {
  const range = sheet.getRange(rangeNotation);

  // Правило для "Error"
  const errorRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextContains("Error")
    .setBackground("#ea9999") // Красный фон
    .setBold(true)
    .setRanges([range])
    .build();

  // Правило для "Warning"
  const warningRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextContains("Warning")
    .setBackground("#fff2cc") // Желтый фон
    .setRanges([range])
    .build();

  const rules = sheet.getConditionalFormatRules();
  rules.push(errorRule, warningRule); // Добавляем оба правила
  sheet.setConditionalFormatRules(rules);
}

Визуализация данных с использованием цветовой шкалы

Задача: Применить градиентную заливку (цветовую шкалу) к диапазону числовых данных, например, для визуализации KPI.

Apps Script позволяет создавать градиентные правила с помощью GradientCriteria.

/**
 * Применяет градиентную цветовую шкалу (зеленый-желтый-красный) к диапазону.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
 * @param {string} rangeNotation - Диапазон числовых данных (например, "E2:E100").
 */
function applyColorScale(sheet: GoogleAppsScript.Spreadsheet.Sheet, rangeNotation: string): void {
  const range = sheet.getRange(rangeNotation);

  const rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMaxpoint("#f4cccc") // Красный для максимальных значений
    .setGradientMidpointWithValue("#fff2cc", SpreadsheetApp.InterpolationType.PERCENTILE, "50") // Желтый для медианы (50-й процентиль)
    .setGradientMinpoint("#d9ead3") // Зеленый для минимальных значений
    .setRanges([range])
    .build();

  const rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

Применение условного форматирования на основе данных из других листов

Задача: На листе ‘Отчет’ выделить строки, ID которых присутствуют в списке ‘Проблемные ID’ на листе ‘Справочник’.

Это классический случай использования whenFormulaSatisfied со ссылкой на другой лист.

/**
 * Выделяет строки на активном листе, если их ID (колонка A) 
 * найден в диапазоне A:A на листе 'Справочник'.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} reportSheet - Лист 'Отчет'.
 * @param {string} reportRangeNotation - Диапазон на листе 'Отчет' (например, "A2:F100").
 * @param {string} referenceSheetName - Имя листа со справочником ID (например, "Справочник").
 */
function highlightBasedOnReference(reportSheet: GoogleAppsScript.Spreadsheet.Sheet, reportRangeNotation: string, referenceSheetName: string): void {
  const range = reportSheet.getRange(reportRangeNotation);
  // Формула проверяет наличие значения из A1 (первой ячейки диапазона) 
  // в колонке A листа 'Справочник'
  const formula = `=COUNTIF('${referenceSheetName}'!$A:$A, $A1)>0`; 

  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(formula)
    .setBackground("#cfe2f3") // Светло-синий фон
    .setRanges([range])
    .build();

  const rules = reportSheet.getConditionalFormatRules();
  rules.push(rule);
  reportSheet.setConditionalFormatRules(rules);
}

Используя Google Apps Script, вы можете гибко и мощно управлять условным форматированием, автоматизируя визуализацию данных и создавая сложные, динамические правила, которые значительно повышают информативность ваших Google Таблиц.