2018年11月10日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps ScriptとNetatmoで職場環境改善【GAS】

頭脳労働する職場環境では、夏は暑さ、冬は寒さといった具合にそのパフォーマンスに大きな影響を及ぼす外的要因がゴロゴロしています。そういった外的要因、感覚ではなく、数値化・視覚化すると何をどう改善すべきか見えてきます。また、この中でとりわけ二酸化炭素濃度については、法令でも1000ppm以下と定められており、これを超えてくると、眠気や思考能力の低下を招きます。

温度、湿度、気圧、騒音、二酸化炭素濃度を計測できるのが今回使用するNetatmoと呼ばれる装置。親機１機に最大４機(室外、室内含む)の子機をぶら下げる事が出来、データはクラウドに蓄積。アプリでいつでもどこでも確認でき、APIでデータを呼び出す事も出来ます。今回このデータを呼出しスプレッドシートに書き込むまでを行ってみたいと思います。

※ちなみにNetatmoは１セットになっていますが、同梱されているのは室外用の子機なので、室内用の子機は別途購入が必要。最大3機まで接続可能です。

1 今回使用する資料・ファイル
2 事前準備
3 ソースコード
- 3.1 GAS側ソースコード
4 認証の実行と結果
5 関連リンク

今回使用する資料・ファイル

事前準備

ライブラリの追加

以下の手順でOAuth2 for Apps Scriptライブラリを追加しましょう。

スクリプトエディタを開きます。
メニューより「リソース」⇒「ライブラリ」を開きます。
ライブラリを追加欄に「1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF」を追加します。
現時点ではバージョンは30が最新ですので、それを選択しておきます。
保存ボタンを押して完了

これで、OAuth2.0認証にまつわる様々な関数を手軽に利用できるようになります。

図：OAuth2ライブラリを追加している様子

リダイレクトURLを取得する

リダイレクトURLとは、認証を完了しAccess Tokenを取得したら戻るべきURLを指定するものです。これは、スクリプトIDをもとに作られているので、スクリプトIDを取得して組み立てます。

スクリプトエディタのメニューより「ファイル」⇒「プロジェクトのプロパティ」を開く
情報の中にある「スクリプトID」を控えておく。
https://script.google.com/macros/d/スクリプトID/usercallback として組み立てる。これがコールバックURLとなる。

図：スクリプトIDはファイル毎に異なるのです。

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("ログアウトしました。")
}

//認証用各種変数

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");
  }
}

//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");
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

//指定日時の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("");

break;

case 2:

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秒間入れています。