GoogleAppsScriptを使ってGmailの本文を解析してSpreadSheetにエクスポートする
2020/09/07
目次
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]というラベルが作成されて付与されます
コード
もっとこうしたらいい、こうするべきってご意見があるとすごく嬉しく思います。
近い内容の記事はありますが、こっちしか見ない人もいると思いますので差分ではなく全部記載します。
メイン処理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
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++; } processedLabel.addToThread(thread); unProcessLabel.removeFromThread(thread); } mailSending((rowNumber - 2) + "通のGmailExportが完了しました。\n" + mailExportSheet.getUrl(),subjectString); }catch(e){ mailSending((rowNumber - 1) + "通目のメールで次のエラーが発生しました。\n" + e.message,subjectString); } }; |
項目名配列の生成
1 2 3 4 5 6 7 8 9 10 |
function textArrayCreate(){ var textArray = []; textArray.push('お名前:'); textArray.push('連絡先:'); textArray.push('メモ:'); return textArray; }; |
処理済ラベルの作成
1 2 3 4 5 6 7 8 |
function createProcessedLabel(processedLabelName){ var processedLabel = GmailApp.getUserLabelByName(processedLabelName); if (!processedLabel){ processedLabel = GmailApp.createLabel(processedLabelName); } return processedLabel; }; |
SpreadSheetの作成
1 2 3 4 5 6 7 8 9 10 |
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対応
1 2 3 4 5 6 7 8 |
function maxRateAvoid(rowNumber){ if (rowNumber >= 90 && rowNumber%90 == 0){ Utilities.sleep(100000); }else{ Utilities.sleep(1000); } }; |
結果のメール送信
1 2 3 4 5 6 7 8 9 10 11 |
function mailSending(messageString,subjectString){ var user = Session.getActiveUser(); if (user){ try{ MailApp.sendEmail(user.getEmail(),subjectString,messageString); }catch(e){ Logger.log(e.message); } } }; |
ブラウザ非依存日付文字列生成
1 2 3 4 5 6 7 8 9 |
function toOriginalDateString(date){ return date.getFullYear().toString() + date.getMonth().toString() + date.getDate().toString() + date.getHours().toString() + date.getMinutes().toString() + date.getSeconds().toString(); }; |
コード解説
gmailAggregate
1 2 3 4 5 6 |
var threads = GmailApp.search('label:' + unProcessLabelName); if (threads.length == 0){ Logger.log('処理終了', '対象のメールがありませんでした。終了します。'); return; } |
GmailAppのsearch(検索文字列)メソッドで特定ラベルのメールスレッドを取得。
0件の場合スレッド配列オブジェクトは出来るのでlengthプロパティで確認。
1 2 |
var mailExportSheet = SpreadsheetApp.create(subjectString + '_' + toOriginalDateString(new Date())); |
SpreadSheetAppクラスのcreate(ファイル名)メソッドで新規SpreadSheetを作成。
1 2 3 4 5 |
var textArray = textArrayCreate(); for (var count in textArray){ mailSheet.getRange(1,parseInt(count)+4).setValue(textArray[count]); } |
1行目に検索対象にした項目名を設定
1 2 3 4 5 6 7 8 |
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は後述。
1 2 3 4 5 6 |
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); |
MessageクラスのgetFrom(),getDate(),getSubject(),getBody()で各要素にアクセス。
除去のためString.fromCharCode(160)でreplace対象を生成。
あわせてreplace(/<(“[^”]“|'[^’]‘|[^'”>])*>|nbsp/g,”)でHTMLタグも除去。
SpreadSheetのセルの限界文字列数50,000文字に切り取り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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配列に項目名文字列の位置を格納する。
配列の最後に文字数を記録します。
1 2 3 4 |
mailSheet.getRange(rowNumber,1).setValue(from); mailSheet.getRange(rowNumber,2).setValue(date); mailSheet.getRange(rowNumber,3).setValue(subject); |
各変数に入れたメール要素の文字列をセルに格納。
1 2 3 4 5 6 7 8 9 10 |
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で文字列を切り取ってセルに入力する。
1 2 3 |
processedLabel.addToThread(thread); unProcessLabel.removeFromThread(thread); |
処理済のメールにラベルを付与して、未処理ラベルを外す。
textArrayCreate
1 2 3 4 5 6 7 8 |
var textArray = []; textArray.push('お名前:'); textArray.push('連絡先:'); textArray.push('メモ:'); return textArray; |
このfunctionの内容を変えれば他の自動送信メールにも対応出来ると思います。
createProcessedLabel
1 2 3 4 5 |
var processedLabel = GmailApp.getUserLabelByName(processedLabelName); if (!processedLabel){ processedLabel = GmailApp.createLabel(processedLabelName); } |
GmailAppクラスのgetUserLabelByName(ラベル名)メソッドでラベルの有無を判定。
なければGmailAppクラスのcreateLabel(ラベル名)メソッドでラベルを作成。
createMailSheet(の作成
1 2 3 4 5 6 |
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
1 2 3 4 5 6 |
if (rowNumber >= 90 && rowNumber%90 == 0){ Utilities.sleep(100000); }else{ Utilities.sleep(1000); } |
max rateにひっかかるからsleepしなさいってエラーが発生するので、ループ1回につき1秒待つ。
それでも90メールを超えるとエラーが発生するので90メールごとに100秒待つ。
mailSending
1 2 3 4 5 6 7 8 9 |
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
1 2 3 4 5 6 7 |
return date.getFullYear().toString() + date.getMonth().toString() + date.getDate().toString() + date.getHours().toString() + date.getMinutes().toString() + date.getSeconds().toString(); |
YYYYMMDDhhmmss文字列をブラウザに依存せずに生成。
以上です。
最後までお読みいただきましてありがとうございました!
「AWS認定資格試験テキスト&問題集 AWS認定ソリューションアーキテクト - プロフェッショナル 改訂第2版」という本を書きました。
「AWS認定資格試験テキスト AWS認定クラウドプラクティショナー 改訂第3版」という本を書きました。
「ポケットスタディ AWS認定 デベロッパーアソシエイト [DVA-C02対応] 」という本を書きました。
「要点整理から攻略するAWS認定ソリューションアーキテクト-アソシエイト」という本を書きました。
「AWSではじめるLinux入門ガイド」という本を書きました。
開発ベンダー5年、ユーザ企業システム部門通算9年、ITインストラクター5年目でプロトタイプビルダーもやりだしたSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。
このブログの内容/発言の一切は個人の見解であり、所属する組織とは関係ありません。
このブログは経験したことなどの共有を目的としており、手順や結果などを保証するものではありません。
ご参考にされる際は、読者様自身のご判断にてご対応をお願いいたします。
また、勉強会やイベントのレポートは自分が気になったことをメモしたり、聞いて思ったことを書いていますので、登壇者の意見や発表内容ではありません。
ad
ad
関連記事
-
Google Apps ScriptでAdmin SDK Directory Serviceを使ってグループメンバー情報を出力する
GoogleAppsのGoogleGroupをメーリングリストとして使っている人 …
-
redmine_omniauth_googleプラグインをRedmine3.0.0で動くようにする
redmine_omniauth_googleプラグインをインストールしたところ …
-
Redmine 3.0.0 のメールサーバーでGoogleApps SMTPサーバーを利用する
Redmineのメール通知サーバーとしてGoogleAppsを使用する方法です。 …
-
RedmineにGoogleAppsアカウントでログイン出来るようにする
RedmineのログインをGoogleアカウントで出来るように[redmine_ …
-
Googleカレンダーの予定をPHPからXMLで取得してWebページに表示する(現在廃止されたAPIなので使えません)
ご注意 ※下記の記事で使用していたAPIは2016年現在使用出来なくなっています …
-
Google Apps Scriptのマニフェストファイルの編集
こちらの配信ライブカレンダーの仕組みを作っているときに、久しぶりにGoogle …
-
共有Googleカレンダーを自分のカレンダーに追加してiPhoneに同期する
配信ライブカレンダーという共有カレンダーを作りました。 このようなGoogleの …
-
youtube-container クラスのheightを調整
当ブログにGoogleカレンダーとかGoogleフォームを埋め込むと、heigh …
-
Chrome リモートデスクトップを使ってみる
自宅のMacをリモートで操作したい 外出用PCはWindows 8 とLinux …
-
個人のGoogleカレンダーの予定をPythonで取得する
Google Calendar Twilio ReminderのGoogleカレ …