ESP32で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で、Googleスプレッドシートに保存したデータをグラフにできます。Windowsでの導入は公式のインストール手順を参照し、サーバーを起動して http://localhost:3000/ を開きます。初回はユーザー名・パスワードともに admin でログインし、パスワードを変更します。

1. Google Sheetsデータソースプラグインをインストールします。公式の設定手順に従い、ConnectionsでGoogle Sheetsを検索してデータソースを追加します。

2. シートの公開範囲に合わせて認証方法を選びます。公開シートにはAPI Key、非公開シートにはサービスアカウントのGoogle JWT Fileを使います。⁶ 設定後、Save & testで接続を確認します。

3. ダッシュボードに可視化パネルを追加し、Google Sheetsデータソースを選択します。クエリエディタの説明に従って、以下を設定します。

   • Spreadsheet ID：対象スプレッドシートのIDまたはURL。
   • Range：日時から照度までの列を含める場合はシート1!A:E。実際のシート名に合わせて変更します。
   • Cache Time：既定は5m（5分）。0sでキャッシュを無効にできます。
   • Use Time Filter：日時の列を使ってダッシュボードの期間で絞り込む場合に有効にします。

画面の項目名はGrafanaやプラグインのバージョンによって異なる場合があります。

Footnotes

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

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

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

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

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

6. GrafanaのAPI Key方式は公開されたスプレッドシート用です。非公開のシートはサービスアカウントの認証を使います。Google CloudでGoogle Sheets APIを有効にし、認証情報とシートへのアクセス権を設定してください。公式の認証設定を参照してください。 ↩