Google スプレッドシートにESP32からデータ登録する

概要

昔からやってみたかったのですが、未着手だったスプレッドシート連携を実験してみました。送信まで時間がかかるので省電力プロジェクトだと適していませんが、無料で気楽に利用できるのが良い点だと思います。

スプレッドシートの新規作成

まずは保存するスプレッドシートを作成します。

無題のスプレッドシートになっていますので、なにかわかりやすい名前に変更します。ただし、スプレッドシートの名前は確認用のため、どんな名前でもあとで識別できれば問題ありません。

今回は「GAS Test」という名前にしてみました。

Apps Scriptの追加

拡張機能からApps Scriptを選択します。これがGoogle Apps ScriptでGASと呼ばれる機能となります。

上記のように作成できました。また無題のプロジェクトとなっていますのでわかりやすいようにスプレッドシートと同じ名前に変更しておきます。

スクリプトの更新

エディタ部分にスクリプトを入力します。

function debug() {
  const e = {
    parameter: {
      d2: "d2_data",
      d1: 'd1_data',
      d3: "d3_data"
    }
  }
  doGet(e)
}

function doGet(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  let params = [new Date()];
  let parameter = Object.keys(e.parameter);
  parameter.sort();
  parameter.forEach((key) => params.push(e.parameter[key]));
  sheet.appendRow(params);
  console.log(e.parameter);
  console.log(params);
  
  return ContentService.createTextOutput('sccess');
}

今回は上記のスクリプトになります。

debug()

ブラウザ上から実行する場合のダミー関数です。通常は使いませんがこの画面からdoGet関数を呼び出すと引数が指定できないので、デバッグのために利用する関数となります。

この関数でdoGet関数の引数であるeを生成して呼び出すだけの関数となります。

doGet()

実際のページを呼び出された場合に実行される関数です。eに呼び出しの際に利用されたパラメータが保存されています。

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

スプレッドシートのURLを開いて、1つ目のシートを開いています。他のサンプルをみてみると名前を指定しているものが多かったですが、1シート目決め打ちのほうがよいと思います。

  let params = [new Date()];

1個目のカラムに実行した時間を入れておきます。

  let parameter = Object.keys(e.parameter);
  parameter.sort();
  parameter.forEach((key) => params.push(e.parameter[key]));

呼び出しで利用されたパラメーターを取り出して、キーの名前でソートしてからparamsに追加しています。あとで実際にどのように追加されるかのサンプルを実行してみたいと思います。

  sheet.appendRow(params);

最後の行にデータを追加します。A列に時間、B列以降にパラメーターのキー名順に保存されることになります。

  console.log(e.parameter);
  console.log(params);

呼び出しのパラメーターと、最終的に保存したパラメーターをログに保存しておきます。

  return ContentService.createTextOutput('sccess');

処理が成功したら「sccess」とだけ表示されます。

デバッグ

呼び出される関数がdebugになっていることを確認します。

実行ボタンを押すと初回だけは承認が必要と言われます。

上記のあとに承認などのボタンを押すと実行されるはずです。

実行ログが表示されました。今回はdebug関数を呼び出したのでd1, d2, d3の3つのパラメーターが送信されています。注意しないといけないのはパラメーターの順番は固定されていません。指定した順番ではなく、入れ替わった順番で保存されているのでsortして名前順に固定化しています。

他の人のサンプルをみるとパラメーター名を指定して、保存するデータを作成している場合が多いですがデータが増えてもGASの修正がないようなスクリプトにしています。

ブラウザ経由で確認するためにデプロイする

デプロイの中にある「新しいデプロイ」を選択します。

デプロイタイプを選択してくださいとあります。

ウェブアプリを選択します。

説明文は必須ではありませんし、あとで変更できるので無くても構いません。実行は権限がある自分にして、アクセスできるユーザーは全員にします。

デプロイできました。

https://script.google.com/macros/s/AKfycbyQ5CXXZcM-LUO-WST6WY8m1sXg0YI4fTodgB-0VGozHBMjNrflPQJAyGflvRZ-K4Y/exec

上記のURLがESP32で送信するときのエンドポイントになります。

https://script.google.com/macros/s/AKfycbyQ5CXXZcM-LUO-WST6WY8m1sXg0YI4fTodgB-0VGozHBMjNrflPQJAyGflvRZ-K4Y/exec?d1=d1_str&d2=d2_str&d3=d3_str&d4=d4_str

上記のようにGETでパラメーターを追加して呼び出します。

ブラウザで呼び出し

先程のURLを開いてみました。できれば別ブラウザやシークレットモードなどでGoogleにログインしていない状態で開くのが好ましいです。権限設定を失敗しているとログインをした状態でのみのアクセスになっておりESP32からは呼び出せなくなります。

スプレットシートを見てみると追加されています。1行目がエディタ上のデバッグで呼び出したもので、2行目が先程のブラウザ経由のものになります。

スクリプトの変更反映

さて、GASのスクリプトを修正しようとして編集しても、先程のURLで呼び出しても反映されません。GASはデプロイをしたときのコードが実行される仕様となっており、エディタ上スクリプトが即時反映されるわけではありません。

デプロイの管理を見てみます。

上記に過去にデプロイしたものが保存されています。また、デプロイごとにURLが変わりますので複数バージョンが同時に動かすことが可能です。そしてデプロイは消すことができず、アーカイブすることで利用停止にはできます。

初回は必ずデプロイしないといけないのですが、試行錯誤している途中のバージョンをデプロイするとこの一覧が汚れるので注意してください。

また新しいデプロイを選択すると常にURLが変わります。ESP32から呼び出す場合には変更されるとURLを変更して転送し直す必要がありかなり面倒です。呼び出し方が変更されていない場合には新しいデプロイではなく、デプロイを管理の画面から鉛筆マークをクリックして、バージョンのところを「新しいバージョン」にして更新することで現状のURLのまま、最新のスクリプトに更新することができます。

開発中の場合にはもっと便利な機能があり「デプロイのテスト」を利用します。

上記のようにURLがすぐにでてきます。

https://script.google.com/macros/s/AKfycbzGgjkP2xZdJLga9gJ43r-nxJS18GXeYufkmggLbfMV/dev

こちらのURLは最後がexecではなくdevのなっています。このURLは特殊でエディタで編集中の最新バージョンが常に動くURLになります。ただしスクリプトの所収者本人しかアクセスできないので注意しましょう。

ログインしていない場合や権限がない場合には実行できませんのでESP32からもこのURLは呼び出せません。

https://script.google.com/macros/s/AKfycbzGgjkP2xZdJLga9gJ43r-nxJS18GXeYufkmggLbfMV/dev?d1=dev&d2=Test

上記のパラメーターで呼び出してみます。

正常に追加されていますね。devである程度テストをして、大丈夫そうであればデプロイすることでスクリプトの修正を反映させることができます。とはいえ、devをつかなくてもdebug関数を利用して実行することで確認は取れると思います。

実行履歴の確認

上記URLを開くことで、GASの実行履歴が表示されます。

ここで実行結果とログの確認ができます。データは3件あるはずですが、ここのデータは2件しかありません。デプロイしたURLはログインしていない他のブラウザで実行したため、ここのログには表示されません。同じようにESP32からの実行結果もここには表示されませんのでご注意ください。

同じような機能として、左メニューに実行数があります。

こちらはログインしていないユーザーの結果も表示されます。ただしログが見えるのはログインしている状態の実行のみとなります。

ESP32での送信部分

#include <Arduino.h>
#include <WiFi.h>
#include <WiFiMulti.h>
#include <HTTPClient.h>

WiFiMulti wifiMulti;

RTC_DATA_ATTR int bootCount = 0;

void setup() {
  Serial.begin(115200);
  bootCount++;
  wifiMulti.addAP("SSID", "KEY");
  wifiMulti.addAP("SSID2", "KEY2");
}

void loop() {
  if ((wifiMulti.run() == WL_CONNECTED)) {
    HTTPClient http;

    String url = "https://script.google.com/macros/s/AKfycbyQ5CXXZcM-LUO-WST6WY8m1sXg0YI4fTodgB-0VGozHBMjNrflPQJAyGflvRZ-K4Y/exec";
    url += "?d1=" + String(bootCount);
    url += "&d2=" + String(random(100));
    url += "&d3=d3_str";
    url += "&d4=d4_str";
    url += "&d5=d5_str";

    http.begin(url);
    http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);
    Serial.println("GET Start");
    int httpCode = http.GET();
    Serial.println("GET End");

    if (httpCode > 0) {
      if (httpCode == HTTP_CODE_OK) {
        String payload = http.getString();
        Serial.println(payload);

        uint64_t time_ms = 20 * 1000;  // 20s
        time_ms -= millis();
        esp_sleep_enable_timer_wakeup(time_ms * 1000);
        esp_deep_sleep_start();
      }
    } else {
      Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());
    }

    http.end();
  }

  delay(1);
}

上記のコードでデータ登録可能でした。

RTC_DATA_ATTR int bootCount = 0;

上記でディープスリープした回数を保存しています。本来は必要ない機能になります。

  wifiMulti.addAP("SSID", "KEY");
  wifiMulti.addAP("SSID2", "KEY2");

wifiMultiを利用して、複数のSSIDを登録して一番強い電波のAPに接続する設定になります。通常は決め打ちで普通のwifiを使ってもよいと思います。

    String url = "https://script.google.com/macros/s/AKfycbyQ5CXXZcM-LUO-WST6WY8m1sXg0YI4fTodgB-0VGozHBMjNrflPQJAyGflvRZ-K4Y/exec";
    url += "?d1=" + String(bootCount);
    url += "&d2=" + String(random(100));
    url += "&d3=d3_str";
    url += "&d4=d4_str";
    url += "&d5=d5_str";

URLを作っているところです。最初はa、b、cとやっていたのですがc=とパラメーターを渡すとエラーになって使えません。なにか使ってはいけないパラメーター名があるようです。シートにはパラメーター名でソートされて保存されるので、ここで指定した順番ではないので注意してください。

    http.begin(url);
    http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);
    Serial.println("GET Start");
    int httpCode = http.GET();
    Serial.println("GET End");

送信している部分です。HTTPSなのですが今回証明書をセットしていないので、証明書はチェックしていません。ルート証明書をセットするのが本来は正しいですが、たまに更新が必要なのがちょっと面倒なので今回は未チェックです。

肝としてはHTTPC_STRICT_FOLLOW_REDIRECTSで、リダイレクトに対応する必要があります。script.google.comにアクセスしたあとにリダイレクトが入って、実際にサーバーに接続する必要があるのでこのフラグをセットしないとデータが登録されません。

    if (httpCode > 0) {
      if (httpCode == HTTP_CODE_OK) {
        String payload = http.getString();
        Serial.println(payload);

        uint64_t time_ms = 20 * 1000;  // 20s
        time_ms -= millis();
        esp_sleep_enable_timer_wakeup(time_ms * 1000);
        esp_deep_sleep_start();
      }
    } else {
      Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());
    }

成功したらディープスリープして指定秒数待機します。実際確認したところ接続と送信で10秒以上必要だったので30秒とか600秒とか長めの間隔で実行するのがおすすめです。接続と送信の時間はその時によってかなり変わりますので、millis()を使って起動経過時間を引いて次回までの待機時間を設定しています。ただし、この処理だと徐々に時間がズレていきますのでRTCなどを使ってタイマー動作をさせたほうが実行時間は安定すると思います。

上記のような感じでデータが追加されました。

まとめ

Google スプレッドシートにESP32からデータを追加するのは比較的かんたんでした。無料でデータ連携ができるのがいいところですね。ただし送信までの時間を考えるとHTTPSの処理などがあり、非常に重いです。バッテリー動作をするのにはちょっと適していない気がしますので、常時給電ができる場所で使うのであれば問題ないと思います。

インターバル1分間の実行回数分数時数日数
20秒3回33,333分556時間23日
30秒2回50,000分833時間35日
1分(60秒)1回100,000分1,667時間69日
2分(120秒)0.5回200,000分3,333時間139日
3分(180秒)0.3回300,000分5,000時間208日
5分(300秒)0.2回500,000分8,333時間347日
10分(600秒)0.1回1,000,000分16,667時間694日

ちなみにスプレッドシートは10万行ぐらいが限界なので20秒間隔だと23日ぐらいで限界になります。30秒間隔にすれば1シート1月分が保存できるのかな。

シートを自動で追加するコードとかがあれば使いやすくなるかもしれません。ただしセル数上限などがあるので無限に大きくはできない感じですね。

続編

上記にデータを月次や日次で保存する方法を検証してみました。

コメント