M5Stackでできること 〜送信データをGoogleスプレッドシートに保存する

IoTシステムの一般的な使い方として、デバイスで何らかのデータを採取してWebサーバに送信、Webサーバでは受信したデータの保存や可視化などを行うというものがあります。
このうちデバイスについては、当ブログでもいろいろと書いているようにさまざまな選択肢があります。

一方のWebサーバについては、私の場合は主に、レンタルサーバを契約し、そこに自分で開発したプログラム群を設置して運用しています。
また、Webサーバ用のプログラム開発やサーバの契約が難しい場合は、「Ambient」などのIoT向けWebサービスもいくつか存在します。しかしそれらのWebサービスにはそれぞれ制約事項があり、選択肢もそれほど多くありません。

この部分の選択肢のひとつとして「Googleスプレッドシート」があります。広く利用されており、使い慣れている人も非常に多いため、デバイスが送信したデータをGoogleスプレッドシートに保存できれば、IoTデータを非常に簡単に活用できるようになりそうです。

私はこれまで、デバイスの送信データをGoogleスプレッドシートに保存する際には「IFTTT」というWebサービスを利用していました。デバイスからIFTTTにデータ送信すると、IFTTTがそのデータをGoogleスプレッドシートに書き込んでくれるというもので、これを利用することで面倒な作業なしにデバイス送信データをスプレッドシートに保存することができます。

しかし先日、このIFTTTの利用料が値上げされました。またIFTTTには若干の無料枠があるのですが、IFTTTでデバイスからのデータ受信を行う「Webhooks」という仕組みは無料枠では利用できなくなってしまいました。
「デバイスから送信したデータ」を「IFTTTのWebhooks」を使って「Googleスプレッドシートに保存」するというフローは便利で、これまでは他の人にも結構勧めていたのですが、無料で利用できないとなると、そう安易にお勧めすることもできません。

そんな訳で、IFTTTを使わずに、「デバイスから送信したデータ」を「Googleスプレッドシートに保存」する方法を調べることにしました。
以下のようなものをつくってみることにします。

• デバイスは「M5Stack Basic」を使用する。
• 一定時間（30秒）毎に、Wi-Fiを使ってデバイスからGoogleスプレッドシートにデータを送信する。
• 1回に送信するデータは「デバイス名」と「3つの乱数の値（val0, val1, val2）」とする。
• 乱数の値は「整数2桁＋小数点以下1桁」とする（気温や湿度などのデータを想定）。
• Googleスプレッドシートでは、データを受信するたびにそのデータを新たな1行に追記する。
• 追記するデータは「受信日時」「デバイス名」「3つの乱数の値（val0, val1, val2）」とする。

私はこれまでGoogle Apps Scriptをあまり使ったことがないため、試行にあたっては こちら の記事を参考にさせていただきました。

以下の環境で試しました。

• パソコン：MacBook Pro（13-inch, M1, 2020、macOS Sonoma 14.3.1）
• Webブラウザ：Chrome（バージョン 122.0.6261.129）＊Safariでは実施できませんでした。

まずはスプレッドシート側の準備を行います。

• Googleスプレッドシートで新しいスプレッドシートを作成して開きます。

• 「拡張機能」＞「Apps Script」をクリックしてスクリプト作成画面を開きます（Webブラウザで「Safari」を使うと、ここでエラーとなりました）。

• スクリプトの名前（「無題のプロジェクト」となっている箇所）を任意の名前（ここでは「addRowFromDevices」）に変更し、以下のソースコードを貼り付けます。ここで「id」にはスプレッドシートのURL（ https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0 ）のうち「spreadsheetId」の部分を記載します。「sheetName」はスプレッドシートのシート名です。

function doGet(e) {
    let id = 'XXXXXXXX';
    let sheetName = 'XXXXXXXX';
    var result;

    if (e.parameter == undefined) {
        result = 'Parameter undefined';
    } else {
        var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
        var newRow = sheet.getLastRow() + 1;
        var rowData = [];

        rowData[0] = new Date();
        rowData[1] = e.parameter.chipid;
        rowData[2] = e.parameter.val0;
        rowData[3] = e.parameter.val1;
        rowData[4] = e.parameter.val2;

        var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
        newRange.setValues([rowData]);
        result =  'Ok';
    }
    return ContentService.createTextOutput(result);
}

• 「デプロイ」＞「新しいデプロイ」をクリックします。

• 「新しいデプロイ」画面で「種類の選択」＞「ウェブアプリ」を選択します。

• 「説明」は空白、「次のユーザーとして実行」は「自分」、「アクセスできるユーザー」は「全員」を選択して「デプロイ」をクリックします。

• 「アクセスを承認」をクリックします。

• 「Choose an account」画面で自分のアカウントをクリックします。

• 「Google hasn’t verified this app」という画面が表示されるので「Advanced」をクリックします。

• 「Go to addRowFromDevices (unsafe)」をクリックします。

• 「addRowFromDevices wants to access your Google Account」という画面が表示されるので「Allow」をクリックします。

• 「新しいデプロイ」画面が表示されるので「デプロイID」「ウェブアプリURL」をコピーして「完了」をクリックします。

次にM5Stack側の準備を行い、動作させます。

• Arduino IDEでスケッチを作成します。内容は以下のとおりです。ここで「ssid」「password」には利用するWi-FiルータのSSIDとパスワードを、「deployid」には先ほどコピーした「デプロイID」を記載します。「addRowToGoogleSheets」という関数の中でつくっている「url」という変数の内容は、先ほどコピーした「ウェブアプリURL」の末尾に送信データを連結した文字列です。

#include <M5Stack.h>
#include <WiFi.h>
#include <HTTPClient.h>

const char* ssid     = "XXXXXXXX";
const char* password = "XXXXXXXX";
const char* deployid = "XXXXXXXX";
uint32_t    interval = 30; // unit:sec
uint16_t    chipid   = ESP.getEfuseMac() % 10000;

void setup() {
  M5.begin();
  M5.Lcd.setTextSize(2);
  M5.Lcd.fillScreen(BLACK);
  M5.Lcd.setCursor(0, 0);
  M5.Lcd.print("Connecting to WiFi ");
  WiFi.begin(ssid, password);
  while(WiFi.status() != WL_CONNECTED) {
    delay(500);
    M5.Lcd.print(".");
  }
  M5.Lcd.println(" WiFi connected");
}

void loop() {
  M5.update();
  float val0 = random(0, 1000) / 10.0;
  float val1 = random(0, 1000) / 10.0;
  float val2 = random(0, 1000) / 10.0;
  M5.Lcd.fillScreen(BLACK);
  M5.Lcd.setCursor(0, 0);
  M5.Lcd.print("ESP");
  M5.Lcd.println(chipid);
  M5.Lcd.printf("[val0] %.1f\n[val1] %.1f\n[val2] %.1f\n", val0, val1, val2);
  addRowToGoogleSheets(val0, val1, val2);
  delay(interval * 1000);
}

void addRowToGoogleSheets(float val0, float val1, float val2) {
  HTTPClient http;
  String url = "https://script.google.com/macros/s/" + String(deployid) + "/exec?chipid=ESP" + String(chipid) + "&val0=" + String(val0) + "&val1=" + String(val1) + "&val2=" + String(val2);
  Serial.println("[HTTP] begin...");
  Serial.println(url);
  http.begin(url);
  Serial.println("[HTTP] GET...");
  int httpCode = http.GET();
  if(httpCode > 0) {
    Serial.printf("[HTTP] GET... code: %d\n", httpCode);
    if(httpCode == HTTP_CODE_OK) {
      String payload = http.getString();
      Serial.println(payload);
    }
  } else {
    Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());
    M5.Lcd.println("[HTTP] GET failed");
  }
  http.end();
}

• 作成したスケッチをM5Stackに書き込みます。書き込みが完了すると動作開始します。最初にWi-Fi接続を行い、その後は30秒毎に3つの乱数の値が送信されます。送信した値はM5StackのLCD画面にも表示されます。

スプレッドシートで動作確認を行います。

• 先ほど新規に作成したスプレッドシートを開きます。

• シートにデータが追記されています。左から「日時」「送信デバイスの名前」「3つの乱数の値」です。スプレッドシートを開いたままにしていると、30秒毎に1行ずつ追記されていくのが確認できます。

思っていたよりも非常に簡単に実現できました。
また、IFTTTで同様の処理を実施していたときには、「1回に送信できるデータは3つまで」「スプレッドシートに書き込めるのは2000行まで（超えると別ファイルになる）」といったいくつかの制約がありましたが、今回の方法ならスクリプト次第でより自由度の高いデータ管理ができそうです。

今回調査した方法は、簡単にちょっとしたIoTシステムを構築するのにとても良い方法だと思います。

---