🌏

IoT その5（温湿度センサーのデータをESP32のWi-Fi経由でGoogleスプレッドシートへ送信して記録する）

2023/08/16に公開

IoT

こんにちは、Ideagearの鈴木陽介です。

今回は、温湿度センサー（DHT11）の値をESP32のWi-Fi経由でGoogleスプレッドシートへ送信し、記録する方法をご紹介します。

はじめに

結論から言えば、Googleスプレッドシートの設定さえちゃんとできれば、リアルタイムでデータを取得するためのソースコードにやや苦戦しましたが、全体的には思ったよりはカンタンでした。

ただ、詳細は後述（トラブルシューティング）しますが、主に2つの点でハマって（内一つは私が居る環境に大きく依存しますが）しまいあきらめかけましたのでw、そのあたりも本記事にてシェアしたいと思います。

参考記事

ESP32からGoogleスプレッドシートにデータを送信する！
https://carrotfighter.com/?p=60

ESP32で百葉箱IoT

Googleスプレッドシートの設定

では、さっそく今回のメインディッシュです。
やや長いですが、やることは単純です。

まずは、Googleスプレッドシートへアクセスします。
https://docs.google.com/spreadsheets

Googleスプレッドシートの作成

左上の「新しいスプレッドシートを作成」の空白の＋をクリックします。

無題のスプレッドシートが作成されました。
このURLは後で使うため、どこかにメモしておきましょう。

スプレッドシートに名前をつけましょう。

・ファイル -> 名前を変更
・タイトルの部分を直接クリック

どちらの方法でも変更できます。

このように変更できました。

ウェブアプリの作成

次はウェブアプリをつくります。

拡張機能 -> Apps Scriptをクリック。

新しいプロジェクトが作成されました。

プロジェクト名を変更しましょう。
プロジェクト名のところを直接クリックしてください。

すると、「プロジェクトの名前を変更」というポップアップが表示されます。

任意の名前に変更してください。

このように変更できました。

コード

同時に、上記の通り、コードのところに下記ソースコードをコピペしてください。

function doGet(e) {
  const url = "https://docs.google.com/spreadsheets/d/xxxx/edit#gid=0";
  const ss = SpreadsheetApp.openByUrl(url);
  const sheet = ss.getSheets()[0];
  const params = {
    "timestamp": new Date(),
    "temperature": e.parameter.temperature,
    "humidity": e.parameter.humidity
  };
  sheet.appendRow(Object.values(params));
  return ContentService.createTextOutput('sccess');
}

但し、const url = の""の中の部分はそのまま使わず、先ほど作成したGoogleスプレッドシートのURLをコピペしてください。

デプロイの作成

次は、デプロイを新規作成します。

デプロイとは？

デプロイは、Google Apps Script（GAS）で作成したアプリやツールなどを外部に公開するためのURLを生成することです。

要は、他のIoTプラットフォームで言うところのトークンを生成するようなイメージですね。

デプロイ -> 新しいデプロイをクリックします。

設定ボタン -> ウェブアプリをクリック。

新しいデプロイの設定画面が出てきました。

説明文を書き入れましょう。

右下のデプロイをクリックすると更新がはじまります。

アクセスを承認します。

どのユーザーでアクセスするか確認されます。

危ないアプリだと言ってきました。

いやいや、あんたんとこのアプリやろ？と、ツッコミたくなりますがw、
Advancedをクリックして展開します。

左下の「Go to DHT11_log（先ほど自分でつけた名前）(unsafe)」
をクリックして先に進みます。

しつこく、大丈夫か？と、聞いてきます。
そろそろイライラしてきますがw、Allowをクリックします。

デプロイが更新されました。

ここの「ウェブアプリのURL」が非常に重要で、他のIoTプラットフォームで言うところのトークンにあたります。これをESP32のソースコードに書き込まないといけないため、どこかにメモしておいてください。

ウェブアプリの確認

ウェブアプリができたので、まずはこれが単独でちゃんと機能するかを確認します。

ブラウザの空のページのURL欄に、先程のウェブアプリのURLをコピペしてクリックし、アクセスしてみてください。

すると、下記の通り「sccess」とだけ表示されます。

スプレッドシートを見ると、A1のセルに時刻だけが表示されます。

このウェブアプリに何度もアクセスすると、A2、A3、A4...と、下のセルに時刻だけが延々と表示されます。

ともかく、これでウェブアプリが機能していることを確認できました。

Googleスプレッドシートへのデータ反映の確認

スプレッドシートの一行目には、わかりやすくするために時刻、気温（℃）、湿度（%）を入れましょう。また、先ほどのウェブアプリのURLの後ろに、

?temperature=25&humidity=60

を入れて、もう一度空のウェブページに貼り付けてアクセスしてみてください。

すると、時刻と共に気温25℃と湿度60%が固定で入力されます。

どういうことかと言うと、ウェブアプリのURLの最後に「?temperature=25&humidity=60」を追加することで、ESP32からGoogleスプレッドシートにセンサーデータが送られて来た状態を再現しているわけです。つまり、

?temperature=25&humidity=60

の25と60の数字の部分に、センサーのリアルタイム値が入るようにESP32のソースコードをつくればいいわけです。

Googleスプレッドシートとウェブアプリ側での確認は以上です。

ESP32側

続いて、ESP32側の配線、Arduino IDEで使うライブラリーとソースコードを見ていきましょう。

配線

ESP32とDHT11間の配線は、下記以前の記事をご覧ください。

ライブラリー

ライブラリーも上記の過去記事を見ればわかると思いますが、一応お伝えしておきますと、

HTTPClient.h：ArduinoHttpClient by Arduino
DHT.h：DHT sensor library by Adafruit

をそれぞれArduino IDEのスケッチ->ライブラリをインクルード->ライブラリを管理->ライブラリマネージャからインクルードしてください。

ソースコード

ソースコードは参考記事のままだと細かなミスがあって動かなかったり、そもそもリアルタイムで記録するようになっていなかったため、それらを参考にしつつも新たにつくりました。

#include <WiFi.h>
#include <HTTPClient.h>
#include "DHT.h"

//Wi-Fi
const char* ssid     = "xxxx"; //Your SSID
const char* password = "xxxx"; //Your Password

//Google Spreadsheet
const String url = "https://script.google.com/macros/s/xxxx/exec";

// DHT
#define DHTPIN 2
#define DHTTYPE DHT11

// Initialize DHT sensor.
DHT dht(DHTPIN, DHTTYPE);

float humidity = 0;
float temperature = 0;

void setup() { 
  Serial.begin(115200);
  delay(1000);

// Setup DHT sensor.
  dht.begin();
  delay(10);
  
  WiFi.mode(WIFI_STA);
  WiFi.disconnect();
  if (WiFi.begin(ssid, password) != WL_DISCONNECTED) {
    ESP.restart();
  }
  
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.print(".");
  }
  Serial.println("Connected to the WiFi network!");
}

void loop() {

//DHT11
  Serial.println("Collecting temperature data.");

  // Reading temperature or humidity takes about 250 milliseconds!
  humidity = dht.readHumidity();
  // Read temperature as Celsius (the default)
  temperature = dht.readTemperature();

  // Check if any reads failed and exit early (to try again).
  if (isnan(humidity) || isnan(temperature)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

  Serial.println("Sending data to Google SpreadSheet:");
  Serial.print("Humidity: ");
  Serial.print(humidity);
  Serial.print(" %\t");
  Serial.print("Temperature: ");
  Serial.print(temperature);
  Serial.println(" *C ");

//Google Spreadsheet
  String urlFinal = url + "?temperature=" + String(temperature) + "&humidity=" + String(humidity);
  Serial.println(urlFinal);

  HTTPClient http;
  http.begin(urlFinal.c_str());
  http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);
    int httpCode = http.GET(); 
    Serial.print("HTTP Status Code: ");
    Serial.println(httpCode);
    //---------------------------------------------------------------------
    //getting response from google sheet
    String payload;
    if (httpCode > 0) {
        payload = http.getString();
        Serial.println("Payload: "+payload);    
    }
    //---------------------------------------------------------------------
    http.end();
 
  delay(2000);
     
}

ただ、上記スケッチの中で、下記の3ヶ所だけはご自身の環境に変更してください。

//Wi-Fi
const char* ssid     = "xxxx"; //Your SSID
const char* password = "xxxx"; //Your Password

//Google Spreadsheet
const String url = "https://script.google.com/macros/s/xxxx/exec";

つまり、SSIDとパスワードの「xxxx」には、今いるWi-Fi環境のSSIDとパスワードを入力。

const String urlに代入する値は、先程メモしたGoogleスプレッドシートのウェブアプリのURLに書き換え（https～execまでを丸ごと置き換える）てください。
※Googleスプレッドシート自体のURLではありませんのでご注意ください。

動作確認

Googleスプレッドシートの設定、ソースコードに記載したWi-FiのSSIDとパスワード、ウェブアプリのURLが間違っていなければ、ESP32が起動してしばらくするとスプレッドシートで温度と湿度が表示されるはずです。

Arduino IDEのシリアルモニタでもWi-FiやGoogleスプレッドシートへの接続確認、検知した温度と湿度が表示されますので、併せてご確認ください。

トラブル発生

ただ、私の場合は悲しいことに、先程のソースコードを実行しただけではGoogleスプレッドシートに接続されてもデータが表示されませんでした。。。

センサーデータの確認

分析した結果、Googleスプレッドシートにデータが反映されませんが、シリアルモニタの内容からするとWi-Fiにはアクセスできているっぽいです。

そこで、シリアルモニタでIPアドレスが確認できますので、それをブラウザに直接打ち込んだところ、上記の通り、温度と湿度はちゃんと更新されます。

つまり、そこから先のGoogleスプレッドシートかウェブアプリの設定に問題がありそうなことがわかりました。

ウェブアプリの設定を再確認

こんな簡単な設定を間違えるとは思えませんが、ウェブアプリの設定をもう一度見直してみます。

デプロイ-> デプロイを管理をクリック。

編集マークをクリックします。

問題を発見しました！

アクセスできるユーザーが「自分のみ」になっています。
これだとESP32からアクセスできませんね。

全員へ変更します。
（私の場合は、実際はこれに加えてWi-Fi環境も変更しましたが、日本に居る場合は無視していい部分ですのでここでは省きます。詳細は後述のトラブルシューティングをご覧ください。）

気を取り直して再確認します。

ただ、問題を切り分けるため、というより、この時点では本記事に記載のソースコードが未完成だったため、まずは参考記事のソースコードを拝借して確認しました。

結果は上記の通りで、B列に25が、C列に60が延々と表示されています。

これは、参考記事のソースコードがセンサーのリアルタイム値を反映させておらず、温度25度、湿度60%の固定の値を送信するつくりになっているからですが、これでESP32からGoogleスプレッドシートへのデータ送信は問題ないことを確認できました。

残りは、センサーのリアルタイム値を送るようにするだけ。
ここまでくればあと少しです。

改めて、センサーの値をリアルタイムで取得できるソースコード（本記事のソースコードに記載のもの）をつくり、無事にArduino IDEのシリアルモニタとGoogleスプレッドシートで同じデータが表示されることを確認しました。

めでたしめでたしw

まぁ技術系のトラブルなんて、解決してみればしょーもないと言いますか、情けないものが大半ですね。

動画も併せてご覧ください。

なお、ESP32を起動して実行したもののWi-Fiの接続でトラブっている場合は、Arduino IDEのシリアルモニタで「・」が延々と表示されます。また、ESP32の青ランプも点滅しません。

この場合は、ESP32のENボタンを押して一度リセットすれば再接続をトライします。
ウェブアプリの各種設定およびソースコードの記載に誤りがなければ、何回かリセットすればつながるはずです。

トラブルシューティング

最後に、冒頭で述べた私がハマった2ヶ所について暴露及び回収しますw

Googleスプレッドシートにアクセスできるのが自分だけになっていた。

Googleスプレッドシート ->拡張機能 ->Apps Script -> デプロイ -> デプロイを管理 -> アクセスできるユーザーのところが、「自分のみ」になっていたのを「全員」へ変更しました。

今回は単なるテストであって他の人に使わせるつもりはないため、セキュリティの意味も兼ねてアクセスできるのは自分だけでいいやと思っていました。

が、よくよく考えたら、その「自分」というのは、「そのGoogleアカウントでログインしている端末」を指していることに気づきました。そりゃESP32からのアクセスは受け付けんわw
両方とも自分自身が操作しているので、意外と盲点でした。

ただ、私の場合はもう一つ大きな壁がありました。それが次です。

Wi-Fi環境を香港SIMからのテザリングへ変更

これは日本に居たらまったく関係ない話ですが、当方は中国の深センという街（香港の隣街）に住んでいるために発生したトラブルです。

ご存じの方も多いと思いますが、中国の大陸側ではGoogleをはじめとする海外のインターネットサービスの利用が制限されています。いわゆるグレート・ファイヤー・ウォール（略して、GFW）と呼ばれるものですが、通常はVPNを使って回避します。

ただ、パソコンやスマホはVPNのアプリをインストールして、それをONすれば、この「壁」を回避することができますが、今回はそういうわけにはいきません。ただ、こういう時のために契約している伝家の宝刀「香港SIM」を使ったところ、無事にGoogleスプレッドシートへアクセスできました！

あとは、ソースコードが参考記事のままだと記述が間違っていたり、使えても決め打ちの固定値しか送信できなかったりと微妙でしたので、温湿度センサーのリアルタイム値が表示できるようアジャアジャ作り直すのに数時間かかりました。

まとめ

いかがでしたでしょうか？

やってみた実感としては、GoogleスプレッドシートもIoTプラットフォームの一つだということです。

つまり、Googleスプレッドシート側の設定さえちゃんとしていれば、あとはWi-FiのSSIDとパスワードが間違っていなければWi-Fiは使えますし、ウェブアプリのURLはIoTプラットフォームのトークンと何ら変わりません。

ソースコードはもちろんそれ専用の部分が必要ですが、Googleスプレッドシートはそういう意味ではかなりメジャーなプラットフォームであるため、必要な情報はネット上にたくさん転がっています。

ということで、トラブルシューティングにも書きましたが、自分の設定ミスと中国在住という二重の（日本在住者にとってはナゾのw）トラブルで勝手に苦しみましたが、結果だけ見れば、Googleスプレッドシートは比較的扱いやすいIoTプラットフォームだと思いました。

ただ、ESP32からGoogleスプレッドシートへの送信はネット上の文献が豊富ですが、その逆は極めて少ないです。

実は、今回の本丸は、Googleスプレッドシートで入力した値をESP32へ送信し、変数や不揮発性メモリに保存されている値を上書きしたい。というものでした。

他方で、ESP32とGoogleスプレッドシートを連結させたこともないのにいきなりそこに取り組むのは無謀だと感じたため、まずは難易度が低い方からやってみて、Googleスプレッドシートを使ったIoTとはどういうものかを体験してみました。

ですので、次回はGoogleスプレッドシートからESP32への送信にトライしたいと思います。

それではまた！