ESP8266からGoogleスプレッドシートに書き込む

種々のIoT用データ投稿サービスはありますが、Googleスプレッドシートに書き込んでしまえばグラフ化・共有等々、何かと便利です。 ESP8266から直接スプレッドシートにデータを蓄積してしまいましょう。

Googleスプレッドシート側スクリプト

今回は温湿度・気圧センサであるBME280を例として用いることとします。BME280から得た温度、湿度、気圧の値をESP8266よりPOSTで送ります。送られてくるクエリストリングの例として

temperature=26.75&pressure=1015.84&humidity=36.42

のような形であると想定します。

データの受信にはGoogle Apps Script(GAS)を利用します。下記の説明はかなり天下り式なので気になった部分についてはGoogle Apps Scriptで検索するとよいかと思います。

まず、Googleスプレッドシートを開き、新規ドキュメントを作成します。その後「ツール」->「スクリプトエディタ」を選択しスクリプトエディタを開きます。

最初はテンプレートが入っていますが、それを消してdoPost関数を書いてやります。

function doPost(e) {
  // パラメータ取り出し
  var temperature = e.parameter.temperature,
      pressure = e.parameter.pressure,
      humidity = e.parameter.humidity;
  
  // パラメータがそろっていなければ終了
  if(temperature && pressure && humidity){
    try{
      // スプレッドシート情報収集
      var spreadsheet = SpreadsheetApp.openById("<スプレッドシートID>"),
          sheet = spreadsheet.getSheets()[0],
          date = new Date();
      // スプレッドシートへ書き込み
      sheet.appendRow([date, temperature, pressure, humidity, e.postData.getDataAsString()]); 
      
    }catch(e){
      console.log(e);
    }
  }
}

こんな感じ。なおスプレッドシートのIDはスプレッドシートのURLに情報があります。

https://docs.google.com/spreadsheets/d/<スプレッドシートID>/edit#gid=0

doPost関数が準備できれば、それを公開する作業に移ります。適当なプロジェクト名で保存し、「公開」->「ウェブアプリケーションとして導入」を選択。

「アプリケーションにアクセスできるユーザー」を「全員（匿名ユーザーを含む）」に変更、「導入」を選択。

認証が必要と言われるので、「許可を確認」し、アカウントでログイン。さらに「このアプリは確認されていません」と出るので、左下「詳細を表示」を選択、下に出てくる「無題のプロジェクト（安全ではないページ）に移動」を選択。

すると、ようやくURLが与えられます。URLをメモしておきます。

このURLをテストしてみます。curlコマンドでPOSTメッセージを送ってみます。

$ curl --include --data 'temperature=26.75&pressure=1015.84&humidity=36.42' -X POST '<コピーしたURL>'

ちゃんとパラメータの対応等がとれていれば、問題なくスプレッドシートに追加されているはずです。うまく追加されない場合はデバッグをする必要がありますが、、、デバッグは少し大変です。

後はこのPOSTメッセージをESP8266で送るだけです。

ESP8266プログラム

ESP8266はArduinoで開発します。BME280とはI2Cで接続し、データ取得はAdafruitのライブラリを利用します。取得したデータのPOSTはWiFiClientSecureを用いて実装しました。

下記プログラムで、約1分毎に温湿度、気圧を測定しPOSTしてくれます。 実際にWiFi機能を利用しているのはpostValues関数内で、毎回接続するようになっています。

少しスプレッドシートを開いて眺めているとおもしろいかもしれません…？

#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>

#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>

const char* ssid     = "<WiFi SSID>";
const char* password = "<WiFi Password>";

const String host = "script.google.com";   // コピーしたURLのホスト部
const String url = "/macros/s/<文字列>/exec";  // URL後半部分

Adafruit_BME280 bme; // I2C

void setup() {
  Serial.begin(9600);
  Serial.println(F("BME280 test"));

  bool status;

  // BME280への接続
  Wire.begin(4, 5); // SDA=IO4, SCL=IO5
  status = bme.begin(0x76, &Wire);  // BME280アドレス指定
  if (!status) {
    Serial.println("Could not find a valid BME280 sensor, check wiring!");
    while (1);
  }

  Serial.println();

  // おまじないに切断
  WiFi.disconnect();
  WiFi.mode(WIFI_STA);
}


void loop() {
  postValues();
  delay(10*1000);
}


void postValues() {
  // WiFi接続開始
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());

  
  WiFiClientSecure sslclient;
  String params;

  // センサデータ取得、パラメータ準備
  params = "temperature=" + String(bme.readTemperature(), 2);
  params += "&pressure=" + String(bme.readPressure()/100.0F, 2);
  params += "&humidity=" + String(bme.readHumidity(), 2);
  Serial.println(params);

  // SSL接続開始、POSTメッセージ送信
  if (sslclient.connect(host, 443) > 0) {
    sslclient.println("POST " + url + " HTTP/1.1");
    sslclient.println("Host: " + host);
    sslclient.println("User-Agent: ESP8266/1.0");
    sslclient.println("Connection: close");
    sslclient.println("Content-Type: application/x-www-form-urlencoded;");
    sslclient.print("Content-Length: ");
    sslclient.println(params.length());
    sslclient.println();
    sslclient.println(params);
    delay(10);
    String response = sslclient.readString();
    int bodypos =  response.indexOf("\r\n");
  } else {
    // HTTP client errors
    Serial.println("[HTTPS] no connection or no HTTP server.");
  }

  // 送信完了、接続終了
  WiFi.disconnect();
}

こんな感じ。

所感

GAS、なかなか便利です。

BME280の温度が体感よりも高めな気がします。 使ったBME280モジュールには3.3Vのレギュレータ等が乗っているため、そこからの熱を拾っているのでしょうか。 精度を追求するなら電源を切るといった事が必要かもしれません。

また、今回はESP8266のスリープ機能を利用していませんが、ロガーとしての用途ならばスリープ機能を利用し電池等で動かしたいですね。