季節は夏。
Cカード講習が続くと、指導団体への申請がごっちゃごちゃになる。
そんな経験はありませんか？
PADIの様に手書きの申請書であれば、その場で書いてもらって郵送。
写真が無い場合は写真が送られて来たらすぐに郵送。
これならそこまでごちゃごちゃになることは無いかもしれません。
一方、NAUIなどWEB上で申請を行う場合、しっかり管理していないと、ごちゃごちゃになりがちです。
もちろんだからといって、WEB申請を否定するわけではなく、申請用紙を紛失するリスクなどを考えると、WEB申請の方が良いと思っています。
さて、NAUIのCカード申請までのフローは以下の通りです。
- 申請番号を（テキストと共に）購入
- 実際に講習を行う
- 講習生がNAUIの用意したフォームに記入、送信
- どの講習生でどの申請番号を使用するかを選択して、申請（WEB上）
今回話題にしたいのは3.の部分です。
一度に大人数の講習をしたり、連日講習を行っていたりすると、どの講習生がフォーム送信を済ませていて、どの講習生がフォーム送信していないか、わからなくなってしまいます。
これを解決しようということです。
もちろん、NAUIのメンバー向けページ（管理画面）でctrl+Fなどで検索をかけて探すこともできます。
また、フォームが送信されるたびに以下の様なメールがNAUIから届くので、そのメールをあさることもできます。
「この人は送信してくれたかな？」とピンポイントで探す分には良いのですが、「この20人のうち誰が未送信かな？」ということを把握するには、なかなか手間がかかります。
送信した方の名前が、スプレッドシートなどにまとまっていれば、もう少し集計も楽になりそうですよね。
先ほどのメールの画像でお気づきの方もいるかもしれませんが、僕は会社宛のメールをGmailで受け取っています。（Gmailをメールアドレスとしてでは無く、メールソフトとして使っている、というイメージです。）
Gmailもスプレッドシートも同じGoogleのサービスなので、連携することが可能です。
ということで、Gmailのデータをスプレッドシートに転記する、ということを実現してみたいと思います。
Step.1 メールにラベルをつける
いちいち全てのメールを検索対象にしてしまうと、システムに負荷がかかってしまうことが予想されるので、検索対象にしたいメールに自動的にラベルがつくように設定します。
まずはラベルを作成します。
右上の歯車をクリックし、「設定」をクリック。
ラベルをクリックし、「新しいラベルを作成」をクリックします。
任意の名前を入力し、作成をクリック。
これでラベルが作成されます。
次に、このラベルが特定の条件を満たすメールに自動で付与される様、フィルタを設定して行きます。
同じ「設定」画面の中の「フィルタとブロック中のアドレス」を選択し、「新しいフィルタを作成」をクリックします。
様々な条件を設定することが可能ですが、今回は
- NAUIの自動送信用のメールアドレスから送信されている
- 件名が「顧客登録がありました。」
この2つの条件に合致する時、ラベルがつくようにしました。
Step.2 GASでごにょごにょする
今回のコードは、こちらのサイトで紹介されている物をベースにさせてもらいました。
作りたいものはこんな感じ。
A列に受信日を、B列に名前を記入して行きます。
A列に受信日を記入することで、次回の実行時に前回の最後の受信日を取得し、その日付以降のメールのみを検索対象にします。
先にコードをご紹介します。
function formpostck(){
var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var objSheet = objSpreadsheet.getSheetByName("フォーム送信チェック");
var sheet = SpreadsheetApp.setActiveSheet(objSheet);
var lastupdate = sheet.getRange(sheet.getLastRow(),1).getValue();
var lastupdatestr = Utilities.formatDate( lastupdate, 'Asia/Tokyo', 'yyyy/MM/dd');
var start = 0;
var max = 100;
var threads = GmailApp.search('label:NAUI after:'+lastupdatestr,start,max);
var row = sheet.getLastRow() + 1;
for(var n in threads){
var thd = threads[n];
var msgs = thd.getMessages();
for(var m in msgs){
var msg = msgs[m];
var date = msg.getDate();
if(date <= lastupdate){
continue;
}
var body = msg.getBody();
var res = body.split('\n')
var user = res[5].split('<br />\n');
var name = user[0].replace('顧客名：', '');
var datestr = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
sheet.getRange(row, 2).setValue(name);
sheet.getRange(row, 1).setValue(datestr);
row++
}
}
sheet.getRange(2,1,sheet.getLastRow()-1,2).sort({column: 1, ascending: true});
}基本的には参考にさせてもらったもののままですが、いくつか工夫（知っていれば当たり前なのでしょうが…）が必要だったので、メモとして残しておきます。
まず一工夫必要だった点が、Gmail内の検索は、メール単位では無くスレッド単位ということ。
スレッドとは、返信の応酬や、同じ内容のメールが複数送られてきている際に、自動でひとまとめにされているものです。
なぜ工夫が必要かというと、NAUIから送られてくる自動送信メールは内容がほぼ同じなので、1つのスレッドにまとめられることが多いです。
例えば5月1日～3日の間に10人がフォームを送信したとして、5月2日に受信したものを調べようとしても、5月2日に受信したものが含まれるスレッド、が結果として返って来ます。
今回のお題は日ごとにチェックをする必要がある内容です。
何も考えずにやってしまうと、以下の様な事が起きます。
- 5月1日のチェック→5月1日に送信してくれた人が集計される
- 5月2日のチェック→5月2日に送信してくれた人もそれ以前に送信してくれた人と同じスレッドなので、5月1日に送信してくれた人も重複して集計される
- 5月3日のチェック→5月3日に送信…以下略
要は、延々と重複して集計されてしまうんです。
そこで今回は、特段難しい処理ではありませんが、以下のコードで対策を行いました。
if(date <= lastupdate){
continue;
}スレッドに所属する各メールを処理する際に、for文で処理して行きますが、受信日時が前回の実行で集計したものの最後の受信日時よりも前なら、その他の処理をスキップしています。
また、やってみてわかったこととして、どうしても受信日時が新しい順に処理が行われてしまいます。
そこで、全ての処理の最後に、受信日時でソートを行っています。
sheet.getRange(2,1,sheet.getLastRow()-1,2).sort({column: 1, ascending: true});もうひとつハマったこととしては検索条件の書き方です。
検索条件の指定は以下のlabel:NAUI after:’+lastupdatestrの部分です。
GmailApp.search('label:NAUI after:'+lastupdatestr,start,max);しれっと書いていますが、複数の条件を指定するにはどうするか、少しだけ悩みました。
また、ラベルや日付以外で絞り込みを行う場合はどの様に書けばよいか、という問題もあります。
答えとしては、試しにGmailの画面で同じ絞り込みを行ってみたとき、表示される内容、です。
Gmailの画面、最上部の検索バーの右端にある▼をクリックすると、詳細な条件でメールを検索することが出来ます。
ここに、実現したい検索条件を入れてみると、検索バーに文字列が表示されます。
この文字列を、必要に応じて必要な部分を変数に変えつつ、コードに入れてあげればOKです。
今回のお題、人力でやってもそこまで手間では無いのかもしれませんが、1秒でも時間の惜しい夏の季節、ちまちまとした作業の時間を大幅に短縮することができました。
またなにか、忙しい夏の事務作業を1秒でも短縮し睡眠時間をダイビングの事を考える時間を1秒でも増やすものがあれば、随時お伝えして行きたいと思います。