Google Apps ScriptとNetatmoで職場環境改善【GAS】
頭脳労働する職場環境では、夏は暑さ、冬は寒さといった具合にそのパフォーマンスに大きな影響を及ぼす外的要因がゴロゴロしています。そういった外的要因、感覚ではなく、数値化・視覚化すると何をどう改善すべきか見えてきます。また、この中でとりわけ二酸化炭素濃度については、法令でも1000ppm以下と定められており、これを超えてくると、眠気や思考能力の低下を招きます。
温度、湿度、気圧、騒音、二酸化炭素濃度を計測できるのが今回使用するNetatmoと呼ばれる装置。親機１機に最大４機(室外、室内含む)の子機をぶら下げる事が出来、データはクラウドに蓄積。アプリでいつでもどこでも確認でき、APIでデータを呼び出す事も出来ます。今回このデータを呼出しスプレッドシートに書き込むまでを行ってみたいと思います。
※ちなみにNetatmoは１セットになっていますが、同梱されているのは室外用の子機なので、室内用の子機は別途購入が必要。最大3機まで接続可能です。
リンク
目次
今回使用する資料・ファイル
事前準備
ライブラリの追加
図：OAuth2ライブラリを追加している様子
リダイレクトURLを取得する
OAuth認証実行用情報のセットアップ
ここでの準備は、Netatmo自身のセットアップは完了済みとしてお話を進めます。APIを使ってデータを呼び出すので、Developer登録をしてゆく作業になります。すでにデータの可視化自体はウェザーステーションページからも確認が可能です。これぞ、Internet of Thingsの王道。
- Netatmo Developerに入ります。
- 右上のMy Accountからログインします。
- Create An Appをクリックして登録します。
- APIの名前をつけ、適当にメアドなども入力します。ロゴもつけられますよ。I accept...のチェックを入れて、SAVEボタンを押します。
- Technical Parameterのセクションでは、控えておいたリダイレクトURLを入れます。
- すでにこの段階で、Client IDとClient Secretが出来ているので、控えておきます。
- App StatusはActivate、そして、SAVEボタンを押します。
図；アプリ登録画面
ソースコード
GAS側ソースコード
OAuth認証用コード
//認証用各種変数
var tokenurl = "https://api.netatmo.com/oauth2/token";
var authurl = "https://api.netatmo.com/oauth2/authorize?";
var clientid = 'ここにクライアントIDを入力';
var clientsecret='ここにクライアントシークレットを入力';
var scope = "read_station";
function startoauth(){
//UIを取得する
var ui = SpreadsheetApp.getUi();
//認証済みかチェックする
var service = checkOAuth();
if (!service.hasAccess()) {
//認証画面を出力
var output = HtmlService.createHtmlOutputFromFile('template').setHeight(310).setWidth(500).setSandboxMode(HtmlService.SandboxMode.IFRAME);
ui.showModalDialog(output, 'OAuth2.0認証');
} else {
//認証済みなので終了する
ui.alert("すでに認証済みです。");
}
}
//アクセストークンURLを含んだHTMLを返す関数
function authpage(){
var service = checkOAuth();
var authorizationUrl = service.getAuthorizationUrl();
var html = "<center><b><a href='" + authorizationUrl + "' target='_blank' onclick='closeMe();'>アクセス承認</a></b></center>"
return html;
}
//認証チェック
function checkOAuth() {
return OAuth2.createService("Netatmo")
.setAuthorizationBaseUrl(authurl)
.setTokenUrl(tokenurl)
.setClientId(clientid)
.setClientSecret(clientsecret)
.setScope(scope)
.setCallbackFunction("authCallback") //認証を受けたら受け取る関数を指定する
.setPropertyStore(PropertiesService.getScriptProperties()) //スクリプトプロパティに保存する
.setParam("response_type", "code");
}
//認証コールバック
function authCallback(request) {
var service = checkOAuth();
var isAuthorized = service.handleCallback(request);
if (isAuthorized) {
return HtmlService.createHtmlOutput("認証に成功しました。ページを閉じてください。");
} else {
return HtmlService.createHtmlOutput("認証に失敗しました。");
}
}
//ログアウト
function reset() {
checkOAuth().reset();
SpreadsheetApp.getUi().alert("ログアウトしました。")
}
- Token取得用のエンドポイントは、https://api.netatmo.com/oauth2/tokenです。
- OAuth認証用URLは、https://api.netatmo.com/oauth2/authorize?です。
- 今回、スコープとしてはステーションのデータの読み取りのみを設定してるので、read_stationだけとなっています。
デバイスリストを返すコード
//Netatomoデバイスデータを取得して返す関数
function getNetatomolist() {
//スプレッドシートを取得する
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("devicelist");
//シートデータをクリアする
ss.getRange("A2:C").clearContent();
//ステーションデータを取得する
var service = checkOAuth();
if (service.hasAccess()) {
var url = stationdata;
var response = UrlFetchApp.fetch(url, {
headers: {
Authorization: "Bearer " + service.getAccessToken()
},
method: "GET",
contentType: "application/json"
});
//ユーザ名を取得する
var results = JSON.parse(response.getContentText());
//親機を取得する
var array = [];
array.push(results['body']['devices'][0]['module_name']);
array.push(results['body']['devices'][0]['_id']);
array.push(JSON.stringify(results['body']['devices'][0]['data_type']));
ss.appendRow(array);
//デバイスリスト（子機）を取得する
var devlength;
devlength = results['body']['devices'][0]['modules'].length
for(var i = 0;i<devlength;i++){
//配列初期化
array = [];
//データの取得
array.push(results['body']['devices'][0]['modules'][i]['module_name']);
array.push(results['body']['devices'][0]['modules'][i]['_id']);
array.push(JSON.stringify(results['body']['devices'][0]['modules'][i]['data_type']));
ss.appendRow(array);
}
//終了メッセージ
ui.alert("デバイスリストの取得が完了しました。");
}else{
//エラーを返す（認証が実行されていない場合）
ui.alert("error");
}
}
- Netatmoのデータ取得エンドポイントURLはhttps://api.netatmo.com/api/getstationsdataとなります。リファレンスはこちら。
- 通常の外部の気象データも取得が可能で、その場合のエンドポイントURLはhttps://api.netatmo.com/api/getpublicdataとなります。リファレンスはこちら。
- Indoorの値（親機）と子機の値の取得方法はちょっと違うので注意！！
- 今回は、デバイス名、デバイスID（あとで利用します）、取得できるデータタイプ（温度や二酸化炭素濃度など）を取得し、スプレッドシートに書き込みしています。
データの塊を取得してスプレッドシートに記述するコード
今回このデータの取得では、スクリプトトリガーを利用して、１時間に１度データを取得するようセットしてみました。APIで取得できるデータは最大1024ポイントです（scaleの指定がmaxの場合）。もちろん期間指定(UNIXタイムスタンプ)も可能です。
//指定日時のUNIXタイムスタンプを取得する
function now2unixtime(date){
var unixtime = Math.floor((date.getTime()/1000)).toString();
return unixtime;
}
//UNIXタイムスタンプを日時に変換する関数
function get_date(_timestamp){
var _d = _timestamp?new Date(_timestamp * 1000):new Date();
var Y = _d.getFullYear();
var m = ("0" + (_d.getMonth() + 1)).slice(-2);
var d = ("0" + _d.getDate()).slice(-2);
var H = ("0" + _d.getHours()).slice(-2);
var i = ("0" + _d.getMinutes()).slice(-2);
var s = ("0" + _d.getSeconds()).slice(-2);
return( Y + '-' + m + '-' + d + ' ' + H + ':' + i + ':' + s );
}
//現時点からみて一度に取得できるデータを全て取得する
function getWeatherMax(){
//スプレッドシートを取得する
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("netatmo");
//実行問い合わせ
var response = ui.alert('直近のデータを取得しますか？既存のデータは全て削除されます。', ui.ButtonSet.YES_NO_CANCEL);
switch(response){
case response.YES:
//作業を続行する
break;
case response.NO:
return 0;
break;
case response.CANCEL:
ui.alert("設定はキャンセルされました。");
return 0;
break;
}
//シートデータをクリアする
ss.getRange("A2:G").clearContent();
//デバイスリストを取得する
var devlist = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("devicelist").getRange("A2:C").getValues();
var devlength = devlist.length;
//ステーションデータを取得する
var service = checkOAuth();
if (service.hasAccess()) {
var url = netatmodata;
var access_token = service.getAccessToken();
var array = [];
//親機のデバイスIDを取得する
var parentid = devlist[0][1];
for(var i = 0;i<devlength;i++){
//devlistが空白ならば終了する
if(devlist[i][1] == ""){
break;
}
//devlistからデバイス値を取得する
var devnum = devlist[i][1];
var devname = devlist[i][0];
var tempdev = devlist[i][2];
var devtype = tempdev.replace( /\[/g , "" ) ;
devtype = devtype.replace( /\]/g , "" ) ;
devtype = devtype.replace( /\"/g , "" ) ;
//送信パラメータ
var params = {
'access_token': access_token,
'device_id': parentid, //70:ee:50:36:ff:88
'module_id': devnum,
'scale': '30min', //スケールを30分単位に設定
'date_begin' : now2unixtime(new Date("2018/11/08")),
'type': devtype //'Temperature,Humidity,CO2,Pressure,Noise'
};
//送信オプション
var options = {
'method': 'post',
'contentType': 'application/x-www-form-urlencoded;charset=UTF-8',
'payload': params
};
var response = UrlFetchApp.fetch(url, options).getContentText('UTF-8');
//一旦10秒スリープさせる(UrlFetch連続実行でエラーにならないようにするため)
Utilities.sleep(10000);
//レスポンスデータを取得する
var data = JSON.parse(response);
Logger.log(data);
var blength = data['body'].length;
//返り値を処理する
for(var j = 0;j<blength;j++){
//一時配列を用意
var tempArray = [];
//一時配列データに基本データを入れる
tempArray.push(devname); //デバイス名を入れる
tempArray.push(get_date(data["body"][j]["beg_time"])); //UNIXタイムを変換して入れる
//観測データを配列に入れる
//対応データの数を調べる
var dtcnt = data["body"][j]["value"][0].length;
for(var z = 0;z<dtcnt;z++){
tempArray.push(data["body"][j]["value"][0][z]);
}
//空の値をpushする
switch(dtcnt){
case 3:
tempArray.push("");
tempArray.push("");
break;
case 2:
tempArray.push("");
tempArray.push("");
tempArray.push("");
break;
}
//書き込み用配列にpushする
array.push(tempArray);
}
}
//配列データを書き込む
var lastColumn = array[0].length //列の数を求める
var lastRow = array.length; //行の数を取得する
ss.getRange(2,1,lastRow,7).setValues(array);
//終了メッセージ
ui.alert("観測データの取得が完了しました。");
}else{
//エラーを返す（認証が実行されていない場合）
ui.alert("error");
}
}
- Netatmoの観測データ取得エンドポイントURLはhttps://api.netatmo.com/api/getmeasureとなります。リファレンスはこちら。
- 取得できるデータタイプは、Temperature, CO2, Humidity, Noise, Pressureの５種類（親機）。子機はNoiseとPressureはありません。また室外用子機はTemperatureとHumidityのみです。
- 今回は30分単位、2018/11/08〜のデータとしてパラメータを構築しています。ただし、１リクエストに対して最大1024レコードまでしか取得できません。
- 日付はUNIXタイムで返ってきます。また日付の指定もUNIXタイムでなければなりません。其のための相互変換用関数を利用させていただいています。
- 複数のタイプの子機と親機が混在しているので、配列にて値を調整しています。
- UrlfetchAppは10秒間隔で発行しないと、エラーとなるため、途中でスリープを10秒間入れています。
認証の実行と結果
スプレッドシートのメニューより、「セッティング」⇒「OAuth認証実行」をクリックすることで、ログイン認証画面が出てきます。Netatomoのサイトに飛び、YESを押すことで、Access Token他が手に入るようになります。
図：OAuth2認証画面が出てきます。
スプレッドシートメニューより、「Netatmo実行」⇒「直近全データ取得」でデータを取得します。といっても、2018/11/08で指定しているので、そこから最大1024ポイント、30分単位のデータが書き込まれます。
図：毎日取得しておけば、全データが揃います。
