Что такое событие onEdit в Google Apps Script и как его использовать?

Что такое Google Apps Script и для чего он используется

Google Apps Script (GAS) – это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность приложений Google Workspace, таких как Google Sheets, Docs, Forms, Calendar и Gmail. Он позволяет интегрировать эти приложения друг с другом и с внешними сервисами, создавая мощные автоматизированные рабочие процессы. Вместо рутинных операций, можно писать код, который выполняет задачи по расписанию, реагирует на события (например, изменение ячейки в таблице) или взаимодействует с другими системами через API.

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

ChatGPT

Google AI

Grok

Perplexity

Обзор триггеров в Google Apps Script (простые и устанавливаемые)

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

• Простые триггеры: Встроены непосредственно в редактор скриптов и имеют ограничение по функциональности. Например, onOpen, onEdit, onInstall, onFormSubmit. Они срабатывают автоматически при наступлении определенного события, но имеют ограничения по авторизации и длительности выполнения.
• Устанавливаемые триггеры: Предоставляют большую гибкость и контроль. Их необходимо создавать программно (через код) или вручную через пользовательский интерфейс. Они могут запускаться от имени пользователя, установившего триггер, и имеют меньше ограничений, чем простые триггеры. Пример: ScriptApp.newTrigger('myFunction').forSpreadsheet(spreadsheetId).onEdit().create().

Определение события onEdit и его место среди триггеров

Событие onEdit — это простой триггер, который автоматически запускает функцию Apps Script, когда пользователь изменяет какие-либо данные в Google Sheets. Функция-обработчик onEdit(e) получает объект события e, содержащий информацию об изменении. Этот триггер полезен для автоматизации задач, связанных с вводом данных, таких как валидация, форматирование и логирование изменений.

Как работает событие onEdit

Синтаксис функции-обработчика onEdit(e)

Функция, которая обрабатывает событие onEdit, должна иметь следующий синтаксис:

/**
 * Функция, обрабатывающая событие onEdit.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  // Код, который выполняется при редактировании ячейки.
  console.log("Ячейка была отредактирована.");
}

Здесь e — это объект события, который содержит информацию о том, что произошло (например, какая ячейка была изменена, какое значение было введено и т.д.).

Объект события ‘e’ и его свойства (range, value, oldValue, etc.)

Объект события e в onEdit(e) предоставляет доступ к множеству полезных свойств, описывающих произошедшее изменение:

• range: Объект Range, представляющий ячейку или диапазон ячеек, которые были изменены. Например e.range.getRow(), e.range.getColumn(), e.range.getValue()
• value: Новое значение ячейки(ячеек) после редактирования. Возвращает null, если ячейка была очищена.
• oldValue: Предыдущее значение ячейки перед редактированием. Если ячейка была пуста, вернет undefined.
• source: Объект Spreadsheet, в котором произошло изменение. e.source.getSheetByName('Sheet1')
• user: Email пользователя, который внес изменения (требуется авторизация).
• authMode: Режим авторизации, в котором выполняется скрипт.

Ограничения и особенности использования onEdit

Необходимо помнить об ограничениях, связанных с использованием простых триггеров onEdit:

• Скрипт выполняется от имени пользователя, редактирующего таблицу, но с ограничениями на доступ к некоторым сервисам (например, отправка email).
• Время выполнения скрипта ограничено (обычно 30 секунд для простых триггеров).
• Нельзя использовать сервисы, требующие авторизации (например, доступ к внешним API от имени пользователя).
• Срабатывает только при редактировании пользователем напрямую, а не при программном изменении ячеек.

Практическое применение onEdit: Примеры кода

Пример 1: Логирование изменений в ячейке

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

/**
 * Логирует изменения в ячейках Google Sheets.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const logSheetName: string = "LogSheet";
  let logSheet: GoogleAppsScript.Spreadsheet.Sheet | null = ss.getSheetByName(logSheetName);

  if (!logSheet) {
    logSheet = ss.insertSheet(logSheetName);
    logSheet.appendRow(["Дата и время", "Пользователь", "Лист", "Ячейка", "Старое значение", "Новое значение"]);
  }

  const now: Date = new Date();
  const user: string = Session.getActiveUser().getEmail();
  const sheetName: string = e.range.getSheet().getName();
  const cell: string = e.range.getA1Notation();
  const oldValue: any = e.oldValue || "";
  const newValue: any = e.value || "";

  logSheet.appendRow([now, user, sheetName, cell, oldValue, newValue]);
}

Пример 2: Автоматическое форматирование данных

Автоматическое приведение данных к нужному формату (например, перевод в верхний регистр или округление чисел).

/**
 * Автоматически форматирует введенные данные в верхний регистр.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  const range: GoogleAppsScript.Spreadsheet.Range = e.range;
  const value: string = String(e.value);

  if (typeof value === 'string') {
    range.setValue(value.toUpperCase());
  }
}

Пример 3: Отправка уведомлений об изменениях

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

/**
 * Отправляет уведомление по электронной почте при изменении определенной ячейки.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  const editedCell: string = e.range.getA1Notation();
  const editedSheetName: string = e.range.getSheet().getName();

  // Отправлять уведомление только при изменении ячейки A1 на листе "Данные"
  if (editedCell === "A1" && editedSheetName === "Данные") {
    const newValue: any = e.value;
    const recipient: string = "example@example.com"; // Замените на свой адрес
    const subject: string = "Изменено значение в ячейке A1!";
    const body: string = `В ячейке A1 листа 'Данные' изменено значение на: ${newValue}`;

    MailApp.sendEmail(recipient, subject, body);
  }
}

Пример 4: Валидация вводимых данных

Проверка введенных данных на соответствие заданным критериям (например, проверка формата email или диапазона чисел).

/**
 * Проверяет, является ли введенное значение допустимым email-адресом.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  const range: GoogleAppsScript.Spreadsheet.Range = e.range;
  const value: string = String(e.value);
  const emailRegex: RegExp = /^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$/;

  if (range.getColumn() === 1) { // Проверяем только первый столбец
      if (!emailRegex.test(value)) {
        SpreadsheetApp.getActiveSpreadsheet().toast('Неверный формат email!', 'Ошибка', 5);
        range.clearContent(); // Очищаем ячейку
      }
  }
}

Продвинутое использование onEdit

Установка триггера onEdit программно (Installable Triggers)

Для более гибкого управления триггерами onEdit, их можно установить программно. Это позволяет задавать дополнительные параметры и обходить ограничения простых триггеров.

/**
 * Устанавливает триггер onEdit для текущей таблицы.
 */
function createOnEditTrigger() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('myOnEditFunction')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

/**
 * Функция, которая будет вызываться при редактировании (должна быть отдельно от createOnEditTrigger).
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function myOnEditFunction(e: GoogleAppsScript.Events.SheetsOnEdit) {
  // Код, который выполняется при редактировании ячейки.
  console.log("Ячейка была отредактирована с использованием устанавливаемого триггера.");
}

Обработка множественных изменений (batch edits)

Иногда необходимо обрабатывать ситуацию, когда пользователь вносит сразу несколько изменений (например, при вставке данных из буфера обмена). В этом случае объект события e.range может представлять собой диапазон ячеек. Необходимо учитывать это и обрабатывать каждую ячейку отдельно.

/**
 * Обрабатывает множественные изменения в ячейках.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события onEdit.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
  const range: GoogleAppsScript.Spreadsheet.Range = e.range;
  const numRows: number = range.getNumRows();
  const numColumns: number = range.getNumColumns();

  for (let i = 1; i <= numRows; i++) {
    for (let j = 1; j <= numColumns; j++) {
      const cell: GoogleAppsScript.Spreadsheet.Range = range.getCell(i, j);
      const value: any = cell.getValue();

      // Выполняем обработку для каждой ячейки
      console.log(`Ячейка ${cell.getA1Notation()} имеет значение ${value}`);
    }
  }
}

Использование onEdit совместно с другими сервисами Google (например, Calendar, Docs)

Событие onEdit можно использовать для интеграции Google Sheets с другими сервисами Google. Например, можно автоматически создавать события в Google Calendar на основе данных, введенных в таблицу, или генерировать документы Google Docs с отчетами.

Решение проблем и отладка кода onEdit

Наиболее распространенные ошибки при использовании onEdit

• Неправильный синтаксис функции-обработчика. Убедитесь, что функция имеет аргумент e и правильный тип (GoogleAppsScript.Events.SheetsOnEdit).
• Превышение лимитов времени выполнения. Старайтесь оптимизировать код и избегать выполнения длительных операций в обработчике onEdit.
• Неправильная обработка объекта события. Внимательно проверяйте типы данных и наличие значений в свойствах объекта e.
• Проблемы с авторизацией. Помните, что простые триггеры имеют ограничения на доступ к некоторым сервисам.
• Использование простых триггеров для задач, требующих более высокой надежности. В таких случаях следует использовать устанавливаемые триггеры.

Инструменты отладки Google Apps Script

Google Apps Script предоставляет встроенные инструменты отладки, которые помогут выявить и исправить ошибки в коде:

• Редактор скриптов. Позволяет устанавливать точки останова (breakpoints) и выполнять код построчно.
• Логгер (Logger.log()). Используется для вывода отладочной информации в консоль.
• Сервис Stackdriver Logging. Предоставляет более продвинутые возможности для логирования и мониторинга скриптов.
• Toast messages. Используются для отображения кратких сообщений в интерфейсе Google Sheets.

Как избежать превышения лимитов времени выполнения

• Оптимизируйте код: избегайте ненужных операций и используйте эффективные алгоритмы.
• Используйте пакетную обработку данных: вместо выполнения множества отдельных операций, сгруппируйте их в одну.
• Разделите сложные задачи на несколько этапов и используйте устанавливаемые триггеры для выполнения каждого этапа по отдельности.
• Избегайте выполнения длительных операций ввода-вывода (например, чтение данных из внешних API) в обработчике onEdit.