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

Что такое Google Apps Script и зачем он нужен в Google Sheets?

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

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

ChatGPT

Google AI

Grok

Perplexity

Для специалистов, работающих с данными, особенно в областях интернет-маркетинга или веб-аналитики, GAS становится мощным инструментом для обработки, анализа и визуализации данных рекламных кампаний, пользовательского поведения или SEO-метрик без необходимости использования стороннего ПО.

Преимущества автоматизации задач в Google Sheets с помощью Apps Script

Автоматизация с помощью GAS в Google Sheets предоставляет ряд ключевых преимуществ:

• Экономия времени: Автоматизация повторяющихся операций (например, форматирование отчетов, отправка уведомлений, импорт данных).
• Повышение точности: Снижение риска ошибок, связанных с ручным вводом и обработкой данных.
• Расширение функциональности: Создание пользовательских функций для специфических расчетов (например, расчет ROI для маркетинговых каналов) и интеграция с внешними API (Google Ads API, Analytics API и др.).
• Кастомизация интерфейса: Добавление пользовательских меню, диалоговых окон и боковых панелей для упрощения взаимодействия с таблицей.
• Централизованное управление: Все скрипты привязаны к конкретной таблице или доступны как дополнения, что упрощает их поддержку.

Как открыть редактор Apps Script из Google Sheets

Доступ к редактору скриптов осуществляется непосредственно из интерфейса Google Sheets:

1. Откройте нужную Google Таблицу.
2. В верхнем меню выберите «Расширения» (Extensions).
3. В выпадающем меню нажмите «Apps Script».

Откроется новая вкладка браузера с редактором кода, привязанным к вашей таблице.

Обзор интерфейса редактора Apps Script

Интерфейс редактора Apps Script включает несколько ключевых областей:

• Панель слева: Навигация по файлам проекта (.gs для кода, .html для веб-интерфейсов), библиотекам, службам Google (например, Sheets, Mail, Drive) и триггерам.
• Область редактора кода: Основное пространство для написания и редактирования кода JavaScript (с поддержкой современного синтаксиса ES6+).
• Панель инструментов: Кнопки для сохранения проекта, выбора и запуска функций, отладки.
• Журнал выполнения (Logs): Отображает вывод Logger.log() и информацию о выполнении скриптов, включая ошибки.
• Настройки проекта: Доступ к идентификатору скрипта, настройкам часового пояса, области видимости OAuth и др.

Основы работы с Google Sheets в Apps Script

Подключение к активной таблице и листам (SpreadsheetApp)

Основным сервисом для взаимодействия с Google Sheets является SpreadsheetApp. Он предоставляет методы для доступа к текущей таблице, конкретным листам и ячейкам.

/**
 * Получает активную Google Таблицу.
 * @returns {GoogleAppsScript.Spreadsheet.Spreadsheet} Активный объект таблицы.
 */
function getActiveSpreadsheet(): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.getActiveSpreadsheet();
}

/**
 * Получает лист по имени.
 * @param {string} sheetName Имя листа.
 * @returns {GoogleAppsScript.Spreadsheet.Sheet | null} Объект листа или null, если лист не найден.
 */
function getSheetByName(sheetName: string): GoogleAppsScript.Spreadsheet.Sheet | null {
  const ss = getActiveSpreadsheet();
  return ss.getSheetByName(sheetName);
}

/**
 * Получает активный лист.
 * @returns {GoogleAppsScript.Spreadsheet.Sheet} Активный объект листа.
 */
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet {
  return SpreadsheetApp.getActiveSheet();
}

Чтение и запись данных в ячейки (getRange, getValue, setValue)

Для работы с данными используются методы getRange() для получения диапазона и getValue() / setValue() (или getValues() / setValues() для массивов данных).

/**
 * Читает значение из указанной ячейки.
 * @param {string} sheetName Имя листа.
 * @param {string} cellA1Notation Адрес ячейки в нотации A1 (например, "A1", "B2").
 * @returns {any} Значение ячейки.
 */
function readCellValue(sheetName: string, cellA1Notation: string): any {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист с именем '${sheetName}' не найден.`);
    return null;
  }
  const range = sheet.getRange(cellA1Notation);
  return range.getValue();
}

/**
 * Записывает значение в указанную ячейку.
 * @param {string} sheetName Имя листа.
 * @param {string} cellA1Notation Адрес ячейки в нотации A1.
 * @param {any} value Значение для записи.
 */
function writeCellValue(sheetName: string, cellA1Notation: string, value: any): void {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист с именем '${sheetName}' не найден.`);
    return;
  }
  const range = sheet.getRange(cellA1Notation);
  range.setValue(value);
  Logger.log(`Значение '${value}' записано в ячейку ${cellA1Notation} листа '${sheetName}'.`);
}

// Пример использования:
function testReadWrite(): void {
  const sheetName = 'Лист1'; // Укажите имя вашего листа
  const cell = 'A1';
  writeCellValue(sheetName, cell, `Обновлено: ${new Date().toLocaleTimeString()}`);
  const readValue = readCellValue(sheetName, cell);
  Logger.log(`Прочитанное значение из ${cell}: ${readValue}`);
}

Работа с диапазонами (A1 notation, row/column indices)

Метод getRange() гибок и позволяет указывать диапазоны несколькими способами:

• Нотация A1: sheet.getRange("A1:C5")
• Индексы строк и столбцов: sheet.getRange(1, 1) (ячейка A1), sheet.getRange(1, 1, 5, 3) (диапазон A1:C5 — строка, столбец, кол-во строк, кол-во столбцов).

Работа с массивами данных (getValues()/setValues()) предпочтительнее для производительности при обработке больших объемов.

/**
 * Читает данные из диапазона как двумерный массив.
 * @param {string} sheetName Имя листа.
 * @param {number} startRow Начальная строка (индекс с 1).
 * @param {number} startCol Начальный столбец (индекс с 1).
 * @param {number} numRows Количество строк.
 * @param {number} numCols Количество столбцов.
 * @returns {any[][]} Двумерный массив данных.
 */
function readDataRange(sheetName: string, startRow: number, startCol: number, numRows: number, numCols: number): any[][] | null {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист с именем '${sheetName}' не найден.`);
    return null;
  }
  return sheet.getRange(startRow, startCol, numRows, numCols).getValues();
}

/**
 * Записывает двумерный массив данных в диапазон.
 * Важно: размер массива должен соответствовать размеру диапазона.
 * @param {string} sheetName Имя листа.
 * @param {number} startRow Начальная строка (индекс с 1).
 * @param {number} startCol Начальный столбец (индекс с 1).
 * @param {any[][]} data Двумерный массив данных.
 */
function writeDataRange(sheetName: string, startRow: number, startCol: number, data: any[][]): void {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист с именем '${sheetName}' не найден.`);
    return;
  }
  if (!data || data.length === 0 || data[0].length === 0) {
      Logger.log('Нет данных для записи.');
      return;
  }
  const numRows = data.length;
  const numCols = data[0].length;
  sheet.getRange(startRow, startCol, numRows, numCols).setValues(data);
  Logger.log(`Данные записаны в диапазон начиная с ${sheet.getRange(startRow, startCol).getA1Notation()}`);
}

Форматирование данных (шрифты, цвета, выравнивание)

GAS позволяет управлять форматированием ячеек и диапазонов.

/**
 * Форматирует заголовок отчета.
 * @param {GoogleAppsScript.Spreadsheet.Range} range Диапазон для форматирования.
 */
function formatHeader(range: GoogleAppsScript.Spreadsheet.Range): void {
  range
    .setBackground('#cfe2f3') // Светло-голубой фон
    .setFontWeight('bold')    // Жирный шрифт
    .setHorizontalAlignment('center'); // Выравнивание по центру
}

// Пример использования:
function applyFormatting(): void {
  const sheet = getActiveSheet();
  const headerRange = sheet.getRange("A1:E1");
  formatHeader(headerRange);
  Logger.log(`Форматирование применено к диапазону ${headerRange.getA1Notation()}`);
}

Автоматизация распространенных задач в Google Sheets

Автоматическая отправка email уведомлений

GAS легко интегрируется с Gmail (сервис MailApp) для отправки email.

/**
 * Отправляет email уведомление.
 * @param {string} recipient Адрес получателя.
 * @param {string} subject Тема письма.
 * @param {string} body Тело письма (можно использовать HTML).
 */
function sendNotification(recipient: string, subject: string, body: string): void {
  try {
    MailApp.sendEmail({
      to: recipient,
      subject: subject,
      htmlBody: body, // Используем htmlBody для форматирования
    });
    Logger.log(`Уведомление отправлено на ${recipient}`);
  } catch (e: any) {
    Logger.log(`Ошибка отправки email: ${e.message}`);
  }
}

// Пример: Уведомление о низком бюджете рекламной кампании
function checkAdBudget(): void {
  const sheetName = 'Campaigns';
  const budgetThreshold = 50; // Порог бюджета
  const emailRecipient = 'marketing-team@example.com'; // Замените на реальный email

  const sheet = getSheetByName(sheetName);
  if (!sheet) return;

  const dataRange = sheet.getDataRange(); // Весь диапазон с данными
  const values = dataRange.getValues();

  // Предполагаем структуру: [Campaign Name, Spent, Budget]
  // Пропускаем заголовок (первую строку)
  for (let i = 1; i < values.length; i++) {
    const campaignName = values[i][0];
    const spent = parseFloat(values[i][1]);
    const budget = parseFloat(values[i][2]);
    const remainingBudget = budget - spent;

    if (remainingBudget < budgetThreshold) {
      const subject = `Низкий бюджет кампании: ${campaignName}`;
      const body = `Внимание! Оставшийся бюджет для кампании "<b>${campaignName}</b>" составляет ${remainingBudget.toFixed(2)}. <br>Потрачено: ${spent.toFixed(2)}<br>Общий бюджет: ${budget.toFixed(2)}<br>Рекомендуется пополнить бюджет.`;
      sendNotification(emailRecipient, subject, body);
    }
  }
}

Создание пользовательских функций (Custom Functions)

Пользовательские функции позволяют использовать логику Apps Script прямо в формулах ячеек.

/**
 * Рассчитывает Click-Through Rate (CTR).
 * @param {number} clicks Количество кликов.
 * @param {number} impressions Количество показов.
 * @returns {number} Значение CTR в процентах или 0, если показов нет.
 * @customfunction
 */
function CALCULATE_CTR(clicks: number, impressions: number): number {
  if (typeof clicks !== 'number' || typeof impressions !== 'number') {
    throw new Error('Оба аргумента должны быть числами.');
  }
  if (impressions === 0) {
    return 0;
  }
  return (clicks / impressions) * 100;
}

/**
 * Извлекает домен из URL.
 * @param {string} url Входной URL.
 * @returns {string} Домен или пустая строка при ошибке.
 * @customfunction
 */
function GET_DOMAIN(url: string): string {
  if (typeof url !== 'string' || !url.trim()) {
      return '';
  }
  try {
      // Простая реализация, можно улучшить для сложных случаев
      const match = url.match(/^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)/im);
      return match ? match[1] : '';
  } catch (e) {
      return ''; // Возвращаем пустую строку в случае ошибки
  }
}

После сохранения кода, эти функции (=CALCULATE_CTR(A2; B2), =GET_DOMAIN(C2)) можно использовать в ячейках таблицы.

Импорт данных из внешних источников (URL Fetch)

Сервис UrlFetchApp позволяет делать HTTP-запросы к внешним API или веб-страницам.

/**
 * Получает данные по API.
 * @param {string} apiUrl URL для запроса.
 * @param {GoogleAppsScript.URL_Fetch.URLFetchRequestOptions} options Опции запроса (метод, заголовки, payload).
 * @returns {string | null} Текст ответа или null при ошибке.
 */
function fetchDataFromApi(apiUrl: string, options?: GoogleAppsScript.URL_Fetch.URLFetchRequestOptions ): string | null {
  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const responseCode = response.getResponseCode();
    if (responseCode === 200) {
      return response.getContentText();
    } else {
      Logger.log(`Ошибка запроса к API ${apiUrl}. Код ответа: ${responseCode}`);
      return null;
    }
  } catch (e: any) {
    Logger.log(`Исключение при запросе к API ${apiUrl}: ${e.message}`);
    return null;
  }
}

// Пример: Импорт данных о курсах валют (используя открытый API)
function importExchangeRates(): void {
  const apiUrl = 'https://api.exchangerate-api.com/v4/latest/USD'; // Пример API
  const sheetName = 'ExchangeRates';

  const responseText = fetchDataFromApi(apiUrl);
  if (!responseText) {
    Logger.log('Не удалось получить данные о курсах валют.');
    return;
  }

  try {
    const data = JSON.parse(responseText);
    const rates = data.rates;
    const sheet = getSheetByName(sheetName) || getActiveSpreadsheet().insertSheet(sheetName);
    sheet.clearContents(); // Очищаем лист перед записью

    const outputData: any[][] = [['Currency', 'Rate (to USD)']];
    for (const currency in rates) {
      outputData.push([currency, rates[currency]]);
    }

    writeDataRange(sheetName, 1, 1, outputData);
    formatHeader(sheet.getRange("A1:B1")); // Форматируем заголовок
    sheet.autoResizeColumns(1, 2); // Автоподбор ширины столбцов

  } catch (e: any) {
    Logger.log(`Ошибка обработки JSON или записи данных: ${e.message}`);
  }
}

Создание пользовательских меню и диалоговых окон

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

/**
 * Выполняется при открытии таблицы, добавляет пользовательское меню.
 */
function onOpen(): void {
  SpreadsheetApp.getUi()
    .createMenu('🚀 Кастомные Инструменты') // Название меню
    .addItem('📊 Импорт Курсов Валют', 'importExchangeRates') // Пункт меню и функция
    .addSeparator() // Разделитель
    .addItem('📧 Проверить Бюджеты Кампаний', 'checkAdBudget')
    .addItem('⚙️ Показать Диалог Настроек', 'showSettingsDialog')
    .addToUi();
}

/**
 * Показывает простое диалоговое окно.
 */
function showSettingsDialog(): void {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt(
    'Настройки',
    'Введите email для уведомлений:',
    ui.ButtonSet.OK_CANCEL);

  // Обработка ответа
  if (result.getSelectedButton() == ui.Button.OK) {
    const email = result.getResponseText();
    // Здесь можно сохранить email, например, в PropertiesService
    PropertiesService.getScriptProperties().setProperty('notificationEmail', email);
    ui.alert(`Email для уведомлений сохранен: ${email}`);
  } else {
    ui.alert('Операция отменена.');
  }
}

// Для отображения более сложных интерфейсов используется HtmlService
// function showSidebar() {
//   const html = HtmlService.createHtmlOutput('<p>Это боковая панель.</p>')
//       .setTitle('Моя Панель');
//   SpreadsheetApp.getUi().showSidebar(html);
// }

Триггеры в Google Apps Script для автоматического запуска скриптов

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

Типы триггеров (onOpen, onEdit, time-driven)

• Простые триггеры:
  • onOpen(e): Срабатывает при открытии таблицы пользователем с правом редактирования.
  • onEdit(e): Срабатывает при изменении значения ячейки пользователем.
  • onInstall(e): Срабатывает при установке дополнения.
  • doGet(e), doPost(e): Для веб-приложений.
  • Ограничения: Не могут вызывать сервисы, требующие авторизации (например, MailApp без явного разрешения), имеют короткое время выполнения.
• Устанавливаемые триггеры (Installable Triggers):
  • Time-driven (По времени): Запускаются по расписанию (каждую минуту, час, день, неделю, месяц).
  • Event-driven (По событию): Срабатывают при открытии (onOpen), редактировании (onEdit), изменении (onChange — структура, формат), отправке формы (onFormSubmit).
  • Преимущества: Могут выполнять действия, требующие авторизации, имеют более длительное время выполнения, гибкая настройка.

Настройка триггеров в редакторе Apps Script

1. Откройте редактор Apps Script.
2. На панели слева нажмите на иконку будильника («Триггеры»).
3. Нажмите кнопку «+ Добавить триггер».
4. Настройте параметры:
   • Выберите функцию для запуска: Укажите функцию из вашего скрипта.
   • Выберите развертывание: Обычно «Головное».
   • Выберите источник события: «От таблицы» или «По времени».
   • Выберите тип события: Конкретное событие (При открытии, При изменении, Ежедневно и т.д.).
   • Настройки уведомлений об ошибках: Как часто получать уведомления при сбоях.
5. Нажмите «Сохранить». Потребуется авторизация скрипта для доступа к вашим данным и сервисам Google.

Управление триггерами (включение/выключение, удаление)

На той же странице «Триггеры» вы можете:

• Редактировать: Нажать на иконку карандаша у существующего триггера.
• Отключить/Включить: Хотя прямой кнопки нет, можно отредактировать и изменить настройки или удалить/создать заново.
• Удалить: Нажать на три точки (…) справа от триггера и выбрать «Удалить триггер».

Примеры использования триггеров для автоматизации

• onEdit(e): Автоматически ставить временную метку в соседнюю ячейку при изменении значения в определенном столбце.

/**
 * Автоматически проставляет дату изменения в столбец B,
 * если значение изменено в столбце A на листе 'Tasks'.
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e Объект события.
 */
function onEditTimestamp(e: GoogleAppsScript.Events.SheetsOnEdit): void {
  const range = e.range; // Диапазон, который был изменен
  const sheet = range.getSheet();

  // Проверяем, что изменение произошло на листе 'Tasks' и в столбце A (индекс 1)
  if (sheet.getName() === 'Tasks' && range.getColumn() === 1 && range.getRow() > 1) { // Исключаем заголовок
    const timestampCell = sheet.getRange(range.getRow(), 2); // Ячейка во втором столбце (B)
    // Устанавливаем текущую дату/время, только если соседняя ячейка пуста, чтобы не перезаписывать
    if (timestampCell.getValue() === '') {
         timestampCell.setValue(new Date()).setNumberFormat('yyyy-MM-dd HH:mm:ss');
    }
  }
}

• Time-driven (Ежедневный): Запускать функцию importExchangeRates() или checkAdBudget() каждое утро.
• onOpen(e): Использовать для создания пользовательского меню (см. пример выше).

Продвинутые техники и лучшие практики

Обработка ошибок и отладка кода

• try...catch: Оборачивайте потенциально проблемные участки кода (например, вызовы API, работу с данными) в блоки try...catch для грациозной обработки ошибок и логирования.
• Logger.log() / console.log(): Используйте для вывода промежуточных значений и сообщений об ошибках в журнал выполнения (Просмотр -> Журналы).
• Отладчик: Встроенный отладчик редактора позволяет устанавливать точки останова (breakpoints), пошагово выполнять код и инспектировать значения переменных.
• Настройки уведомлений триггеров: Настройте получение email-уведомлений о сбоях триггеров для оперативного реагирования.

// Пример с try...catch
function processDataSafely(): void {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DataInput');
    if (!sheet) throw new Error('Лист DataInput не найден!');
    const data = sheet.getDataRange().getValues();
    // ... сложная обработка данных ...
    Logger.log('Данные успешно обработаны.');
  } catch (error: any) {
    // Логируем ошибку для анализа
    Logger.log(`Произошла ошибка: ${error.message}\nСтек: ${error.stack}`);
    // Можно отправить уведомление администратору
    // MailApp.sendEmail('admin@example.com', 'Ошибка в скрипте обработки данных', Logger.getLog());
    SpreadsheetApp.getUi().alert(`Произошла ошибка при обработке данных. Подробности в журнале.`);
  }
}

Оптимизация производительности скриптов

• Минимизируйте вызовы к сервисам Google: Чтение/запись данных (getValue, setValue, getValues, setValues, getRange) являются относительно медленными. Старайтесь считывать и записывать данные большими блоками (массивами), а не по ячейкам.
• Используйте getValues() и setValues(): Вместо циклического чтения/записи ячеек, считайте весь нужный диапазон в массив, обработайте его в JavaScript и запишите результат обратно одним вызовом setValues().
• Кэширование: Используйте CacheService для временного хранения данных, которые не требуют частого обновления (например, результаты API-запросов, настройки), чтобы избежать повторных медленных операций.
• Избегайте циклов внутри циклов с вызовами сервисов.
• PropertiesService: Для хранения небольших объемов настроек или состояния между выполнениями скрипта используйте PropertiesService вместо чтения/записи в ячейки.

Использование библиотек и внешних API

• Библиотеки Apps Script: Подключайте готовые библиотеки (созданные вами или сообществом) для переиспользования кода. Добавляются через Ресурсы -> Библиотеки (в старом редакторе) или через Настройки проекта -> Библиотеки (в новом редакторе).
• Внешние API: Используйте UrlFetchApp для взаимодействия с любыми внешними HTTP API (Google Ads, Google Analytics, Facebook Ads, CRM-системы и т.д.). Не забывайте про обработку авторизации (OAuth2, API-ключи).
  • OAuth2 for Apps Script: Существует библиотека OAuth2, упрощающая процесс авторизации для многих популярных сервисов Google и сторонних.

Безопасность и авторизация в Apps Script

• Области видимости (Scopes): Скрипты запрашивают разрешения (scopes) только на те действия, которые им необходимы. При первой авторизации пользователь видит список запрашиваемых разрешений. Минимизируйте необходимые разрешения.
• Авторизация: Пользователь должен явно авторизовать скрипт для доступа к своим данным и сервисам Google. Устанавливаемые триггеры выполняются от имени пользователя, их установившего.
• PropertiesService: Не храните чувствительные данные (пароли, API-ключи) напрямую в коде. Используйте PropertiesService (Script или User properties) для их хранения. Для большей безопасности рассмотрите использование Secret Manager (требует GCP проекта).
• Веб-приложения и API: При создании веб-приложений или API на Apps Script тщательно проверяйте права доступа и валидируйте входящие данные.
• Защита скрипта: Ограничьте доступ к проекту скрипта только доверенным пользователям.