ESP32でオンラインデータロガーを作る ④Google spreadsheet側での後処理

経時変化を実際のスケールで表示したい

最後、IFTTTによって送られてきたデータがGoogle driveのSpreadsheetにどんどん溜まっている。ここで一つ問題が発生する。
IFTTTは自動的にタイムスタンプを1列目に挿入してくれるが、このタイムスタンプがGoogle spreadsheetで日時として認識されない。そのままだとx軸が設定できないので何時のデータかよく分かりらず、データに欠損があったり、飛び飛びだった場合に正しく表示できていないことになる。
下図は実際の室温データだが、上が得られたデータそのまま、下が日時を認識可能な状態にして作ったもの。欠損のある部分が分からなくなってしまっている。

f:id:azospiran:20190805051054p:plain — 上: x軸そのまま、下: 日時として認識させた正しいスケール, 一定温度になってるところはエアコンが動いてる時間帯

ロギングの停止を自動検出したい

どうしてもWIFI接続なので時々不安定になることは十分考えられる。ロギングが不意に止まった場合、それを自動検出して通知ぐらいはして欲しい。

関数で日時を認識可能な状態にする

タイムスタンプの日時の間に挿入されている"at"が邪魔をしていることになる。この方法が正しいのかよく分からないが以下の様な関数で文字列を抽出し、連結すると日時として認識された。
一部のブログだと翻訳機能の関数を使って日時として認識させる方法があるみたいだが、私の場合"AM"が翻訳されるときとされないときがあって不安定だったので使えなかった。 f:id:azospiran:20190805051816p:plain

Google scriptの設定と自動実行

ここで一つ問題となるのが、データが新たに追加されたときに、その行に対応する関数を毎回手動で入れる訳にはいかないということである。
IFTTTが投稿する際に関数を予め投稿できる様に書式変更している方もいたが、ちょっと複雑だったので躊躇した。あと、二つ目の目的であるロギングの停止の検出もできていないので、Google scriptで自動実行させることにした。 Spreadsheetでスクリプトを作成するときは Tools→Script editor で開く。

Javascript･･･全然分からないのですが、一先ずこんな感じで動いた。 f:id:azospiran:20190805055105p:plain 作戦としては、図にある赤枠の部分(日時の書式を整える最終行)に注目。
5分ごと( データが5分間で投稿されてくるので)にスクリプトを自動実行し、上の他セルから書式コピーしてくれば日時が自動計算されるというズボラな考え。一方、もしここに値が有った場合、前回の実行からデータが更新されていない→IFTTTかESP32に問題があってロギングが止まってることを意味する。
scriptもエラーになることがあるので、冗長性をもたせるため、最終3行の空白を判定することにした。また、gmailでメール送る簡単な関数(GmailApp.sendEmail())があったのでそれでロギングが止まった場合の通知をすることにした。
スクリプト自体のログは自動実行したときに状態を見たいので、console.log()で出力した。

function copylastcolumn2() {
    var spreadsheet = SpreadsheetApp.getActiveSheet();//getRange+変数はsheetクラスじゃないと動作しないよ！！！
    var LastRow = spreadsheet.getLastRow();
    var value = spreadsheet.getRange(LastRow,6).getValues();
    var value2 = spreadsheet.getRange(LastRow-1,6).getValues();
    var value3 = spreadsheet.getRange(LastRow-2,6).getValues();

//  下から3番目のcellが空白かどうか判断、空白だったらコピー
   if(value3 == ""){
      spreadsheet.getRange(2,6).copyTo(spreadsheet.getRange(LastRow-2,6), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
     //logger.log
     console.log("Succeed3");
  }  
//下から2番目のcellが空白かどうか判断、空白だったらコピー
     if (value2 == ""){
      spreadsheet.getRange(2,6).copyTo(spreadsheet.getRange(LastRow-1,6), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
     //logger.log
     console.log("Succeed2");
     } 
//下から1つ目のcellが空白かどうか判断、空白だったらコピー(通常はこれが動く)
     if(value == ""){
  spreadsheet.getRange(2,6).copyTo(spreadsheet.getRange(LastRow,6), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
     //logger.log
     console.log("Succeed1");
     }
//空白のcellがない→ロギングが止まってるのでメールで通知
  else{
    console.log("The logging is stopping");
    //Send email
GmailApp.sendEmail("*******@gmail.com","ESP32","The logging may have stopped.");
  }
}

躓いたところは、"spreadsheet.getRange(LastRow-1,6)"　みたいにgetRangeの参照先に変数で指定したい場合、上手くいかなかった。結局冒頭の部分を以下の様に”Spreadsheet”から"Sheet"に書き換えたら上手くいった。勉強不足でここら辺の扱いがどうなっているのか理解はしてない･･･。

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
↓
var spreadsheet = SpreadsheetApp.getActiveSheet();

自動実行の設定

f:id:azospiran:20190805061039p:plain 詳しくはそれを解説しているサイトを見ていただけたらと思うが、時間指定でこのスクリプトを走らせることができる。Script editorのメニューのところにある時計マークで設定可能。今回は一先ず5分ごとに実行するようにした。

※ ロギングが止まったときメール通知が5分ごとに来ることになるのがちょっとダメ。そして、それが原因でスパムとみなされてスクリプトを強制停止されてしまう。後で要改善。

最後に

これで一先ず、ざっくりだがオンラインロギングシステムができた。センサーは何でも良いので非常に汎用性が高い。今のところ安定性も特に問題ないようである(データが欠損してたところは意図的に電源を切ってた)。メール通知も、例えば異常値などの監視としても使える。
今後色々測っていきたい。

参考にしたサイト

GASでのメール送信についてまとめてみる - Qiita

【arduino農業】ビニールハウスの温度をクラウドで管理する | おうち栽培

これまでの内容はこちら

azospiran.hatenablog.jp

とある実験のーと

趣味ブログ