ESP32でGoogle Spreadsheetを使う

データを収集するのにSpreadsheetは便利

以前M5Stackの服薬管理アプリでSpreadsheetは使っていますが、ESP32でSpreadsheetを使ってみました。
s51517765.hatenadiary.jp

そのまま移植しようとしたら上手くいかなくて、というのも中身がよくわかっていないところが多くコピペで乗り切っていただけなので、この機会にもう少し中身を理解してみようと思いました。

SpreadsheetにPOSTする

POSTについては以下を参考にしました。
qiita.com

これによると、POSTは以下のように簡単にできます。
ここではクエリでデータを追加しています。

String url= "https://script.google.com/macros/s/*********/exec"
String PostRequest(String url, float temperature, float humidity)
{
  HTTPClient http;
  String data = "?temperature=" + String(temperature) + "&humidity=" + String(humidity);
  http.begin(url + data); //データを投げるだけならクエリでこれだけで完了出来る
}

このクエリで付加されたデータをGASで処理します。
GASではアクセス権を設定しておきます。

クエリはeで取得できます。
これでシートに記入したり、計算したり自由に使えます。

function doGet(e) {
  if(e!=undefined){
    //データがあれば
    const params = {
    "timestamp": new Date(),
    "temperature": e.parameter.temperature,
    "humidity": e.parameter.humidity
    };
  sheet.getRange(2, 2).setValue(params.temperature);    //データをセルに記録
}
}

ここで、GASの動作確認はcurlを使うと便利です。
curlはWindowsでもコマンドプロンプトから実行でき、GASを動作させることが出来ます。
ESP32やM5stackはビルド・書き込みが時間がかかるのでdebug時にESP32から実行していては効率が悪いです。

今回のようにクエリがあるときは下記のようにurl全体をダブルクォーテーションで囲みます。
クエリが無いときはダブルクォーテーションが無くても動く場合がありそうです。
このようにして期待通りにGASが動かなければ、アクセス権やurlが違う、クエリの書式が違う、GASがバグっているなどを疑います。

$ curl "https://script.google.com/macros/s/xxxxxxxxxxxxx/exec?temperature=28&humidity=50"

SpreadsheetからGETする

GETは少し複雑です。
リクエストをすると一度リダイレクトされてからレスポンスが得られます。
全体の流れは以下のようになります。

//httpアクセス
http.begin(url);
//データをPOST
http.POST(data);
//リダイレクトされる
//リダイレクト先にアクセス
http.begin(redirect_url)
//目的のレスポンスが得られる
String res = http.getString(); 

こちらもcurlでは自動的にリダイレクトを追いかけるオプション-Lがあり、以下のようにするとレスポンスが得られます。

curl -L "https://script.google.com/macros/s/*******/exec?temperature=28&humidity=50"
>>>Fri Feb 11 2022 04:03:47 GMT-0500 (アメリカ東部標準時) , Fri Feb 11 2022 04:02:58 GMT-0500 (アメリカ東部標準時)

ここでは、POST時刻と、ひとつ前のPOST時刻を文字列として取得しています。

POSTしてGETする関数の全体では以下のようになります。

#include <WiFi.h>
#include <WebServer.h>
#include <HTTPClient.h>
#include "auth.h" //認証情報を書いてあるファイル

String PostRequest(String url, float temperature, float humidity)
{
  HTTPClient http;
  String data = "?temperature=" + String(temperature) + "&humidity=" + String(humidity);
  http.begin(url + data); // beginは必要、データを投げるだけならクエリでこれだけで完了出来る
  //リダイレクトして、データを送る
  const char *headerNames[] = {"Location"}; // Locationをとる
  http.collectHeaders(headerNames, sizeof(headerNames) / sizeof(headerNames[0]));

  Serial.print("[HTTP POST] ...\n");
  int httpCode = http.POST(data);
  int status_code = http.GET();
  Serial.printf("\npost request: status code = %d\n", httpCode);
  Serial.printf("\nget request: status code = %d\n", status_code);

  if (status_code == HTTP_CODE_FOUND)
  {
    //リダイレクトされた 302
    String payload = http.getString();
    Serial.println(payload);

    // ヘッダのLocation（リダイレクト先URL）を取り出す
    Serial.println("Location");
    Serial.println(http.header("Location"));

    // リダイレクト先にGetリクエスト
    String redirect_url = http.header("Location");
    http.begin(redirect_url); // HTTP
    int httpCode = http.GET();

    if (httpCode == HTTP_CODE_OK)
    {
      String res = http.getString(); //目的のレスポンスが得られる
      Serial.println("GET RESPONSE!");
      Serial.println(res);
      return res;
    }
  }
}

GASでレスポンスはreturnに設定します。

var res=sheet.getRange(2, 1).getValue();                //returnするデータを取得
res=res+" , "+sheet.getRange(3, 1).getValue();
return ContentService.createTextOutput(res);

まとめ

ESP32でSpreadsheetを使えるようにしました。
また、curlも少し使えるようになりました。
ESP32やM5stack(中身はESP32ですけど)はネットワークが安く使えますがビルド・書き込みが遅いのでGASのDebugにはcurlは必須ですね。
github.com