This blog is written in Japanese for about “Real-time data analytics from IoT device with Google Spreadsheet”. Sample movies on youtube are below.
大阪と東京で 2016年6月10日(金) および13日(月) に開催したデベロッパーキャンプでは、私の担当するセッションで「IoT デバイスからの値を Google Spreadsheet にリアルタイムに送信しつつ、グラフやヒートマップもリアルタイムに更新する」というデモをご紹介しました。
以下のリンクはYoutubeで公開中の第32回デベロッパーキャンプのムービーから、実際に行ったライブデモのシーンだけを抜き出したものです。
そしてデータをGoogle Spreadsheetに流し込む手法については夕方の Lightning Talkでも披露いたしましたが、5分間という時間枠では概要しかご説明できませんでしたので、その方法およびヒートマップ化のために使用したワークシート関数について改めて説明いたします。
まずは Google Spreatsheet のサンプルを用意しましたので、以下のURLを開きつつ読み進めていただければと思います。
https://docs.google.com/spreadsheets/d/1pIEwJCNgOH5QXRzqyND_Papvmntq_5BurvUBfNBn-T0/
Spreadsheet の解説
この Spreadsheet で行っていることは以下の通りです。
- Google Form 経由で計測値を受信する。
- Google Form の回答用シートの値を別シートから参照する。
- 各地点ごとの滞在数カウント。
- 滞在数カウントに基づくヒートマップの描画。
なお、この方法によるデータ収集や分析は、データ分析方法を検討試行する段階に向いています。
データサイエンティストの方なら R 言語等を使うほうが便利ですし、Web系の方は ElasticSeearch + Kiibana を利用するのが得意でしょう。本番稼働時にはこの手のツールを使った分析を考えるべきと思いますが、この手のツールにあまり馴染みがない場合は Microsoft Excel や Google Spreadsheet をはじめとするスプレッドシートが最も身近なツールですので、これを使うのは一つの選択肢としてアリです。
ただし Google Spreadsheet にデータを大量に保存すると閲覧処理が重くなりますし、ここで紹介する方法は第三者による偽のデータの投げ込みを防止することは困難です。そういう意味でも本番稼働時は適切なデータベースサーバとの組み合わせを検討することが必要です。
それでは順番にご説明します。ここでは主にBeaconFenceの測位データから簡易ヒートマップを描画する手順をご説明していますが、IoTセンサーからの値をグラフ化したい場合も基本的には同じ手順で行えます。
Google Form 経由で計測値を受信する
Google Form は Google Drive 上で Web フォームを作成する機能です。本来はアンケートフォームや問い合わせフォームの作成を行うツールなのですが、これをデータの受け渡しに使うことで Google Form → Google Spreadsheet のデータ連携が可能となります。
ところでGoogle Form でフォームを作成すること自体の説明は省略いたします。Google アカウント（または Google Apps アカウント）をお持ちなら以下のリンクから実際に操作してください。非常に簡単にフォームを作成できることが分かると思います。
https://www.google.com/forms/about/
さて、Google Form ではフォームが簡単に作れますが、収集した回答内容は初期状態では Google Spreadsheet にはリンクされていません。Form の編集画面から CSV 形式でダウンロードできるだけです。これでは今回の目的に合致しないので、回答内容を Google Spreadsheet に出力するように設定を変更します。これは以下のURLに手順説明があります。
https://support.google.com/docs/answer/2917686
ここまでの作業により、Google Form から送信したデータが Google Spreadsheet に受け渡されるようになりました。
あとは、Google Form からの回答を実際に受け取っているURLやパラメータがわかれば、アプリケーション側からこれを呼ぶことでデータの投げ込みが可能になります。この形式は基本的には以下のフォーマットを取ります。
https://docs.google.com/forms/d/[FORM_ID]/formResponse?entry.[NAME_1]=[Value_1]&entry.[NAME_2]=[Value_2]
上記は GET メソッドの形式ですが、POST で受け渡すことも可能です。
FOMR_IDやNAME_n, Value_n はフォーム作成時に自動的に割り当てられる一意な値ですが、これはブラウザでデベロッパーモードを有効にした状態で実際にフォーム送信すれば容易に確認できます。Google Form へのデータ投げ込み用の URL を確認したら、実際にそのURLに対してリクエストを投げ込むテストを行います。ブラウザでGETメソッドでリクエストを投げてもよいですし、curl や wget などのコマンドラインツールを用いてもよいでしょう。
上記形式のURLより値が適切に投げ込めることが確認できたら、RAD Studio のアプリケーションでは TNetHTTPClient を用いて以下のようにURLを呼ぶようにすればOKです。
|
1 2 3 4 5 |
NetHTTPClient1.Get( <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'https://docs.google.com/forms/d/[FORM_ID]/formResponse'</span> + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'?entry.[NAME_1]=[value_1]'</span> + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'&entry.[NAME_2]=[value_2]'</span> ); |
BeaconFenceで測位した位置情報を送信する場合のコードは OnPositionEstimated イベントに紐づけます。コードの例は以下のとおりです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<span class="hljs-function"><span class="hljs-keyword" style="color:#000080;font-weight:bold;">procedure</span> <span class="hljs-title" style="color:#0000ff;font-weight:bold;">TForm1</span>.<span class="hljs-title" style="color:#0000ff;font-weight:bold;">OnPositionEstimated</span><span class="hljs-params">(AEstimatedPoint, APointToPath: TPointF)</span>;</span> <span class="hljs-keyword" style="color:#000080;font-weight:bold;">const</span> BaseURL: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span> = <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'https://docs.google.com/forms/d/[FORM_ID]/formResponse'</span>; ParamEstX: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span> = <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'entry.xxxxxxxxxx'</span>; ParamEstY: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span> = <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'entry.yyyyyyyyyy'</span>; ParamPathX: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span> = <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'entry.XXXXXXXXXX'</span>; ParamPathY: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span> = <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'entry.YYYYYYYYYY'</span>; <span class="hljs-keyword" style="color:#000080;font-weight:bold;">var</span> Url: <span class="hljs-keyword" style="color:#000080;font-weight:bold;">String</span>; <span class="hljs-keyword" style="color:#000080;font-weight:bold;">begin</span> Url := BaseURL; Url := Url + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'?'</span> + ParamEstX + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'='</span> + AEstimatedPoint.X.ToString; Url := Url + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'&'</span> + ParamEstY + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'='</span> + AEstimatedPoint.Y,ToString; Url := Url + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'&'</span> + ParamPathX + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'='</span> + APointToPath.X.ToString; Url := Url + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'&'</span> + ParamPathY + <span class="hljs-string" style="color:#0000ff;font-weight:bold;">'='</span> + APointToPath.Y.ToString; NetHTTPClient1.Get(Url); <span class="hljs-keyword" style="color:#000080;font-weight:bold;">end</span>; |
Google Form の回答用シートの値を別シートから参照する
Form からの送信を受け取るシート上で様々な分析を行ってもよいのですけど、これは誤操作によってデータを棄損する可能性があります。あるいは Form の項目を増やした場合に不都合が出る可能性もあります。
このため、分析用には別のシートを作成しておき、そこから回答用シートの値を参照することをおススメいたします。
別シートの値を参照するには「ワークシート関数の QUERY 関数」を使用します。今回のサンプルの Spreadsheet では A2 セルに以下のような計算式を記入しています。
|
1 2 |
=QUERY('Form Responses 1'!A:C,"select A,B,C order by A desc limit 60") |
抽出条件は書式は SQL に似た形式で記述できます。ここでは直近60件分のデータ(limit 60)を時刻の逆順(order by A desc)で取得しています。
各地点ごとの滞在数カウント
今回のサンプルでは、F4:L14 のすべてのセルに COUNTIFS() を用いた下記の計算式を記入しています。（最初に F4 に以下の式を作成後、他のセルに計算式をコピーしています）
|
1 2 3 4 5 6 |
=COUNTIFS( 'analyze 1meter mesh'!$B:$B,">=" & E$3, 'analyze 1meter mesh'!$B:$B,"<" & F$3, 'analyze 1meter mesh'!$C:$C,">=" & $E3, 'analyze 1meter mesh'!$C:$C,"<" & $E4) |
また、この計算式ではE列と3行を縦横の見出し行として相対参照しています。この計算式を F4:L14 に配置することで1mメッシュの各地点の滞在数を算出しています。
滞在数カウントに基づくヒートマップの描画
F4:L14に滞在数がカウントされている状態で「条件付き書式のカラースケール」を設定すると、滞在数の大小に対応して色の濃淡で塗り分けが行われます。これは簡易ヒートマップとして利用できます。
以上の流れにより、アプリケーション側で取得したデータを Google Spreadsheet に流し込みつつリアルタイムでヒートマップを描画することが可能になります。
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition
