ESP8266からGoogleスプレッドシートにデータをPOSTしてWEB（Googleサイト）で表示する #GoogleAppsScript

ESP8266(ESPeDeveloper)につないだセンサー情報、つまりPINの今の状態を、WEBで表示させます。
本当は、ちゃんとDBにして、ちゃんとWEBサーバで受ければいいんですが、無料にしたいですし、勉強兼ねて、Googleスプレッドシートをデータベースにする方法で作成しました。

いつものように、勢いでテキトーにコードをコピペしたので、不要なコードも混じってるかもです・・。

ハードウェア

ESPrDeveloperの13ピンに緑LED、15ピンに青LEDをつなぎ、16ピンにセンサーをつなぐようにしました。
動作中は緑LEDが光り、POSTしてるとき青LEDが光ります✨

ESP8266側のアプリ

WiFi Manager

別で投稿しましたが、WiFiManagerを入れたので、最初にWiFi設定ができます。
まっさらなマシンだったら、例えば、iPhoneなどで、「AutoConnectAP」のSSIDをみつけ、パスワード＝passwordで接続すると、設定画面がでますので、それに入力すればOKです。
一度、設定すると、覚えてますので、次はそのままつながっちゃいます。
尚、Sketchを入れ直しても、覚えてましたので、後述のWEBコマンドで、設定をリセットしてあげます。

NTP

NTPで24H毎に時刻あわせしています。
POSTするときに、今の時間を送っています。

mDNS,HTTPサーバ

WEB画面をつけて、手動でも情報通知できるようにしました。
また、ここで、WiFiManagerで設定した設定をリセットできます。
詳細は、以前の投稿で・・・。
mDNSは、
iot-XXXXXXXXXXXXXXXX.local
としました。マシンを何台も同時運用することを考えて、XXXXXXXXXXXXXXXXは、ハードウェアのMACアドレスです。
今のところ、他に手段が思いつかなかったので、プログラムを書き込むときなどにコンソールに表示されるのをメモります。

httpsでのPOST

ここで、かなりはまりました。
実は、httpで先に作っていたんですが、GoogleスプレッドシートをWEBサーバにすることにしたので、httpsが必要になり、いろいろ検索してコードをコピペしたんですが、うまく動かず、試行錯誤して、今のコードになりました。
20秒毎に、16ピンの情報を読みとり、ハードウェアのMACアドレス、現在時刻、現在の「時」と一緒に送ります。
現在の「時」を送るのは、スプレッドシート側のデータを定期的にクリーニング（削除）するとき、１時間前以前のデータだけ消したい！とかやりたかったから、データで送っちゃいました。でも、時刻だけを送ればスプレッドシートで時間だけ抜き出せばいいだけなので無駄でした・・。修正面倒なので、そのまま^^;

以下、ソースです。

#include <ESP8266HTTPClient.h>
#include <NTPClient.h>
#include <Time.h>
#include <TimeLib.h>
#include <FS.h>
#include <DNSServer.h>
#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>
#include <WiFiManager.h>
#include <ESP8266mDNS.h>
#include <WiFiClient.h>
#include <WiFiClientSecure.h>
#include <stdio.h>

#define SERIAL_SPEED 115200


// mDNS
char mdns_name[30];

// Senser_Server settings
const String Senser_server_host = "script.google.com";
const String Senser_server_path = "/macros/s/[あなたの公開導入後URLの文字列]/exec";

//  NTP
WiFiUDP ntpUDP;
const char *NTP_SERVER = "ntp.nict.jp";  // NICT NTP Server
const int TIME_OFFSET = 9 * 60 * 60;  // UTC+9h (JST)
const unsigned long NTP_INTERVAL_TIME = 3 * 60 * 60 * 1000;  // adjust intarval = 3h
NTPClient timeClient(ntpUDP, NTP_SERVER, TIME_OFFSET, NTP_INTERVAL_TIME);
unsigned long ntp_interval = 0;

// Pin No
const int GreenLEDPin = 13;
const int BlueLEDPin = 15;
const int wifiStatusPin = 13;
const int httpStatusPin = 15;
const int pinMotion = 16;

// MAC address
byte mac_addr[6];
char s_mac_addr[20]; 

// Senser WiFi send timing
const unsigned long CHECK_INTERVAL_TIME = 20 * 1000;  // 20s
unsigned long check_interval;

// Web page (Port 8080)
ESP8266WebServer server(8080);
static const char* cpResponse200 = "<HTML>"
 "<BODY style='background-color:#ffffde;font-family:sans-serif;font-size:40px;'>"
 "<h2>IoT Machine</h2><br>"
 "<hr>"
 "<a href=/cmd?CMD=post>Post now status.</a><br><br>"
 "<hr>"
 "<a href=/cmd?CMD=reset>Reset WiFi Setting.</a><br>"
 "<hr>"
 "</BODY></HTML>\r\n";

//　SETUP
void setup() {
  Serial.begin(SERIAL_SPEED);
  for(int i=0;i<3;i++) Serial.println("");

  // LED
  pinMode(pinMotion, INPUT_PULLDOWN_16);  // センサー入力
  pinMode(httpStatusPin, OUTPUT);  // LED blue
  pinMode(wifiStatusPin, OUTPUT);  // LED Green
  digitalWrite(httpStatusPin, LOW);
  digitalWrite(wifiStatusPin, LOW);
  delay(500);

  // WiFi
  //WiFiManager
  WiFiManager wifiManager;
  digitalWrite(BlueLEDPin, HIGH);
  Serial.println("-");
  wifiManager.setBreakAfterConfig(true);
  if (!wifiManager.autoConnect("Auto Connect AP", "password")) {
    Serial.println("failed to connect, we should reset as see if it connects");
    delay(3000);
    digitalWrite(BlueLEDPin, LOW);
    ESP.reset();
    delay(5000);
  }
  digitalWrite(BlueLEDPin, LOW);
  Serial.println("-");
  Serial.println("WiFi Connected.");
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());
  Serial.println("-");
  Serial.println("");
  digitalWrite(wifiStatusPin, HIGH);

  // NTP
  timeClient.begin();
  timeClient.update();
  setTime(timeClient.getEpochTime());
  Serial.println("-");
  ntp_interval = millis();
  Serial.print("setup:ntp_interval= ");
  Serial.println(ntp_interval);
  Serial.print("setup:time = ");
  Serial.println(timeClient.getFormattedTime());
  Serial.println("NTP started.");
  Serial.println("-");

  // mDNS responder:
  WiFi.macAddress(mac_addr);
  sprintf(mdns_name,"iot-%02x%02x%02x%02x%02x%02x",mac_addr[0],mac_addr[1],mac_addr[2],mac_addr[3],mac_addr[4],mac_addr[5]);
  Serial.print("setup:mdns_name = ");
  Serial.println(mdns_name);
  if (!MDNS.begin(mdns_name)) {
    Serial.println("Error setting up MDNS responder!");
    while (1) {
      delay(1000);
      digitalWrite(GreenLEDPin, LOW);
      delay(1000);
      digitalWrite(GreenLEDPin, HIGH);
      digitalWrite(BlueLEDPin, LOW);
      delay(1000);
      digitalWrite(BlueLEDPin, HIGH);
    }
  }
  Serial.println("mDNS responder started");
  Serial.println("-");

  // WebServer
  server.on("/cmd", WebCommand);
  server.begin();
  Serial.println("Web Server started");
  Serial.println("-");

  // Add service to MDNS-SD
  MDNS.addService("http", "tcp", 8080);
}


void loop() {
  MDNS.update();
  server.handleClient();
  timeClient.update();
  if (millis() > check_interval + CHECK_INTERVAL_TIME) {
      Serial.println("-");
      Serial.print("loop:time= ");
      Serial.println(timeClient.getFormattedTime());
      check_interval = millis();
      https_post(digitalRead(pinMotion));
  }
}


void WebCommand() {
  String cmd = server.arg("CMD");
  if (cmd == "reset")  {
    WiFiManager wifiManager;
    digitalWrite(GreenLEDPin, LOW);
    digitalWrite(BlueLEDPin, LOW);
    delay(1000);
    Serial.println("");
    Serial.println("WiFi Reset !");
    Serial.println("");
    for(int i=0;i<3;i++) {
      digitalWrite(BlueLEDPin, HIGH);
      delay(250);
      digitalWrite(BlueLEDPin, LOW);
      delay(250);
    }
    wifiManager.resetSettings();
    delay(3000);
    digitalWrite(BlueLEDPin, HIGH);
    delay(250);
    digitalWrite(BlueLEDPin, LOW);
    ESP.reset();
  }
  if (cmd == "post") https_post(digitalRead(pinMotion));
  Serial.println("  cmd=" + cmd);
  server.send(200, "text/html", cpResponse200);
}


// Post SenserStatus to Server
void https_post(int human_status_flag) {
  // MAC Address
  WiFi.macAddress(mac_addr);
  sprintf(s_mac_addr,"%02X:%02X:%02X:%02X:%02X:%02X",mac_addr[0],mac_addr[1],mac_addr[2],mac_addr[3],mac_addr[4],mac_addr[5]);
  delay(500);

  Serial.println("-");
  Serial.print("https_post:MAC Address = ");
  Serial.println(s_mac_addr);
  Serial.print("https_post:host = ");
  Serial.println(Senser_server_host);
  Serial.print("https_post:path = ");
  Serial.println(Senser_server_path);

  WiFiClientSecure sslclient;
  sslclient.setTimeout(500);
  sslclient.setInsecure();

  String params;
  params  = "hour=" + String(timeClient.getHours());
  params += "&hhmmss=" + String(timeClient.getFormattedTime());
  params += "&macaddress=" + String(s_mac_addr);
  params += "&status=" + String(human_status_flag);
  Serial.print("https_post:params= ");
  Serial.println(params);

  int connection_result;
  Serial.print("https_post:connection_result= ");
  Serial.println(connection_result=sslclient.connect(Senser_server_host, 443));  
  if (connection_result > 0) {
    digitalWrite(httpStatusPin, HIGH); 
    sslclient.println("POST " + Senser_server_path + " HTTP/1.1");
    sslclient.println("Host: " + Senser_server_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");
    Serial.print("https_post:bodypos= ");
    Serial.println(bodypos);
    digitalWrite(httpStatusPin, LOW); 
  } 
  else {
    // HTTPS client errors
    Serial.println("https_post: Connection Failed.");
  }
}

Googleスプレッドシートの設定

シートの作成

３つシートを作りました。

POSTdata
- POSTされるデータとして、「hour」「hhmmss」「macaddress」「status」をカラム名として1行目に記載しました。
LastRowSelect
- 最後のレコードだけを知りたかったので、最後の行がわかるように１シートはさみました。macaddress単位で最新（最終）の行に「1」が立ちます。 =if(countifs(POSTdata!C2:C$997,POSTdata!C2)=1,A2,"")
Summary
- WEBに表示したい内容を見せるシートです。vlookupで拾った最新データで「利用中」「空き」と表示します。ちなみに、WEBはJavaScriptでテーブル可変表示にしましたので行数は増やせます(HTML参照)。列は手抜きしたので増やせません・・。 =if(vlookup(A2,LastRowSelect!B2:C$1000,2,false)=1,"【利用中】","【空き】")

スクリプトエディタ

「ツール」→「スクリプトエディタ」で、エディタを開きます。
初期状態は、「コード.gs」だけが表示されていると思うので、
「ファイル」→「New」→「HTMLファイル」で、HTMLファイルの入力画面を開きます。
初期状態では下記になっていますので、これにHTMLを書いていきます。

HTML の記載

実際に表示されるHTMLを書きます。
GASで、doGETされたときに、スプレッドシートの内容を読み込んで、それをHTMLを表示させます。
HTMLの真ん中あたりの「formタグ」に「<?=lastRow?>」と「<?=record?>」がありますが、これが、GETしたときに動的に送られてくる情報になります。
この情報を表に展開するHTMLとしました。

<html>
  <head>
    <base target="_top">
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
    /* 表のスタイル */
    <style>
      td {
        border: solid 1px;
        width:auto;
      }
      table{
        border: solid 1px;
        border-collapse:  collapse;
        white-space: nowrap;
      }
    </style>
  </head>
  <body>
    <div>
      <h1>Data View</h1>
      <form method="get" action="https://script.google.com/macros/s/[あなたの公開導入後URLの文字列]/exec">
        <div id ='maintable'></div>
        <script>
          var lastRow = <?=lastRow?> ;
          var record = <?=record?> ;
          var recordArray = record.split(',');
          var tr;
          var td;
          var j = 0;
          var table = document.createElement('table');
          for (var i = 0; i < lastRow-1; i++) {
            // tr
            tr = document.createElement('tr');
              // td
              // 場所
              td = document.createElement('td');
              td.textContent = recordArray[j++];
              tr.appendChild(td);
              // 状態
              td = document.createElement('td');
              td.textContent = recordArray[j++];
              tr.appendChild(td);
            table.appendChild(tr);
          }
          document.getElementById('maintable').appendChild(table);
        </script>
      </form>
    </div>
  </body>
</html>

Google Apps Script 部分

ESP8266からPOSTされる部分

doPOST関数で受けます。
受けたら、appendRowで「POSTdata」シートの行追加するだけのコードです。

ポイントは、１時間毎に古いデータを消すようにしたことです。
ずっと保存しておくようにすると、前述のようにシート上の数式で表示データとかを作っちゃってるので、増えた分を考慮するのがイヤだったのと、行が増えるとGASが重くなること、どうせ個人ベースで過去の状態なんてみることないから、ってのが理由です。
delete_old_records関数は、トリガーを使って１時間毎に起動するようにしました。
センサーマシンから、このために、現在の「時」を送ってもらってます。トリガーは「時刻を決められない時間起動」なので、念の為、今の時間-2以前の行を削除しています。

WEB表示部分

こっちは、doGET関数を使います。
「Summary」シートを「getValues()」メソッドで一括で「data」という変数に読み込み、「record」という変数に、csv形式になるように、項目を追加していきました。この「record」と、何行あったかを示す「lastRow」をパラメータにしました。このパラメータを前述のHTMLに渡し、HTMLのjavascriptで「split（’,’）」して、表示させる仕組みです。

コード.gs

function doPost(e) {
  var hour = e.parameter.hour;
  var hhmmss = e.parameter.hhmmss;
  var macaddress = e.parameter.macaddress;
  var status = e.parameter.status;

  if (hour && hhmmss && macaddress && status) {
    // シート取得
    var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
    var sheet = ss.getSheetByName("POSTdata"); 
    // データ入力
    sheet.appendRow([hour, hhmmss, macaddress, status]);
  }  
}


function delete_old_records(e) {
  // シート取得→データ取得
  const sheet = SpreadsheetApp.getActive().getSheetByName('POSTdata');
  var data = sheet.getDataRange().getValues();
  const lastRow = sheet.getLastRow();

  // 現在時刻
  var d = new Date();
  var h = d.getHours();
  if (h == 0) h = 24;

  // 最短1時間以上前の行を削除
  for (i=lastRow-1;i>0;i--) { // 逆順がミソ
    Logger.log(data[i][0]);
    if (data[i][0] < h-1 ) {
      sheet.deleteRows(i+1,1);
      //sheet.getRange(i+1,5).setValue("deleted."); テスト用
      //Logger.log("deleled.");
    }
  }  
}

function doGet(e){
  //index.html
  var html = HtmlService.createTemplateFromFile('index');
  var record = "";

  // シート取得→データ取得
  const sheet = SpreadsheetApp.getActive().getSheetByName('Summary');
  const lastRow = sheet.getLastRow();
  const lastColomn = sheet.getLastColumn();
  var data = sheet.getDataRange().getValues();

  html.lastRow = lastRow;
  for(var i=1; i<lastRow; i++) {
    record += data[i][1] + "," + data[i][2];
    if (i<lastRow-1) record += ","
  }
  html.record = record;

  return html.evaluate();
}

ウェブアプリケーションとして導入

「公開」→「ウェブアプリケーションとして導入」します。
自分のgoogle君は、なぜか英語表示です。日本語にしたいけどやり方がわかりません・・・(;_;)
「Current web app URL:」が公開URLです。
これを、上記の各ソース（ESP8266、HTML）に入れます。
「Project Version」は、いつも、「New」にします。
うまく行けば、この画面の「latest code」を押すと、WEB画面が表示できます。
この状態ですと、「このアプリケーションは・・・」って勝手に出ちゃいますので、これを次に消します。

↓こんな感じ。

Google Sitesで公開

「このアプリケーションは・・・」のを消すには、Googleサイトに埋め込めばOKです。
Googleサイトを新規に作ります。
右側の「埋め込む」を選びます。

ここに、公開URLを記載すればOKです。

めでたしめでたし。