はじめに
SlackやらMicrosoft Teamsやら、チャットへシフトしている時代でも、メールをうまいこと利用して、いい感じにオートメーション化する方法を紹介します。
必要なのはGoogleアカウントのみで、サーバーレスかつセキュアなクラウド環境下にて完結できます。なおかつ無料です。
Gmail→Googleスプレッドシート（gas）→Googleデータポータルですので、一般的に利用できるSaaSのみでOKです。
子どもの塾入退室管理
塾によっては、入退室管理システムなるものを導入し、保護者にメールにより入退室を教えてくれるサービスを導入しています。
このメールをGmailで受信し、GAS（GoogleAppsScript）でGoogleスプレッドシートにデータを頂いて、Googleデータポータルで可視化してみました。
子どもの塾入退室管理をオートメーション化しました。
また、BI(ビジネスインテリジェンス)ツールであるGoogleデータポータルを使うことで、勉強時間の解析をすることもできます。
pay管理
キャッシュレスは便利だし、お得だから、ついつい使いすぎてしまう。なんてこともありますよね。
利用したときにメールでお知らせしてくれるサービスがあります。
これを利用して、同じ要領でダッシュボードを作って見える化してみました。
何がしたい
このやり方を応用すれば、Gメールで受信できるものは全てデータベース化することができます。
例えば、申請をメールで受けた時点で内部書類や社内起案文書ができていて、報告書から台帳登録、データ見える化まで自動で。なんてこともできてしまいます。
しかも高価なサーバーをバンバンたてて、システム導入やそれらのセキュリティ対策含むインフラ管理をすることなく、Googleのサービスを組み合わせるだけで、セキュアかつ無料のサーバーレス運用なわけです。
ゴール
Googleデータポータルで塾の入退室時間を管理
準備
Googleアカウント
持っていない人は作成してください。
https://support.google.com/accounts/answer/27441?hl=ja
構築
Gmail設定
お知らせメールを開き、ラベルマークをクリックします。
新規作成ボタンをクリックし、ラベル名をつけます。
塾入退室にしてみました。一旦保存します。
先ほどのラベルマークの横にある、丸の中に縦に点が並んでいるマークをクリックします。
メールの自動振り分け設定をクリックします。
Fromにメールアドレスが自動で入力されていることを確認します。
件名に退室のお知らせと入力します。
入室退室以外に、同じメールアドレスからお知らせが届くかもしれないので、メールの件名でフィルタをかける設定をしておきます。
ラベルを塾入退室にし、下の〇件の一致するスレッドにもフィルタを適用する。にもチェックをいれてフィルタを作成します。
同じ方法で入室メールにもフィルタ設定をしておきます。
件名が異なるので、件名に退室のお知らせと入力した箇所に入室のお知らせと入力して同じように作成します。
これでGmailの設定は終了です。
Googleスプレッドシート
Googleスプレッドシートをデータベースとして利用します。
とりあえず名前を塾入退室管理にしてみました。
シート名はテーブルにしておきます。
セルA1、B1、C1、D1、E1、F1に順番に次のとおり入力します。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 日付 | 差出人 | 件名 | 内容 | ID | url |
GAS
ツール、スクリプトエディタの順にクリックします。
すると、GASのスクリプトエディタが起動します。
次のソースコードを入力します。
ソースコード
var mySheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('テーブル');
function searchContactMail() {
/* Gmailから特定条件のスレッドを検索しメールを取り出す */
var strTerms = 'label:塾入退室';//検索ラベル
var myThreads = GmailApp.search(strTerms,0,30);
var myMsgs = GmailApp.getMessagesForThreads(myThreads);//二次元配列
var valMsgs = [];
/* 各メールから日時、送信元、件名、内容を取り出す */
for(var i=0;i<myMsgs.length;i++){
for(var j=0;j<myMsgs[i].length;j++){
var msid = myMsgs[i][j].getId();//メッセージIDを取得
//もしメッセージIDがスプレッドシートに存在しなければ
if(!hasId(msid)){
var date = myMsgs[i][j].getDate();
var from = myMsgs[i][j].getFrom();
var subj = myMsgs[i][j].getSubject();
var body = myMsgs[i][j].getPlainBody().slice(0,200);
var perm = myThreads[i].getPermalink();
valMsgs.push([date,from,subj,body,msid,perm]);
}
}
}
/* スプレッドシートに出力 */
if(valMsgs.length>0){//新規メールがある場合、末尾に追加する
var lastRow = mySheet.getDataRange().getLastRow();
mySheet.getRange(lastRow+1, 1, valMsgs.length, 6).setValues(valMsgs);
}
}
function hasId(id){
var data = mySheet.getRange(1, 5, mySheet.getLastRow(),1).getValues();//E列(メッセージID)を検索範囲とする
var hasId = data.some(function(value,index,data){//コールバック関数
return (value[0] === id);
});
return hasId;
}
貼り付ける時は最初入っているコードごと上書きしてください。
名前を付けます。
今回は塾入退室にしてみました。
気を付ける点は、Googleスプレッドシートのシート名と、Gmailのラベル名です。
ここが異なるとデータをとってこないのでご確認ください。
GASを実行
いきなり実行してみましょう！
承認画面がポップアップされれば、許可を確認をクリックします。
アカウントを選択します。
当然、今作っているので確認されていません。
詳細をクリックします。
塾入退室（安全ではないページ）に移動をクリックします。
今あなたが作ったアプリがGmailとGoogleスプレッドシートの操作をすることを許可するかどうかといった画面です。
許可します。
いきなりエラーでちゃいます。
これは最初に実行する関数を選んでいなかったためです。
searchContactMailを選びます。
気を取り直して再度実行してみてください。
メール一覧のデータがシートに入ってきたら成功です。
はい、一つアプリを作成できました。
おめでとうございます！
トリガーの設定を1時間単位にすれば、自動で収集してくれます。
エディタから編集、現在のプロジェクトのトリガーから設定します。
トリガーを追加をクリックして、searchContactMailを選択します。
時間主導型、時間ベースのタイマー、1時間おきで設定しておきます。
データ抽出
ここから必要な情報だけ抜き出していきます。
まず、日付について、実は時間もデータとして持っているので、表示形式を変更して時間まで確認できるように設定してみましょう。
A列を選択して、表示形式、数字、日時の順にクリックします。すると、秒まで表示されます。
データを入れるシートをテーブルとし、編集するシートをクエリとしたいので、新たにシートを作成し、名前をクエリにしておきます。
クエリシートのセルA1に=SORT('テーブル'!A:F,1,FALSE)と式を作成します。
テーブルのデータを引っ張ってきて、なおかつソート（並び替え）します。
G1からJ1に表題、G2からJ2に数式を次の通り入力します。
| G | H | I | J | |
|---|---|---|---|---|
| 1 | 年月 | 時刻 | 時間 | 時間変換 |
| 2 | =ARRAYFORMULA(if(C2:C="退室のお知らせ",year(A2:A)&MONTH(A2:A),"")) | =ARRAYFORMULA(MID(A2:A,FIND(" ",A2)+1,8)) | =ARRAYFORMULA(if(C2:C="退室のお知らせ",A2:A-A3:A,"")) | =ARRAYFORMULA((value(I2:I))) |
G列には塾から退室した年月が表示されます。
H列には時刻、I列は塾に入室していた時間が入ります。
J列は、データベースを加工しやすいように、時間を数値に変換しておきます。
Googleデータポータル
GoogleのBIツールを利用します。
以前は利用に○○○万円必要だったツールが現在は無料で提供されていたりましす。
しかもGoogleの中で完結できるので、高度なセキュリティの中で運用できます。
ダッシュボード作成
まず、Googleデータポータルにアクセスし、
https://datastudio.google.com/navigation/reporting
空のレポートをクリックして新規作成します。
右下の新しいデータソースを作成からGoogleスプレッドシートを選択し、先程作成した塾入退室管理を選びます。ワークシートはクエリを選び、最後に右上の接続を選びます。
すると接続されたデータが表示されるので、フィールドの設定をしていきます。
右上のフィールドを追加から、フィールドをいくつか追加します。
項目名をWEEK_DEFAULT_DECIMALとし、計算式を次の通り入力します。
WEEKDAY(日付, "DEFAULT_DECIMAL")
右下の更新ボタンをクリックし、左上のすべてのフィールドをクリックしてもどります。
続いて同じ要領で、項目名をWEEKとし、次の計算式を入力したフィールドを作成します。
CASE
WHEN WEEK_DEFAULT_DECIMAL = 0 THEN "日"
WHEN WEEK_DEFAULT_DECIMAL = 1 THEN "月"
WHEN WEEK_DEFAULT_DECIMAL = 2 THEN "火"
WHEN WEEK_DEFAULT_DECIMAL = 3 THEN "水"
WHEN WEEK_DEFAULT_DECIMAL = 4 THEN "木"
WHEN WEEK_DEFAULT_DECIMAL = 5 THEN "金"
WHEN WEEK_DEFAULT_DECIMAL = 6 THEN "土"
ELSE "other"
END
これで、曜日のデータも見える化できます。
項目名が時間変換VALUE
計算式
時間変換*24*60*60
のフィールドも作成しておきます。
こちらは、数式のとおりです。
タイプを数値、持続時間（秒）に変換しておきます。
これでフィールドの設定が完了したので、いよいよダッシュボードを作っていきます。
右上のレポートに追加をクリックします。
初めての場合はポップアップくるかもですが、レポートに追加でOKです。
いきなり、表が勝手に作成されていますので（たぶん）、ディメンジョンに日付、時刻、urlを追加設定します。
指標は、勝手にRecord Countが入っています。
カーソルを上に合わすと右に×マークが現れるので、それをクリックして削除します。
並べ替えは日付の降順で設定しておきます。
フィルタで、不要なデータを表示しないようにしたいので、フィルタを追加をクリックし、名前を空白排除、一致条件を除外条件、項目を選択をID、条件を選択をnullであるを選び、保存します。
次からもこのフィルタは再利用できますので覚えておきましょう。
忘れていました。
左上で名前をつけておきましょう。
塾 入退室管理にしました。
続いて、グラフをどんどん追加していきます。
棒グラフを選択し、任意の場所に設置します。
ディメンジョンを日付、指標を時間変換VALUE、並べ替えを日付にしてみました。
凡例が時間変更VALUEになっているので、名称変更しておきます。
指標の上にカーソルを合わせると鉛筆マークが現れるので、こちらをクリックします。
ここで名前を塾滞在時間等に変更すると反映されます。
フィルタの空白排除設定もお忘れなくお願いします。
次は集計グラフを作成します。
グラフを追加から折れ線、複合グラフを追加します。
期間のディメンジョンに日付、ディメンジョンに月日、指標に件名、時間変換VALUEを選択します。
並び替えは月日、フィルタを集計用に作成します。
名称を集計にし、除外条件、年月、nullであるで保存します。
凡例の名前も件名を回数、時間変換VALUEを時間に修正しておきます。
グラフを追加から、円グラフ（ドーナツ）を追加します。
ディメンジョン、指標、並び替えをWEEKにして、指標の集計方法を個別件数から件数に変更します。
名前を変える方法と同じ設定画面から変更できます。
さらにフィルタの空白排除を選択します。
グラフを追加からスコアカードを追加します。
指標を時間変換VALUEに変更します。
フィルタ設定は空白排除です。
気をつけるのは、すべての期間のディメンジョンは日付で統一することです。
期間を選択するためのツールを設定します。
画面上のカレンダーマークをクリックして、任意の場所に配置します。
ここで期間を選択すると、それぞれのグラフが期間のディメンジョンをもとにフィルタリングされます。
部品が整ってきたので、テーマやデザインを整えます。
テーマが用意されていて、好きなものを選びます。
視力低下を防ぎたい、節電の観点から、いつもダーク系を基本にしているので、ラグーンを選びました。
ここのグラフ等もレイアウトを編集できます。
オブジェクトを選択して、スタイルを設定してみましょう。
好みにあわせて色々いじってみてください。
おまけ
同じ要領で、様々なメールからデータベースを取得できます。
例えば、payの利用状況メールを利用して、ダッシュボードで集計したり、スプレッドシートで必要な様式に帳票したり、好き放題加工可能です。
スプレッドシートで、メール本文から、必要な箇所だけ抜き出したい！がこのメールから始まる自動化生活の要なわけですが、次の数式をご活用ください。
=ARRAYFORMULA(value(MID(D2:D,FIND("決済総額",D2:D)+7,FIND("円",D2:D)-7-FIND("決済総額",D2:D))))
MIDとFINEを組み合わせることで、ほしい文字列を抽出することが可能です。
上記の例では、メール本文から、決済総額の金額をH列に抽出しています。
同じように、G列に場所を抽出しています。
F列のマップについては、
=ARRAYFORMULA(HYPERLINK("https://www.google.co.jp/maps/place?q="&SUBSTITUTE(SUBSTITUTE(G2:G, " ", ""), " ", "")))
と記述することで、G列の名称からハイパーリンクでグーグルマップで検索と表示を同時にする感じにしています。
これもGoogleデータスタジオ側のハイパーリンク関数でダイレクトに反映することができます。
メールデータベース化ハッカソンを密かに期待して、一例を紹介してみました。
面白いアイデアを実践された方は内容についてコメントいただき、一緒に勉強できたらうれしいです。
記事をお読みいただき、ありがとうございました。