IKZAP(育児ゼロアクションプロジェクト)を”ほぼ”ノンプログラミングで作るためのTips #GAS

ヴァル研究所 Advent Calendar 2018 16日目の記事です。

この投稿は

エンジニアではない自分が、前回の投稿で書いたIKZAPをノンプログラミングで作る事ができた裏側をご紹介
IKZAPの仕組みの中でinputとなるログ収集や、outputとなるメッセージ送信の仕組みについてはIFTTTの力が大きいが、集計などの処理で足りない部分を補うために関数やGASを使っています
- スプレッドシートで使える関数の知識はある程度あったのでできたのかも
- GASの情報もググったりQiitaで検索で何とかなります。ネットの海は広大です
プログラミングできる人にはたぶん役に立たない内容ですｗ

ログの入るスプレッドシートにタイムスタンプを入れる

IFTTTのActionでもスプレッドシートにタイムスタンプは入るのですが、年、月(数字でなくDecemberのような月名)などが関数で扱いにくい形式でした
シンプルに日付と時刻が数値で入るようにしたかったのでGASを使って入れています

var sheet = SpreadsheetApp.getActiveSheet();
function addDate() {
  setDate();
}
function setDate(col, format) {
  var lastrow = sheet.getLastRow();
  var dateRange = sheet.getRange(lastrow, 1);
  var timeRange = sheet.getRange(lastrow, 2);
  var now = new Date();
  // 最終行に時刻が設定されていない場合、現在時刻をセットする
  if (dateRange.getValue() == "") {
    dateRange.setValue(formatDate(now, "yyyy/M/d"));
    timeRange.setValue(formatDate(now, "M/d H:m:s"));
  }
  var actionRange = sheet.getRange(lastrow, 4);
  if (actionRange.getValue() == "stop") {
    var startTimeRange = sheet.getRange(lastrow-1, 2);
    var resultTimemil = timeRange.getValue() - startTimeRange.getValue();
    // フォーマットするときAsia/Tokyoを指定すると+9hされるので、UTC指定
    sheet.getRange(lastrow, 5).setValue(Utilities.formatDate(new Date(resultTimemil), 'UTC', 'H:m:s'));
  }
}
function formatDate(date, format) {
  return Utilities.formatDate(date, 'Asia/Tokyo', format)
}

スプレッドシート編集(行追加)毎に動作するようにしています
３列目(C列)以降にログが入るようにしているので、ログが入るたびに１列目(A列)に日にち、２列目(B列)に日にちと時刻が入ります
これを使って「当日の記録」「前日の記録」のフィルタリングをしたり、「最後に授乳した時刻はいつか」の判定をしています

スプレッドシートが1000行を超えないための仕掛け

IFTTTからスプレッドシートに書き込みをした場合、1000行目以上は別のシートを作成してそちらに書き込む仕様になっています
ログの入るシートが変わることでそれ以降の集計などの起点がすべて変わってしまうため、これを回避するためにもGASを使用しています

function deleteRecords(){
  var mySheet = SpreadsheetApp.getActiveSheet();
  mySheet.deleteRows(2);
}

1行目はヘッダにしていたため2行目を削除するスクリプトを用意しました
スプレッドシート編集(行追加)毎に動作するようにすれば、1行追加されるごとに一番古い行(2行目)が削除されるため、1000行を超えることはありません

前日分のカウント

前述のタイムスタンプがあるので、関数[=TODAY()-1]で前日の日付を指定、さらに関数[=FILTER(値を抜き出したい列の範囲,値を抜き出したい日付)]でフィルタリングして前日分だけのログを抜き出し、そこからおしっこなどの各回数をカウントします

最後の授乳から○分後にお知らせをする

ログ上に「最後に授乳した時刻」は2種類あります
- １．母乳をあげた時間を計るために「授乳開始」と言った後の「授乳終了」を言った時刻
- ２．粉ミルクの量を記録するために「授乳○cc」を言った時刻
あらかじめ授乳関係のログだけをまとめる列を作っておき、関数=MAX(指定列)で「最後に授乳した時刻」を取得します
さらに関数=取得した時刻の入ったセル+time(0,120,0)のような感じで「最後に授乳してから１２０分後の時刻」を取得します
「最後に授乳してから１２０分後の時刻」と「現在時刻」を比較し、現在時刻のほうが大きくなったらセルにメッセージが入ること(＝セル更新)をトリガーに送信しています

授乳にかけた時間を計測する

授乳のログから関数[=Dmax(参照する列の範囲,取得したい時刻の入っている列,授乳開始または終了)]で最後の「授乳開始」「授乳終了」の時刻を取得し、その時間差があるセルに入るようにします
セルに新しい値が入ること(＝セル更新)をトリガーにメッセージ送信しています
単に更新だけをトリガーにすると、新しい「授乳開始」のときにマイナスの値が入って(前回の授乳終了時刻との差をとってしまう)それをメッセージ送信してしまうので注意が必要です
- 今回はif文を使ってプラスの値しか入らないようにして問題を回避しました

まとめ

この試みについて、ピジョンさんのメディア「コモドライフ」で公開したところ、SNS上で「どうやったら使えるのか？」などの投稿も散見されたので、前回と2回に亘って作り方をまとめてみました
プログラムが書ける人から見たらかなり遠回りな作業をしている自覚はあるものの、それでも関数とGASでだいたいやりたいことは実現できます
なお、このときの内容をClova単体でも使えるようにした「話すだけ育児記録」というClovaスキルもリリースしているので、「ここまでして作るのはちょっと…」という方にはお試しいただきたいです(この投稿で紹介しているものとは一部機能や使い方が異なります)