Google Apps Script: Как установить правило проверки данных?

Что такое правила проверки данных и зачем они нужны?

Правила проверки данных в Google Sheets — это механизм, позволяющий контролировать тип и значение данных, вводимых пользователями в ячейки. Они необходимы для обеспечения целостности и точности данных, предотвращения ошибок ввода и стандартизации информации в таблицах.

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

Обзор возможностей Google Apps Script для работы с проверкой данных

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

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

Основные способы установки правил проверки данных с помощью Google Apps Script

Для создания правил проверки используется класс DataValidationBuilder, получаемый из объекта Range методом newDataValidation(). После настройки необходимых критериев правило применяется к диапазону с помощью метода setDataValidation(rule).

Установка простых правил (число, текст, дата)

Apps Script позволяет легко устанавливать базовые критерии проверки:

Числа: Ограничение ввода числами в заданном диапазоне (requireNumberBetween, requireNumberEqualTo, requireNumberGreaterThan, etc.).

Текст: Проверка на содержание определенного текста (requireTextContains), соответствие шаблону (requireTextMatchesPattern), или проверку на валидный URL/Email (requireTextIsUrl, requireTextIsEmail).

Дата: Ограничение ввода корректными датами, до или после определенной даты (requireDate, requireDateBefore, requireDateOnOrAfter, etc.).

Установка правил из списка значений

Часто требуется ограничить ввод значениями из предопределенного списка. Это реализуется с помощью requireValueInList(values, showDropdown), где values — массив строк, а showDropdown — булево значение, определяющее, отображать ли выпадающий список в ячейке.

Также возможно использовать значения из диапазона ячеек с помощью requireValueInRange(range, showDropdown). Это удобно для динамически обновляемых списков.

Установка правил с использованием пользовательских формул

Для сложных сценариев проверки можно использовать пользовательские формулы (requireFormulaSatisfied(formula)). Формула должна возвращать TRUE, если введенное значение корректно, и FALSE в противном случае. Это позволяет реализовывать зависимые проверки, сравнивать значения с другими ячейками и использовать встроенные функции Google Sheets.

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

Ниже приведены примеры кода с использованием JSDoc для аннотаций типов и комментариев.

Пример 1: Проверка числового значения в диапазоне

Предположим, нам нужно убедиться, что в ячейках столбца B (начиная со второй строки) вводятся только значения CTR (Click-Through Rate) в диапазоне от 0 до 1 (или 0% до 100%).

/**
 * Устанавливает правило проверки данных для столбца B,
 * разрешая ввод только чисел от 0 до 1.
 */
function setCtrValidationRule() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = sheet.getRange('B2:B'); // Применяем ко всему столбцу B, начиная со строки 2

  // Создаем правило: число должно быть между 0 и 1
  const rule = SpreadsheetApp.newDataValidation()
      .requireNumberBetween(0, 1)
      .setAllowInvalid(false) // Запрещаем ввод невалидных значений
      .setHelpText('Введите значение CTR от 0 до 1 (например, 0.05 для 5%).')
      .build();

  range.setDataValidation(rule);
  Logger.log('Правило проверки CTR установлено для диапазона %s', range.getA1Notation());
}

Пример 2: Проверка текстового значения на соответствие шаблону

Допустим, в столбце C необходимо указывать UTM-метку источника (utm_source), и мы хотим ограничить возможные значения стандартными (‘google’, ‘yandex’, ‘vk’, ‘facebook’).

/**
 * Устанавливает правило проверки для столбца C,
 * разрешая ввод только из предопределенного списка источников.
 */
function setUtmSourceValidation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = sheet.getRange('C2:C');

  const allowedSources = ['google', 'yandex', 'vk', 'facebook', 'email'];

  // Создаем правило: значение должно быть в списке
  const rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(allowedSources, true) // true - показать выпадающий список
      .setAllowInvalid(false)
      .setHelpText('Выберите источник трафика из списка.')
      .build();

  range.setDataValidation(rule);
  Logger.log('Правило проверки UTM Source установлено для диапазона %s', range.getA1Notation());
}

Пример 3: Выбор значения из выпадающего списка

Предположим, у нас есть лист ‘Settings’, где в диапазоне A1:A5 хранятся статусы рекламных кампаний (‘Active’, ‘Paused’, ‘Archived’, ‘Pending’, ‘Ended’). Мы хотим использовать эти статусы для проверки данных в столбце D основного листа.

/**
 * Устанавливает правило проверки для столбца D,
 * используя список значений из другого листа ('Settings').
 */
function setCampaignStatusValidation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getActiveSheet();
  const settingsSheet = ss.getSheetByName('Settings');

  if (!settingsSheet) {
    Logger.log('Лист "Settings" не найден.');
    SpreadsheetApp.getUi().alert('Лист "Settings" не найден. Правило не установлено.');
    return;
  }

  const statusesRange = settingsSheet.getRange('A1:A5');
  const targetRange = mainSheet.getRange('D2:D');

  // Создаем правило: значение должно быть в диапазоне на листе 'Settings'
  const rule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(statusesRange, true) // true - показать выпадающий список
      .setAllowInvalid(false)
      .setHelpText('Выберите статус кампании из списка.')
      .build();

  targetRange.setDataValidation(rule);
  Logger.log('Правило проверки статуса кампании установлено для диапазона %s', targetRange.getA1Notation());
}

Продвинутые техники и советы

Динамическое изменение правил проверки данных

Правила проверки, основанные на диапазонах (requireValueInRange), автоматически обновляются при изменении данных в исходном диапазоне. Однако, если сама логика проверки должна меняться (например, список допустимых значений зависит от выбора в другой ячейке), потребуется скрипт, который будет перестраивать и применять новое правило DataValidation при изменении зависимой ячейки (например, с помощью триггера onEdit).

Обработка ошибок и пользовательские сообщения об ошибках

Метод .setAllowInvalid(false) запрещает ввод некорректных данных. Если установлено значение true (по умолчанию), невалидные данные будут допущены, но ячейка будет помечена визуально.

Метод .setHelpText(text) позволяет задать подсказку, которая отображается при выделении ячейки. Это отличный способ предоставить пользователю контекст или инструкции по заполнению.

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

Для автоматического применения правил проверки к новым строкам или при открытии документа можно использовать триггеры Google Apps Script:

onOpen(e): Устанавливает или обновляет правила при открытии таблицы. Подходит для статических правил.

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

onChange(e): Более общий триггер, срабатывающий на различные изменения структуры или данных листа (вставка строк, удаление и т.д.). Может использоваться для применения правил к новым строкам.

Заключение

Краткое содержание и ключевые выводы

Google Apps Script предоставляет гибкие и мощные средства для управления правилами проверки данных в Google Sheets. Использование DataValidationBuilder позволяет автоматизировать настройку правил, применять сложные критерии и создавать динамические проверки.

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

Дополнительные ресурсы

Для более глубокого изучения рекомендуется обратиться к официальной документации Google Apps Script, в частности к разделам, посвященным сервису SpreadsheetApp и классу DataValidationBuilder. Поиск по конкретным методам (requireNumberBetween, requireValueInList и т.д.) также предоставит детальные примеры и описания. Экспериментирование с различными критериями и настройками поможет лучше понять их возможности.