LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@ABK28(ABK28)

Google Spreadsheetを管理テーブルにした、ESP8266タイマースイッチ（AC）

Posted at

インタネット接続のWiFiルーターって不定期に不安定になりませんか？
そのたびに、電源をOFF-ONしてたりするんですが、面倒ですよね？？
予防保守じゃないですが、タイマーで夜中とかにOFF-ONしたかったので、作ってみました。
こういいのは、買っても安いものなので、買ったほうが安いんですが、勉強ってことで・・。

ハードウェア

timer-sw-構成図.png

↑こんな感じのハードです。

パーツは以下です。
・いつものESPrDeveloper(wroom02)
https://www.switch-science.com/catalog/2652/
・5Vリレー（なんと1個で200円）
https://www.amazon.co.jp/gp/product/B08CKGNHP9/ref=ppx_yo_dt_b_asin_title_o04_s00
・ダイソーのUSB充電器
https://note.com/tomorrow56/n/n4c8c31f35391?magazine_key=ma0073059b5ac
上記サイトを参考にし（ありがとうございます！）、ここから、5Vを取り出し、AC線も分岐しています。
このサイト、100均機器の情報満載で嬉しいサイトです！
・ACプラグ（オスメス）、ACケーブル　→ホームセンターで購入

Google Spreadsheet

管理テーブル用のシート

こんなカラムのシートを作りました。
シート名は「timer-table」にしました。
別のハードを作ることも想定して、「System」っていうのでパラメータを自由に定義できるようにしときました。
timer-table.png

それぞれのパラメータは下記の仕様としました。
１）weekday
　実行する曜日を0〜6で指定
２）hour
　実行する時刻の「時」を指定。24個まで可（最大毎時実行）。
３）min
　実行する時刻の「分」を指定。１時間に1回までにしたかったので、1つだけ設定可。

尚、プログラムの手抜きのため、要素数も入力させることとし、その分だけデータを読むようにしました。
カンマ区切りでデータを出力します。

パラメータを読み出すAPI

下記のコードをGASで作ります。
「ツール」→「スクリプトエディタ」

getで動くようにしました。
出来上がったら
「公開」→「ウェブアプリケーションの導入」
で、APIとして動くようにします。

function doGet(e){
  var system = e.parameter.system;
  var rv = "";
  var i = 0;
  var j = 0;

  // シート取得→データ取得
  const sheet = SpreadsheetApp.getActive().getSheetByName('timer-table');
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  data = sheet.getDataRange().getValues();
  for(i=1; i<lastRow; i++) if (data[i][0] == system) for(j=1; j<lastColumn; j++) if (data[i][j] != "") rv += data[i][j] + ",";

  return ContentService.createTextOutput(rv);
  // 戻り値：対象systemのスプレッドシート全項目
}

Systemカラム（1列目：配列では0）は、getの引数で指定したsystemと同じ行だけ出力するようになっています。

今回の場合、system = router-reboot でパラメータをセットしたので、下記の呼び出しで、データを取得するようにしたって感じです。

https://script.google.com/macros/s/[ほげほげほげほげ]/exec?system=router-reboot

結果ログを書き出すシート

同じファイルに、こんなシートを作りました。
こんどのシート名は「log」です。
log.png

画像は、ログが書き出された状態です。
カラムは、日付、月、日、システム、プロセス、結果としてみました。

ログを書き込むAPI

下記のコードをGASで作ります。
「ツール」→「スクリプトエディタ」

こっちは、postで動くようにしました。
出来上がったら
「公開」→「ウェブアプリケーションの導入」
で、APIとして動くようにします。

function doPost(e) {
  var p_check = e.parameter.check;
  var p_system = e.parameter.system;
  var p_process = e.parameter.process;
  var p_result = e.parameter.result;

  var check_key = "hogehogehogehogehoge";
  
  var today = new Date();
  var datetime = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
  
  if (p_check == check_key) if (p_system && p_process && p_result) {
    // シート取得
    var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
    var sheet = ss.getSheetByName("log");
    // 日付
    var month = today.getMonth() + 1;
    var day = today.getDate();
    // データ入力
    sheet.appendRow([datetime, month, day, p_system, p_process, p_result]);
  }
  var rv = datetime + "," + p_system + "," + p_process + "," +p_result ;
  return ContentService.createTextOutput(rv);
}

誰でもログが書き込めちゃうのはイヤなので、
ゆるいセキュリティですが、パラメータに「check」ってのをつけて、呼び出し側とAPI側で照合してOKな場合だけ動くようにしました。
あとは、投げたパラメータの内容をそのままスプレッドシートにアペンドしてるだけです・・。

ログの定期削除

上記のようにログをアペンドで書き込んでいくだけだとどんどん増えちゃいますので、定期的に削除するようにしました。
１ヶ月前のデータを消します。
スクリプトエディタの「トリガー」ボタンを押して、12時間毎に実行されるようにしました。
ほんとは、もっと長い間隔がいいんですが、12時間が最長みたい・・・。

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

  // 現在時刻
  var d = new Date();
  var m = d.getMonth() + 1;
  var dy = d.getDate();

  // 1ヶ月以上前の行を削除（あってないかも・・）
  for (i=lastRow-1;i>0;i--) { // 逆順がミソ
    var mm = data[i][1];
    var dd = data[i][2];
    if (mm == 12) mm = 0;
    if (mm == 11) mm = -1;
    if (dd == 31) dd = 0;
    if (dd == 30) dd = 0;
    if (dd == 29) dd = 0;
    if (dd == 28) dd = 0;
    if ((mm < m) && (dd < dy)) {
      sheet.deleteRows(i+1,1);
      //sheet.getRange(i+1,5).setValue("deleted."); テスト用
      //Logger.log("deleled.");
    }
  }  
}

ESP8266側

上記のGETでテーブル値を読み出し、その設定値でスイッチをOFF-ONし、ログをPOSTで書き出すっていうコードを書きます。
ここで、ハマったのは、Google SpreadsheetのAPIの結果は、一度リダイレクトされるってところで、Arduinoのライブラリがリダイレクトに対応してなかったのです。
先ずリダイレクト先（location）を読むので1ステップ、そして実際の内容を読むので２ステップってなりました。

基本的に、以前作った
https://qiita.com/ABK28/items/26f98c191ca31e6be5a4
の焼き直しです。
動作テスト用に、タイマーの時間をハードコーディングもしてあって、フラグでDB（スプレッドシート）を読むかどうかを切り替えるようにしてあります。

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

# define SERIAL_SPEED 115200

# define HARD_CODING 0  // 1の場合、下記固定値で実行

// タイマー設定（ハードコーディング版）
static const int reboot_weekday[] = { 0,1,2,3,4,5,6 } ; // 0:SUN〜6:SAT で指定。いくつも可。
static const int reboot_hour[] = { 22 } ; // 24時間で指定。いくつも可。
static const int reboot_min = 22  ; // 分は１つだけ指定

// Google Spreadsheet からパラメータ読み込み
// 1st step　リダイレクトするところまで
const String Google_server_host = "script.google.com";
const String Google_server_path = "/macros/s/[ほげほげほげほげステップ１：GASで確認した文字列を入れる]/exec"; //
const String Google_get_parameter = "?system=router-reboot";
// 2nd step リダイレクトしたあと
const String Google_server_redirect_host = "script.googleusercontent.com";
const String Google_server_redirect_path = "/macros/echo?user_content_key=";
// 読み込んだデータ
String body; //読み込んだbody
int weekday_data[20]; // 0:SUN〜6:SAT で指定。いくつも可。[0]には要素数が入る。
int hour_data[50];  // 24時間(0-23)で指定。いくつも可。[0]には要素数が入る。
int min_data; // 分(0-59)で指定。1つだけ可。
// LogPost
const String p_key = "hogehogehogehoge"; //チェックキーで照合
const String p_system = "router-reboot";

// mDNS
static const char* mdns_name = "router-timer-sw";

// WiFi
const char* ssid="あなたのSSID";
const char* password="あなたのパスフレーズ";

//  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 = 12 * 60 * 60 * 1000;  // adjust intarval = 12h
NTPClient timeClient(ntpUDP, NTP_SERVER, TIME_OFFSET, NTP_INTERVAL_TIME);
unsigned long ntp_interval = 0;

// Pin No
const int RelayPin = 12;  //リレーにつなぐピン番号

// Web page (Port 8080)
ESP8266WebServer server(8080);
static const char* cpResponse200 = "<HTML>"
 "<BODY style='background-color:#ffffde;font-family:sans-serif;font-size:40px;'>"
 "Router-Timer-SW WEB<br/><br/>"
 "<a href=/cmd?POS=ON> Switch ON</a><br/>"
 "<a href=/cmd?POS=OFF> Switch OFF</a><br/>"
 "<br/>"
 "<a href=/cmd?POS=RESTART> Switch RESTART (OFF -> ON)</a><br/>"
 "<br/>"
 "<br/>"
 "</BODY></HTML>\r\n";

void setup() {
  Serial.begin(SERIAL_SPEED);
  Serial.println("");
  
  // LED
  pinMode(RelayPin, OUTPUT);
  digitalWrite(RelayPin, LOW);

  // まず、スイッチオン →WiFiルータのOFF/ONさせたいので、こうしておかないと自機がWiFiに繋げない（にわとりたまご）。
  SwitchOn();
  
  // WiFi
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid,password);
  while(WiFi.status() != WL_CONNECTED) {
    delay(300);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi Connected.");
  Serial.print("ESP8266 MAC: ");
  Serial.println(WiFi.macAddress());
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());
  Serial.println("");

  // Set up mDNS responder:
  if (!MDNS.begin(mdns_name)) {
    Serial.println("Error setting up MDNS responder!");
    while (1) {
      delay(1000);
    }
  }
  Serial.println(mdns_name);
  Serial.println("mDNS responder started");
  Serial.println(mdns_name);
  Serial.println("");

  // NTP
  timeClient.begin();
  timeClient.update();
  setTime(timeClient.getEpochTime());
  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("");

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

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

  if (HARD_CODING != 1) GoogleSpreadheetRead();
}

void WebCommand() {
  String cmd = server.arg("POS");
  if (cmd == "ON")  { SwitchOn(); }
  else if (cmd == "OFF")  { SwitchOff(); }
  else if (cmd == "RESTART")  { SwitchRestart(); }
  Serial.println("web-cmd=" + cmd);
  server.send(200, "text/html", cpResponse200);
}

void loop() {
  MDNS.update();
  server.handleClient();
  timeClient.update();

  String hh = String(timeClient.getHours());
  String mm = String(timeClient.getMinutes());
  String ss = String(timeClient.getSeconds());
  String wd = String(timeClient.getDay());

  if (HARD_CODING == 1) {
    int wd_ct = sizeof(reboot_weekday)/sizeof(int);
    int hh_ct = sizeof(reboot_hour)/sizeof(int);
    if (mm.toInt() == reboot_min) {
      for(int wi=0; wi<wd_ct; wi++) {
        for(int hi=0; hi<hh_ct; hi++) {
          if ((reboot_weekday[wi] == wd.toInt()) && (reboot_hour[hi] == hh.toInt())) SwitchRestart();
        }
      }    
    }
  }
  else {
    if ((mm == "30") && (ss == "0")) GoogleSpreadheetRead(); // 毎時30分にタイムテーブル参照
    //
    int wd_ct = weekday_data[0];
    int hh_ct = hour_data[0];
    if (mm.toInt() == min_data) {
      for(int wi=1; wi<=wd_ct; wi++) {
        for(int hi=1; hi<=hh_ct; hi++) {
          if ((weekday_data[wi] == wd.toInt()) && (hour_data[hi] == hh.toInt())) SwitchRestart();
        }
      }    
    }
  }
  //
}

void SwitchOn() {
  digitalWrite(RelayPin, HIGH);
  Serial.println("【Switch ON 】");
  log_post("Switch","ON");

}

void SwitchOff() {
  digitalWrite(RelayPin, LOW);
  Serial.println("【Switch OFF】");
  log_post("Switch","OFF");
}

void SwitchRestart() {
  SwitchOff();
  delay(60000); //分単位で動くようにしたので60秒は間隔をあける・・
  SwitchOn();
}

void GoogleSpreadheetRead() {
  WiFiClientSecure sslclient;
  sslclient.setTimeout(500);
  sslclient.setInsecure();

  char c_temp;
  int connection_result;
  String user_content_key;
  Serial.println("1st [GET] Start.");
  Serial.print("connection_result= ");
  Serial.println(connection_result=sslclient.connect(Google_server_host, 443));  
  if (connection_result > 0) {
    sslclient.println("GET " + Google_server_path + Google_get_parameter + " HTTP/1.1");
    sslclient.println("Host: " + Google_server_host);
    sslclient.println("User-Agent: ESP8266/1.0");
    sslclient.println("Connection: close");
    sslclient.println();
    delay(100);
    while (sslclient.connected()) {
      String line = sslclient.readStringUntil('\n');
      //Serial.println(line);
      String key = line.substring(0,9);
      if (key == "Location:") user_content_key = line.substring(76); //リダイレクト先を取得
      if (line == "\r")  break;  // ヘッダの末尾は\r\n
    }
    while (sslclient.available()) {
      c_temp = sslclient.read();
      //Serial.write(c_temp);
    }
    sslclient.stop();
    //Serial.println("");
    Serial.println("1st [GET] End.");
    Serial.println("");
  }
  else {
    // HTTPS client errors
    Serial.println("1st [GET] : Connection Failed.");
    log_post("1st [GET]","connection failed.");
    return;
  }
  //
  Serial.println("2nd [GET] Start.");
  Serial.print("connection_result= ");
  Serial.println(connection_result=sslclient.connect(Google_server_redirect_host, 443));  
  if (connection_result > 0) {
    sslclient.println("GET " + Google_server_redirect_path + user_content_key + " HTTP/1.1");
    sslclient.println("Host: " + Google_server_redirect_host);
    sslclient.println("User-Agent: ESP8266/1.0");
    sslclient.println("Connection: close");
    sslclient.println();
    delay(100);
    while (sslclient.connected()) {
      String line = sslclient.readStringUntil('\n');
      //Serial.println(line);
      if (line == "\r") break; // ヘッダの末尾は\r\n
    }
    body = "";
    //Serial.println("---");
    while (sslclient.available()) {
      c_temp = sslclient.read();
      //Serial.write(c_temp);
      body = body + String(c_temp);
    }
    sslclient.stop();
    //Serial.println("---");
    Serial.println("2nd [GET] End.");
    Serial.println("");
  }
  else {
    // HTTPS client errors
    Serial.println("2nd [GET] : Connection Failed.");
    log_post("2nd [GET]","connection failed.");
    return;
  }
  //
  int i,j;
  String s_temp;
  String s_temp2;
  //
  // weekday
  i = body.indexOf("weekday") + 7;
  if (i < 0) { Serial.println("Error. [Weekday] Record is not found."); return; }
  s_temp = "";
  s_temp2 = body.substring(++i,i+1);
  while (s_temp2 != ",")  {
    s_temp = s_temp + s_temp2;
    s_temp2 = body.substring(++i,i+1);
  }
  weekday_data[0] = s_temp.toInt();
  Serial.print("weekday_data[0]="); Serial.println(weekday_data[0]);
  for(j=1; j<=weekday_data[0]; j++) {
    s_temp = "";
    s_temp2 = body.substring(++i,i+1);
    while (s_temp2 != ",")  {
      s_temp = s_temp + s_temp2;
      s_temp2 = body.substring(++i,i+1);
    }
    weekday_data[j] = s_temp.toInt();
    Serial.print("weekday_data["); Serial.print(j); Serial.print("]="); Serial.println(weekday_data[j]);
  }
  // hour
  i = body.indexOf("hour") + 4;
  if (i < 0) { Serial.println("Error. [hour] Record is not found."); return; }
  s_temp = "";
  s_temp2 = body.substring(++i,i+1);
  while (s_temp2 != ",")  {
    s_temp = s_temp + s_temp2;
    s_temp2 = body.substring(++i,i+1);
  }
  hour_data[0] = s_temp.toInt();
  Serial.print("hour_data[0]="); Serial.println(hour_data[0]);
  for(j=1; j<=hour_data[0]; j++) {
    s_temp = "";
    s_temp2 = body.substring(++i,i+1);
    while (s_temp2 != ",")  {
      s_temp = s_temp + s_temp2;
      s_temp2 = body.substring(++i,i+1);
    }
    hour_data[j] = s_temp.toInt();
    Serial.print("hour_data["); Serial.print(j); Serial.print("]="); Serial.println(hour_data[j]);
  } 
  // min
  i = body.indexOf("min") + 3;
  if (i < 0) { Serial.println("Error. [min] Record is not found."); return; }
  s_temp = "";
  s_temp2 = body.substring(++i,i+1);
  while (s_temp2 != ",")  {
    s_temp = s_temp + s_temp2;
    s_temp2 = body.substring(++i,i+1);
  } // 分のときは要素数はダミーなので読み飛ばす
  s_temp = "";
  s_temp2 = body.substring(++i,i+1);
  while (s_temp2 != ",")  {
    s_temp = s_temp + s_temp2;
    s_temp2 = body.substring(++i,i+1);
  }
  min_data = s_temp.toInt();
  Serial.print("min_data="); Serial.println(min_data);
  Serial.println("");
  Serial.println("[Set Data] Terminated.");
  log_post("Set Data.","terminated.");
}

void log_post(String p_process, String p_result) {
  WiFiClientSecure sslclient;
  sslclient.setTimeout(500);
  sslclient.setInsecure();

  String params;
  params  = "check=" + p_key;
  params += "&system=" + p_system;
  params += "&process=" + p_process;
  params += "&result=" + p_result;
  Serial.print("params= ");
  Serial.println(params);

  int connection_result;
  Serial.print("connection_result= ");
  Serial.println(connection_result=sslclient.connect(Google_server_host, 443));  
  if (connection_result > 0) {
    sslclient.println("POST " + Google_server_path + Google_get_parameter + " HTTP/1.1");
    sslclient.println("Host: " + Google_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);
    sslclient.println("Connection: close");
    sslclient.println();
    delay(10);
    while (sslclient.connected()) {
      String line = sslclient.readStringUntil('\n');
      //Serial.println(line);
      if (line == "\r") break; // ヘッダの末尾は\r\n
    }
    while (sslclient.available()) {
      char c_temp = sslclient.read();
      //Serial.write(c_temp);
    }
    sslclient.stop();
    Serial.println("Log_post: Terminated.");
  } 
  else {
    // HTTPS client errors
    Serial.println("log_post: Connection Failed.");
  }
}

String ipToString(uint32_t ip){
    String result = "";
    result += String((ip & 0xFF), 10);
    result += ".";
    result += String((ip & 0xFF00) >> 8, 10);
    result += ".";
    result += String((ip & 0xFF0000) >> 16, 10);
    result += ".";
    result += String((ip & 0xFF000000) >> 24, 10);
    return result;
}

完成

めでたしめでたし

しかし・・

やっぱりルーターのWifiの調子が悪すぎで、１日１回のリブートでは改善されず、結局新しいWiFiルータを買っちゃいました。
ので、本機はお蔵入りです・・

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?