Отправка писем из Google Sheets

Google Apps Script — это встроенный механизм автоматизации для сервисов Google (Sheets, Docs, Gmail и т. п.). Возможность автоматически отправлять электронные письма делает его особенно полезным для регулярных отчётов, рассылок с персонализированными данными и напоминаний.

Кому это пригодится:

Сотрудникам, которые регулярно отсылают ежемесячные отчёты руководству.
Менеджерам, которые отправляют персональные сводки членам команды.
Администраторам, которым нужно рассылать уведомления или сводки на основе данных в таблицах.

Зачем использовать скрипт вместо ручной рассылки

Экономия времени: один клик вместо множества писем.
Консистентность: одинаковый формат и структура писем.
Персонализация: вставка динамических данных из таблицы для каждого получателя.

Важно: проверяйте ограничения аккаунта Google и квоты сервисов (ежедневные лимиты отправки писем, использование API). Актуальные лимиты смотрите в официальной документации Google Apps Script.

Подготовка листа с данными для рассылки

Чтобы скрипт отправлял письма, подготовьте лист с именами, email-адресами и фрагментами сообщений. Каждый ряд будет соответствовать одному письму.

В вашем файле Google Sheets добавьте новый лист, назовите его, например, Send-Emails.

Лист Send-Emails в Google Sheets

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

Шаблон столбцов: имя, email, части сообщения

Вставляйте в ячейки формулы для импорта данных из других листов (например, SUMIFS, VLOOKUP, QUERY). Статичная часть письма вводится как текст прямо в ячейку.

Пример заполненного шаблона с формулами

В итоге лист будет выглядеть как таблица с рядами-письмами.

Готовый лист рассылки

Примечание: сохраняйте шаблон столбцов и документируйте порядок, чтобы скрипт корректно ссылался на нужные столбцы.

Открытие редактора скриптов и создание функции

Откройте Редактор скриптов через меню «Инструменты» → «Редактор скриптов».

Открытие редактора скриптов

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

function sendArticleCountEmails() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  ss.setActiveSheet(ss.getSheetByName("Send-Emails"));  
  var sheet = SpreadsheetApp.getActiveSheet();  
  var dataRange = sheet.getRange("A2:F4");  
  var data = dataRange.getValues();  
  for (i in data) {  
    var rowData = data[i];  
    var emailAddress = rowData[1];  
    var recipient = rowData[0];  
    var message1 = rowData[2];  
    var message2 = rowData[3];  
    var parameter2 = rowData[4];  
    var message3 = rowData[5];  
    var message = 'Dear ' + recipient + ',\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;  
    var subject = 'Your article count for this month';  
    MailApp.sendEmail(emailAddress, subject, message);  
  }  
}

Заметьте: пример использует фиксированный диапазон A2:F4. Для практических сценариев лучше использовать динамический диапазон, чтобы не корректировать код при росте таблицы.

Улучшения кода — рекомендации и альтернативные варианты

Ниже несколько полезных модификаций. Каждая предлагает улучшение по читаемости, гибкости или функционалу.

1) Динамический диапазон (рекомендуется)

function sendArticleCountEmailsDynamic() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Send-Emails');
  ss.setActiveSheet(sheet);
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // нет данных
  var lastCol = sheet.getLastColumn();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, lastCol);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; i++) {
    var rowData = data[i];
    var recipient = rowData[0];
    var emailAddress = rowData[1];
    // остальные части письма, например rowData[2..]
    var message = 'Здравствуйте ' + recipient + ',\n' + rowData[2];
    var subject = 'Ежемесячная сводка';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Почему так лучше: не нужно вручную менять диапазон при добавлении строк; код безопасно завершится, если нет данных.

2) HTML-письмо с альтернативным текстом

var htmlMessage = 'Здравствуйте ' + recipient + ',' +
                  'Ваши результаты: ' + rowData[4] + '';
MailApp.sendEmail({
  to: emailAddress,
  subject: subject,
  htmlBody: htmlMessage,
  body: 'Здравствуйте ' + recipient + ',\nСмотрите HTML-версию этого письма.'
});

Используйте HTML, если нужно форматирование, ссылки или таблицы. Всегда добавляйте text/plain в body для клиентов, которые не отображают HTML.

3) Прикрепить файл из Google Drive

var file = DriveApp.getFileById('ID_ФАЙЛА');
MailApp.sendEmail({
  to: emailAddress,
  subject: subject,
  body: message,
  attachments: [file.getAs(MimeType.PDF)]
});

Полезно для отправки PDF-отчётов, инвойсов и т. п.

Детальный разбор исходного кода

Ключевые шаги скрипта:

Открыть активную таблицу и выбрать нужный лист:

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Send-Emails"));
var sheet = SpreadsheetApp.getActiveSheet();

Получить диапазон и значения в виде массива:

var dataRange = sheet.getRange("A2:F4");
var data = dataRange.getValues();

getRange принимает координаты или строку-диапазон. getValues() возвращает двумерный массив: data[row][col]. Индексация в массиве начинается с 0.

Итерация по строкам и сбор полей письма:

for (i in data) {
  var rowData = data[i];
  var emailAddress = rowData[1];
  var recipient = rowData[0];
  // ...
}

Рекомендация: используйте классический цикл for (var i = 0; i < data.length; i++) для предсказуемого поведения.

Сборка тела письма и отправка:

var message = 'Dear ' + recipient + ',\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;
var subject = 'Your article count for this month';
MailApp.sendEmail(emailAddress, subject, message);

Правило: экранируйте перенос строки как \n. Для более сложного форматирования используйте htmlBody.

Как добавить пункт меню «Send Emails» при открытии документа

Найдите или создайте функцию onOpen() в редакторе и вставьте туда код, который добавит пользовательское меню:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Send Emails', functionName: 'sendArticleCountEmailsDynamic'}
  ];
  spreadsheet.addMenu('Send Emails', menuItems);
}

После сохранения скрипта при следующем открытии таблицы в меню появится пункт “Send Emails”.

Пользовательское меню в Google Sheets

Совет: локализуйте имя меню для вашей команды, например “Отправить письма”.

Тестирование и критерии приёмки

Критерии приёмки:

Письма отправляются на все строки с данными.
Тема и тело письма содержат корректные динамические значения.
Сценарий корректно обрабатывает пустые строки и некорректные email.
В случае ошибки скрипт не ломает остальные отправки (обрабатываются исключения).

Минимальный план тестирования:

Поместите 2–3 тестовых адреса (с вашим адресом и адресами коллег) в лист.
Запустите функцию вручную в редакторе скриптов (Run) и проверьте входящие.
Проверьте поведение при пустых ячейках и некорректном email.
Тестируйте HTML-версию и вложения отдельно.

Пример обработки ошибок внутри цикла:

try {
  MailApp.sendEmail(emailAddress, subject, message);
} catch (e) {
  Logger.log('Ошибка отправки для ' + emailAddress + ': ' + e.message);
  // продолжить отправки другим получателям
}

Безопасность, конфиденциальность и соответствие

Important: таблицы часто содержат персональные данные (имена, электронные адреса, служебные показатели). Перед запуском рассылки:

Убедитесь, что у вас есть право обрабатывать эти адреса.
Ограничьте доступ к файлу Google Sheets.
Не включайте чувствительные данные в тело письма без согласия получателя.
Для корпоративных сценариев обсудите требования безопасности с IT/Compliance.

Когда такой подход не подходит — ограничения и альтернативы

Когда это может не сработать:

Нужна сложная логика маршрутизации или большие объёмы рассылки — лучше использовать специализированные сервисы рассылок (Mailchimp, SendGrid) или Gmail API с серверной очередью.
Большие вложения или частые массовые рассылки могут столкнуться с лимитами аккаунта Google.
Нужна сквозная аналитика доставки и открытия — Apps Script предоставляет базовую отправку, но не детальную аналитику.

Альтернативные подходы:

Использовать Google Workspace Add-ons или готовые надстройки для слияния почты (Mail Merge add-ons).
Перенести логику на сервер (например, Cloud Functions) и использовать Gmail API или сторонние SMTP/ESP для масштабируемых рассылок.

Ролевые чек-листы

Для сотрудника (который запускает рассылку):

Убедиться, что адреса актуальны.
Проверить шаблон письма и переменные в таблице.
Запустить тестовую рассылку на 2–3 адреса.
Проверить исходящие письма и корректность данных.

Для менеджера/создателя шаблона:

Настроить столбцы с понятными заголовками.
Обеспечить документацию порядка столбцов.
Уточнить частоту рассылки и формат (текст/HTML).

Для администратора G Suite/Workspace:

Проверить квоты и права доступа.
Настроить аудит и логи.
При необходимости подключить ограниченные сервисы или перенаправить на API.

Edge-cases и распространённые ошибки

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

Пример простой валидации email:

function isEmailValid(email) {
  var re = /^[^@\s]+@[^@\s]+\.[^@\s]+$/;
  return re.test(String(email).toLowerCase());
}

Полезная шпаргалка (cheat sheet)

sheet.getRange(row, column, numRows, numCols) — универсальный способ задать диапазон.
sheet.getLastRow(), sheet.getLastColumn() — определить границы данных.
MailApp.sendEmail(options) — поддерживает объект с ключами to, subject, body, htmlBody, attachments.
DriveApp.getFileById(id) — получить файл для вложения.

Заключение

Автоматизация рассылок из Google Sheets с помощью Google Apps Script — это быстрый путь сократить ручной труд, повысить точность и персонализировать коммуникацию. За пару часов настройки вы получаете инструмент, который экономит часы на каждой итерации.

Summary: