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

Краткое описание Google Apps Script

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

Преимущества использования Google Apps Script в Sheets: автоматизация, расширение функциональности и интеграция

Интеграция GAS с Google Sheets открывает широкие возможности:

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

Расширение функциональности: Создание пользовательских функций, недоступных в стандартном наборе Sheets, добавление кастомных меню и боковых панелей для упрощения взаимодействия с таблицей.

Интеграция: Подключение к другим сервисам Google (Gmail, Calendar, Drive) и внешним API для обмена данными, например, загрузка данных о рекламных кампаниях из API рекламных сетей или отправка лидов в CRM.

Примеры задач, которые можно автоматизировать с помощью Google Apps Script

Ежедневный импорт данных о расходах из API рекламных систем (Google Ads, Facebook Ads).

Автоматическая отправка email-уведомлений при достижении определенных KPI в таблице (например, превышение бюджета).

Создание пользовательской функции для извлечения UTM-меток из URL.

Очистка и форматирование данных, импортированных из CSV-файлов.

Генерация кастомных отчетов на основе данных из нескольких листов.

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

Открытие редактора скриптов через меню «Расширения»

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

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

Перейдите в меню «Расширения» (Extensions).

Выберите пункт «Apps Script».

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

Создание нового скрипта с нуля или использование существующего

При первом открытии редактора для таблицы будет автоматически создан проект скрипта с одним файлом Code.gs. Вы можете начать писать код в этом файле или создать дополнительные файлы (.gs для кода, .html для пользовательских интерфейсов) через меню «Файл» > «Создать».

Если у вас уже есть непривязанный (standalone) скрипт, его нельзя напрямую «привязать» к таблице через интерфейс редактора. Однако вы можете скопировать код из непривязанного скрипта в редактор привязанного скрипта.

Основы работы с редактором Google Apps Script

Интерфейс редактора скриптов: основные элементы и их назначение

Новый интерфейс редактора включает:

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

Область редактора кода (центр): Основное пространство для написания и редактирования кода с подсветкой синтаксиса, автодополнением и проверкой ошибок.

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

Консоль/Журналы (снизу): Отображает вывод console.log() и Logger.log(), а также информацию об ошибках выполнения.

Написание и запуск простого скрипта для Google Sheets (например, вывод сообщения)

Давайте создадим простую функцию, которая выводит всплывающее сообщение в активной таблице:

/**
 * Выводит приветственное сообщение в активной Google Таблице.
 */
function showWelcomeToast() {
  // Получаем доступ к активной электронной таблице
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Проверяем, что таблица успешно получена
  if (!ss) {
    console.error('Не удалось получить доступ к активной таблице.');
    return;
  }

  // Выводим всплывающее сообщение (toast)
  ss.toast('Добро пожаловать в Google Apps Script!', 'Статус', 5); // Сообщение, Заголовок, Время показа в секундах
  console.log('Сообщение успешно показано.');
}

Чтобы запустить скрипт:

Сохраните изменения (значок дискеты или Ctrl+S/Cmd+S).

В выпадающем меню рядом с иконкой отладки (жук) выберите функцию showWelcomeToast.

Нажмите кнопку «Выполнить».

При первом запуске потребуется предоставить разрешения скрипту на доступ к вашим данным Google Sheets. Внимательно изучите запрашиваемые разрешения и подтвердите их.

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

Проекты Apps Script сохраняются автоматически по мере внесения изменений, но рекомендуется использовать ручное сохранение (Ctrl+S/Cmd+S или иконка дискеты) перед запуском или закрытием редактора. Дайте вашему проекту осмысленное имя, кликнув на «Проект без названия» в левом верхнем углу.

Как упоминалось ранее, скрипт, созданный из меню «Расширения» > «Apps Script», уже привязан к конкретной таблице. Эта привязка дает скрипту специальные методы, такие как SpreadsheetApp.getActiveSpreadsheet(), для прямого взаимодействия с этой таблицей.

Примеры полезных скриптов для Google Sheets

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

Скрипт для мониторинга бюджета рекламной кампании и отправки уведомления при превышении порога:

/**
 * Проверяет бюджет рекламной кампании в ячейке B2
 * и отправляет email-уведомление при превышении лимита.
 *
 * @param {number} budgetLimit Максимально допустимый бюджет.
 * @param {string} recipientEmail Email получателя уведомления.
 */
function checkCampaignBudget(budgetLimit = 1000, recipientEmail = 'your_email@example.com') {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet(); // Или ss.getSheetByName('Campaign Data');

  // Получаем текущие расходы из ячейки B2
  const budgetCell = sheet.getRange('B2');
  const currentSpending = budgetCell.getValue();

  // Проверяем, является ли значение числом
  if (typeof currentSpending !== 'number') {
    console.error('Значение в ячейке B2 не является числом.');
    return;
  }

  console.log(`Текущие расходы: ${currentSpending}, Лимит: ${budgetLimit}`);

  // Проверяем превышение бюджета
  if (currentSpending > budgetLimit) {
    const subject = `Превышен бюджет кампании в таблице: ${ss.getName()}`;
    const body = `Бюджет кампании превышен!

Текущие расходы: ${currentSpending}
Установленный лимит: ${budgetLimit}

Таблица: ${ss.getUrl()}`;

    try {
      MailApp.sendEmail(recipientEmail, subject, body);
      console.log(`Уведомление отправлено на ${recipientEmail}`);
    } catch (error) {
      console.error(`Ошибка отправки email: ${error}`);
    }
  } else {
    console.log('Бюджет в пределах нормы.');
  }
}

// Для запуска этой функции можно настроить временной триггер
// (например, ежедневно проверять бюджет)

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

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

/**
 * Извлекает значение указанного UTM-параметра из URL.
 *
 * @param {string} url URL для анализа.
 * @param {string} parameterName Имя UTM-параметра (например, 'utm_source').
 * @return {string} Значение параметра или пустая строка, если параметр не найден.
 * @customfunction
 */
function GET_UTM_PARAMETER(url, parameterName) {
  if (!url || !parameterName) {
    return '';
  }
  try {
    // Используем регулярное выражение для поиска параметра
    const regex = new RegExp('[?&]' + parameterName + '=([^&#]*)');
    const results = regex.exec(url);
    // Декодируем найденное значение
    return results === null ? '' : decodeURIComponent(results[1].replace(/\+/g, ' '));
  } catch (e) {
    return ''; // Возвращаем пустое значение в случае ошибки
  }
}

// Использование в ячейке: =GET_UTM_PARAMETER(A1; "utm_medium")

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

Импорт данных о курсах валют из публичного API.

/**
 * Получает текущий курс USD к RUB с использованием публичного API
 * и записывает его в ячейку C1.
 */
function fetchExchangeRate() {
  const apiUrl = 'https://api.exchangerate-api.com/v4/latest/USD'; // Пример API
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const targetCell = sheet.getRange('C1');

  try {
    const response = UrlFetchApp.fetch(apiUrl, { 'muteHttpExceptions': true });
    const responseCode = response.getResponseCode();
    const jsonResponse = response.getContentText();

    if (responseCode === 200) {
      const data = JSON.parse(jsonResponse);
      const rate = data.rates.RUB;

      if (rate) {
        targetCell.setValue(rate);
        console.log(`Курс USD/RUB (${rate}) успешно записан в ячейку C1.`);
      } else {
        console.error('Не удалось найти курс RUB в ответе API.');
      }
    } else {
      console.error(`Ошибка запроса к API. Код: ${responseCode}, Ответ: ${jsonResponse}`);
    }
  } catch (error) {
    console.error(`Не удалось выполнить запрос к API: ${error}`);
    targetCell.setValue('Ошибка API');
  }
}

Автоматическая обработка данных и форматирование

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

/**
 * Находит строки, где значение в колонке B (Источник) равно 'Google Ads',
 * и выделяет их фон желтым цветом, а текст делает полужирным.
 */
function formatGoogleAdsLeads() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = sheet.getDataRange(); // Получаем весь диапазон с данными
  const values = range.getValues();
  const sourceColumnIndex = 1; // Индекс колонки B (нумерация с 0)
  const targetSource = 'Google Ads';

  console.log(`Начинаем обработку ${values.length} строк.`);

  // Проходим по всем строкам, начиная со второй (пропускаем заголовок)
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const source = row[sourceColumnIndex];

    if (source === targetSource) {
      // Получаем диапазон текущей строки (нумерация строк в getRange с 1)
      const rowRange = sheet.getRange(i + 1, 1, 1, sheet.getLastColumn());
      try {
        rowRange.setBackground('#FFFF00'); // Желтый фон
        rowRange.setFontWeight('bold'); // Полужирный шрифт
        console.log(`Строка ${i + 1} отформатирована (Источник: ${source}).`);
      } catch(e) {
        console.error(`Ошибка форматирования строки ${i + 1}: ${e}`);
      }
    }
  }
  console.log('Обработка завершена.');
}

Отладка и устранение неполадок в Google Apps Script

Использование логов для отслеживания выполнения скрипта

Логирование — ключевой инструмент для понимания хода выполнения скрипта и диагностики проблем. Используйте console.log() (рекомендуется для нового V8 runtime) или Logger.log() (старый Rhino runtime) для вывода значений переменных или сообщений о статусе.

Просмотреть логи можно на вкладке «Выполнения» в левой панели редактора. Выберите конкретное выполнение, чтобы увидеть его статус и подробные логи.

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

Редактор предоставляет встроенный отладчик:

Точки останова (Breakpoints): Кликните на номер строки слева от кода, чтобы установить точку останова. Выполнение скрипта приостановится на этой строке.

Пошаговое выполнение: После остановки на точке останова используйте кнопки «Шаг с обходом» (F10), «Шаг с заходом» (F11), «Шаг с выходом» (Shift+F11) для детального контроля выполнения.

Инспекция переменных: В режиме отладки можно навести курсор на переменную или использовать панель «Отладчик» для просмотра текущих значений переменных в области видимости.

Запускайте скрипт в режиме отладки, нажав кнопку «Отладка» (значок жука).

Поиск и исправление распространенных ошибок

Ошибки разрешений (Authorization): Убедитесь, что скрипту предоставлены все необходимые разрешения. Иногда требуется повторная авторизация после добавления новых служб (например, MailApp).

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

Неправильные диапазоны (Range): Ошибки типа «Range not found» часто возникают из-за неправильного имени листа или неверных координат ячеек/диапазонов.

Лимиты и квоты Google Services: Скрипты имеют ограничения на время выполнения, количество вызовов API, отправленных писем и т.д. Ознакомьтесь с квотами Apps Script. Используйте Utilities.sleep() для добавления пауз между вызовами API, если это необходимо.

Типы данных: JavaScript имеет динамическую типизацию, что может приводить к ошибкам. Убедитесь, что операции выполняются с ожидаемыми типами данных (например, не пытайтесь выполнить математические операции со строками без преобразования).

Проблемы с API: Внешние API могут быть недоступны или изменять формат ответа. Реализуйте надежную обработку ошибок (try...catch) и проверяйте коды ответа HTTP при использовании UrlFetchApp.