Edited at

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

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

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

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

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


作業のサマリ


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

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

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

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

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


作業詳細


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

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


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

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

image

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

image


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

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


  • 抽出したい情報

  • その情報が有効になる日付

  • その情報が無効となる日付

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

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 のスクリプトエディタに貼ります。

image

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行目は見出し行とし、最新のデータを常に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]));

}
}

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

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

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

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

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

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

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

例えば通知されるメール数が不定の場合は、メールの検索条件を調整して、未処理のメールだけを抽出する工夫が必要です。これを実現するアイデアの一つとして、処理済みのメールには処理済みのラベルをつけておく、という方法があると思います。これなら、メール抽出時には、そのラベルがついていないものを抽出するように検索文字列にGmailApp.search("-label:処理済み")にずれば良いはずです。そして処理が行われたメールには処理済みのラベルをスクリプトで自動的に割り当てるようにします。

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

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


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

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