本記事では主に日常生活でよく利用され、しかも無料で使えるGoogleのサービスを利用して、問い合わせなどの定型フォーマットのメールを自動的にGoogleのスプレッドシートに書き込んで集計する仕組みを紹介します。
こんにちは、webの仕事を携わっているギンです。
イメージしやすいよう、例えば受信メールはこんなフォーマットになっているのであれば、今回のスクリプトですんなり取得できます。
このように、お問い合わせ、セミナーのご案内、アンケートの回答はこのような形式になることが多いでしょう。
単純に項目を並べる場合が多いと思いますが一件一件手で集計するのは時間も労力もかかりますので、今回はGoogleのスクリプトを使って、受信データをスプレッドシートに自動的に書き出す方法を紹介します。
ご留意点:後半はスクリプトを必要に応じて書き換えたりする部分が出てきますので、どうしてもうまくいかない場合は、scriptがわかる方に聞いていただくか、本記事のコメント欄にてご質問いただければ回答いたします。
事前準備1、受信しているメールにラベルをつけよう
最初なので少し細かく紹介します。実は付け方は簡単です。
1、Gmailの設定ボタンを開き、設定をクリック
2、設定画面が開くので矢印が指している「フィルタとブロックの中のアドレス」を選択
3、ページの一番下へスクロールして、「新規フィルタ作成」をクリック
4、フィルタの条件、つまり検索と同じ検索条件を入れ、右下の「この検索条件でフィルタ作成」をクリック
5、「新しいラベル」をクリックして
6、ラベルに名前をつけて、「作成」をクリック、これで新しいラベルが作成されますね。
で、ラベルつけたいメールを見てみよう。矢印が指している部分のように、さっき作ってたラベル名が表示されればOK。
事前準備2、スプレッドシートを用意する
注意点としては、シート名にもわかりやすい名前をつけておこう。
スクリプトを用意する!
ここからが本番です。ちょっとしたミスや書き間違いで動かなくなりますので、気合いを入れましょう!!!
ツール>スクリプトエディタ を選ぶ
すると、スクリプトの画面が表示されます。
そこに下記のコードをコピーしてそのまま貼り付けてください。
function getMail01(){ var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var objSheet = objSpreadsheet.getSheetByName("集計シート");//シート名をここに入力 var sheet = SpreadsheetApp.setActiveSheet(objSheet); //GMAILから取得するものを指定 var start = 0; var max = 500; var threads = GmailApp.search('label:問い合わせ is:unread',start,max); var row = sheet.getLastRow() + 1;//最後の行探してそれ以降に追加 for(var n in threads){ var thd = threads[n]; var msgs = thd.getMessages(); for(m in msgs){ var msg = msgs[m]; var body = msg.getBody(); var res = body.split('<br>\n<br>') // replaceで不要な文字列を削除、splitで改行ごとにuserという変数に代入 var user = res[0].split('<br />\n'); //この部分はメール文面の前半部分になります。取りたり文字列を必要に応じて書き換えてください sheet.getRange(row, 1).setValue(String(user[0]).replace("[MANAGE_NUMBER] : ","")); sheet.getRange(row, 2).setValue(String(user[1]).replace("[title] : ","")); sheet.getRange(row, 3).setValue(String(user[2]).replace("[com] : ","")); sheet.getRange(row, 4).setValue(String(user[3]).replace("[nam] : ","")); sheet.getRange(row, 5).setValue(String(user[4]).replace("[e_mail] : ","")); sheet.getRange(row, 6).setValue(String(user[5]).replace("[tel] : ","")); sheet.getRange(row, 7).setValue(String(user[6]).replace("[date] : ","")); sheet.getRange(row, 8).setValue(String(user[7]).replace("[doui] : ","")); sheet.getRange(row, 9).setValue(String(user[8]).replace("[cid] : ","")); row++ } thd.markRead(); Utilities.sleep(1000); } }
参考記事:http://webimemo.com/web/8403
後半部分は、メールの固定項目を置き換えるためのものです。適宜自分の都合に合わせて書き換えてください。
貼り付けたらこんな感じになりますね↓
そこで、実行してみよう。下記のように、▶︎のマークをクリックして
最初だけ、承認が必要ですので、「許可を確認」をクリック。
Google からの確認を承認し、許可をクリック。
これで、スクリプトが実行されます。
では実行結果を作ったスプレッドシートに戻ってデータが書き込まれているかを確認しよう。
2件書き込まれましたね。これで出来上がりです。
再度、このスクリプトを定期的に自動実行させればOK。
時計のマークをクリックして
実行のタイミングを設定すれば良いのです。
これで完成です。
ぜひご活用ください。
メールからGoogleスプレッドシートへの自動掲載の方法を詳しく紹介してくれてすごく助かりました!
作成したEXCELスプレッドシートに上手く改行が出来ません。
名前とか電話番号とかメールアドレスがすべて同じ行になってしまうんですが、詳しく教えてください!
スクリーンショットでもお見せしたいので、ご返信お待ちしております。
ご覧いただきありがとうございます。メールでは改行されているのに、スプレッドシートでは改行されずに、一つのセルに入っているということでしょうか。おそらくですが、取得したメール文面は、うまく分解できていない可能性がありますので、var user = res[0].split(‘
\n’); の部分を改行タグを入れるなど、アレンジしてみてください。それでもだめなら、受信している文面の行ごとに、目印(例えば「;」など)を入れて、目印で分解してみてください。
メールの内容は表示されたのですが、全部表示されまして、スプレッド上にはundefinedのセルが並んでしまってます…
どうすればいいですか??
すみません
ぎんさん
詳細にGmailの記録内容をご記載いただき助かりました。
現在挑戦しているのですが、中々うまくいきません。
流れとしては記載の通り
コード入力→トリガー設定
の順で入力を進めております。
ご助言をいただけますと助かります。
宜しくお願いいたします。
分かりやすい説明や設定方法の紹介ありがとうございます!
スクリプトを走らせてデータを取り込むと~と不要な情報まで取り込まれてしまいます。
メールに受信した内容が、テキスト形式ではなくHTML形式の場合上記とは別のコードになるのでしょうか
お問い合わせいただきありがとうございます。結構前に書いたコードですが、まだ使えそうでよかったです。HTML形式の場合、余計な記号などが取り込まれていると思いますが、うまくreplace関数を使ってなんとかいらないものを消していくコードを加えた方がよいかと思います。よろしくお願いいたします。
ご丁寧なご説明ありがとうございます!
一点ご質問があるのですが、こちらからデータを取得後定期的に問い合わせ受付を自動化させているのですが、
何社か重複が発生してしまうようです。
実行の度に過去の企業も取得してしまうようでして、、こちらの解決する策はございますか?
返信遅くなりすみません。データの重複は自分もありましたね。その場合2つの可能性があり、一つは単純にお客さんが何回か問合せボタンをクリックして複数同じ内容の問い合わせが飛んできた。この場合は余分の問合せ消して特に気にする必要はないかと思います。もう一つの可能性としては、本スクリプトはメールを取り込んだ後に既読しています、つまり未読をトリガーに取り込みしているのですが、そこでついメールを確認したあとに、未読にしてしまうと、もう一回取り込みされますので、そこは気を付ける部分かと思います。ご参考までに。よろしくお願いいたします。
とてもわかりやすいご説明ありがとうございます。
初歩的で恐縮ですが、一点質問がございます。
ご説明通りに進めていくと、以下のようになります。
ツール>スクリプトエディタ>Apps Script画面に遷移
Apps Scriptへの変更に伴い、ご説明以外に「デプロイ」は必要になるのでしょうか。
ご返信よろしくお願いいたします。
最近更新できておらず、申し訳ありません。デプロイはどういったものかわからなかったのですが、調べてみたら、自分が作ったプログラムを公開する?のような機能だそうです。
ということは、外部向けに公開したい場合は、デプロイを使えば良くて、自分用の場合は不要かと思います。ご検討いただければと思います。