Как экспортировать почту Gmail в Google Sheets с помощью Google Apps Script?

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

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

ChatGPT

Google AI

Grok

Perplexity

Преимущества экспорта данных Gmail в Google Sheets

• Централизованный анализ: Таблицы Google позволяют легко фильтровать, сортировать и визуализировать данные из писем.
• Автоматизация рабочих процессов: Данные из писем могут триггерить дальнейшие действия в других сервисах Google или внешних системах.
• Резервное копирование: Создание структурированной копии важной переписки.
• Отслеживание: Мониторинг статусов заказов, обращений клиентов, лидов из почты.

Области применения: Анализ почты, резервное копирование, отслеживание задач

Практические кейсы включают:

• Анализ маркетинговых кампаний: Сбор откликов на email-рассылки.
• Мониторинг клиентской поддержки: Агрегация запросов из почты для анализа SLA и тем обращений.
• Управление проектами: Извлечение задач и дедлайнов из переписки.
• Финансовый учет: Сбор счетов и чеков из почты.

Необходимые знания: Google Apps Script и основы работы с Gmail API

Для успешной реализации потребуется понимание основ синтаксиса Google Apps Script (основанного на JavaScript) и базовое знакомство с объектами и методами сервиса GmailApp, который предоставляет доступ к функциям Gmail.

Подготовка: Настройка Google Apps Script и Google Sheets

Создание нового Google Sheets документа

Начните с создания новой таблицы в Google Sheets. Именно сюда будут экспортироваться данные из Gmail. Задайте заголовки столбцов в первой строке, например: Дата, Отправитель, Получатель, Тема, Текст письма.

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

В созданном документе Google Sheets перейдите в меню Расширения -> Apps Script. Откроется редактор скриптов, привязанный к вашей таблице.

Активация Gmail API в Google Apps Script (необходимость и процедура)

При первом запуске скрипта, использующего сервис GmailApp, Google автоматически запросит у вас необходимые разрешения на доступ к вашему почтовому ящику Gmail. Явная активация API в Google Cloud Console обычно не требуется для базовых сценариев с GmailApp.

Настройка прав доступа для Google Apps Script проекта

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

Написание кода: Экспорт данных из Gmail в Google Sheets

Получение доступа к Gmail API и выбор почтового ящика

В Google Apps Script доступ к почте текущего пользователя осуществляется через глобальный объект GmailApp. Нет необходимости в сложной аутентификации, так как скрипт выполняется от имени пользователя, его запустившего (после авторизации).

Поиск писем по заданным критериям (отправитель, тема, дата)

Сервис GmailApp предоставляет метод search(query, start, max), который позволяет искать письма с использованием стандартного синтаксиса поисковых запросов Gmail. Например:

• 'from:support@example.com' — письма от конкретного отправителя.
• 'subject:"Ваш заказ"' — письма с определенной темой.
• 'newer_than:7d' — письма за последнюю неделю.
• 'label:unread' — непрочитанные письма.

Критерии можно комбинировать: 'from:no-reply@example.com subject:"Отчет" newer_than:1d'.

Извлечение необходимых данных из каждого письма (отправитель, получатель, тема, дата, текст письма)

Результатом поиска GmailApp.search() является массив объектов GmailThread. Каждый поток может содержать одно или несколько сообщений (GmailMessage). Необходимо итерировать по потокам и сообщениям, извлекая нужную информацию с помощью методов:

• message.getDate()
• message.getFrom()
• message.getTo()
• message.getSubject()
• message.getPlainBody() или message.getBody() (для HTML)

Запись извлеченных данных в Google Sheets

Для записи данных используется сервис SpreadsheetApp.

1. Получаем активный лист: SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().
2. Подготавливаем данные для записи в виде двумерного массива, где каждый вложенный массив представляет строку.
3. Используем метод sheet.appendRow(rowData) для добавления одной строки или sheet.getRange(startRow, startCol, numRows, numCols).setValues(dataArray) для пакетной записи, что более эффективно.

Оптимизация скрипта для обработки большого количества писем (пагинация)

Gmail API имеет квоты на время выполнения скрипта и количество обрабатываемых данных. Для обработки больших объемов почты:

• Используйте пагинацию в GmailApp.search(query, start, max), обрабатывая письма порциями (например, по 50-100 за раз).
• Между обработкой порций можно использовать Utilities.sleep(milliseconds), чтобы избежать превышения лимитов частоты запросов.
• Рассмотрите использование триггеров по времени для запуска скрипта с интервалами, сохраняя состояние (например, дату последнего обработанного письма) в PropertiesService.

Пример кода и его объяснение

Полный код Google Apps Script для экспорта почты

/**
 * Экспортирует письма из Gmail в активный лист Google Sheets
 * на основе заданных критериев поиска.
 */
function exportGmailToSheets(): void {
  // Настройки
  const searchQuery: string = 'label:inbox newer_than:7d'; // Пример: непрочитанные письма за последнюю неделю
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startRow: number = sheet.getLastRow() + 1; // Начать запись после последней заполненной строки
  const maxEmailsPerRun: number = 100; // Максимальное количество писем для обработки за один запуск

  try {
    // Поиск потоков писем
    const threads: GoogleAppsScript.Gmail.GmailThread[] = GmailApp.search(searchQuery, 0, maxEmailsPerRun);

    if (threads.length === 0) {
      Logger.log('Новые письма по запросу "%s" не найдены.', searchQuery);
      return;
    }

    const dataToAppend: any[][] = [];

    // Обработка каждого потока
    threads.forEach((thread: GoogleAppsScript.Gmail.GmailThread) => {
      const messages: GoogleAppsScript.Gmail.GmailMessage[] = thread.getMessages();

      // Обработка каждого сообщения в потоке
      messages.forEach((message: GoogleAppsScript.Gmail.GmailMessage) => {
        const messageDate: Date = message.getDate();
        const sender: string = message.getFrom();
        const recipients: string = message.getTo(); // Может содержать несколько адресов
        const subject: string = message.getSubject();
        const body: string = message.getPlainBody().substring(0, 500); // Ограничиваем длину тела письма

        // Добавляем данные для записи в массив
        dataToAppend.push([
          messageDate,
          sender,
          recipients,
          subject,
          body
        ]);
      });
    });

    // Запись данных в таблицу
    if (dataToAppend.length > 0) {
      const numRows: number = dataToAppend.length;
      const numCols: number = dataToAppend[0].length;
      sheet.getRange(startRow, 1, numRows, numCols).setValues(dataToAppend);
      Logger.log('%s писем успешно экспортировано в лист "%s".', numRows, sheet.getName());
    }

  } catch (error) {
    // Обработка ошибок
    if (error instanceof Error) {
      Logger.log('Ошибка при экспорте писем: %s\n%s', error.message, error.stack);
      // Дополнительно: отправить уведомление об ошибке
      // MailApp.sendEmail('admin@example.com', 'Ошибка скрипта экспорта Gmail', error.message);
    } else {
      Logger.log('Произошла неизвестная ошибка: %s', error);
    }
  }
}

/**
 * Добавляет пользовательское меню для запуска скрипта.
 */
function onOpen(): void {
  SpreadsheetApp.getUi()
    .createMenu('Экспорт Gmail')
    .addItem('Запустить экспорт', 'exportGmailToSheets')
    .addToUi();
}

Подробное объяснение каждой части кода

1. exportGmailToSheets(): Основная функция.
   • searchQuery: Строка с критериями поиска писем в Gmail.
   • sheet: Получает ссылку на активный лист таблицы.
   • startRow: Определяет строку, с которой начнется запись новых данных.
   • maxEmailsPerRun: Ограничивает количество обрабатываемых писем за раз для предотвращения таймаутов.
   • GmailApp.search(): Выполняет поиск потоков писем по заданному запросу.
   • Циклы forEach: Перебирают найденные потоки и сообщения внутри них.
   • message.get...(): Методы для извлечения данных из каждого сообщения.
   • dataToAppend.push(): Формирует массив строк для записи в таблицу.
   • sheet.getRange().setValues(): Эффективно записывает все собранные данные в таблицу одним вызовом.
   • try...catch: Блок для перехвата и логирования возможных ошибок во время выполнения.
   • Logger.log(): Записывает информацию о ходе выполнения и ошибках в лог Apps Script.
2. onOpen(): Стандартная функция-триггер Apps Script. Выполняется при открытии таблицы и добавляет пользовательское меню «Экспорт Gmail» с пунктом для запуска функции exportGmailToSheets().

Настройка параметров скрипта (почтовый ящик, критерии поиска, диапазон записи в Sheets)

• Почтовый ящик: Скрипт всегда работает с ящиком того пользователя, который его авторизовал и запустил. Для доступа к другому ящику (например, общему) потребуется другая настройка (например, делегирование доступа в Gmail или использование Service Account, что выходит за рамки GmailApp).
• Критерии поиска: Измените значение переменной searchQuery в соответствии с вашими требованиями к фильтрации писем.
• Диапазон записи: Скрипт автоматически определяет следующую свободную строку (sheet.getLastRow() + 1). Заголовки столбцов должны быть в первой строке листа. Убедитесь, что порядок данных в dataToAppend.push([...]) соответствует порядку столбцов в вашей таблице.

Запуск и автоматизация: Выполнение скрипта и планирование задач

Ручной запуск скрипта в редакторе Google Apps Script

Откройте редактор скриптов (Расширения -> Apps Script). Выберите функцию exportGmailToSheets в выпадающем списке над редактором кода и нажмите кнопку Выполнить. При первом запуске потребуется предоставить разрешения.

Также можно запустить скрипт из пользовательского меню, созданного функцией onOpen(), непосредственно в Google Sheets.

Настройка триггеров для автоматического запуска скрипта (по времени, при изменении Sheets)

Для автоматизации экспорта:

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

Устранение ошибок и отладка скрипта

• Логи: Используйте Logger.log() для вывода значений переменных и отслеживания хода выполнения. Просмотреть логи можно в редакторе Apps Script в разделе Выполнения.
• Отладчик: В редакторе доступен встроенный отладчик (значок жука), позволяющий устанавливать точки останова и пошагово выполнять код.
• Квоты Google Apps Script: Следите за ограничениями на время выполнения скрипта (6 минут для обычных аккаунтов), количество вызовов API и т.д. Информация доступна в панели управления скриптом и в официальной документации Google.
• Ошибки авторизации: Если скрипт перестал работать, проверьте, не отозваны ли разрешения, и при необходимости авторизуйте его заново.

Дополнительные возможности: отправка уведомлений, логирование

• Уведомления: Используйте MailApp.sendEmail() для отправки email-уведомлений о завершении работы скрипта, найденных данных или ошибках.
• Расширенное логирование: Вместо Logger.log() можно записывать логи в отдельный Google Sheet документ или использовать console.log() для интеграции с Google Cloud Logging (требует настройки проекта в Google Cloud Platform).
• Обработка дубликатов: Добавьте логику для проверки, не было ли уже экспортировано конкретное письмо (например, по его ID — message.getId()), чтобы избежать дублирования записей при повторных запусках.