Gmail で受領したメールを GAS でスプレッドシートに取り込む

はじめに

_s__o_ です。

最近のおしゃんなシステムであれば、イベント (障害) などを検知して、API を発行したりして他システムとの連携を図れると思います。しかしながら、これがちょっと古いシステムだと、SNMP トラップがついていればまだいい方で、大体のものがメールによるアラート通知となっています (まあ、メールアラートすらないものもままありますが。。。)。

メールアラートは、仕込みや受け取りが楽な反面、受領したものを俯瞰視 (イベントを並べて比較したり、相関を見つけたりなど) することにはあまり適していません。逆に、このあたりが得意なのは、やはり表計算のソフトウェアとなります。1 イベント毎 各行に入れ、フィルタやソート機能などで絞ったり並べ替えたり、必要に応じてグラフで可視化したりなど、色々できます。

というわけで、今回は Gmail で受領したメールアラートを、GAS (Google Apps Script) を用いて、Google スプレッドシートに転写してみたいと思います。

使うもの

• Gmail
    - 適当なラベルを 1 つ準備します。なぜ必要かは後述します。また、このラベルを付与するルールも準備します
• Google スプレッドシート
    - 適当に準備します。3 列しか使いません。また、1 行目はヘッダ行として使用します
• GAS
    - 上記のスプレッドシートに紐付ける形で作成します。Gmail にもアクセスするため、初回実行時に確認プロンプト (Gmail へのアクセスを許可するか) がでますので留意しておいてください

前提

アラートメール

上記のようなアラートメールが届く前提です。障害の場合は「【xxx】機器障害 通知メール」、復旧の場合は「【xxx】機器障害 通知メール【復旧】」という件名となります。また、メール本文には、「:」区切りで諸情報 (対象機器、IP アドレスなど) が記載されています。

準備

Gmail

未処理スレッドを特定するために「99.GAS処理前」というラベルを作成します。また、メール受信のタイミングで、特定件名のスレッドに左記ラベルを付与するよう、フィルタルールを設定します。

スプレッドシート

適当に準備します。1 行目はヘッダ行なので、左から順番に「日時」列、「状態」列、「機器名」列とします。既に値が入っていますが、下記のようなイメージです。

GAS

上記のスプレッドシートから作成していきます。作成方法は本記事では割愛しますので、必要に応じて こちら などを参照してください。

Gmail とラベルの仕様

Gmail の仕様

ここで Gmail の仕様に触れておきます。普段 Gmail に触れる際は意識することはありませんが、GAS から Gmail を扱う際は留意する必要があります。

上図は、Gmail のオブジェクト構造を示しています。一番大事なポイントとして、Gmail は「スレッド」という単位で大きく管理されています。我々が一回のメール送受信でやりとりする内容は、「メッセージ」という「スレッドの要素の一つ」として扱われます。

ですので、たとえば、我々にとっては「2020/06/14 20:11 xxx さんからのメール」であっても、Gmail 的には「"スレッド 2" の "メッセージ 2b"」だったりします。当然、そのメールを参照するときも「スレッド名[要素番号]」みたいな呼び出し方をします。

ラベルの仕様

上記の Gmail 仕様のもう一つ大きな点は、「ラベルはスレッドに紐付く」ということです。メッセージではないのです。メッセージを「束ねた」スレッドに、紐付くのです。

たとえば、GAS で処理したメッセージに目印としてラベル (「処理済み」ラベル等) を付けた場合、そのラベルはスレッドについてしまいます。仮に新しいメッセージがそのスレッドに属した場合、既にラベルが付与されているため、新しいメッセージにも関わらず GAS の処理対象とはならないのです。

地味なハマりどころでした。

ラベルの仕様を受けての対策

上述の例のように、当初は GAS で処理済みのメッセージにラベルを付けるような実装を考えていました。しかしながら、ラベルはメッセージではなくスレッド単位にくっついてしまうため、新メッセージの処理が漏れてしまう可能性があります。

そこで発想を逆転し、GAS で処理後「処理済みのラベルを付ける」のではなく、GAS で処理後「処理前のラベルを外す」ようにしました。Gmail のフィルタルールで都度ラベルを付与しているのはそのためです。

ただ、Gmail のフィルタで「ラベルを付け」、GAS の処理で「ラベルを外す」ことで、新メッセージの取り漏らしは無くなりましたが、逆に旧メッセージの重複取得が発生するようになりました。そのため、今回の GAS のコードでは、最後に重複排除する処理を入れています。

GAS のコード

コード全文

// https://qiita.com/kazinoue/items/1e8ed4aebfb5c3c886db
// https://tonari-it.com/gas-gmail-get-thread/
// https://techblog.lclco.com/entry/2018/03/15/083000

var SearchString = "Subject:【xxx】機器障害 通知メール label:99.GAS処理前";

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

  if ( labelDomain == null ) {
    labelDomain = GmailApp.createLabel(labelString);
  }

  return labelDomain;
}

function transcribeMail() {
  var myThreads = GmailApp.search(SearchString, 0, 20);
  var myMsgs = GmailApp.getMessagesForThreads(myThreads);
  var objSheet = SpreadsheetApp.getActive().getSheetByName("<シート名>");

  for (var threadIndex = 0 ; threadIndex < myThreads.length ; threadIndex++) {
    for (var msgIndex = 0 ; msgIndex < myMsgs[threadIndex].length ; msgIndex++) {
      var mail = myMsgs[threadIndex][msgIndex];
      var mailSubject = mail.getSubject();
      var mailBody = mail.getPlainBody();
      var mailDate = mail.getDate();

      var date = mailDate;
      var data = mailBody.match(/機器名：(.+)/);

      var status    = ""
      if (mailSubject.match(/【復旧】/)) {
        status = "復旧";
      } else {
        status = "障害";
      }

      var maxRow = objSheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得

      objSheet.getRange(maxRow+1, 1).setValue(date);
      objSheet.getRange(maxRow+1, 2).setValue(status);
      objSheet.getRange(maxRow+1, 3).setValue(data[1]);

      // ラベルを外す処理。
      // (フィルターで都度ラベルを付け、ここで都度ラベルを外す)
      var LabelProceed = _createLabel("99.GAS処理前");
      myThreads[threadIndex].removeLabel(LabelProceed);
    }
  }

  // A 列 (日時) のフォーマットを整える。
  objSheet.getRange('A:A').activate();
  objSheet.getActiveRangeList().setNumberFormat('yyyy/MM/dd HH:mm:ss');

  // A 列 (日時) を降順ソートする。
  objSheet.getRange('A1').activate();
  objSheet.getFilter().sort(1, false);
  //spreadsheet.getFilter().sort(1, true); // 昇順

  // A 列 (日時) を重複排除する。
  objSheet.getRange('A:C').activate();
  objSheet.getActiveRange().removeDuplicates().activate();

}

コード説明 (主なところだけを)

var SearchString = "Subject:【xxx】機器障害 通知メール label:99.GAS処理前";

取込対象のメールを特定します。メール件名とラベルで絞り込んでいます。

function _createLabel(labelString)

ラベルが存在しない場合、新規にラベルを作成する関数です。今回の場合、ラベルは事前に作成する前提なので、機能を使用することはありませんが。。。

var myThreads = GmailApp.search(SearchString, 0, 20);

一回で処理するスレッド数を指定します。スレッドの大きさ (=メールの受領頻度) に依ると思いますが、自分は一旦「20」にしています。

var data = mailBody.match(/機器名：(.+)/);

メール本文から値を取り出します。正規表現を使い、「:」の右側の値を「data[0]」に格納しています。

if (mailSubject.match(/【復旧】/))

メール件名に「【復旧】」が含まれるか判定しています。含まれる場合は変数「status」に「復旧」を、変数「status」に「障害」を入れるようにしています。

 var maxRow = objSheet.getDataRange().getLastRow();

スプレッドシートへの追記方法はいろいろあると思いますが、今回は末尾に追加する方法にしています。

myThreads[threadIndex].removeLabel(LabelProceed);

処理済みのスレッドに関して、「99.GAS処理前」ラベルを外しています。

objSheet.getActiveRange().removeDuplicates().activate();

上述のとおり、旧メッセージを何度も取り込む可能性があるため、ここで重複排除しています。

まとめ

以上、「Gmail で受領したメールを GAS でスプレッドシートに取り込む」方法でした。

スプレッドシートに取り込んでからの処理は自由です (スプレッドシートの仕様の範囲であれば)。自分の場合、もう一個別シートを作成し、機器ごとに障害時間と復旧時間を整理し、停止時間も分かるようにしたりしました。また、スプレッドシートへの取込処理も、定期実行 (時限実行) としています。このように、発生条件 (トリガー) を自由に設定できるのも GAS の強みです。

GAS は、wget のように HTTP リクエストを発生することも可能なようです (UrlFetchApp.fetch ？)。つまり、Web API を持つようなシステムが存在する場合、その API を叩くことが可能ということです。うまく作り込めば、アラートメールしか連携手段が無いようなシステムも、GAS を仲介することで、別システムの Web API を叩けるようになるかもしれません。

GAS は、まだまだ色々なことができそうなので、日常のちょっとした不便を見つけながら、ちょこちょこ触っていきたいと思います。