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

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

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

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

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

作業のサマリ

1. メールで受信したデータを保存するスプレッドシートを新規に作成する
2. スプレッドシートの「ツール」メニューから「スクリプトエディタ」を選んで、スクリプトの編集画面を出す
3. この記事で例示するスクリプトをスクリプトエディタに貼った上で、内容を適切に修正する
4. 試験実行して目的のデータが抽出できることを確認する

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

作業手順はできるだけ詳細に記していますが、メールからの抽出という処理の都合上、どうしても個別の要件に合わせたカスタマイズが必須です。Gmail 自体の検索条件を作ったり、メール本文からの情報抽出を正規表現で記述するといった処理は要件にあわせて頑張って実装してください。

Google Apps Script 自体に不慣れな方は、下記記事の実装の実装を先に試しておくのも良いと思います。
Google Spreadsheet の googletranslate 関数の代わりに LanguageApp を使うワークシート関数を作ってイケてる翻訳ができるようにする

作業詳細

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  for ( var threadIndex = 0 ; threadIndex < myThreads.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]+) /);

    // シートへ値を追加する：最初に作ったコードで、Spreadsheet への依存性があるコード
    // 単独の Google Apps Script から Spreadsheet を操作する場合はこう書く
    // var objSpreadSheet = SpreadsheetApp.openByUrl("[データの差し込みを行うスプレッドシートのURLを書く]");
    // var objSheet = objSpreadSheet.getSheetByName("[データを差し込むシート名を書く]");

    // シートへ値を追加する：getActive()を使って Spreadsheet への依存性を下げたコード
    // Spreadsheet の Apps Script として書く場合はこれでよい
    var objSheet = SpreadsheetApp.getActive().getSheetByName("[データを差し込むシート名を書く]");

    // 指定されたシートの1行目は見出し行とし、最新のデータを常に２行目に表示させたいので、１行目の後ろ（２行目）に空行を差し込む。
    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]));

  }
}

このコードは、少なくとも、下記３点を調整してください。

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

[データを差し込むシート名を書く] の部分を実際のスプレッドシートで使用するシート名に合わせます。Spreadsheet のメニューから作成したスクリプトでは、SpreadsheetApp.getActive() で Spreadsheet Object が取得できるので、タブ名だけを SpreadsheetApp.getActive().getSheetByName() に指定するだけで済みます。

なお、以前に買いていたコードでは、SpreadsheetApp.openByUrl("[データの差し込みを行うスプレッドシートのURLを書く]"); のようにしてスプレッドシートを取得していました。このときは getActive() を知らなかったために、こういう依存性の高い残念な実装をしてしまいました。ただし、この方法は Spreadsheet とは別のスクリプトから操作する場合には必須の実装なので、用途にあわせて２種類の実装ができることは理解しておきたい点です。

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

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

この例ではメール検索で見つかったメールのうち、最新のものだけを対象に処理を行うという想定で　GmailApp.search(SearchString, 0, 1) と実装しています。しかし実際の検索条件は実際の事情に合わせて適切に調整します。

例えば集計処理を1日に1回だけ実行すれば良いなら、検索条件に newer_than:1d をつけておけば、24時間以内に受信したメールだけを処理できます。

しかし集計をもっと頻繁に実行したい場合や処理を不定期に実行したい場合は、未処理のメールだけを抽出する工夫が必要です。これを実現するアイデアの一つとして、処理済みのメールに処理済みのラベルをつけることで未処理のメールを抽出しやすくするという方法があります。メール抽出時には、ラベルがついていないものを抽出するように検索文字列にGmailApp.search("-label:処理済みフラグ")にして、処理が行われたメールには「処理済みフラグ」のラベルをスクリプトで自動的に割り当てるように実装すればよいわけです。

このような実装は次のように行えます。

var SearchString = "from:なにかのアドレス Subject:所定のサブジェクト -label:処理済みフラグ";

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

  for ( var threadIndex = 0 ; threadIndex < myThreads.length ; threadIndex++ ) {
    // メールを抽出してSpreadsheetに追加する処理をここに書く

    // 処理済みラベルを付ける処理
    var LabelProceed = _createLabel("処理済みフラグ");
    myThreads[threadIndex].addLabel(LabelProceed);
  }
}

function _createLabel(labelString) {
  labelDomain = GmailApp.getUserLabelByName(labelString);

  // 指定されたラベルが無い場合は作る
  if ( labelDomain == null ) {
    labelDomain = GmailApp.createLabel(labelString);
  }

  return labelDomain;
}

ただし、この実装を行っても過去に処理済みのメールが再度処理されてしまうケースがあるようです。たとえば Subject が不変の場合は Gmail 側が適当に同じスレッドにまとめてくれたりしますが、それによって新しいメールと古いメールが混じることで再度処理対象となってしまうことがあるようです。

このような状況が起きる場合は次のリンク先で紹介されているような実装で重複データを削除するのが簡単です。ただしこの実装を用いるとシートの更新履歴は使い物にならなくなる点に注意してください。 sheet.clearContents(); という実装を含むので、実行するごとにシート全体の書き換えが発生します。

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

今回の実装例では3つの情報（開始日、終了日、その他メール本文に含まれるキーワード）をメールから取り出しています。これらの値は正規表現でメール本文から抽出してstartTime, endTime, dataの配列変数に格納しています。正規表現の()で囲んだ部分が順番にstartTime[1], startTime[2], startTime[3] などに格納されていますので、これを適切に組み合わせてGoogle SpreadSheetのセルに書き込めばよいわけです。

この部分だけはどうしても個別の要件ごとの実装が必要なので、目的を満たす抽出処理を実装できるように各自で頑張って頂く必要があります。プログラミング経験が少ない方にはちょっとだけ荷が重いかもしれませんが、情報の抽出方法は実際の要件に合わせて適切に調整してください。

その際は次のように正規表現のパターンマッチだけをテストする関数を作ると良いでしょう。変数 mailBody に実際のメール本文を設定してパターンマッチを実行し、抽出された文字列をログに出力させてみます。

function regExTestcode() {

  var mailBody = 
  "Start Time: 05/12/2018 12:00:00\n\
  End Time: 05/19/2018 12:00:00\n\
  \n\
  何かの情報のインデックス: 実際の情報";

  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]+) /);

  Logger.log(data[1]);

  Logger.log(Utilities.formatString("%d/%d/%d", startTime[3], startTime[1], startTime[2]));
  Logger.log(Utilities.formatString("%d/%d/%d",   endTime[3],   endTime[1],   endTime[2]));
}

これを実行するとログに次のように出力されますので、意図通りに情報が抽出できることが確認できました。

これと同様に大抵のケースでは抽出処理を正規表現で書けるはずですが、正規表現を読み書きするのは意外に手間が掛かりますので、regexr.com や regexper.com などを利用して実際の動作を試しながら実装したり、書いた正規表現を可視化したうえで実際の実装に落とし込むのが便利です（ただし regexr.com を利用するのは関係者外秘の情報を含まない場合だけですが）。

regexr.com や regexper.com で実際に試す場合の例は以下で確認してみてください。

https://regexr.com/4rnsg
https://regexr.com/4rnsj
https://regexr.com/4rnsm

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

試験実行して、値がシートに追記されたら実装は完了です。あとはこのスクリプトが Timer で定期的に実行されるように仕込みましょう。最短では1分ごとの実行トリガーを設定できますが、通常は10分に1回くらいでも実務上の問題は無いでしょう。更新頻度や参照頻度によっては1時間毎、1日毎、くらいの頻度でもよいはずです。