重要なお知らせ

2024年10月04日 弊社製品の無線通信機能に関するお詫びとお願い

T&D ラボ

スプレッドシートの複数のシートへデータを自動入力する方法

  • TR7シリーズ
  • RTR500Bシリーズ
  • TR4Aシリーズ
  • おんどとりease
  • おんどとり Web Storage
  • スプレッドシート

更新日2023.8.30 Wed
公開日2023.8.30 Wed

1.はじめに

今回は おんどとり Web Storage API (以降、公開API と表記) と Google Apps Script (以降、GAS と表記) を使って おんどとり Web Storage に登録した機器を指定し、スプレッドシートのそれぞれのシートにデータを自動入力する方法を紹介します。下記のサンプル画像は、シート1 に「RTR501B」、シート2 に「RTR503B」、シート3 に「RTR-576」のデータを自動入力した例です。

上記サンプルデータの詳細はこちらからご確認ください。

2.動作概要

公開API を GAS と連携させて、おんどとり Web Storage に登録した機器のシリアルナンバーを指定します。指定した機器のデータをシート毎に分けて自動入力します。

図1:公開APIの使用イメージ

3.事前に用意するもの

  • ・おんどとり Web Storage のアカウント
  • ・おんどとり Web Storage に対応した T&D の製品
  • ・Google アカウント

おんどとり Web Storage のアカウントは以下のリンクから作成してください。
https://ondotori.webstorage.jp/

4.公開 API の発行について

おんどとり Web Storage にログインし、メニューから [アカウント管理] - [開発者向けAPI管理] の順に選択してください。

開発者向け API 管理画面から下記の手順で API KEY を発行します。

1. [API KEY を発行する] を選択
2. [API KEY] を発行しましたと表示
3. [アカウント管理へ戻る] を選択
4. [開発者向けAPI管理] を選択
5. 利用可能なAPI KEY:xxxxxxxxxxxxxxxxxxxxxx が表示

API KEY は 後で GAS を記述する際に使用します。

5.スプレッドシートの準備

事前に用意した Google アカウントからスプレッドシートを開きます。今回は おんどとり Web Storage にアップロードされた RTR501B・RTR503B・RTR-576 のデータをスプレッドシートのそれぞれのシートへ自動入力する例を紹介します。まずはスプレッドシート内画面左下の「+」(シートを追加) アイコンを選択して 3枚分のシートを追加しましょう。シート名は機器の型番等といった判別しやすい名称を入力するとよいでしょう。本記事では例として機器の型番を入力しています。ここで入力したシートの名称はこの後の手順「6.GAS の入力」で使用します。

下記の参考画像のように「シート1」に「RTR501B」のデータを自動入力する場合、チャンネル1は「温度」となりますので、それぞれ以下のように項目名を入力します。

  • ・「A」列には測定日時を入力していくので「A1」のセルに「日時」と入力
  • ・「B」列には機器名称を入力していくので「B1」のセルに「機器名称」と入力
  • ・「C」列にはチャンネル1のデータを入力していくので「C1」のセルに「温度」と入力

「シート2」に RTR503B のデータを自動入力する場合、チャンネル1は「温度」、チャンネル2は「湿度」となりますので、それぞれ以下のように項目名を入力します。

  • ・「A」列には測定日時を入力していくので「A1」のセルに「日時」と入力
  • ・「B」列には機器名称を入力していくので「B1」のセルに「機器名称」と入力
  • ・「C」列にはチャンネル1のデータを入力していくので「C1」のセルに「温度」と入力
  • ・「D」列にはチャンネル2のデータを入力していくので「D1」のセルに「湿度」と入力

「シート3」にRTR-576 のデータを自動入力する場合、「CO2」「温度」「湿度」と 3つの測定項目があるので、それぞれ以下のように入力します。

  • ・「A」列には測定日時を入力していくので「A1」のセルに「日時」と入力
  • ・「B」列には機器名称を入力していくので「B1」のセルに「機器名称」と入力
  • ・「C」列にはチャンネル1のデータを入力していくので「C1」のセルに「CO2」と入力
  • ・「D」列にはチャンネル2のデータを入力していくので「D1」のセルに「温度」と入力
  • ・「E」列にはチャンネル3のデータを入力していくので「E1」のセルに「湿度」と入力

上記の項目名は例として挙げていますので、わかりやすい名称を入力していただいても構いません。

項目名の入力が完了したらスプレッドシートのメニューにある「拡張機能」の「Apps Script」を選択してコード入力画面を表示します。
この画面に次の章の GAS を入力していきます。

6.GAS の入力

以下のソースコードを基に GAS を入力します。
API KEY、おんどとり Web Storage アカウントID・パスワード、機器のシリアル番号の項目をご自身の情報に置き換えて入力してください。

リスト1

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
function myFunction() {
  //===== 書き込み先スプレッドシートの用意(このGASを埋め込んだシートで動作します) =====
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_501 = ss.getSheetByName('RTR501B');    //入力したシート名
  var sheet_503 = ss.getSheetByName('RTR503B');    //入力したシート名
  var sheet_576 = ss.getSheetByName('RTR-576');    //入力したシート名

  //===== T&D Web Storage Web APIのアクセス先 =====
  var api_url    = "https://api.webstorage.jp/v1/devices/current"; //現在値取得のAPIです。

  //===== T&D Web Storage アカウント(ご自身のアカウント情報・機器情報をxxxxxxの部分に入力) =====
  var api_key    = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";  //おんどとり Web Storageで発行された APIKEY
  var login_id   = "xxxxxxxx";        //おんどとり Web Storage のアカウントID
  var login_pass = "xxxxxxxx";        //おんどとり Web Storage のパスワード

  //===== 設定する HTTPヘッダフィールド =====
  var headers = {
    "X-HTTP-Method-Override": "GET",
    "Content-Type": "application/json"
  };

  //===== POSTで送信するデータを配列で一時作成 =====
  var payload = {
    "api-key": api_key,
    "login-id": login_id,
    "login-pass": login_pass,
  };

  //===== 送信するデータを配列で作成 =====
  var options = {
    "method": "post",
    "headers": headers,
    "payload": JSON.stringify(payload) // 用意したデータ配列をJSON文字列に変換
  };

  var response = UrlFetchApp.fetch(api_url, options);
  var data = JSON.parse(response.getContentText());
  if(response.getResponseCode() == 200){    //正常にデータを受信出来ていた場合に、データ処理する
    var devices = data.devices; //受信データから機器の情報を抽出
    var sheet = "";
    for (var i = 0; i < devices.length; i++) {   //受信データに含まれる機器の台数分ループ。
      var device = devices[i]; // 機器1台分を抽出
      if(device.serial == "xxxxxxxx"){    //RTR501B のシリアルナンバー
        sheet = sheet_501;
        var lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()+1;
        sheet.getRange(lastRow, 1).setValue(Utilities.formatDate(new Date( unixtime * 1000 ), "JST", "yyyy/MM/dd HH:mm:ss")); //unixtimeを日本時間に変更
        var unixtime = device.unixtime; // 機器(子機)の最新測定値の時刻(unixtime)

        // スプレッドシートに書き込むデータ配列を作成
        sheet.getRange(lastRow, 1).setValue(Utilities.formatDate(new Date( unixtime * 1000 ), "JST", "yyyy/MM/dd HH:mm:ss")); //unixtimeを日本時間に変更
        sheet.getRange(lastRow, 2).setValue(device.name);
        for (var i2 = 0; i2 < device.channel.length; i2++) {
          if(device.channel[i2].num == 1){
            //チャンネルが1の場合
            sheet.getRange(lastRow, 3).setValue(device.channel[i2].value);
          }
        }

      } else if(device.serial == "xxxxxxxx"){    //RTR503B のシリアルナンバー
        sheet = sheet_503;
        var lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()+1;
        var unixtime = device.unixtime; // 機器(子機)の最新測定値の時刻(unixtime)

        // スプレッドシートに書き込むデータ配列を作成
        sheet.getRange(lastRow, 1).setValue(Utilities.formatDate(new Date( unixtime * 1000 ), "JST", "yyyy/MM/dd HH:mm:ss")); //unixtimeを日本時間に変更
        sheet.getRange(lastRow, 2).setValue(device.name);
        for (var i2 = 0; i2 < device.channel.length; i2++) {
          if(device.channel[i2].num == 1){
            //チャンネルが1の場合
            sheet.getRange(lastRow, 3).setValue(device.channel[i2].value);
          } else if(device.channel[i2].num == 2){
            //チャンネルが2の場合
            sheet.getRange(lastRow, 4).setValue(device.channel[i2].value);
          }
        }

      } else if(device.serial == "xxxxxxxx"){    //RTR-576 のシリアルナンバー
        sheet = sheet_576;
        var lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()+1;
        var unixtime = device.unixtime; // 機器(子機)の最新測定値の時刻(unixtime)

        // スプレッドシートに書き込むデータ配列を作成
        sheet.getRange(lastRow, 1).setValue(Utilities.formatDate(new Date( unixtime * 1000 ), "JST", "yyyy/MM/dd HH:mm:ss")); //unixtimeを日本時間に変更
        sheet.getRange(lastRow, 2).setValue(device.name);
        for (var i2 = 0; i2 < device.channel.length; i2++) {
          if(device.channel[i2].num == 1){
            //チャンネルが1の場合
            sheet.getRange(lastRow, 3).setValue(device.channel[i2].value);
          } else if(device.channel[i2].num == 2){
            //チャンネルが2の場合
            sheet.getRange(lastRow, 4).setValue(device.channel[i2].value);
          } else if(device.channel[i2].num == 3){
            //チャンネルが3の場合
            sheet.getRange(lastRow, 5).setValue(device.channel[i2].value);
          }
        }
      }
    }
  }
}
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
            

入力が完了したら「プロジェクトを保存」を選択します。

続いて画面左側の「トリガー」を選択し、トリガーの設定画面を表示します。

画面右下の「トリガーを追加」を選択します。

トリガーの設定を行い、最後に画面右下の「保存」を選択します。今回は RTR501B・RTR503B・RTR-576 の親機 (今回は RTR500BW) の現在値送信間隔に合わせて 「10分おき(※1)」に設定しました。

保存すると設定したトリガーの情報が反映されます。

スプレッドシートへ戻ると、データが反映(※2)されていきます。

(※1) API からのデータの取得にはレートリミットが設けられています。詳細は以下リンクの「現在値の取得」をご参照ください。 https://ondotori.webstorage.jp/docs/api/reference/devices_device.html

(※2) ワークブック内のセルは自動で追加されていきますが、最大セル数は 10,000,000 となっています。最大セル数に達する前に適宜データの保存・削除といった対応を取ってください。

7.カラースケールの設定について

サンプルのような色付けを行う場合、以下の手順で設定します。

1. 色を付ける列を選択
2. 「右クリック」して「条件付き書式」を選択
3. 画面右側の [条件付き書式設定ルール] から「カラースケール」を選択
4. 「最小値」「最大値」それぞれ任意の色を設定
5. 設定ができたら「完了」を選択

8.グラフの設定について

グラフ化したい列を選択して、[グラフを挿入] を選択します。適宜、縦軸や横軸の名称を設定します。
詳しい設定方法については Google 社のヘルプページをご参照ください。

9.データの共有について

スプレッドシートを共有する際、相手に GAS が見えないよう権限を設定する必要があります。データの共有に関する詳細は Google 社が公開しているこちらのヘルプページをご参照ください。

10.最後に

今回はスプレッドシートのそれぞれのシートへ指定した機器のデータを自動入力する方法をご紹介しました。自動入力させたい機器が多い場合、こちらのやり方を参考にしていただくと確認しやすくなるのではないかと思います。もしそれぞれのシートへデータを自動入力する必要がなければ、1枚のシートにまとめて自動入力する方法を「スプレッドシートにデータと室内の簡易的な WBGT を自動入力する方法」で紹介していますので、そちらをご参照ください。本記事を参考にしていただき、おんどとりのデータを活用していただければ幸いです。

免責

本記事の中で紹介したスクリプトは、全ての環境、条件において動作する保証をするものではありません。
また、記載されている情報の利用やスクリプトの実行等による万一の不利益に対して、弊社では責任を負いかねます。

プライバシー設定

プライバシー設定

当ウェブサイトでは、サイトの利便性やサービスの向上を目的に、cookieを使用しております。このまま当ウェブサイトをご利用になる場合、cookieの使用に同意いただいたものとみなされます。cookieに関する詳細や設定については「個人情報保護方針」をご覧ください。

保存する