Автоматическая отправка писем из Google Таблиц через Google Scripts
TL;DR
Google Scripts позволяет автоматически собирать данные из Google Таблиц и рассылать персонализированные письма одним кликом. В статье показано, как подготовить лист с адресами и текстом, написать скрипт для отправки, добавить пункт меню и улучшить скрипт: HTML-письма, вложения, обработка ошибок и тестирование.
Эта статья проведёт вас шаг за шагом через процесс настройки листа, создания скрипта и добавления удобного пункта меню, который отправляет персонализированные письма всем нужным людям. На практике это экономит часы ручной работы при регулярных рассылках статусов, отчётов и уведомлений.
Что вы научитесь
- Подготовить Google Таблицу с данными для рассылки.
- Написать рабочий Google Apps Script, который отправляет письма по строкам.
- Добавить пункт меню, чтобы запускать отправку одним кликом.
- Расширить скрипт: HTML-письма, вложения из Drive, обработка ошибок, тестирование и правила безопасности.
Начало: как подготовить лист для рассылки
Первый и ключевой шаг — создать отдельный лист внутри вашей таблицы, где каждая строка будет представлять одно отправляемое письмо.
В примере автор использует лист с названием ‘Send-Emails’. На листе создайте заголовки колонок примерно такими:
- Recipient — имя получателя.
- Email — адрес получателя.
- Part1, Part2, … — фрагменты тела письма (статические или с помощью формул).
- Параметры — динамические значения (например, число статей за месяц).
Добавляйте формулы в ячейки, чтобы подтягивать данные из других листов. Это даёт возможность подготовить отчёт автоматически и только затем запустить рассылку.
После подготовки листа он может выглядеть так:
Важно: используйте очевидные имена листа и колонок. Это упростит поддержку и отладку.
Как открыть редактор скриптов и создать функцию
В Google Таблицах выберите меню “Инструменты” → “Редактор сценариев” (Script editor). В открывшемся окне создайте новую функцию и вставьте базовый скрипт ниже.
Базовый пример скрипта для отправки простого текстового письма
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 (var i = 0; i < data.length; i++) {
var rowData = data[i];
var recipient = rowData[0];
var emailAddress = rowData[1];
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);
}
}Этот скрипт читаем и прост. Но у него есть ограничения: зафиксированный диапазон и простая отправка текстом. Ниже мы разберём улучшения.
Разбор кода по строкам
- Получение и активация листа:
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 возвращает двумерный массив.
- Обход строк и формирование письма:
var rowData = data[i];
var emailAddress = rowData[1];
var recipient = rowData[0];
// затем собирается message из фрагментов- Отправка письма:
MailApp.sendEmail(emailAddress, subject, message);MailApp — встроенный сервис Google Apps Script для отправки электронных писем простым способом.
Улучшение: автоматический диапазон и пропуск пустых строк
Фиксированный диапазон неудобен. Лучше определять последний заполненный ряд программно и пропускать пустые почты.
function sendArticleCountEmails_dynamic() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Send-Emails');
var lastRow = sheet.getLastRow();
if (lastRow < 2) return; // нет строк с данными
var dataRange = sheet.getRange(2, 1, lastRow - 1, 6); // с A2 по F
var data = dataRange.getValues();
for (var i = 0; i < data.length; i++) {
var rowData = data[i];
var recipient = rowData[0];
var emailAddress = rowData[1];
if (!emailAddress) continue; // пропустить пустой адрес
// составление и отправка как ранее
}
}Отправка HTML-писем и использование GmailApp
Чтобы облегчить читабельность и добавить форматирование, можно отправлять HTML-письма. Для этого подойдет MailApp.sendEmail с полем htmlBody или GmailApp.
MailApp.sendEmail({
to: emailAddress,
subject: subject,
htmlBody: 'Dear ' + recipient + ',
' + message1 + '
'
});При использовании GmailApp вы получаете дополнительные возможности по работе с черновиками и вложениями.
Добавление вложений из Google Drive
Если нужно отправлять файл-отчёт как вложение, загрузите его из Drive.
var file = DriveApp.getFileById('FILE_ID');
MailApp.sendEmail({
to: emailAddress,
subject: subject,
body: 'Смотрите вложение',
attachments: [file.getAs(MimeType.PDF)]
});Не забудьте использовать корректные права доступа к файлам и указывать правильный MIME-тип, если требуется.
Добавление пункта меню для запуска из таблицы
Чтобы запускать функцию из меню Google Таблиц, используйте onOpen и addMenu. Вставьте в редактор скриптов код ниже.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Send Emails', functionName: 'sendArticleCountEmails_dynamic'}
];
spreadsheet.addMenu('Send Emails', menuItems);
}После сохранения и обновления таблицы в меню появится ваш пункт. Нажатие вызовет функцию.
Обработка ошибок и логирование
Добавьте try/catch и логирование, чтобы знать, какие письма были отправлены, а какие упали с ошибкой. Также полезно записывать статус отправки в соседнюю колонку.
try {
MailApp.sendEmail({to: emailAddress, subject: subject, htmlBody: htmlBody});
sheet.getRange(rowIndex + 2, 8).setValue('SENT');
} catch (err) {
Logger.log('Error sending to ' + emailAddress + ': ' + err);
sheet.getRange(rowIndex + 2, 8).setValue('ERROR: ' + err.message);
}В колонке состояния храните timestamp и статус: SENT / ERROR, чтобы затем можно было повторно обработать неотправленные.
Безопасность, права и лимиты
- Скрипт запросит доступ к вашему аккаунту при первом запуске. Проверьте разрешения и используйте учётную запись с нужными правами.
- Учитывайте ежедневные и почтовые лимиты сервиса Google. Ознакомьтесь с официальной документацией по квотам.
- Не храните в открытых таблицах чувствительную информацию. Если письма содержат персональные данные, удостоверьтесь в соответствии с вашей политикой безопасности и локальными законами.
Тестирование и отладка: советы
- Всегда тестируйте на небольшой выборке, например, на своём адресе.
- Используйте отдельный столбец для отметок об успешной отправке.
- Логируйте ошибки через Logger.log или записывайте их в лист.
- Тестируйте HTML-письма в разных почтовых клиентах.
- Проверяйте, что динамические формулы в таблице корректно обновляются перед рассылкой.
Расширенные шаблоны и локализация
- Если вы отправляете письма на разных языках, заведите колонку “lang” и формируйте тело на нужном языке.
- Для более сложных шаблонов используйте шаблонизатор на стороне скрипта: заменяйте метки вида {{name}} на значение из строки.
Пример простого шаблонизатора:
function renderTemplate(template, data) {
return template.replace(/{{\s*(\w+)\s*}}/g, function(_, key) {
return data[key] || '';
});
}
var template = 'Здравствуйте, {{name}}!\nВаш показатель: {{count}}';
var body = renderTemplate(template, {name: recipient, count: parameter2});Резервный план и повторная отправка
Если рассылка прерывается, используйте колонку статуса для повторной отправки только тех строк, у которых статус не равен SENT. Это простая, безопасная стратегия повторения.
Когда подход не подходит: ограниченные случаи
- Если у вас очень большой объём рассылок (массовые маркетинговые кампании), лучше использовать профессиональные сервисы рассылок и API с поддержкой отписки и аналитики.
- Если у вас строгие требования к GDPR и аудиту, храните логи и согласия отдельно и проконсультируйтесь с юристом по данным.
Чеклист перед первой серьёзной рассылкой
- Проверить список получателей и избавиться от дубликатов.
- Убедиться, что динамические формулы в таблице дают ожидаемые значения.
- Тестировать на ваших адресах и как минимум на нескольких почтовых клиентах.
- Добавить логирование и колонку статуса.
- Убедиться в правах доступа к Drive, если нужны вложения.
- Ознакомиться с текущими квотами Google и правилами отправки почты.
Шаблон SOP для регулярной рассылки (Playbook)
- Подготовка данных: обновить исходные листы и дождаться перерасчётов.
- Открыть лист ‘Send-Emails’ и проверить случайные 5 строк.
- Нажать «Send Emails» в меню.
- Проверить колонку статуса и логи.
- Повторно отправить строки с ошибками после исправления.
Примеры расширений: автоматический триггер и ежемесячная отправка
Чтобы полностью автоматизировать отправку, можно настроить триггер по времени в редакторе скриптов: «Добавить триггер» → запускать функцию по расписанию (напр., ежемесячно). Помните про права и проверку до первого автоматического запуска.
Критерии приёмки
- Все строки с валидным адресом получили статус SENT.
- Нет незапланированных дубликатов рассылки.
- Журнал ошибок пуст или содержит только обработанные исключения.
- Письма отображаются корректно в основных почтовых клиентах.
Полезные сниппеты/шпаргалка
- Получить последний ряд: sheet.getLastRow()
- Получить диапазон по номеру строки и колонки: sheet.getRange(startRow, startCol, numRows, numCols)
- Отправить HTML: MailApp.sendEmail({to: addr, subject: sub, htmlBody: html})
- Приложить файл из Drive: attachments: [file.getAs(MimeType.PDF)]
Частые ошибки и как их исправлять
- Неправильный диапазон → используйте getLastRow и проверяйте число строк.
- Пустые или неверные email → добавляйте валидацию перед отправкой.
- Ошибки прав доступа к файлам → проверьте права на файл в Drive и на приложение (OAuth).
Короткий план действий для менеджера: роль-based checklist
- Менеджеру по продукту: подтвердить содержание и сегментацию получателей.
- Оператору данных: проверить и обновить формулы и источники данных.
- Тестировщику: проверить шаблон письма и итоговое отображение в почтовых клиентах.
Заключение
Автоматизация рассылок через Google Scripts даёт быстрый выигрыш в времени и снижает ручной труд. Начните с простого рабочего скрипта, протестируйте, затем постепенно добавляйте функциональность: HTML-письма, вложения, логирование и автоматические триггеры. Вложите немного усилий в настройку — и сэкономите часы работы каждый период.
Поделитесь в комментариях своими примерами использования Google Scripts — какие шаблоны и приёмы вы применяли для автоматизации?
Сводка
- Настройте лист с адресами и фрагментами сообщения.
- Напишите функцию для динамического диапазона и отправки писем.
- Добавьте меню onOpen для простого запуска.
- Улучшайте скрипт: HTML, вложения, логирование, валидация и обработка ошибок.
Важно: перед массовой рассылкой всегда тестируйте и проверяйте настройки безопасности и квоты.
