GASでGmailのデータをスプレッドシートに出力する

Posted By: oho 2024年2月22日

はじめに

筆者が担当しているシステムの保守業務の一つで、システムから送信されてくるエラー通知メールを確認し、特定のエラーの場合にログやデータを参照して詳細な調査を行い、お客様に報告するという対応をしています。
業務効率化のため、受信メールをGAS（Google Apps Script）で定期的にチェックし、詳細な調査を必要とするメールの情報をスプレッドシートに出力する仕組みを作ったので、その紹介をします。

前提

  • メールの件名は【〇〇システム】◯◯処理エラー通知のような体裁とする。
  • エラーコードは本文にE-001のような文字列で記載されている。
  • メールサービスはGmailを使用する。Gmailの仕様により同一件名のメール（メッセージ）はスレッドにまとめられる。

エラーメールのイメージは下記の通りです。

要件

受信メールをGASで定期的にチェックし、詳細な調査を必要とするメールについて、受信日時、件名、エラーコードをスプレッドシートに出力する（筆者がスプレッドシートを毎朝チェックし、必要に応じて調査と報告を行うという運用を想定しています）

おことわり

実際の業務では取り扱う情報や調査の要否を判断する条件がいくつかあるのですが、本記事では説明を簡単にするためにそのあたりの要素は省略しています。

手順

スプレッドシート作成

Googleドライブでスプレッドシートを作成し、適切な名前を付けます。シート名はメールにしておきます。1行目はヘッダとして、日付、時刻、件名、エラーコード、ステータスの列を記載します。ステータスの列は調査が完了したときに手動で入力するための列です。行の固定やフィルタは任意です。

GASプロジェクト作成

スプレッドシートのメニュー拡張機能のApps ScriptをクリックするとGASの画面が開くので、適切な名前を付けます。

コード作成

コードのエディタに下記のコードを貼り付けます。差出人のメールアドレスは適切に設定してください。また、下記のコードでは一度GASでチェックしたメールにはGASチェック済みというラベルを付けるので、Gmailの画面で予めラベルを作成してください。

// 差出人
const FROM_ADDRESS = 'xxxx@example.com';
// 件名
const ERROR_SUBJECT = '【〇〇システム】◯◯処理エラー通知';
// GASでチェックしたメッセージに付けるラベルの名称
const LABEL_NAME = 'GASチェック済み';
// メールの情報を出力するスプレッドシートのシート名
const MAIL_SHEET_NAME = 'メール';

function main() {
  // エラーメールのスレッドを取得
  // [条件]
  // 1.差出人がシステムのメールアドレスである
  // 2.件名がエラーメールを示す文字列を含む
  // 3.GASチェック済みのラベルが付いてないメッセージを含む
  let threads = GmailApp.search(`from:${FROM_ADDRESS} subject:${ERROR_SUBJECT} -label:${LABEL_NAME}`);
  // 記録対象のメッセージの情報を格納する配列
  let messageInfo = [];
  // スレッド単位の処理
  threads.forEach(function(thread) {
    // スレッド内のメッセージを取得
    let messages = thread.getMessages();
    // メッセージ単位の処理
    messages.forEach(function(message) {
      // メッセージの情報を保持する
      messageInfo.push(getMessageInfo(message));
    });
    // スレッドに処理済みラベルを付ける
    let label = GmailApp.getUserLabelByName(LABEL_NAME);
    thread.addLabel(label);
  });
  // メッセージを日時の降順でソート
  messageInfo.sort(function(a, b) {
    return a.date - b.date;
  });
  // メッセージをスプレッドシートに出力する
  addMessagesToSpreadSheet(messageInfo);
}

// メッセージの情報を取得する
function getMessageInfo(message) {
  // 日付
  let date = Utilities.formatDate(message.getDate(), 'Asia/Tokyo', 'yyyy/MM/dd');
  // 時刻
  let time = Utilities.formatDate(message.getDate(), 'Asia/Tokyo', 'HH:mm:ss');
  // 件名
  let subject = message.getSubject();
  // スプレッドシートに出力する情報
  let info = {
    date: date,
    time: time,
    subject: subject,
    errorCode: null,
  }
  // 本文中のエラーコードを取得
  let plainBody = message.getPlainBody();
  let result = plainBody.match(/E-\d{3}/);
  if (result) {
    info.errorCode = result[0];
  }
  return info;
}

// スプレッドシートにメッセージの情報を出力する
function addMessagesToSpreadSheet(messageInfo) {
  // スプレッドシートを取得
  let sheet = SpreadsheetApp.getActive().getSheetByName(MAIL_SHEET_NAME);
  messageInfo.forEach(function(info) {
    // ２行目に空白行を挿入
    sheet.insertRows(2, 1);
    // 値を書き込む
    sheet.getRange(2, 1).setValue(info.date);
    sheet.getRange(2, 2).setValue(info.time);
    sheet.getRange(2, 3).setValue(info.subject);
    sheet.getRange(2, 4).setValue(info.errorCode);
  });
}

できればメッセージ単位で処理したいのですが、スレッド単位で処理する関数しか見当たらなかったのでそのようにしています。

実行

実行ボタンを押します。初回はGASがGoogleアカウントにアクセスする許可を求めるメッセージが出るので許可します。スプレッドシートに下記のように出力されます。

問題点

ここで一つ困ったことがありました。さらにエラーメールが届いてスレッドに追加された場合、上記のコードを再実行すると、スレッド内のすべてのメッセージが再度処理されてスプレッドシートに出力されてしまいます（前回出力したメッセージも再度出力される）。
上にも書いたように、本当はメッセージ単位で「GASチェック済み」のラベル有無を判定したかったのですが、筆者が探した限りではそのような関数は見当たりませんでした。

対策

そこで苦肉の策として、別のシートにコードの最終実行日時を記録し、実行のたびに前回実行日時～今回実行日時の間に受信したエラーメールのみスプレッドシートに出力することにしました。最終実行日時のシートを下記のように追加し、過去の日時を入力しておきます。

コードの改修

上記対策の処理を追加したコードが下記です。

// 差出人
const FROM_ADDRESS = 'xxxx@example.com';
// 件名
const ERROR_SUBJECT = '【〇〇システム】◯◯処理エラー通知';
// GASでチェックしたメッセージに付けるラベルの名称
const LABEL_NAME = 'GASチェック済み';
// メールの情報を出力するスプレッドシートのシート名
const MAIL_SHEET_NAME = 'メール';
// コードの最終実行日時を記録するシート名
const LAST_EXEC_DATETIME_SHEET_NAME = '最終実行日時';

function main() {
  // 実行日時を取得
  const now = new Date();
  // 前回の実行日時を取得
  let sheet = SpreadsheetApp.getActive().getSheetByName(LAST_EXEC_DATETIME_SHEET_NAME);
  const lastExecDatetime = new Date(sheet.getRange(1, 1).getValue());
  // エラーメールのスレッドを取得
  // [条件]
  // 1.差出人がシステムのメールアドレスである
  // 2.件名がエラーメールを示す文字列を含む
  // 3.GASチェック済みのラベルが付いてないメッセージを含む
  let threads = GmailApp.search(`from:${FROM_ADDRESS} subject:${ERROR_SUBJECT} -label:${LABEL_NAME}`);
  // 記録対象のメッセージの情報を格納する配列
  let messageInfo = [];
  // スレッド単位の処理
  threads.forEach(function(thread) {
    // スレッド内のメッセージを取得
    let messages = thread.getMessages();
    // メッセージ単位の処理
    messages.forEach(function(message) {
      // 送信日時が前回実行以前であれば処理されているはずなのでスキップ
      if (message.getDate() < lastExecDatetime) {
        return;
      }
      // メッセージの情報を保持する
      messageInfo.push(getMessageInfo(message));
    });
    // スレッドに処理済みラベルを付ける
    let label = GmailApp.getUserLabelByName(LABEL_NAME);
    thread.addLabel(label);
  });
  // メッセージを日時の降順でソート
  messageInfo.sort(function(a, b) {
    return a.date - b.date;
  });
  // メッセージをスプレッドシートに出力する
  addMessagesToSpreadSheet(messageInfo);
  // 最終実行日時を更新する
  sheet.getRange(1, 1).setValue(now);
}

// メッセージの情報を取得する
function getMessageInfo(message) {
  // 日付
  let date = Utilities.formatDate(message.getDate(), 'Asia/Tokyo', 'yyyy/MM/dd');
  // 時刻
  let time = Utilities.formatDate(message.getDate(), 'Asia/Tokyo', 'HH:mm:ss');
  // 件名
  let subject = message.getSubject();
  // スプレッドシートに出力する情報
  let info = {
    date: date,
    time: time,
    subject: subject,
    errorCode: null,
  }
  // 本文中のエラーコードを取得
  let plainBody = message.getPlainBody();
  let result = plainBody.match(/E-\d{3}/);
  if (result) {
    info.errorCode = result[0];
  }
  return info;
}

// スプレッドシートにメッセージの情報を出力する
function addMessagesToSpreadSheet(messageInfo) {
  // スプレッドシートを取得
  let sheet = SpreadsheetApp.getActive().getSheetByName(MAIL_SHEET_NAME);
  messageInfo.forEach(function(info) {
    // ２行目に空白行を挿入
    sheet.insertRows(2, 1);
    // 値を書き込む
    sheet.getRange(2, 1).setValue(info.date);
    sheet.getRange(2, 2).setValue(info.time);
    sheet.getRange(2, 3).setValue(info.subject);
    sheet.getRange(2, 4).setValue(info.errorCode);
  });
}

実行すると、前回実行日時～今回実行日時の間に受信したエラーメールがスプレッドシートに追加されます。

定期実行の設定

コードが定期的に実行されるように設定しておきます。詳細はこちらの記事を参照してください。

おわりに

今回はシンプルな例を紹介したのであまり必要性を感じられなかったかもしれませんが、同じような対応が必要な方の参考になれば幸いです。もしメッセージ単位で処理する方法をご存知の方はコメント頂けますと幸いです。