GoogleAppsScriptを使ってGmailの本文を解析してSpreadSheetにエクスポートする

2015/03/02 2016/03/27

GoogleAppsScriptを使ってGmailの本文を解析してSpreadSheetにエクスポートする

例えばGoogleAppsをこれから導入しようとする企業や組織があったとします。
それまで使用していたメールクライアントでは、Webフォームかだ自動送信されるメールをCSVとかでエクスポートして集計していたのだ、と現場に言われたとします。
そもそもWebフォームが使っているデータベースから出力するなり、集計するなりやり方を変えればいいのですが、フォームからメールを送信しているだけのサービスであったりとか、中にはデータ出力に別費用が発生するサービスもあり、世の中には思っているより、そんな事も出来ないのって、という以前にメールが届かなかったらそれってロストするんですよねって、サービスがわりと多かったりします。

話がそれましたが、だからといってセキュリティ面から言ってGmailのIMAPを開放したり、という事はあまりしたくありません。

なのでCSVで出力出来るだけではなく、定形の自動送信メールであれば、本文を解析して出力してあげればいいのではないか、というのを思ったのでやってみました。

https://sites.google.com/site/yamamanx/tools/gmailaggregator#

動作仕様

Gmailの特定ラベルを対象にし、本文の特定文字の後の値を分割して、SpreadSheetに出力します。
Google Apps Scriptなので5分で出来る範囲の量だけです。
例として [FormMail] というラベルで各項目が、お名前: , 連絡先: , メモ: という文字列のあるメールとします。
SpreadSheetは[GmailAggregate_YYYYMMDDhhmmss]というファイル名でGoogle Driveに新規作成します
出力したメールには[FormMailProcessed]というラベルが作成されて付与されます

コード

もっとこうしたらいい、こうするべきってご意見があるとすごく嬉しく思います。
近い内容の記事はありますが、こっちしか見ない人もいると思いますので差分ではなく全部記載します。

メイン処理

function gmailAggregate(){

var rowNumber = 2;
  var unProcessLabelName = 'FormMail';
  var processedLabelName = 'FormMailProcessed';
  var subjectString = 'GmailAggregate';

try{
    var unProcessLabel = GmailApp.getUserLabelByName(unProcessLabelName);
    if (!unProcessLabel){
      Logger.log(unProcessLabelName + "がないので処理を中断しました。");
      return;
    }
    var threads = GmailApp.search('label:' + unProcessLabelName);
    if (threads.length == 0){
      Browser.msgBox('処理終了', '対象のメールがありませんでした。\n終了します。', Browser.Buttons.OK);
      return;
    }
    var mailExportSheet = SpreadsheetApp.create(subjectString + '_' + toOriginalDateString(new Date()));
    var mailSheet = createMailSheet(mailExportSheet,subjectString);
    var textArray = textArrayCreate();
    for (var count in textArray){
      mailSheet.getRange(1,parseInt(count)+4).setValue(textArray[count]);
    }
    var processedLabel;
    processedLabel = createProcessedLabel(processedLabelName);
    for (var threadIndex in threads){
      var thread = threads[threadIndex];
      var messages = thread.getMessages();
      for (var messageIndex in messages){
        maxRateAvoid(rowNumber);
        var message = messages[messageIndex];
        var from = message.getFrom();
        var date = message.getDate();
        var subject = message.getSubject();
        var nbsp = String.fromCharCode(160);
        var body = message.getBody().replace(/&<("[^"]*"|'[^']*'|[^'">])*>|nbsp/g,'').replace(/&; |　/g,'').substring(1,50000);
        var positionArray = [];
        for (var count in textArray){
          switch(count){
            case '0':
              positionArray.push(body.indexOf(textArray[0]));
              break;
            case textArray.length.toString():
              positionArray.push(body.length);
              break;
            default:
              Logger.log(count);
              positionArray.push(body.indexOf(textArray[count],positionArray[parseInt(count)-1] + textArray[parseInt(count)-1].length));
              break;
          }
        }
        mailSheet.getRange(rowNumber,1).setValue(from);
        mailSheet.getRange(rowNumber,2).setValue(date);
        mailSheet.getRange(rowNumber,3).setValue(subject);
        for (var count in textArray){
          mailSheet.getRange(rowNumber,parseInt(count)+4)
          .setValue(
            body.substring(
              positionArray[count] + textArray[count].length,
              positionArray[parseInt(count)+1]
              ).replace(/(^\s+)|(\s+$)/g, "")
            );
        }
        rowNumber++;<br />
      }
      processedLabel.addToThread(thread);
      unProcessLabel.removeFromThread(thread);
    }
    mailSending((rowNumber - 2) + "通のGmailExportが完了しました。\n" + mailExportSheet.getUrl(),subjectString);
  }catch(e){
    mailSending((rowNumber - 1) + "通目のメールで次のエラーが発生しました。\n" + e.message,subjectString);
  }
};

function gmailAggregate(){

var rowNumber = 2;

var unProcessLabelName = 'FormMail';

var processedLabelName = 'FormMailProcessed';

var subjectString = 'GmailAggregate';

try{

var unProcessLabel = GmailApp.getUserLabelByName(unProcessLabelName);

if (!unProcessLabel){

Logger.log(unProcessLabelName + "がないので処理を中断しました。");

return;

}

var threads = GmailApp.search('label:' + unProcessLabelName);

if (threads.length == 0){

Browser.msgBox('処理終了', '対象のメールがありませんでした。\n終了します。', Browser.Buttons.OK);

return;

}

var mailExportSheet = SpreadsheetApp.create(subjectString + '_' + toOriginalDateString(new Date()));

var mailSheet = createMailSheet(mailExportSheet,subjectString);

var textArray = textArrayCreate();

for (var count in textArray){

mailSheet.getRange(1,parseInt(count)+4).setValue(textArray[count]);

}

var processedLabel;

processedLabel = createProcessedLabel(processedLabelName);

for (var threadIndex in threads){

var thread = threads[threadIndex];

var messages = thread.getMessages();

for (var messageIndex in messages){

maxRateAvoid(rowNumber);

var message = messages[messageIndex];

var from = message.getFrom();

var date = message.getDate();

var subject = message.getSubject();

var nbsp = String.fromCharCode(160);

var body = message.getBody().replace(/&<("[^"]*"|'[^']*'|[^'">])*>|nbsp/g,'').replace(/&; |　/g,'').substring(1,50000);

var positionArray = [];

for (var count in textArray){

switch(count){

case '0':

positionArray.push(body.indexOf(textArray[0]));

break;

case textArray.length.toString():

positionArray.push(body.length);

break;

default:

Logger.log(count);

positionArray.push(body.indexOf(textArray[count],positionArray[parseInt(count)-1] + textArray[parseInt(count)-1].length));

break;

}

mailSheet.getRange(rowNumber,1).setValue(from);

mailSheet.getRange(rowNumber,2).setValue(date);

mailSheet.getRange(rowNumber,3).setValue(subject);

for (var count in textArray){

mailSheet.getRange(rowNumber,parseInt(count)+4)

.setValue(

body.substring(

positionArray[count] + textArray[count].length,

positionArray[parseInt(count)+1]

).replace(/(^\s+)|(\s+$)/g, "")

);

}

rowNumber++;<br />

}

processedLabel.addToThread(thread);

unProcessLabel.removeFromThread(thread);

}

mailSending((rowNumber - 2) + "通のGmailExportが完了しました。\n" + mailExportSheet.getUrl(),subjectString);

}catch(e){

mailSending((rowNumber - 1) + "通目のメールで次のエラーが発生しました。\n" + e.message,subjectString);

}

};

項目名配列の生成

function textArrayCreate(){
  var textArray = [];

textArray.push('お名前:');
  textArray.push('連絡先:');
  textArray.push('メモ:');

return textArray;
};

function textArrayCreate(){

var textArray = [];

textArray.push('お名前:');

textArray.push('連絡先:');

textArray.push('メモ:');

return textArray;

};

処理済ラベルの作成

function createProcessedLabel(processedLabelName){
  var processedLabel = GmailApp.getUserLabelByName(processedLabelName);
  if (!processedLabel){
    processedLabel = GmailApp.createLabel(processedLabelName);
  }
  return processedLabel;
};

function createProcessedLabel(processedLabelName){

var processedLabel = GmailApp.getUserLabelByName(processedLabelName);

if (!processedLabel){

processedLabel = GmailApp.createLabel(processedLabelName);

}

return processedLabel;

};

SpreadSheetの作成

function createMailSheet(mailExportSheet,subjectString){
  var mailSheet = mailExportSheet.getSheets()[0];
  mailSheet.setName(subjectString);
  mailSheet.getRange(1,1).setValue("From");
  mailSheet.getRange(1,2).setValue("DateTime");
  mailSheet.getRange(1,3).setValue("Subject");

return mailSheet;
};

function createMailSheet(mailExportSheet,subjectString){

var mailSheet = mailExportSheet.getSheets()[0];

mailSheet.setName(subjectString);

mailSheet.getRange(1,1).setValue("From");

mailSheet.getRange(1,2).setValue("DateTime");

mailSheet.getRange(1,3).setValue("Subject");

return mailSheet;

};

Gmailのmax rate対応

function maxRateAvoid(rowNumber){
  if (rowNumber >= 90 && rowNumber%90 == 0){
    Utilities.sleep(100000);
  }else{
    Utilities.sleep(1000);
  }
};

function maxRateAvoid(rowNumber){

if (rowNumber >= 90 && rowNumber%90 == 0){

Utilities.sleep(100000);

}else{

Utilities.sleep(1000);

}

};

結果のメール送信

function mailSending(messageString,subjectString){
  var user = Session.getActiveUser();
  if (user){
    try{
      MailApp.sendEmail(user.getEmail(),subjectString,messageString);
    }catch(e){
      Logger.log(e.message);
    }
  }
};

function mailSending(messageString,subjectString){

var user = Session.getActiveUser();

if (user){

try{

MailApp.sendEmail(user.getEmail(),subjectString,messageString);

}catch(e){

Logger.log(e.message);

}

};

ブラウザ非依存日付文字列生成

function toOriginalDateString(date){
  return date.getFullYear().toString()
  + date.getMonth().toString()
  + date.getDate().toString()
  + date.getHours().toString()
  + date.getMinutes().toString()
  + date.getSeconds().toString();
};

function toOriginalDateString(date){

return date.getFullYear().toString()

+ date.getMonth().toString()

+ date.getDate().toString()

+ date.getHours().toString()

+ date.getMinutes().toString()

+ date.getSeconds().toString();

};

コード解説

gmailAggregate

var threads = GmailApp.search('label:' + unProcessLabelName);
if (threads.length == 0){
　　Logger.log('処理終了', '対象のメールがありませんでした。終了します。');
return;
}

var threads = GmailApp.search('label:' + unProcessLabelName);

if (threads.length == 0){

　　Logger.log('処理終了', '対象のメールがありませんでした。終了します。');

return;

}

GmailAppのsearch(検索文字列)メソッドで特定ラベルのメールスレッドを取得。
0件の場合スレッド配列オブジェクトは出来るのでlengthプロパティで確認。

var mailExportSheet = SpreadsheetApp.create(subjectString + '_' + toOriginalDateString(new Date()));

1	var mailExportSheet = SpreadsheetApp.create(subjectString + '_' + toOriginalDateString(new Date()));

SpreadSheetAppクラスのcreate(ファイル名)メソッドで新規SpreadSheetを作成。

var textArray = textArrayCreate();
for (var count in textArray){
  mailSheet.getRange(1,parseInt(count)+4).setValue(textArray[count]);
}

var textArray = textArrayCreate();

for (var count in textArray){

mailSheet.getRange(1,parseInt(count)+4).setValue(textArray[count]);

}

1行目に検索対象にした項目名を設定

for (var threadIndex in threads){
  var thread = threads[threadIndex];
  var messages = thread.getMessages();

for (var messageIndex in messages){
    maxRateAvoid(rowNumber);
    var message = messages[messageIndex];

for (var threadIndex in threads){

var thread = threads[threadIndex];

var messages = thread.getMessages();

for (var messageIndex in messages){

maxRateAvoid(rowNumber);

var message = messages[messageIndex];

スレッド配列のループでスレッドメッセージ配列を取得。
スレッドメッセージ配列のループでメッセージオブジェクトを取得。
maxRateAvoidは後述。

var from = message.getFrom();
var date = message.getDate();
var subject = message.getSubject();
var nbsp = String.fromCharCode(160);
var body = message.getBody().replace(/<("[^"]<em>"|'[^']</em>'|[^'">])<em>>|nbsp/g,'').replace(/&; |　/g,'').substring(1,50000);

var from = message.getFrom();

var date = message.getDate();

var subject = message.getSubject();

var nbsp = String.fromCharCode(160);

var body = message.getBody().replace(/<("[^"]<em>"|'[^']'|[^'">])<em>>|nbsp/g,'').replace(/&; |　/g,'').substring(1,50000);

MessageクラスのgetFrom(),getDate(),getSubject(),getBody()で各要素にアクセス。
除去のためString.fromCharCode(160)でreplace対象を生成。
あわせてreplace(/<(“[^”]“|'[^’]‘|[^'”>])>|nbsp/g,”)でHTMLタグも除去。
SpreadSheetのセルの限界文字列数50,000文字に切り取り。

var positionArray = [];

for (var count in textArray){
  switch(count){
    case '0':
      positionArray.push(body.indexOf(textArray[0]));
      break;
    case textArray.length.toString():
      positionArray.push(body.length);
      break;
    default:
      positionArray.push(body.indexOf(textArray[count],positionArray[parseInt(count)-1] + textArray[parseInt(count)-1].length));
      break;
   }
}

var positionArray = [];

for (var count in textArray){

switch(count){

case '0':

positionArray.push(body.indexOf(textArray[0]));

break;

case textArray.length.toString():

positionArray.push(body.length);

break;

default:

positionArray.push(body.indexOf(textArray[count],positionArray[parseInt(count)-1] + textArray[parseInt(count)-1].length));

break;

}

positionArray配列に項目名文字列の位置を格納する。
配列の最後に文字数を記録します。

mailSheet.getRange(rowNumber,1).setValue(from);
mailSheet.getRange(rowNumber,2).setValue(date);
mailSheet.getRange(rowNumber,3).setValue(subject);

mailSheet.getRange(rowNumber,1).setValue(from);

mailSheet.getRange(rowNumber,2).setValue(date);

mailSheet.getRange(rowNumber,3).setValue(subject);

各変数に入れたメール要素の文字列をセルに格納。

for (var count in textArray){
　mailSheet.getRange(rowNumber,parseInt(count)+4)
  .setValue(
    body.substring(
     positionArray[count] + textArray[count].length,
     positionArray[parseInt(count)+1]
    ).replace(/(^\s+)|(\s+$)/g, "")
  );
}

for (var count in textArray){

　mailSheet.getRange(rowNumber,parseInt(count)+4)

.setValue(

body.substring(

positionArray[count] + textArray[count].length,

positionArray[parseInt(count)+1]

).replace(/(^\s+)|(\s+$)/g, "")

);

}

positionArray配列を使ってsubstringで文字列を切り取ってセルに入力する。

processedLabel.addToThread(thread);
unProcessLabel.removeFromThread(thread);

1 2	processedLabel.addToThread(thread); unProcessLabel.removeFromThread(thread);

処理済のメールにラベルを付与して、未処理ラベルを外す。

textArrayCreate

var textArray = [];

textArray.push('お名前:');
textArray.push('連絡先:');
textArray.push('メモ:');

return textArray;

var textArray = [];

textArray.push('お名前:');

textArray.push('連絡先:');

textArray.push('メモ:');

return textArray;

このfunctionの内容を変えれば他の自動送信メールにも対応出来ると思います。

createProcessedLabel

var processedLabel = GmailApp.getUserLabelByName(processedLabelName);
if (!processedLabel){
　processedLabel = GmailApp.createLabel(processedLabelName);
}

var processedLabel = GmailApp.getUserLabelByName(processedLabelName);

if (!processedLabel){

　processedLabel = GmailApp.createLabel(processedLabelName);

}

GmailAppクラスのgetUserLabelByName(ラベル名)メソッドでラベルの有無を判定。
なければGmailAppクラスのcreateLabel(ラベル名)メソッドでラベルを作成。

createMailSheet(の作成

var mailSheet = mailExportSheet.getSheets()[0];
mailSheet.setName(subjectString);
mailSheet.getRange(1,1).setValue("From");
mailSheet.getRange(1,2).setValue("DateTime");
mailSheet.getRange(1,3).setValue("Subject");

var mailSheet = mailExportSheet.getSheets()[0];

mailSheet.setName(subjectString);

mailSheet.getRange(1,1).setValue("From");

mailSheet.getRange(1,2).setValue("DateTime");

mailSheet.getRange(1,3).setValue("Subject");

1つ目のシートにシート名を設定して1列目にタイトル行を作成。

maxRateAvoid

if (rowNumber >= 90 && rowNumber%90 == 0){
 Utilities.sleep(100000);
}else{
 Utilities.sleep(1000);
}

if (rowNumber >= 90 && rowNumber%90 == 0){

Utilities.sleep(100000);

}else{

Utilities.sleep(1000);

}

max rateにひっかかるからsleepしなさいってエラーが発生するので、ループ1回につき1秒待つ。
それでも90メールを超えるとエラーが発生するので90メールごとに100秒待つ。

mailSending

var user = Session.getActiveUser();
if (user){
 try{
  MailApp.sendEmail(user.getEmail(),subjectString,messageString);
 }catch(e){
  Logger.log(e.message);
 }
}

var user = Session.getActiveUser();

if (user){

try{

MailApp.sendEmail(user.getEmail(),subjectString,messageString);

}catch(e){

Logger.log(e.message);

}

ログインユーザのオブジェクトをSessionクラスのgetActiveUser()メソッドで取得。
MailAppクラスのsendEmail(宛先,件名,本文)メソッドで送信。
スクリプトを作成したユーザと別ドメインのユーザではgetActiveUser()で何も帰らないようです。
メールアドレスとかを悪用出来ないようにしているのでしょうね。
今回はこのスクリプトをgmail.comユーザで作成しているのですが、
という事は同じAppsドメインなら同じ組織だからいいけど、
gmail.com同士はきっと他人だからだめなんでしょうね。

toOriginalDateString

return date.getFullYear().toString()
  + date.getMonth().toString()
  + date.getDate().toString()
  + date.getHours().toString()
  + date.getMinutes().toString()
  + date.getSeconds().toString();

return date.getFullYear().toString()

+ date.getMonth().toString()

+ date.getDate().toString()

+ date.getHours().toString()

+ date.getMinutes().toString()

+ date.getSeconds().toString();

YYYYMMDDhhmmss文字列をブラウザに依存せずに生成。

以上です。

@yamamanx

開発ベンダー5年、ユーザ企業システム部門通算8年目のSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

- Google, GoogleAppsScript gas, google

Comment

@yamamanx より:

2016年3月27日 11:23 AM

返信遅くなりましてすいません。
列数は固定出来るのでややシンプルになりますが、開始位置と回答の文字数は本文の文字を読んで計算しないといけないのであまりシンプルにはならないかもですね。

mailSheet.getRange(rowNumber,列数).setValue(body.substring(開始位置,文字数))

返信
ぱいんちゃんより:

2016年3月10日 12:00 AM

[FormMail]title

お名前: ○○○
連絡先: △△△

メモ: ×××

上記のようなメールを定期的に受信する場合、
スプレッドシートに各項目を作り書き出していくだけの場合は、スクリプトはもっとシンプルに仕上がりますか？

返信