Как начать работу с Apps Script в Google Таблицах: Полное руководство

Что такое Apps Script и зачем он нужен в Google Таблицах?

Apps Script – это облачная платформа разработки от Google, позволяющая автоматизировать задачи и расширять функциональность сервисов Google, включая Google Таблицы. По сути, это JavaScript, но с библиотеками для взаимодействия с сервисами Google. С его помощью можно создавать собственные функции, автоматизировать рутинные операции, интегрировать Таблицы с другими сервисами (например, Gmail, Google Calendar, Google Drive) и многое другое. Он нужен для существенного повышения эффективности работы с данными, особенно при обработке больших объемов информации.

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

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

Автоматизация рутинных задач: Устранение необходимости вручную выполнять повторяющиеся действия, такие как сортировка данных, отправка уведомлений или создание отчетов.

Расширение функциональности Google Таблиц: Добавление пользовательских функций и инструментов, не предусмотренных стандартным функционалом.

Интеграция с другими сервисами Google и сторонними API: Подключение Таблиц к другим приложениям для обмена данными и выполнения сложных операций.

Создание веб-приложений на основе данных из Таблиц: Разработка интерактивных дашбордов и инструментов анализа.

Совместная работа и распространение: Возможность делиться скриптами и расширениями с другими пользователями.

Основные понятия: скрипты, функции, триггеры

Скрипт: Это набор инструкций (код), написанный на Apps Script, который выполняет определенную задачу.

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

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

Первые шаги: Открытие редактора Apps Script и написание простого скрипта

Как открыть редактор Apps Script из Google Таблицы

Откройте Google Таблицу.

Выберите Инструменты -> Редактор скриптов.

Интерфейс редактора Apps Script: основные элементы

Редактор Apps Script имеет следующие основные элементы:

Редактор кода: Область для написания и редактирования скриптов.

Панель инструментов: Кнопки для сохранения, запуска, отладки и развертывания скриптов.

Редактор проектов: Отображает файлы скрипта и связанные ресурсы (например, библиотеки).

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

Написание первого скрипта: ‘Hello, World!’ для Google Таблиц

/**
 * Отображает всплывающее окно с приветствием.
 */
function helloWorld() {
  // Получаем интерфейс пользователя таблицы
  const ui = SpreadsheetApp.getUi();

  // Отображаем всплывающее окно с приветствием
  ui.alert('Hello, World from Apps Script!');
}

Запуск скрипта и проверка результатов

Сохраните скрипт (значок дискеты).

Выберите функцию helloWorld в выпадающем списке на панели инструментов.

Нажмите кнопку Выполнить (значок play).

Вам может потребоваться предоставить скрипту разрешения на доступ к вашей таблице.

После выполнения скрипта в Google Таблице должно появиться всплывающее окно с текстом "Hello, World from Apps Script!".

Работа с данными в Google Таблицах через Apps Script

Получение доступа к таблице, листам и ячейкам

/**
 * Пример доступа к таблице, листу и ячейке.
 */
function accessSpreadsheet() {
  // Получаем активную таблицу
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Получаем активный лист
  const sheet = spreadsheet.getActiveSheet();

  // Получаем ячейку A1
  const cell = sheet.getRange('A1');

  Logger.log(`Имя листа: ${sheet.getName()}`);
  Logger.log(`Значение ячейки A1: ${cell.getValue()}`);
}

Чтение данных из ячеек и диапазонов

/**
 * Пример чтения данных из ячеек и диапазона.
 */
function readData() {
  // Получаем активный лист
  const sheet = SpreadsheetApp.getActiveSheet();

  // Читаем значение из ячейки B2
  const cellValue = sheet.getRange('B2').getValue();
  Logger.log(`Значение ячейки B2: ${cellValue}`);

  // Читаем значения из диапазона A1:C5
  const rangeValues = sheet.getRange('A1:C5').getValues();

  // Выводим значения в лог
  for (let i = 0; i < rangeValues.length; i++) {
    Logger.log(rangeValues[i].join(', '));
  }
}

Запись данных в ячейки и диапазоны

/**
 * Пример записи данных в ячейки и диапазон.
 */
function writeData() {
  // Получаем активный лист
  const sheet = SpreadsheetApp.getActiveSheet();

  // Записываем значение в ячейку C2
  sheet.getRange('C2').setValue('Новое значение');

  // Записываем значения в диапазон D1:F3
  const data = [
    ['Данные 1', 'Данные 2', 'Данные 3'],
    ['Данные 4', 'Данные 5', 'Данные 6'],
    ['Данные 7', 'Данные 8', 'Данные 9']
  ];
  sheet.getRange('D1:F3').setValues(data);
}

Работа с формулами и форматированием

/**
 * Пример установки формулы и форматирования.
 */
function setFormulaAndFormat() {
  // Получаем активный лист
  const sheet = SpreadsheetApp.getActiveSheet();

  // Устанавливаем формулу в ячейку A1 (сумма диапазона B1:B10)
  sheet.getRange('A1').setFormula('=SUM(B1:B10)');

  // Форматируем ячейку A1 (жирный шрифт)
  sheet.getRange('A1').setFontWeight('bold');

  //Устанавливаем формат даты
  sheet.getRange('B1').setNumberFormat('dd.MM.yyyy');
}

Автоматизация задач с помощью триггеров

Что такое триггеры и какие они бывают

Триггеры – это механизмы, позволяющие запускать скрипты автоматически при наступлении определенных событий. Существуют два основных типа триггеров:

Простые триггеры: Встроенные триггеры, которые автоматически запускаются при определенных событиях (например, onOpen, onEdit).

Устанавливаемые триггеры: Триггеры, которые устанавливаются вручную или программно и позволяют более гибко настраивать условия запуска.

Наиболее распространенные типы триггеров:

onOpen: Запускается при открытии таблицы.

onEdit: Запускается при редактировании ячейки.

onChange: Запускается при любых изменениях в таблице.

onFormSubmit: Запускается при отправке формы, связанной с таблицей.

Триггеры по времени: Запускаются периодически, через заданные интервалы времени.

Установка триггеров вручную и программно

Вручную: В редакторе скриптов выберите Изменить -> Триггеры текущего проекта. Нажмите Добавить триггер и настройте параметры триггера.

Программно: Используйте службу ScriptApp для создания и управления триггерами. Пример:

/**
 * Пример создания триггера по времени.
 */
function createTimeDrivenTrigger() {
  // Создаем триггер, который будет запускать функцию 'myFunction' каждый час
  ScriptApp.newTrigger('myFunction')
    .timeBased()
    .everyHours(1)
    .create();
}

/**
 * Функция, которая будет выполняться по триггеру.
 */
function myFunction() {
  Logger.log('Триггер сработал!');
}

Примеры использования триггеров: отправка уведомлений, автоматическое обновление данных

Отправка уведомлений: Создайте триггер onEdit, который будет отправлять уведомление по электронной почте при изменении определенной ячейки.

Автоматическое обновление данных: Создайте триггер по времени, который будет периодически обновлять данные из внешнего источника (например, API контекстной рекламы).

Управление триггерами и их отладка

Триггерами можно управлять в редакторе скриптов (Изменить -> Триггеры текущего проекта). Для отладки триггеров используйте Logger.log для записи информации в лог выполнения. Также полезно использовать отладочный режим редактора скриптов для пошагового выполнения кода.

Практические примеры скриптов для Google Таблиц

Скрипт для автоматической сортировки данных

/**
 * Сортирует данные в диапазоне A2:E по столбцу C (по возрастанию).
 */
function sortData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A2:E'); // Предполагаем, что первая строка - заголовки
  range.sort({ column: 3, ascending: true }); // Сортируем по 3 столбцу (C) по возрастанию
}

Скрипт для отправки электронных писем на основе данных из таблицы

/**
 * Отправляет электронные письма на основе данных из столбцов A (email) и B (имя) начиная со строки 2.
 */
function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const emailColumn = 0; // Индекс столбца с email (A)
  const nameColumn = 1;  // Индекс столбца с именем (B)
  const subject = 'Приветствие!';

  // Пропускаем первую строку (заголовки)
  for (let i = 1; i < values.length; i++) {
    const emailAddress = values[i][emailColumn];
    const name = values[i][nameColumn];
    const message = `Здравствуйте, ${name}! Спасибо за регистрацию.`;

    MailApp.sendEmail({
      to: emailAddress,
      subject: subject,
      body: message
    });
  }
}

Скрипт для создания резервных копий таблицы

/**
 * Создает резервную копию текущей таблицы с указанием даты и времени.
 */
function createBackup() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const name = spreadsheet.getName();
  const date = Utilities.formatDate(new Date(), Session.getTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  const newName = `${name} Backup ${date}`;
  DriveApp.getFileById(spreadsheet.getId()).makeCopy(newName);
}

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

/**
 * Импортирует данные JSON из внешнего API в таблицу.
 */
function importDataFromAPI() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const apiUrl = 'https://jsonplaceholder.typicode.com/todos/1'; // Пример API

  try {
    const response = UrlFetchApp.fetch(apiUrl);
    const json = JSON.parse(response.getContentText());

    // Записываем данные в ячейки
    sheet.getRange('A1').setValue(json.userId);
    sheet.getRange('B1').setValue(json.id);
    sheet.getRange('C1').setValue(json.title);
    sheet.getRange('D1').setValue(json.completed);

  } catch (e) {
    Logger.log(`Ошибка при импорте данных: ${e}`);
    SpreadsheetApp.getUi().alert(`Ошибка при импорте данных: ${e}`);
  }
}