Google Apps Scriptでメールを受信してスプレッドシートに整理

2019-02-21

Googleスプレッドシートは、Googleが作成したものなので、他のGoogleサービスとの連携が得意です。連載でプログラム機能(Google Apps Script)を使って、Googleスプレッドシート上でGmailの受信データを取得する方法について紹介しています。

前回は、Gmailの「検索条件」を上手く使って、欲しいメール「だけ」を抽出する方法を紹介しました。今回は、Google Apps Scriptを使ってスプレッドシート上でGmailの受信データから欲しいメール「だけ」を取得・リスト化する方法について紹介します。

ポイント 完成イメージ

検索条件に一致するメールを蓄積していきます。最終チェック日時を管理しているので、定期的にチェックプログラムを実行することで、重複なくメールを蓄積していくことができます。

プログラム(Google Apps Script)の定期実行の方法は、こちら

Google スプレッドシート:定期的にプログラムを自動実行(GASトリガー機能)
当ラボでは、誰もが無料で使うことができるGoogleスプレッドシートを使って様々なサイトから必要な情報の取得を仕組み化したツールを提供しています。連載で「IMPORTFEE...

ポイント ツール用シートの作成

上の図のようなシートを作成します。

最終受信日時

最終受信日時(プログラムの最終実行日時)を取得します。この日時を使って取得するメールの検索を行います。

検索条件

検索条件を記入します。記入箇所は複数あり、いずれか1つの条件に一致するメールを抽出します。
Gmailで使える検索条件は、こちらにまとめられています。

検索結果

受信日時、送信元、タイトル、本文の出だしを表形式で抽出します。データは最終行に追加されます。

ポイント プログラムの作成

今回のプログラム(Google Apps Script)は、このような流れになります。
・最終データ取得日時をUNIX時間に換算
・メールの検索条件の作成
・検索条件に当てはまるメールを抽出
・スプレッドシートに出力
・最終受信日時を記入

今回、使用するスクリプト(Google Apps Script)は、こちらです。
なお、スクリプトの作成及び実行方法はこちらにまとめてあります。

Googleスプレッドシートのスクリプト作成から実行までの基本的な流れ
当ラボでは、誰もが無料で使うことができるGoogleスプレッドシートを使って様々なサイトから必要な情報の取得を仕組み化したツールを提供しています。今回は、Googleス...

function receive_Mail2() {
 
  var sheet= SpreadsheetApp.getActive().getSheetByName('シート1');  //←適切なシート名を設定して下さい
  
  var now = new Date();  //現在の日時取得

  //**************************************
  // 最終データ取得日時をUNIX時間に換算
  //**************************************
  const date = sheet.getRange("C2").getValue() ;//最終取得日時
  if(date==""){date= new Date(now - (1000 * 60 *60 * 24 ));}  //日付欄が空欄だったら、24時間前の時刻を入力
  const unixTime = date.getTime();//UNIX TIMEに変換
  const term = Math.floor(unixTime/1000); //ミリ秒を秒に変換
  const termStr = term.toString(); //検索期間を文字列に変換

  //**************************************
  // メールの検索条件の作成
  //**************************************
  var condstr=sheet.getRange(4,3,30,1).getValues()
  var strTerms = 'after:'+ term +' '
  for(var i=0;i<condstr.length;i++){
    strTerms = strTerms+condstr[i]+'|'
  }

  //**************************************
  // 検索条件に当てはまるメールを抽出
  //**************************************  
  Logger.log(strTerms)
  var myThreads = GmailApp.search(strTerms, 0, 5); //条件にマッチしたスレッドを取得
  var myMsgs = GmailApp.getMessagesForThreads(myThreads); //スレッドからメールを取得する →二次元配列で格納
  Logger.log("myMsgs.length"+myMsgs.length);
  var valMsgs = [];
  var matomeArray = []; 
  /* 各メールから日時、送信元、件名、内容を取り出す*/
  var chatmsg=""
  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] = "=hyperlink(" + String.fromCharCode(34) +   myThreads[i].getPermalink() + String.fromCharCode(34) + "," + String.fromCharCode(34) +  myMsgs[i][0].getSubject() + String.fromCharCode(34) +")" ;
    valMsgs[i][3] = "'"+myMsgs[i][0].getPlainBody().slice(0,50);
    matomeArray.push([valMsgs[i][0],valMsgs[i][1],valMsgs[i][2],valMsgs[i][3]])
  }

  //**************************************
  // スプレッドシートに出力
  //**************************************
    var mlen=matomeArray.length;
    var tmp=sheet.getRange("F:F").getValues()
    var lastrow = tmp.filter(String).length+2;
    Logger.log(lastrow);

  if(myMsgs.length>0){
    sheet.getRange(lastrow, 6, mlen, 4).setValues(matomeArray); //シートに貼り付け
    
  }
  
  //**************************************
  //最終受信日時を記入
  //**************************************
  sheet.getRange("C2").setValue(now)
}

ポイント まとめ

 今回は、Google Apps Scriptを使って、Googleスプレッドシート上でGmailの受信データから欲しいメール「だけ」を取得・リスト化する方法を紹介しました。
このスクリプトを改良していけば、大切なメールの受信をスプレッドシートでリスト化し、処理状況を把握することが可能となります。

いろいろ試してみて下さい。

Copyright(c) 2016 e-Shikumi-Labo All Rights Reserved.