Edited at

リビングの環境監視ダッシュボードを60分で作る方法(Nature Remo Cloud APIとGoogleサービス連携)


はじめに

アレクサで、古い家電を操作する目的で購入したNature Remo

センサーがとても秀逸だったので、部屋のモニタリングする仕組みを作ってみました。

部屋の環境が一目でわかるようになり、色々活用できそうです。

Nature Remo Cloud APIを利用して、Googleのサービスで作ってみました。

GAS(GoogleAppsScript)Googleスプレッドシートにデータを頂いて、Googleデータポータルにまとめてみました。これでサーバーレスかつセキュアなクラウド型環境監視ダッシュボードが完成しました。

とりあえず、トリガーで1時間ごとに自動更新することにしました。

kankyoukanshiimg1.JPG

Nature Remo、Googleスプレッドシート、Googleデータポータルの順を現した写真


今回のゴール

Googleデータポータルでリビングの照度、温度、湿度を監視。

1時間おきのログを目視的に確認できるようにする。

kankyoukanshi.PNG


必要なもの


Nature Remo


https://nature.global/

Nature Remo(ネイチャーリモ)は、お使いの家電をインターネットに繋げることで、手軽にスマートホームを実現するスマートリモコンです。


持ってない人は購入してくださいね。

Nature Remo

今回の説明は、Nature Remoセットアップ完了後を想定して説明します。


Googleアカウント

持っていない人は作成してください。


 https://support.google.com/accounts/answer/27441?hl=ja


NatureRemoのアクセストークンを発行する


 https://home.nature.global/

↑URLからログイン

kankyoukanshiimg20.JPG


Googleアカウントでログイン

kankyoukanshiimg21.JPG


Remoへのアクセスをリクエストする

Remoへのリクエストを許可します。

kankyoukanshiimg22.JPG

kankyoukanshiimg23.JPG


アクセストークンの生成

アクセストークンの生成をクリックします。

kankyoukanshiimg24.JPG

コピーします。

kankyoukanshiimg25.JPG

アクセストークンはとても大切なので、公開することのないように、厳重に管理してください。

この後、このトークンを使用しますので、適切に保存してください。


Googleスプレッドシート作成


https://docs.google.com/spreadsheets/u/0/

↑URLから作成できます。

kankyoukanshiimg30.JPG

Googleスプレッドシートが開きます。

kankyoukanshiimg31.JPG


シート作成

kankyoukanshiimg32.JPG

スプレッドシート名は任意でOKです。

A1に日時、B1に湿度、C1に湿度、D1に照度、E1に日時(補正)を入力して表題部分を作成します。

kankyoukanshiimg33.JPG

シート名をlogに変更しておきます。


GASでNatureRemo Cloud APIを呼び出す

GASって何?

簡単に言うと、Googleが開発した軽量アプリケーション開発用のスクリプト言語です。

Google Apps Script 入門


GASを作成

シートのツール、スクリプトエディタの順にクリックします。

kankyoukanshiimg42.JPG

すると、GASのスクリプトエディタが起動します。

kankyoukanshiimg36.JPG

次のソースコードを入力します。


ソースコード

var access_token = '***************'//←トークンを入れる

var spreadsheetId = '*****************'//←スプレッドシートのIDを入れる
function remo() {
var data = getNatureRemoData();    //data取得
var lastData = getLastData();     //最終date取得
setLaremoData(
{
te:data[0].newest_events.te.val,  //温度
hu:data[0].newest_events.hu.val,  //湿度
il:data[0].newest_events.il.val,  //照度
},
lastData.row + 1//最終data追加作業
);
}

function getNatureRemoData() {      //Remoのapiをお借りします
var url = "https://api.nature.global/1/devices";
var headers = {
"Content-Type" : "application/json;",
'Authorization': 'Bearer ' + access_token,
};

var postData = {

};

var options = {
"method" : "get",
"headers" : headers,
};

var data = JSON.parse(UrlFetchApp.fetch(url, options));
Logger.log(data[0].newest_events)
Logger.log(data[0].newest_events.te.val)
Logger.log(data[0].newest_events.hu.val)
Logger.log(data[0].newest_events.il.val)

return data;

}

function getLastData() {
var datas = SpreadsheetApp.openById(spreadsheetId).getSheetByName('log').getDataRange().getValues()  //logシートをゲットする
var data = datas[datas.length - 1]

return {
totalpoint:data[1],
coupon:data[2],
row:datas.length,
}
}

function setLaremoData(data, row) {
SpreadsheetApp.openById(spreadsheetId).getSheetByName('log').getRange(row, 1).setValue(new Date())//A2にゲットした日時ほりこむ
SpreadsheetApp.openById(spreadsheetId).getSheetByName('log').getRange(row, 2).setValue(data.te)  //B2に温度追加
SpreadsheetApp.openById(spreadsheetId).getSheetByName('log').getRange(row, 3).setValue(data.hu)  //C2湿度追加(幅があるけど気にしない)
SpreadsheetApp.openById(spreadsheetId).getSheetByName('log').getRange(row, 4).setValue(data.il)  //D2照度追加
}

よくわかんないや!ってかたは、とにかくソースコードを貼り付けます。

貼り付ける時は最初入っているコードごと上書きしてください。

kankyoukanshiimg36.JPG

こんな感じになると思います

kankyoukanshiimg37.JPG

任意の名前を付けます。

kankyoukanshiimg38.JPG

ちなみに今回はリビング環境監視にしてみました。


NatureRemoのアクセストークンを指定する

初めに取得したアクセストークン(大切なやつ)をaccess_token = 'から後の***マークの部分に貼り付けます。

kankyoukanshiimg39.JPG


スプレッドシートのIDを指定する

続いて今回作成したスプレッドシートのIDをspreadsheetID = 'から後の***マーク部分に貼り付けます。

※スプレッドシートのIDって何よ!?って人は、一つ下を参考にしてください。

kankyoukanshiimg40.JPG


スプレッドシートのID確認方法

今、スクリプトエディタが開いている場合は、タブでスプレッドシートを開いてURLを確認してください。

kankyoukanshiimg41.JPG


GASを実行

ついに準備が整いました。

今、画面にスプレッドシートを開いている場合はスクリプトエディタを再度開いてください

エディタの上の方に三角の再生ボタンがありますよね。

いきなり実行してみてください

kankyoukanshiimg42.JPG


承認

すると、承認が必要です画面がポップアップされるので、許可を確認をクリックします。

kankyoukanshiimg43.JPG

アカウントを選択します。

kankyoukanshiimg44.JPG

このアプリは確認されていません

当然今作っているので確認されてたらおかしいのですが、とりあえず詳細を選択。

kankyoukanshiimg45.JPG

つづいてリビング環境監視(安全ではないページ)に移動を選択。

kankyoukanshiimg47.JPG

アカウントへのアクセスを許可

許可を選択します。

kankyoukanshiimg48.JPG

これで実行されたかと思います。

確認してみましょう。


スプレッドシートを確認

表題の下に数値が入っているでしょうか?

入っていれば、APIの呼び出しに成功です。

kankyoukanshiimg49.JPG

日時(補正)が気になりますよね。

生データだと、秒まで入っているので、不要な部分を取り除くための処理です。

GASで表示形式(フォーマット)を指定して日時を追加するようにすればいいのだけですが、スプレッドシートにはエクセルには存在しない素晴らしい関数があるので、そちらを紹介することにします。(まぁ、ぶっちゃけ手抜きです。)

これで、年月日と時間までのフォーマットのデータの列が完成しました。

E2に=ARRAYFORMULA(TEXT(A2:A,"YYYYMMDDHH"))と入力します。

このARRAYFORMULA。発想がクレイジーで、一発目に使用すれば、数式を配列形式で表示します。Excelに無い概念ですね。

kankyoukanshiimg53.JPG

つづいて、表を整理します。

少しでも不具合を回避するために、不要な部分を消します。

3行目から1000行目(最終行)までを削除しておきます。

kankyoukanshiimg54.JPG

3行目を選択して、最終行をShiftを押したまま選択すると3行目から1000行目(最終行)までを選択できます。

kankyoukanshiimg55.JPG

行3-1000を削除

kankyoukanshiimg56.JPG

とてもシンプル

kankyoukanshiimg57.JPG

※F列からZ列も同じように削除しておきましょう。また、setシートについても同じように不要な行列は削除しておきましょう。


トリガー設定

続いて、このデータを定期的にスプレッドシートに自動でためていく処理を実装します。

スクリプトエディタに戻って編集、現在のプロジェクトのトリガーを選択。

トリガーって引き金っていうことだと思うんだけど、条件によってさっき作ったGASを実行しますよ!ってことができるんです。

kankyoukanshiimg60.JPG

トリガーの設定画面に移るので、右下のトリガーを追加を選択します。

kankyoukanshiimg61.JPG

イベントのソースを選択で時間帯主導型を選択します。

kankyoukanshiimg62.JPG

時間の間隔を選択(時間)で今回は1時間おきを選択し保存します。

kankyoukanshiimg64.JPG

これでトリガーの設定は完了です。

kankyoukanshiimg65.JPG

スクリプトエディタに戻って保存ボタンを押して保存してスクリプトエディタを閉じます。

kankyoukanshiimg66.JPG


管理シート追加

logの最新情報を拾い出すためのシートを作成します。

kankyoukanshiimg50.JPG

シート名変更をsetにします。

kankyoukanshiimg51.JPG

setシートを作成していきます。

データの表題を作成します。

A1から1列目を順に温度、湿度(グラフ)、湿度、照度と入力します。

kankyoukanshiimg52.JPG.PNG

続いて式を作成していきます。

logシートのデータから、最新のものを表示してみます。

温度A2に=LOOKUP(10^10,log!B:B)

湿度(グラフ)B2に=LOOKUP(10^10,log!C:C)

kankyoukanshiimg67.JPG

つづいて、湿度については円グラフを作りたいので、残りの100分率を求めます。

ですので、B3に=100-B2と入力します。

kankyoukanshiimg68.JPG

湿度C2に=LOOKUP(10^10,log!C:C)

kankyoukanshiimg69.JPG

照度D2に=LOOKUP(10^10,log!D:D)

kankyoukanshiimg70.JPG

おつかれさまです。第2部 スプレッドシートの作成はこれで完了です!

いよいよこのシートを使って環境監視画面(ダッシュボード)を作成していきます。


Googleデータポータル

Googleデータポータルは、Googleが提供するBI(ビジネスインテリジェンス)ツールです。

データを接続して、グラフや表を使って可視化することができます。

詳しくはGoogle先生に聞いてみてくださいね。


リビング環境監視作成

で、早速新しいレポートの開始をクリック。

空白から作りたいと思います。(空白良い響きですね)

https://datastudio.google.com/u/0/

kankyoukanshiimg71.JPG

すると無題のレポートが作成されます。

kankyoukanshiimg72.JPG

名前をリビング環境監視に変更します。

kankyoukanshiimg73.JPG


データソース接続

右下の新しいデータソースを作成をクリック。

kankyoukanshiimg74.JPG

Google スプレッドシートを選択します。

kankyoukanshiimg75.JPG

データソースを選択する画面になります。

ここで、先ほど作成したGoogle スプレッドシートのシートを一つづつ選択します。

kankyoukanshiimg76.JPG

まず、リビング環境監視を選択、続いてワークシートのlogを選択します。

kankyoukanshiimg77.JPG

右上の接続をクリックします。

kankyoukanshiimg78.JPG

レポートに追加をクリックします。

kankyoukanshiimg79.JPG

データソースを追加します。

レポートに追加を再度クリックします

これで、Google スプレッドシートのlogシートが追加されました

kankyoukanshiimg80.JPG

もう一つ、同じ要領でsetシートも追加します。

リソースをクリックします。

kankyoukanshiimg81.JPG

先ほど追加したデータソースが表示されています。

下のデータソースを追加をクリックします。

kankyoukanshiimg82.JPG

Google スプレッドシートを選択

kankyoukanshiimg84.JPG

リビング環境監視を選択、続いてワークシートのsetを選択します

kankyoukanshiimg85.JPG

データソースを接続する画面に切り替わります

kankyoukanshiimg86.JPG

右上の接続をクリックします

kankyoukanshiimg78.JPG

レポートに追加をクリックします。

kankyoukanshiimg87.JPG

データソースを追加します。

レポートに追加を再度クリックします。

kankyoukanshiimg88.JPG


監視画面作成

データソース接続が完了したので、いよいよ作りこんでいきます。

先にテーマを設定します。

kankyoukanshiimg89.JPG

現在のテーマがシンプルになっているので、シンプル(暗)を選択します。

私の場合は、ディスプレイの消費電力量、眼球に対する負荷、の2点(実際の影響は微小または気分だけ)からダーク色を基本としています。

※ここら辺は好みに合わせて色々いじってみてくださいね。

kankyoukanshiimg90.JPG


一覧表作成

いよいよグラフを作成していきます。

グラフを追加を選択します。

kankyoukanshiimg91.JPG

するとプルダウンしますので、まずは表を選択してみます。

kankyoukanshiimg92.JPG

えっ、できた!?と一瞬思います。が、

Google先生が適当に作ってくれるので、これを整えていきます。

使用可能な項目からドラッグアンドドロップの方式で、必要な設定項目にデータソースを設定していきます。まず、日時(補正)を期間のディメンジョンの日時の上にドロップします。

kankyoukanshiimg93.JPG

日時(補正)に変わっていれば成功。

kankyoukanshiimg94.JPG

続いて、表に湿度照度を追加してみます。

先ほどと同じ方法で、指標の指標を追加の上あたりに対して、使用可能な項目から、湿度照度をドラッグアンドドロップします。

表に追加されれば成功です。

kankyoukanshiimg95.JPG

ん?日時(補正)の順番がおかしい?

並べ替えを確認します。

温度とかになっていたら、クリックして、日時(補正)を選択します。

kankyoukanshiimg96.JPG

日時(補正)順に並び替わると成功です。

また、表の見た目を変更するにはスタイルをいじってみてください。

kankyoukanshiimg97.JPG


温度・湿度折れ線グラフ作成

続いて温度と湿度の折れ線グラフを作成していきます。

kankyoukanshiimg98.JPG

またまたいい感じにGoogle先生が作ってくれるので、先ほど同様修正していきます。

期間のディメンジョンを日時(補正)に変更します。

kankyoukanshiimg99.JPG

日時(補正)になっているか確認します。

kankyoukanshiimg100.JPG

並べ替えを、日時(補正)に変更します。

kankyoukanshiimg101.JPG

つづいて、降順を昇順に変更します。

kankyoukanshiimg102.JPG

温度と湿度をドラッグアンドドロップで入れ替えます。

kankyoukanshiimg103.JPG


照度の棒グラフ

照度の棒グラフを作ります。

kankyoukanshiimg104.JPG

先ほど同様、期間のディメンションを日時(補正)に変更します。

kankyoukanshiimg105.JPG

日時(補正)になっているか確認します。

kankyoukanshiimg106.JPG

指標を照度、並べ替えを日時(補正)に変更します。

kankyoukanshiimg107.JPG

続いて降順を昇順に変更します。

kankyoukanshiimg108.JPG


湿度の円グラフ

湿度を表す円グラフを作成します。

グラフを追加から円グラフを選択します。

kankyoukanshiimg109.JPG

なんかド派手なんができてしまいました。

これは、logのソースコードを使用したためです。

データソースリビング環境監視をクリックします。

kankyoukanshiimg110.JPG

リビング環境監視-setを選択します。

kankyoukanshiimg111.JPG

で戻ります。

kankyoukanshiimg112.JPG

特に変化はありません

kankyoukanshiimg113.JPG

ディメンション、並べ替えを湿度に、指標を湿度(グラフ)に変更します。

すると、円グラフが現れました。

500.JPG

降順を昇順に変更します。

501.JPG


湿度・温度・照度スコアボード

湿度、温度、照度の現在の値を作成していきたいと思います。

グラフを追加からスコアカードを選択します。

kankyoukanshiimg116.JPG

いきなり温度のスコアボードが完成しました。

kankyoukanshiimg117.JPG

作成したスコアボードをコピーします。

kankyoukanshiimg118.JPG

任意の場所で右クリックして貼り付けます。

kankyoukanshiimg119.JPG

指標を湿度に変更します。

kankyoukanshiimg120.JPG

同様にコピーから照度のスコアボードも作成します。

kankyoukanshiimg121.JPG


スコアボードのデザインを作る

湿度のスコアボードを先ほど作成した円グラフの真ん中に移動させます。

kankyoukanshiimg122.JPG

温度と照度のデザインも円形で囲んで統一感を出したいと思います。

円形をクリックします。

kankyoukanshiimg123.JPG

円形が作成されるので、円グラフと同じ形状になるように調整します。

kankyoukanshiimg124.JPG

円形のプロパティで背景色を透明に、枠線の色と太さを調整します。

kankyoukanshiimg125.JPG


期間の指定

期間を指定して、情報を見ることができるように、期間を選択のコンボボックスを作成します。

上部のカレンダー(期間)をクリックすると作成できます。

kankyoukanshiimg126.JPG


リンク

下のソースデータにアクセスするためのリンクを作成します。

上部のT(テキスト)をクリックします。

kankyoukanshiimg127.JPG

データにアクセスすると入力してみます。

kankyoukanshiimg128.JPG

入力した文字を選択し、リンクをクリックします。

kankyoukanshiimg129.JPG

いったん、スプレッドシートのURLをコピーし、URLを貼り付けて適用をクリックします。

kankyoukanshiimg130.JPG

リンクの色が見ずらい場合は変更できます。

kankyoukanshiimg131.JPG

テキストのプロパティでフォントの色を変更します。

kankyoukanshiimg132.JPG


デザイン仕上げ

グラフ全体のデザインを自分の好みに調整してみてください。

各グラフを選択して、スタイルのフォント色やグラフの系列色などを変更してみてください。

これで今回のゴールに到着です。

大変お疲れさまでした。

さっそく、ビューをクリックして出来上がりをご確認ください。

kankyoukanshiimg133.JPG

お付き合いいただきありがとうございました!!

kankyoukanshiimg134.JPG


おまけ


スプレッドシートセル上限対応(暫定)

500万セル制限により、いつかはスプレッドシートの上限値到達によりダッシュボードが停止してしまいます。

IMPORTRANGE関数を利用する方法もありますが、半永久的(サービス変更がなければ)にオートメーション化したいので、上限前に指定ファルダにデータをコピーして、データをクリアする方法を作ってみました。

っていっても、不要なセルを全て削除した状態かつ、現在のルールでは100年以上上限値になることは無いのですが、とりあえず100年後にバックアップとクリアを繰り返すことにしました。

※logシートF列からZ列削除とsetシートについても同じように不要な行列削除状態。


何列まで稼働するのか?

setシート 12行×4列=48セル

上限5,000,000セル-48セル=4,999,952セル

logシート 5行なので

4,999,952÷5行=999,990.4列

≠999,990列までは稼働可能


100年設定

365日×75年+366×25年=36,252日 36,252日×24時間+1行=876,601列

まず、Googleドライブでバックアップ用のフォルダを作成します。

そして、GoogleスプレッドシートのGASを次のとおり変更します。

(フォルダIDを出力フォルダを指定するコードのID部分に入力)

var access_token = '***************'//←トークンを入れる

var spreadsheetId = '*****************'//←スプレッドシートのIDを入れる
function remo() {
var data = getNatureRemoData();    //data取得
var lastData = getLastData();     //最終date取得
setLaremoData(
{
te:data[0].newest_events.te.val,  //温度
hu:data[0].newest_events.hu.val,  //湿度
il:data[0].newest_events.il.val,  //照度
},
lastData.row + 1//最終data追加作業
);
}

function getNatureRemoData() {      //Remoのapiをお借りします
var url = "https://api.nature.global/1/devices";
var headers = {
"Content-Type" : "application/json;",
'Authorization': 'Bearer ' + access_token,
};

var postData = {

};

var options = {
"method" : "get",
"headers" : headers,
};

var data = JSON.parse(UrlFetchApp.fetch(url, options));
Logger.log(data[0].newest_events)
Logger.log(data[0].newest_events.te.val)
Logger.log(data[0].newest_events.hu.val)
Logger.log(data[0].newest_events.il.val)

return data;

}

function getLastData() {
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('log') //20190815追加
var datas = sheet.getDataRange().getValues() //20190815変更
var data = datas[datas.length - 1]

return {
totalpoint:data[1],
coupon:data[2],
row:datas.length,
}
}

function setLatestData(data, row) {
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('log') //20190815追加
sheet.getRange(row, 1).setValue(new Date())//20190815変更
sheet.getRange(row, 2).setValue(data.te)//20190815変更
sheet.getRange(row, 3).setValue(data.hu)//20190815変更
sheet.getRange(row, 4).setValue(data.il)//20190815変更

//20190815追加 スプレッドシートを指定フォルダにコピーしてデータ削除 (スプレッドシートセル上限対応)
 if (LastRow = 2) {
//E2に=ARRAYFORMULA(TEXT(A2:A,"YYYYMMDDHH"))挿入
var range = sheet.getRange("E2").setValue('=ARRAYFORMULA(TEXT(A2:A,"YYYYMMDDHH"))');

} else {
Logger.log('そのまま');
}

// A列の値を配列で取得
const columnBVals = sheet.getRange('A:A').getValues();
//空白を除き、配列の数を取得
const LastRow = columnBVals.filter(String).length;
Logger.log(LastRow);

// 行数が指定した数値を上回れば処理実行 365日×75年+366×25年=36252日 36252日×24時間+1行=876601行
if (LastRow > 876601) {
Logger.log('処理実行');
// コピー元ファイル
var templateFile = DriveApp.getFileById(spreadsheetId);
// 出力フォルダ
var OutputFolder = DriveApp.getFolderById('*****************');//←バックアップフォルダのIDを入れる

// 出力ファイル名
var OutputFileName = templateFile.getName().replace('_template', '')+'_'+Utilities.formatDate(new Date(), 'JST', 'yyyyMM')
templateFile.makeCopy(OutputFileName, OutputFolder);
sheet.deleteRows(2,876601);
} else {
Logger.log('そのまま');
}

}

これで、100年間リビングの環境データを蓄積し、その後データをバックアップしてリセットします。

Googleの99%以上の可用性を誇るクラウドが100年後もあることを願って仕組みを作ってみましたが、リビングが100年以上ある可能性が極めて低いことに気づいてしまいました。というオチでした。


参考