Google Apps Script: автоматизация Google Таблиц

Что такое Apps Script

Google Apps Script — платформа на JavaScript, встроенная в Google Workspace. Откройте таблицу и выберите Расширения → Apps Script: появится редактор с проектом, привязанным к этому файлу. Код выполняется на серверах Google, поэтому ничего не нужно устанавливать, а скрипт работает, даже когда ваш компьютер выключен.

Для чего он подходит:

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

Основные объекты: SpreadsheetApp (листы и диапазоны), MailApp (почта), UrlFetchApp (HTTP-запросы) и PropertiesService (настройки и секреты).

Свои функции

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

/**
 * Возвращает цену с налогом.
 * @param {number} price Цена без налога
 * @param {number} rate Ставка, например 0.15
 * @return {number}
 * @customfunction
 */
function WITH_TAX(price, rate) {
  if (Array.isArray(price)) {
    return price.map(row => row.map(p => p * (1 + rate)));
  }
  return price * (1 + rate);
}

В ячейке: =WITH_TAX(B2:B100; 0,15) в русской локали или =WITH_TAX(B2:B100, 0.15) в английской. Передать весь диапазон один раз намного быстрее, чем копировать формулу в сотни ячеек.

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

Триггеры

Простые триггеры — функции с зарезервированными именами: onOpen(e), onEdit(e). Они работают без настройки, но выполняются недолго и не могут обращаться к сервисам, которым нужны разрешения. Пример: ставить отметку времени, когда меняется столбец C на листе Tasks.

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Tasks' || e.range.getColumn() !== 3) return;
  sheet.getRange(e.range.getRow(), 4).setValue(new Date());
}

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

Устанавливаемые триггеры создаются в редакторе (Триггеры → Добавить триггер) или в коде. Они запускаются по расписанию, при отправке формы или изменении таблицы и работают с правами пользователя, который их создал.

function createHourlyTrigger() {
  ScriptApp.newTrigger('loadRates').timeBased().everyHours(1).create();
}

Такую функцию запускайте один раз, иначе каждый запуск создаст ещё один триггер.

Отправка писем из таблицы

Лист Invoices: A — email, B — имя, C — сумма, D — статус, E — дата отправки. Скрипт отправляет напоминание по каждой неоплаченной строке, по которой письма ещё не было:

function sendReminders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Invoices');
  const data = sheet.getDataRange().getValues();
  const sent = data.map(row => [row[4]]);
  for (let i = 1; i < data.length; i++) {
    const [email, name, amount, status, sentAt] = data[i];
    if (status !== 'Не оплачен' || sentAt) continue;
    MailApp.sendEmail(email, 'Напоминание об оплате',
      `Здравствуйте, ${name}! Счёт на сумму ${amount} пока не оплачен.`);
    sent[i][0] = new Date();
  }
  sheet.getRange(1, 5, sent.length, 1).setValues(sent);
}

Столбец с датой отправки защищает от повторных писем, если скрипт запустится снова. Сначала протестируйте на своём адресе.

Загрузка данных из API

function loadRates() {
  const key = PropertiesService.getScriptProperties().getProperty('API_KEY');
  const res = UrlFetchApp.fetch('https://api.example.com/rates', {
    headers: { Authorization: `Bearer ${key}` },
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) throw new Error(res.getContentText());
  const rows = JSON.parse(res.getContentText()).items.map(r => [r.code, r.rate]);
  const sheet = SpreadsheetApp.getActive().getSheetByName('Rates');
  sheet.getRange('A2:B').clearContent();
  if (rows.length) sheet.getRange(2, 1, rows.length, 2).setValues(rows);
}

Храните ключи в свойствах скрипта (Настройки проекта), а не в коде: любой, у кого есть права на редактирование таблицы, может открыть скрипт.

Квоты и лимиты

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

Что учитывать заранее:

• Время одного запуска ограничено (около шести минут). Длинные задачи обрабатывайте порциями, сохраняйте прогресс в PropertiesService и продолжайте на следующем триггере.
• Суточные лимиты действуют на получателей писем, вызовы UrlFetch и общее время работы триггеров. MailApp.getRemainingDailyQuota() показывает, сколько писем осталось.
• Скорость. Читайте и записывайте диапазоны пачками через getValues() и setValues(). Вызов getValue() для каждой ячейки в цикле — самая частая причина медленных скриптов.
• Параллельность. Если два триггера могут сработать одновременно, используйте LockService, чтобы они не перезаписывали друг друга.