Google スプレッドシートでセンサデータの可視化

はじめに

ESP32 Wi-Fi Kit2に温湿度センサを取り付けるだけで、環境センサが出来上がります。この環境センサをインターネットに繋げば、スプレッドシートでセンサデータの可視化が簡単に出来ます。

用意するもの

• ESP32 Wi-Fi Kit2
• 以下のいずれかのセンサを使用
  • DHT22¹ (温度・湿度)
  • 4-Sensors(温度・湿度・照度)
• 単4ニッケル⽔素電池x3本
• 開発用パソコン
• Wi-Fiルータ
  

デモ動画

Google Apps Scriptの設定

Google Apps Scriptを使って、Leafonyにソースコードを書く時に必要なWebアプリの設定を行います。

1. sheets.google.com でスプレッドシートのホーム画面を開き、新しいスプレッドシートを作成をします。
   

2. スプレッドシートを開き拡張機能→Apps Scriptをクリックし、以下のソースコードを貼り付けます。
   spreadsheetIdとsheetNameは、以下を参照し入力します。
   

   • spreadsheetId: こちらを参照し入力します。
     
   • sheetName :スプレッドシートのシート名を入力します。デフォルトは、シート1になっています。
     

function doGet(e) {

    let id = 'spreadsheetId'; //Insert spreadsheetId
    let sheetName = 'sheetName'; //Insert sheetName
    var result;

    // e.parameter has received GET parameters, i.e. temperature, humidity, illumination
    if (e.parameter == undefined) {
        result = 'Parameter undefined';
    } else {
        var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
        var newRow = sheet.getLastRow() + 1;  // get row number to be inserted
        var rowData = [];

        // get current time
        rowData[0] = new Date();
        rowData[1] = e.parameter.UniqueID;
        rowData[2] = e.parameter.temperature;
        rowData[3] = e.parameter.humidity;
        rowData[4] = e.parameter.illumination;

        // 1 x rowData.length cells from (newRow, 1) cell are specified
        var newRange = sheet.getRange(newRow, 1, 1, rowData.length);

        // insert data to the target cells
        newRange.setValues([rowData]);
        result =  'Ok';
    }

    return ContentService.createTextOutput(result);
}

Google Apps Scriptのデプロイ

GASのデプロイ²の手順を説明します。

1. スプレッドシートを開き拡張機能→Apps Scriptをクリックします。
   

2. 右上のデプロイ→新しいデプロイをクリックします。
   

3. 新しいデプロイの画面が表示されたら、設定→ ウェブアプリ をクリックします。
   

4. 新しいデプロイの詳細設定画面が表示されたら、以下の設定をし、最後に右下のデプロイをクリックします。
   

   • 説明：特に何も入力せず空欄でOK
     
   • 次のユーザーとして実行：自分
     
   • アクセスできるユーザー：全員
     
     

5. データへのアクセスを許可するために、アクセスを承認をクリックして下さい。
   

6. デプロイが完了できたら、デプロイIDとウェブアプリURLが表示されます。
   

   • デプロイID：ソースコードを書く時に使用します。
     

   • ウェブアプリURLは、 Google Apps Scriptのテストに使用します。
     

7. デプロイIDとウェブアプリURLをコピーして、完了をクリックします。

Leafonyの組み立て

DHT22の場合

ESP32 Wi-Fi Kit2を箱から取出し、DHT22と29pin headerを以下の通りに結線します。

DHT22	29pin header
VCC-power supply	1
DAT-signal	15
GND	27

4-Sensorsの場合

ESP32 Wi-Fi Kit2のRTC & microSDを外して、4-Sensorsを取付けます。

ソースコード

開発環境の設定

Arduino IDEか、 PlatformIO IDE の開発環境を設定して下さい。

ソースコードのダウンロード

開発用パソコンに、ソースコードをダウンロードします。

IDE	DHT22	4-Sensors
Arduino IDE	Arduino_DHT22ソースコード	Arduino_4-Sensorsソースコード
PlatformIO IDE	PlatformIO_DHT22ソースコード	PlatformIO_4-Sensorsソースコード

• DHT22は、DHT-sensor-libraryを使用します。

ソースコードの編集

個人や家庭などのWi-Fiルータ(WPA2 Personal ³)は、ソースコードに以下の内容を記述して下さい。

• ssid:Wi-FiルータのSSIDを入力します。
  
• password：Wi-Fiルータのパスワードを入力します。
  
• google_scripts_key：Google Apps ScriptのデプロイIDを入力します。
  

大学や企業などのWi-Fiルータ(wpa2 Enterprise ⁴)は、ソースコードに以下の内容を記述して下さい。

• //#define ENTERPRISEのコメント(//)を外します。
  
• identity: 利用するユーザのIDを入力します。
• password：利用するユーザのパスワードを入力します。
  
• ssid:Wi-FiルータのSSIDを入力します。
  
• google_scripts_key：Google Apps ScriptのデプロイIDを入力します。
  

ソースコードの書き込み

ソースコードの編集が完了したら、パソコンと接続して、書き込みます。

実行結果

1. スプレッドシートを開き、1行目にDatetime、UniqueID、Temperature、Humidity、Illumination⁵ののカラム名を記入します。
   
2. ESP32 MCUのリセットボタンを押してプログラムを実行させると、センサの値がスプレッドシートに書き込まれます。
   
   

Tips

Google Apps Scriptのテスト

GASが正しく実行出来るかテストをします。

1. ウェブアプリURLの末尾に、?UniqueID=Leafony_A&temperature=10&humidity=20&illumination=30を加えてWebにアクセスします。
2. スプレッドシートに、Datetime、Leafony_A、10、20、30の値が書き込まれていれば、正しく実行しています。
   

Google Apps Scriptを編集する場合

1. スプレッドシートを開き、拡張機能→Apps Scriptをクリックします。
   
2. 右上のデプロイ→デプロイを管理をクリックします。
   
3. デプロイを管理が表示されたら、 編集のアイコンをクリックして新バージョンを選択し、デプロイをクリックします。
   

Grafanaでスプレッドシートをグラフ化

Grafanaは、データベースに保存されたデータに対して、リアルタイムにてデータを表示するマルチプラットフォームのWebアプリケーションです。ここでは、WindowsでGrafanaを起動し、Googleスプレッドシートに保存されているデータをグラフにします。

1. Grafanaのダウンロードページ よりWindows版のインストーラをダウンロードし、画面の指示に従ってインストールします。
   
2. C:\Program Files\GrafanaLabs\grafana\bin\grafana-server.exe を実行してブラウザを開き、以下のリンクにアクセスします。
   
   • http://localhost:3000/
3. 以下でログインして、初期パスワードを変更します。
   
   • Email or username：admin
     
   • Password：admin
     
4. 左上のToggle Menueをクリックし、サイドバーを表示させます。
   
5. Administration→Pluginsを開き、Google Sheetsを検索し、インストールして下さい。
   
6. Administration→Data sourcesを開き、Add data sourceをクリックし、Google Sheetsを Data Sourceに追加します。
   
7. Google Sheets Settigsの画面で以下の項目を入力し、Save & Testをクリックします。
   
   • Auth:API Keyを選択
     
   • API Key:API Keyは画面下のGenerate an API keyに従って発行します。⁶
8. Dashboards→New(右側)をクリックし、New Dashboardを選択します。
9. Add Visualizationをクリックすると、編集画面が表示されるので、以下の項目を入力します。詳しくは、こちらを参照して下さい。
   
   • spreadsheetId: こちらを参照し入力します。
     
   • Range:シート1!A:Bのような文字列で、範囲をしてします。
     
   • Cache time:デフォルトのキャッシュ時間は 5 分に設定されていますが、キャッシュ時間を 0 に設定すると、キャッシュがバイパスされます。
     
   • Time filter:時間フィルターをONにします。

Footnotes

1. ドキュメントとサンプルコードは こちらを参照して下さい。
   ↩

2. 一般的にデプロイとは、ソフトウェアを利用可能にする活動全般を指す用語で、Google Apps Scriptの場合は、ソフトウェアなどを実行できる状態にして、URLを生成することを意味しています。
   ↩

3. 個人や家庭、小規模事業所などためのセキュリティの仕様。端末毎に設定してあるパスワードにより認証を行う。
   ↩

4. 企業など比較的規模の大きいネットワークのためのセキュリティの仕様。利用者毎に登録したIDとパスワードにより認証を行う。
   ↩

5. 　Illuminationは、4-Sensorsの場合だけ表示されます。
   ↩

6. API Keyの設定は、こちらを参照して下さい。また、スプレッドシートを共有にしないと、Google Sheets APIが表示されません。適切に共有の設定をして下さい。
   ↩