概要
前回はESP8266からGoogleスプレッドシートへ値を書き込む方法に関して説明しました。今回は逆にESP8266からGoogleスプレッドシートの値を読み込む方法に関して説明します。具体的にはGoogleスプレッドシートで作成した表をGASでJSON形式のデータに整え、ESP8266からGETリクエストが送信されるとレスポンスで返す感じです。
開発環境
- Windows 11 Home 23H2
- Arduino IDE 2.3.0
- ArduinoJson 5.13.5
- cURL 8.4.0
Google Apps Script
まず、以下のような感じでGoogleスプレッドシートを新規に作成しました。表の値はチェックを付けると真、チェックを外すと偽になります。
GASで作成したスクリプトの内容は以下の通りです。JSON形式のデータをレスポンスで返します。
function doGet(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var records = [];
for (var i = 0; i < 6; i++) {
var record = [];
for (var j = 0; j < 7; j++) {
var value = sheet.getRange(i + 2, j + 2).getValue();
record.push(value);
}
records.push(record);
}
var object = new Object;
object["time"] = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm:ss");
object["table"] = records;
var response = JSON.stringify(object);
return ContentService.createTextOutput(response);
}
これをWebアプリとして公開し、最終的に取得できるURLを後述するプログラムに貼り付けます。手順などは以前の記事に載せた内容と重複するので割愛します。
リダイレクトに対応する
ここではcURLを使用し、スクリプトが正しく実行されるのか試してみます。以下のコマンドをターミナルに貼り付けて実行し、GETリクエストを送信します。
curl "https://script.google.com/macros/s/{id}/exec"
まずは以下のようなレスポンスを得ることができると思います。ただし、このままだとJSON形式のデータは受け取ることができていません。リダイレクトされてしまうからです。
<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="{url}">here</A>.
</BODY>
</HTML>
つまり、ここからリダイレクト先のURLを抽出し、再度GETリクエストを送信する必要があります。具体的にはHTMLのA要素で指定されていますが、特殊文字1を含む場合は元の文字に変換しておく必要がありました。例えば&を&に置き換えます。
curl "{url}"
最終的に以下のようなレスポンスを得ることができたら成功です。
{"time":"2024/03/11 17:19:22","table":[[false,false,false,true,false,false,false],[false,true,true,true,true,false,false],[false,false,true,false,true,true,false],[false,true,true,true,true,true,false],[false,true,false,false,false,false,false],[false,false,false,false,false,false,false]]}
ちなみにcURLではリダイレクトに追従するオプションも用意されています。以下のコマンドなら上記のレスポンスを直に取得できます。
curl -L "https://script.google.com/macros/s/{id}/exec"
Arduino
5分毎にGETリクエストを送信し、データの取得を試みます。リダイレクトに対応する方法は上記の通りです。
#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
#include <WiFiClientSecure.h>
#include <ArduinoJson.h>
const char *ssid = "{ssid}"; //put your Wi-Fi SSID
const char *password = "{password}"; //put your Wi-Fi password
const String url = "https://script.google.com/macros/s/{id}/exec";
void setup() {
WiFi.begin(ssid, password);
Serial.begin(74880);
Serial.print("Connecting...");
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println();
Serial.println("Connected to Wi-Fi");
readGoogleSheets();
//go to sleep
Serial.println("Start sleeping...");
ESP.deepSleep(5 * 60 * 1000000); //5 minutes
delay(1000);
}
void loop() {
}
void readGoogleSheets() {
HTTPClient http;
WiFiClientSecure client;
client.setInsecure();
//open a connection
http.begin(client, url);
//send a GET request
http.GET();
//receive a response
String response = http.getString();
Serial.println("Done");
Serial.println(response);
//redirect
int from = response.indexOf("<A");
int to = response.indexOf("A>");
String destination = response.substring(from + 9, to - 8);
destination.replace("&", "&");
http.begin(client, destination);
http.GET();
//receive a response
response = http.getString();
Serial.println("Done");
Serial.println(response);
//close the connection
http.end();
//parse the JSON value
DynamicJsonBuffer buffer;
JsonObject& object = buffer.parseObject(response);
if (!object.success()) {
Serial.println("Failed to parse a JSON value");
return;
}
//convert the JSON value
JsonArray& array = object["table"].asArray();
if (!array.success()) {
Serial.println("Failed to convert the JSON value");
return;
}
//show the table on a serial monitor
for (int i = 0; i < array.size(); i++) {
for (int j = 0; j < array[i].size(); j++) {
bool value = array[i][j].as<bool>();
Serial.print(value ? "1" : "0");
}
Serial.println();
}
}
動作確認
今回の場合、シリアルモニタに以下のようなレスポンスが表示されたら成功です。しっかりGoogleスプレッドシートの内容に合致しました。
0001000
0111100
0010110
0111110
0100000
0000000
まとめ
このような感じでGASを経由し、GoogleスプレッドシートなどのGoogleが提供する各種サービスをESP8266やESP32と連携することが可能です。もちろん、誰でも無料で利用できるため、とても有能に思いました。