GoogleAppsScript
gmail
GoogleSpreadSheet

Gmailで受信した情報をGoogleAppsScriptで抽出してGoogle Spreadsheetに転記する

とても古いシステムでは、何かのデータが追加や更新されるたびにメールで通知されるように実装されている場合があります。

こういったシステムではメール本文の形式が所定の書式で出力されるように実装されていることが多く、また、そのデータは Excel のファイルに手作業で追記するという運用も併せて行われているだろうと思います。

しかしこういうデータを手作業でコピペするのは時間の無駄なので、Google Apps Script を用いてメール本文から必要な情報を抽出して Google SpreadSheet に転記してみます。

Google Apps Script を使用しますので、メールの受信は Gmail を前提とします。自社のメールが G Suite の場合はそのまま適用可能ですが、そうではないケースでは、特定の情報を一般向けの Gmail アカウントで扱うことが情報セキュリティポリシーに抵触しないことを確認してください。

作業のサマリ

  1. メールで受信したデータを保存するスプレッドシートを新規に作成する。
  2. スプレッドシートの「ツール」メニューから「スクリプトエディタ」を選んで、スクリプトの編集画面を出す。
  3. 下記のスクリプトをスクリプトエディタに貼った上で、内容を適切に修正する。
  4. スプレッドシートのURLや、データの保存先タブの名前を、スクリプト内の固定文字列部分に置換する。
  5. 試験実行して目的のデータが抽出できることを確認する。

このような流れで動くものができたら、あとはこのスクリプトを Timer で定期的に実行させれば実装は完了です。(本来はメール受信のイベントドリブンで運用したいけれど、そういう方法は無いようです)

作業詳細

メールで受信したデータを保存するスプレッドシートを新規に作成する。

これは http://drive.google.com/ あたりから行えばよいだけなので、特記事項なし。

スプレッドシートの「ツール」メニューから「スクリプトエディタ」を選んで、スクリプトの編集画面を出す。

こんな風にメニューを選ぶと、
image

スクリプトエディタが開きます。
image

下記のスクリプトをスクリプトエディタに貼った上で、内容を適切に修正する。

この処理ではメールの中に以下の情報が含まれているものとします。

  • 抽出したい情報
  • その情報が有効になる日付
  • その情報が無効となる日付

これが、例えば、こんな感じでメールで送られてきます。

From: どこかのシステム
To: 受け側のシステム
Subject: なにかのサブジェクト

Start Time: 05/12/2018 12:00:00 AM
End Time: 05/19/2018 12:00:00 AM

何かの情報のインデックス: 実際の情報

これらを抽出して Google Spreadsheet に転記する処理を Google Apps Script で実装するわけです。

使用するスクリプトの例は以下のとおりですので、まずは Google Apps Script のスクリプトエディタに貼ります。

image

var SearchString = "[メールを検索するための条件を書く]";

function myFunction() {
  var myThreads = GmailApp.search(SearchString, 0, 1);
  var myMsgs = GmailApp.getMessagesForThreads(myThreads);

  for ( var threadIndex = 0 ; threadIndex < myMsgs.length ; threadIndex++ ) {
    // メールから data と Start, End を抜き出す
    var mailBody = myMsgs[threadIndex][0].getPlainBody();

    // 正規表現マッチにより、メール本文から情報を抽出する。
    var data      = mailBody.match(/[何かの正規表現](.+)[何かの正規表現]/);

    // 今回の事例では、上記情報の有効期限がメール本文に差し込まれてきているため、それも抽出しておく。
    var startTime = mailBody.match(/Start Time: ([0-9]+)\/([0-9]+)\/([0-9]+) /);
    var endTime   = mailBody.match(  /End Time: ([0-9]+)\/([0-9]+)\/([0-9]+) /);

    // シートへ値を追加する
    var objSpreadSheet = SpreadsheetApp.openByUrl("[データの差し込みを行うスプレッドシートのURLを書く]");
    var objSheet = objSpreadSheet.getSheetByName("[データを差し込むシート名を書く]");

    // 指定されたシートの1行目は見出し行とし、最新のデータを常に2行目に表示させたいので、1行目の後ろ(2行目)に空行を差し込む。
    objSheet.insertRowAfter(1);

    // 抽出した情報は A2 セルに書き込む。
    objSheet.getRange("A2").setValue(data[1]);

    // メール本文から抽出した有効期限情報は B2 = 開始日、C2 = 終了日 に書き込む。
    // ここでは mm/dd/yyyy なフォーマットで日付が表記されている前提で処理を行っている。   
    objSheet.getRange("B2").setValue(Utilities.formatString("%d/%d/%d", startTime[3], startTime[1], startTime[2]));
    objSheet.getRange("C2").setValue(Utilities.formatString("%d/%d/%d",   endTime[3],   endTime[1],   endTime[2]));

  }
}

スプレッドシートのURLや、データの保存先タブの名前を、スクリプト内の固定文字列部分に置換する。

[データの差し込みを行うスプレッドシートのURLを書く] や [データを差し込むシート名を書く] の部分を実際のスプレッドシートに合わせます。

検索条件を設定してメールを適切にフィルタリングする

対象となるメールの検索条件を GmailApp.search() の SearchString に設定します。この検索条件は Gmail で実際にメール検索する際の条件文字列をそのまま記述できます。

今回の実装ではメール検索で見つかったメールのうち、最新のものだけを対象に処理を行うために GmailApp.search(SearchString, 0, 1) と実装しています。しかしこのあたりのフィルタリングは様々な工夫がありますので、実際の事情に合わせて調整します。例えば、通知される情報の数が不定の場合は、情報を抽出したメールには特定のラベルを付与しておく、というフィルタリングができると思います。そうすると検索時にはラベルが付いていないメールを対象とした検索文字列が使えます。

情報の抽出処理を実装する

今回の実装では抽出データ、開始日、終了日の3つをメールから抽出します。data や startTime, endTime の変数に抽出したデータが格納されます。この実装では正規表現マッチでデータを取り出しています。おそらくはこの手のメールを処理するロジックは正規表現マッチで書けるケースが多いと思うのですが、要件に合わせて適切な方法で実装します。

試験実行して目的のデータが抽出できることを確認する。

試験実行して、値がシートに入るようなら実装は完了です。あとは Timer で定期的に実行されるように仕込みましょう。最短では1分ごとの実行トリガーを設定できますが、こんなに短いタイミングでの実行は不要だと思います。せいぜい、10分に1回くらいでも実務上の問題は無いと思いますし、更新頻度や参照頻度によっては1時間毎、1日毎、くらいの頻度でも十分なはずです。