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

はじめに

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

用意するもの

  • ESP32 Wi-Fi Kit2
  • 以下のいずれかのセンサを使用
  • 単4ニッケル⽔素電池x3本
  • 開発用パソコン
  • Wi-Fiルータ

デモ動画

Google Apps Scriptの設定

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

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

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

    • 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のデプロイ2の手順を説明します。

  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ソースコード

ソースコードの編集

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

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

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

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

ソースコードの書き込み

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

実行結果

  1. スプレッドシートを開き、1行目にDatetime、UniqueID、Temperature、Humidity、Illumination5ののカラム名を記入します。
  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に従って発行します。6
  8. Dashboards→New(右側)をクリックし、New Dashboardを選択します。
  9. Add Visualizationをクリックすると、編集画面が表示されるので、以下の項目を入力します。詳しくは、こちらを参照して下さい。
    • spreadsheetId: こちらを参照し入力します。
    • Range:シート1!A:Bのような文字列で、範囲をしてします。
    • Cache time:デフォルトのキャッシュ時間は 5 分に設定されていますが、キャッシュ時間を 0 に設定すると、キャッシュがバイパスされます。
    • Time filter:時間フィルターをONにします。

前のページに戻る


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

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

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

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

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

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


最終更新 May 31, 2023