ヤマムギ

growing hard days

*

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

      2016/03/27


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

Google-apps-script-superman

例えば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]というラベルが作成されて付与されます

コード

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

メイン処理

項目名配列の生成

処理済ラベルの作成

SpreadSheetの作成

Gmailのmax rate対応

結果のメール送信

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

コード解説

gmailAggregate

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

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

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

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

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

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

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

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

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

textArrayCreate

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

createProcessedLabel

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

createMailSheet(の作成

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

maxRateAvoid

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

mailSending

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

toOriginalDateString

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

以上です。

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

 - Google, GoogleAppsScript ,

ad

ad

Comment

  1. わたなべ より:

    はじめまして、非常に参考になりました。
    有用に扱わせて頂こうと思います。

    1点質問がございます。
    新着のものだけ出力したい(or読み取りたい)のですが、
    どうしても新着がある度にスレッドごと出力してしまいます。
    Gmailapp.search自体がスレッドを拾ってくるもののようなので、これはどうにもならないのでしょうか・・?

    • @yamamanx @yamamanx より:

      わたなべさん

      新着 = 未読 でOKなら、下記のようにラベルの中でも未読のものだけを検索するようにしてみてはいかがでしょうか?

      GmailApp.search(‘is:unread label:’ + unProcessLabelName);

      (試して見てないのでうまくいくかはわかりませんが)

  2. @yamamanx @yamamanx より:

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

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

  3. ぱいんちゃん より:

    [FormMail]title

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

    メモ: ×××

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

Message

メールアドレスが公開されることはありません。

  関連記事

Google Apps ScriptでAdmin SDK Directory Serviceを使ってユーザの最終ログイン情報一覧を出力する

GoogleAppsで使わなくなったアカウントを確認する方法として、最終ログイン …

Redmine 3.0.0 のメールサーバーでGoogleApps SMTPサーバーを利用する

Redmineのメール通知サーバーとしてGoogleAppsを使用する方法です。 …

Google関連のAPIを試すためのGoogleSiteを作ってみました

組み込み機能で出来る事は組み込み機能で と思いまして、Googleサイトを試験的 …

chromium OS をインストールするためのUSBをMacで作る

別にChromeBookを購入するお金をけちるわけではないのですが。 chrom …

RedmineにGoogleAppsアカウントでログイン出来るようにする

RedmineのログインをGoogleアカウントで出来るように[redmine_ …

自転車でナビ+記録出来るアプリ+ホルダー+バッテリー試してみた

目的 記録を蓄積して振り返りやる気を継続する 飽きないように単調にならないように …

個人のGoogleカレンダーの予定をPythonで取得する

Google Calendar Twilio ReminderのGoogleカレ …

redmine_omniauth_googleプラグインをRedmine3.0.0で動くようにする

redmine_omniauth_googleプラグインをインストールしたところ …

GoogleAppsScriptを使ってGmailをSpreadSheetにエクスポートする

GoogleAppsScriptを使ってGmailをSpreadSheetにエク …

Googleカレンダーの予定をV3 APIでPHPからJSONを取得してWebページに表示する

Googleカレンダーを使って管理しているライブスケジュールを日付によって、 未 …