GoogleAppsScriptを使い、Gmailに来たWordPressコピー通知メールをスプレッドシートに整形する

シゴトクリエイター　大橋　弘宜

公開日：2017/10/26

今回はGAS（GoogleAppsScript）で、Gmail内容を検索し、Googleスプレッドシートに書き出すということをしてみます。

これによって何が出来るかというと、

WordPressでコピーされた通知メールを取り出す（プラグインのCCCを入れてる前提）
スプレッドシートに出された整形データからどういうニーズがあるか分析できる

というところです。もちろん、Gmail検索が出来るのでどう使うかはアイデア次第です。Gmailを毎回検索して見直すなど定形作業化しているものがあれば、だるさから少し解放されるかもしれません。

なお技術レベルはそれほど高くはないとは思いますがとはいえスクリプトを書いて実行して修正する感じはまさにプログラミングなのであまり初心者の方にはおすすめできません。動かない場合は自分で考える必要があるため、動かないときが辛いかなという感じですね。

ひとまずいってみましょうー。

1 GoogleAppsScrpitでGmailスレッドを検索し、スプレッドシートに書き出す
2 おわりに

GoogleAppsScrpitでGmailスレッドを検索し、スプレッドシートに書き出す

今回出来るもの

まず、結論的にどういうイメージになるかというと、

Googleスプレッドシートに整形して書き出す

というものが、やや小さいですが左上にある「run」ボタンを押すと書き出されるというシンプルなものです。この画面はGoogleスプレッドシートになります。

便利な点は、毎回Gmailでショートカットでjとかk押して内容を見ていってということをしなくても実行ボタンを押せば、スプレッドシートを見るだけで良いということです。

その前に課題が1つあるのでそこを共有しておきます。

現状の課題

Gmailで検索した結果が確かに得られるが、スレッド単位であり1スレッドに複数メールがある場合は1件しか得られない

検索対象が20件あり書き出されたとします。実際のGmailでは20スレッドであり、1スレッドに複数メールがあることが普通です。例えば、本日に何回もコピーされると通知メールとしては「通知メール（５）」みたいに、5件がまとまって1スレッドとなるということです。そして、5件のうち最も古いもの？の内容を取ってくるとなっています。

僕の解釈では、ループを回して20スレッド取ってくる、さらに複数メールがある場合は、例えば5メールある場合はそれらも取るというように多重ループの処理で対応できるはずです。が、これは実装出来ていません。実装しようとしたのですが、途中で断念。少なくとも必要なのは、該当スレッドに何件メールがあるかを調べるのが必要で、その分をループで回すことになります。

そうすれば、仮に20スレッドでメールが複数ついていても、全て展開されるということになります。もちろんスレッド単位で良ければこのままで十分使えます。

作り方

今回参考にしたのは、【GAS】Gmailからメールを検索してスプレッドシートに書き出すの記事です。大変参考になりました。ありがとうございます。ほぼこのままといっていいです。

手順としては、Gmail環境があること、googleドライブなどGoogleスプレッドシートが使えることです。これらが分からない方は調べてみてください。

１．Googleスプレッドシートを立ち上げ、「ツール」→「スクリプトエディタ」を選ぶ

２．以下のコードを書く

function searchContactMail() {
  
  //シートのクリア
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange("A2:F100").clear();

  // Gmailの検索条件
  var strTerms = 'after:2017/9/1 before:2017/9/30 以下の本文がコピーされたようです。';
 
  var myThreads = GmailApp.search(strTerms, 0, 100); //条件にマッチしたスレッドを検索して取得
  var myMessages = GmailApp.getMessagesForThreads(myThreads); //スレッドからメールを取得する　→二次元配列で格納

  var myMsgs = GmailApp.getMessagesForThreads(myThreads); //スレッドからメールを取得する　→二次元配列で格納

  var valMsgs = [];
  var indexIP = 0;
  var ip_str_end = 0;
  
  /* 各メールから日時、送信元、件名、内容を取り出す*/
  for(var i = 0;i < myMsgs.length;i++){

    valMsgs[i] = [];
    valMsgs[i][0] = myMsgs[i][0].getDate();
    valMsgs[i][1] = myMsgs[i][0].getFrom();
    valMsgs[i][2] = myMsgs[i][0].getSubject();
    valMsgs[i][3] = myMsgs[i][0].getPlainBody().slice(0,200);
    valMsgs[i][4] = myThreads[i].getPermalink();
    indexIP = myMsgs[i][0].getPlainBody().indexOf('IP');
    if ( indexIP != -1) {
      ip_str_end = get_returncode(myMsgs[i][0].getPlainBody(),indexIP);
      //Browser.msgBox(ip_str_end);
      valMsgs[i][5] = myMsgs[i][0].getPlainBody().substr(indexIP+4,ip_str_end);
    }

  }

  /* スプレッドシートに出力 */
  if(myMsgs.length>0){
    
    SpreadsheetApp.getActiveSheet().getRange(2, 1, i, 6).setValues(valMsgs); //シートに貼り付け
    
  }
  
  for(var i=0;i < myMessages.length;i++){
    Logger.log(myMessages[i][0].getSubject());  //各スレッドの1番目のメールの表題をログ出力
  }
  
}

//改行コードの位置を返す
function get_returncode(body,startpos){

  var r = 0;
  
  //IPアドレスにつく改行コードを探す
  r=body.substr(startpos+4,20).indexOf("\n");
  //Browser.msgBox(r);
  if ( r != -1 ){
    return r;
  }
  
  r=body.substr(startpos+4,20).indexOf("\r\n");
  //Browser.msgBox(r);
  if ( r != -1 ){
    return r;
  }
  
  r=body.substr(startpos+4,20).indexOf("\r");
  //Browser.msgBox(r);
  if ( r != -1 ){
    return r;
  }
    
}

３．上のコードを保存する

４．スプレッドシートに戻り、「挿入」→「図形描画」で適当な四角形を書き保存して閉じる（ボタンとして使うため）

５．作った四角ボタンを左クリックすると、右上に三点リーダーが出るのでそれをクリックし「スクリプトを割り当て」を押す

６．テキスト欄に「searchContactMail」とコピペでいいので入れて完了する（リストボックスとかあっても良さそうですが出てこない、上のコードのメイン関数を入れるってことですね）

７．ボタンをクリックして処理を実行し、出来ることを確認する（スクリプトを実行していますという処理メッセージがスプレッドシート上部出てきます）

８．書き出されたデータを見てあとはご自由に煮るなり焼くなりどうぞって感じです。

簡単なコードの解説

基本的なことなのでしょうが、スクリプトエディタをいつも見失うので、ツール→スクリプトエディタというのは覚えておきましょう。
上の手順でうまくいけばいいのですが行かない場合も多いかもしれません、それぞれのコードの解説を若干しておきます。
コードは２つの関数に分かれています。１つはsearchContactMail関数です。これがメインです。もう１つは、get_returncode関数です。これは改行コードの位置を返すもので精度悪いですね。僕が作っただけなのでどんどん改造してください。基本的にこの改行コードはIPアドレスを取り出すために作っているので、不要なら消してもオッケーです。
シートのクリアらへんは、そのままです。エクセルのマクロっぽいですね。
Gmailの検索条件は、Gmailを使っている方で検索する方は分かりやすいと思います。strTermsには、Gmailで検索ボックスに入れるものをそのまま書けばオッケーです。今回は「after:2017/9/1 before:2017/9/30 以下の本文がコピーされたようです。」としていますが、これは、２０１７年９月１日から９月３０日までの範囲で、「以下の本文がコピーされたようです。」が入るメッセージを探してねということになります。本文検索をしているわけですが別にそうでなく、「特定のメールアドレス」とかもできますし、それはgmailの仕様で出来るものですね。GoogleAppsScriptでいろいろな条件を指定してメールを取得するなんかにはそんなパラメタも少し書かれていたり参考にしてみてください。
GmailApp.searchはとくに最後の100は最大スレッド数です。つまり、100とあると100スレッドまでしか出しません。500くらいが最大のようです。逆に言えば500を超える膨大なものを一気にやる時は向いていませんね。
getMessagesForThreadsでは、この配列にメール内容を格納することになります。僕の解釈ではこの配列は2次元配列になっていて、それらをループで1つずつ分解するということになります。
forループの中がそのメール内容分解です。ここが肝といっていいでしょう。指定したものを取り出していければ自由にカスタマイズ出来ると思います。
今回は、日付、差出人メールアドレス（From）、件名、本文、メールリンク、IPアドレスを取得しています。get何々という感じで取得しています。本文はsliceで200文字で冒頭部分のみにしています。全文取るのもありですね。メールリンクとはGmailのリンクであり、該当スレッドをクリックすれば見えるので便利な気がします。
最後にスプレッドシートに出力します。このgerRangeの指定範囲は分かりづらいと思うのですが、2行目の1列目（1番＝A列）から取り出したスレッド数行分、6番目（F列）に書き出すとなります。要は、A2:F何の指定ですね。スレッド数は取り出さないと分からないので変数iとなっています。iに取り出したスレッド数が格納されているわけですね。仮に項目数を減らしたりすれば減らせばいいし、増やしたいなら増やすこととなります。
ログ表示のLoggerとは全然使ってなかったのですが、スクリプトエディタのツール→ログか、CTRL+ENTERで出てくるやつです。要はデバッグ用に使えるので、スクリプトが上手く動かなくてそもそもどうなってんだ？という時は、スプレッドシートにいって実行がたるい時にも使えそうですね。消してももちろん構いません。

注意点

スプレッドシートで図形描画してボタン風なものを作るのは面白いのですが、文字で日本語がどうもうまく入らない気がします。英語とかでいきましょう。
そのボタンでスクリプト関数を割り当てていても、「文字を編集する」などすると「スクリプト関数」がクリアされるっぽいです。そうすると、「クリックしても実行」されないので焦るので注意です（笑）割当が空だと何も起きないですね。

追加アイデア

説明を書いていてそういえばと思ったのは、例えばIPアドレスについてです。これはIPアドレス検索などをすればいいので、それを自動化できるかはおいておいて出来ると面白そうですね。日本国内のどこからが多いのかなくらいの話です。
そういえば日付の昇順とか出来ないかなと思ったら、Gmailの取得の仕方は変えられるかは不明（そもそもその視点は微妙か）なので、スプレッドシートに出したものを変えればいいと思いました。コードとしては、シンプルで例えば、今回のコードであれば、スプレッドシートに出力後であれば、こんな感じで行けました。gerRangeはそのまま同じでオッケーです。対象範囲に対して、sortで1列＝A列の日付でascending＝昇順でソートしてねってことになります。シートのデータを昇順・降順でソートするを参考にしました。
```
  /* スプレッドシートに出力 */
  if(myMsgs.length>0){
    
    SpreadsheetApp.getActiveSheet().getRange(2, 1, i, 6).setValues(valMsgs); //シートに貼り付け
    
  }
  
  //日付昇順でソート
  var range = sheet.getRange(2,1,i,6);
  range.sort([{column: 1, ascending: true}]);
```
ブログ記事のURLデータも今回はたまたまあるので、IPアドレスよりもURLを取り出したほうが使えそうな気が今更ながらしました（笑）ちなみに取り出した場合改行コードがあるならTrim関数を自作するなどが一番早そうです。[GAS]文字列のスペースを除去(Trim)するには: 逆引きGoogle Apps Scriptが参考になります。
記事URL名からある程度記事内容は分かるわけですが、やはり記事名も欲しいですよね。それもURLFetchAppを使えばいけました。Google SpreadsheetでURLからタイトルに変換が参考になります。ちなみに、利用例のようにセルに関数を入れてもいいですが、当然コードに入れて使うことも出来ます。これによって記事URL、さらにタイトルも分かるので、どの記事タイトルをみてコピーしたかがすぐ分かるようになって便利になりました。

とまあ、キリがないのでこのあたりにしますが、色々アイデアで改造できるので試してみると面白いと思います。