Google Spreadsheetで非常用持ち出し袋の在庫管理をする

背景

我が家で数年前に非常用持ち出し袋というものを作ったのですが、中身の乾パンだとか長期保存できる羊羹だとかがそろそろ古くなってきています。
また、最近新しく家族が増えたのもあって色々と中身の増強が必要そうなのと、賞味期限とか使用期限とかの管理も今までよりしっかりやりたいなーというのがありました。

そこで、Google Spreadsheetを使って在庫の表を管理し、Google Apps Scriptが週一で期限を自動でチェックして期限が切れそうなものをメールで通知するような仕組みを作りました。

実装

Spreadsheetを作る

シンプルにこんな感じのものを作りました。
f:id:miyataro32:20180610233004p:plain

単純にGoogle Spreadsheetの機能を使っただけなのでなんてことはないのですが、、

  • 分類はちゃんとマスタ（別シートで分類の一覧を用意した）から選択するような入力制御をかけた
  • チェックボックスが使えたので破棄したフラグをチェックボックスで表現してみた
  • 期限が近いものには「期限アラート」が出るように関数を組んだ

というのが工夫した点です。

期限アラートを出す関数は下記のような感じです。

=IF(ISBLANK(F2),,IF(OR(DAYS(F2, TODAY()) > 30, G2 = TRUE), , "❗"))

実際には30とか❗とかは変更しやすいよう設定シートを作って外出ししていますが。

Spreadsheetができたので期限切れをチェックしてメールを送るスクリプトを書きます。

Google Apps Scriptを書く

Spreadsheetに紐付いたスクリプトを作成するのは簡単で、下記のように[ツール] > [スクリプト エディタ]をクリックするだけです。
f:id:miyataro32:20180610234041p:plain

期限切れが近いレコードを抽出する部分の実装

var inventory = {};
inventory.SHEET = '物品一覧';
inventory.COLUMNS = {
  CATEGORY : '分類',
  NAME : '物品名称',
  QUANTITY : '数量',
  SELLER : '購入元',
  AQUISITION_DATE : '購入日',
  EXPIRY_DATE : '使用期限／賞味期限',
  DISPOSED : '処分済',
  EXPIRY_ALERT : '期限アラート'
};

inventory.getSheet = function () {
  if (!inventory.sheet) {
    inventory.sheet = SpreadsheetApp.getActive().getSheetByName(inventory.SHEET);
  }
  return inventory.sheet;
};

inventory.getData = function () {
  var rawData = inventory.getSheet().getDataRange().getDisplayValues();
  var keys;
  var data = [];
  for (var i = 0; i < rawData.length; i++) {
    var row = rawData[i];
    
    if (i === 0) {
      keys = row;
    } else if (row[0] && row[1]) {
      var record = {};
      for (var j = 0; j < keys.length; j++) {
        record[keys[j]] = row[j];
      }
      data.push(record);
    } else {
      // when empty row is detected
      break;
    }
  }
  return data;
};

Google Apps ScriptではSpreadsheetAppというオブジェクトが用意されており、このオブジェクトを使ってSpreadsheetの操作ができます。
inventory.getDataでは表をまるごと{列名 : 値}の連想配列の配列として取得できるようにしました。

inventory.getExpiringItems = function() {
  return inventory.getData().filter(function(record) {
    return !!record[inventory.COLUMNS.EXPIRY_ALERT]
  });
};

inventory.getDataで取得した情報をフィルターして「期限アラート」列に何らかの値（❗ですね）が入っているデータのみを抽出しています。
これで期限が切れそうな物品のみを表から抽出できるようになりました。

メール通知の実装

function checkInventory(isDebug) {
  var expiringItems = inventory.getExpiringItems();
  if (expiringItems.length > 0) {
    var DEVELOPER = ['hogehoge@dummy.com']; // 私のメールアドレス
    var USER = ['fugafuga@dummy.com']; // 妻のメールアドレス
    var recipient = isDebug ? DEVELOPER : DEVELOPER.concat(USER);
    
    var subject = '備蓄品の期限切れ通知 (' + expiringItems.length + '件)';
    var body = '下記の備蓄品の期限が近くなっています。\n\n';
    
    for (var i = 0; i < expiringItems.length; i++) {
      var item = expiringItems[i];
      body = body + '・' + item[inventory.COLUMNS.NAME] + ' (' + item[inventory.COLUMNS.EXPIRY_DATE] + ')\n';
    }
    
    body = body + '\n詳細はスプレッドシートで確認してください。\nhttps://docs.google.com/spreadsheets/d/dummy/edit#gid=0';
    
    MailApp.sendEmail(recipient.join(','), subject, body);
    Logger.log('sent e-mail : ' + {recipient : recipient, subject : subject, body : body}.toString());
  } else {
    Logger.log('no expiring items');
  }
}

function test() {
  checkInventory(true);
}

Spreadsheetのときと同様にMailAppというオブジェクトが用意されており、こいつのsendEmailというメソッドを叩くことでメールを送ることができます。
一応デバッグモードを用意し、isDebugがtrueのときには妻にメールが行かないようにしてあります。こういう気遣いはエンジニアとして大事ですよね。
特筆すべきことはないのですが、備蓄品の期限切れ通知 (3件)のようなタイトルで、本文には期限切れ間近の物品名とSpreadsheetのURLが書いてあるメールが飛びます。

ここまで書いてエディタからtest()を実行すると私のところにメールが飛びます。

そして、エディタのメニューで[編集] > [現在のプロジェクトのトリガー]と選ぶと下記のように週一でcheckInventory()を実行する設定ができます。
f:id:miyataro32:20180610235611p:plain

まとめ

きっと探せばスマホアプリがある気がしますが、個人的にこういうたまにしか使わないものは専用アプリをごちゃごちゃ入れるよりもGoogle Spreadsheetかなにかに集約しておいたほうが無くさないし共有できるし楽です。
今回は非常用持ち出し袋を対象にしているので在庫の増減（特に減の方）が頻繁には発生せず、Spreadsheetでもそれほど煩雑ではないのでこの仕組が長く活躍してくれるはずです。

ちなみに、ずっと冷蔵庫の在庫を管理したいと思っているのですが、冷蔵庫レベルで増減が頻繁に発生するとSpreadsheetではすぐに実態と記録が乖離することが容易に予想できるので同じやり方はダメそう。。
バーコード読み取って入出庫登録できるようなデバイスが冷蔵庫に磁石でくっついてたりしたら行けるんじゃないかという気がするんですが、誰か作ってくれないだろうか。
入庫の周期とか賞味期限とかからネットショップで注文しろってサジェストしたりとか儲かる要素はある気がするので、冷蔵庫の在庫管理というサービスは既にありそう。

Google Apps Scriptの実行時間を改善した話

背景

先日これを作ったんですが、「アレクサ、育児ノートでうんちを記録して」みたいに頼んだときの応答が遅いという問題があり、妻から苦情が寄せられていました。 miyataro.hatenablog.com

処理の流れとしてはこんな感じです。
f:id:miyataro32:20180503224401p:plain

Lambdaの実行時ログを追ってみたところ、実行時間は4.5~5.0秒程度で、そのうちの殆どをGoogle Apps Script APIの実行が占めていました。ただし、私が書いたスクリプトの実行時間そのものは2秒程度で、それをAPIとして実行するときのオーバーヘッドがかなりあるようでした。
集計してないので一個適当に拾ったログから出した値を載せておくと以下のような感じでした。

処理 実行時間
- Lambdaの処理全体 4807 ms
  - Google Apps Script APIの呼び出し 4741 ms
    - Google Apps Scriptの実行時間 2065 ms
    - API実行にかかるオーバーヘッド(?) 2676 ms
  - Lambda上のその他の処理 66 ms

オーバーヘッドだとしたら長すぎだろっていう気がするけど、調べても何も出てこなかった。

とりあえず、こちらで簡単に改善できる部分のなかで改善の余地があるのが「Google Apps Scriptの実行時間」しかないので、ここを改善しました。

方針

一般的な情報の調査

まず、ここにある情報が全部入りな印象です。
Best Practices  |  Apps Script  |  Google Developers

ここに書いてある内容で速度改善に効果がありそうなものは下記。

  • Minimize calls to other services → やってないので関係ない
  • Use batch operations (Spreadsheetの読み書きはまとめてやったほうが早いよっていう内容)
    → もともとこれには結構気を使って作ったので改善の余地が少なそう
  • Use the Cache service → こんなのあったの知らなかった…

読み書きはまとめたほうが早いよっていうのは以下のサイトを読んで気をつけていたのでそれなりに出来ているはず。
Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法
[GAS][スプレッドシート]処理速度を向上するには : 逆引きGoogle Apps Script

今までSpreadsheetの読み書きをしていた部分をCache使って高速化するのが効果大きそうなので、その方向で考えることにしました。

ぼくがかんがえたさいきょうの高速化

基本方針は以下の通り

  • Amazon Echoに対する発話から同期的に起こる処理の中ではできるだけSpreadsheetの読み書きをしない
    • キャッシュを利用した書込キューを実装する
      • 書込はすぐには行わずに書込内容をキューに詰めて応答を返す
      • Google Apps Scriptの定時実行機能を使ってバッチ処理でキューの中身を書込む
    • うんちの記録に対して「本日○回目のうんちです」の応答を返すためにSpreadsheetの読込を行っているが、回数はキャッシュの中の値をインクリメントすることでカウントし、読込をなくす

この記事では書込部分を扱います。

実装

キューの実装

キューはキャッシュに対してrecords-queue-1, records-queue-2...というキーで書込内容のJSONを詰めることで実装する。
enqueue用のポインタとdequeue用のポインタを別々に用意してそれらもキャッシュに置いておく。

まずはキューに詰めるところ。

var recordsBufferCache = {}; // 名前空間の定義

recordsBufferCache.QUEUE_CACHE_KEY_PREFIX = 'records-queue-';
recordsBufferCache.QUEUE_ENQUEUE_POINTER_KEY = 'records-queue-enqueue-pointer';

// 念のため、CacheService.getScriptCache()を何回も呼ばなくて良いようにした。
recordsBufferCache.getCache = function () {
  if (!recordsBufferCache.cache) {
    recordsBufferCache.cache = CacheService.getScriptCache();
  }
  return recordsBufferCache.cache;
};

// これがキューに詰めるメソッド。引数に書込む内容を受け取る。
recordsBufferCache.enqueue = function(row) {
  var startTime = Date.now();
  Logger.log('enqueue started with row : ' + JSON.stringify(row));
  
  var cache = recordsBufferCache.getCache();
  
  // 次に詰めるキューのpointerをキャッシュから取得
  var enqueuePointer = cache.get(recordsBufferCache.QUEUE_ENQUEUE_POINTER_KEY);
  if (enqueuePointer === null) {
    Logger.log('enqueuePointer is missing. Newly start from 0');
    enqueuePointer = 0;
  }
  Logger.log('enqueuePointer is ' + enqueuePointer);

  // キャッシュにキューの情報を詰める。第三引数はキャッシュの保持時間で、最大値(21600sec = 6h)を指定。
  cache.put(recordsBufferCache.QUEUE_CACHE_KEY_PREFIX + enqueuePointer, JSON.stringify(row) ,21600);

  // 次に詰めるキューのポインタをキャッシュに入れる
  enqueuePointer++; // implicitly converted to Number
  cache.put(recordsBufferCache.QUEUE_ENQUEUE_POINTER_KEY, enqueuePointer, 21600);
  
  var executionTime = Date.now() - startTime;
  Logger.log('enqueue took ' + executionTime + ' ms');
};

キューの内容をSpreadsheetに書込むところ

recordsBufferCache.QUEUE_DEQUEUE_POINTER_KEY = 'records-queue-dequeue-pointer';

// これが書込むメソッド。返り値は書込んだ行数。
recordsBufferCache.dequeue = function() {
  var startTime = Date.now();
  Logger.log('dequeue started');
  
  var cache = recordsBufferCache.getCache();
  var enqueuePointer = cache.get(recordsBufferCache.QUEUE_ENQUEUE_POINTER_KEY);
  var dequeuePointer = cache.get(recordsBufferCache.QUEUE_DEQUEUE_POINTER_KEY);

  if (enqueuePointer === null) {
    Logger.log('enqueue has never been called. dequeue process will terminate');
    return 0;
  }
  if (dequeuePointer === null) {
    Logger.log('dequeuePointer is missing. Newly start from 0');
    dequeuePointer = 0;
  }
  Logger.log('enqueuePointer is ' + enqueuePointer + ', dequeuePointer is ' + dequeuePointer);
  
  var rowCnt = 0;

  for (var i = Number(dequeuePointer); i < enqueuePointer; i++) {
    Logger.log('processing with pointer ' + i);
    var rowStr = cache.get(recordsBufferCache.QUEUE_CACHE_KEY_PREFIX + i);
    Logger.log('Write a new record : ' + rowStr);
    var row = JSON.parse(rowStr);

    // ループの中で都度書き込みするのはアンチパターンだが、この処理はバッチ実行するのである程度速度がかかることは許容した
    records.getSheet().appendRow(row);
    
    // 消費済のキューは削除
    cache.remove(recordsBufferCache.QUEUE_CACHE_KEY_PREFIX + i);

    // 次に読むキューのポインタをキャッシュに詰める
    cache.put(recordsBufferCache.QUEUE_DEQUEUE_POINTER_KEY, i + 1, 21600);
    rowCnt++;
  }
  
  var executionTime = Date.now() - startTime;
  Logger.log('dequeue took ' + executionTime + ' ms');
  
  return rowCnt;
};

このような内部関数を作り、それを呼び出す関数を以下の通り実装した。

function writeRecordsInQueue () {
  var startTime = Date.now();
  Logger.log('writeRecordsInQueue started');
  
  var newRowCount = recordsBufferCache.dequeue();
  if (newRowCount > 0) {
    // これはレコード追加に伴ってフロントとして使っているシートを更新する処理
    dashboard.updateDashboardOnRecordsChange(true);
  }
  
  var executionTime = Date.now() - startTime;
  Logger.log('writeRecordsInQueue took ' + executionTime + ' ms');
}

この関数を定期実行するよう設定すればOK
f:id:miyataro32:20180506143408p:plain

キャッシュの期限切れを防止

Google Apps Scriptの仕様でキャッシュは最大6時間で期限切れになってしまうので、定期的にキャッシュを詰め直すことでキャッシュの期限切れを防止してみた。
こういうのGoogle的には絶対やってほしくないやつだと思うけど、、、

function maintainCache () {
  var startTime = Date.now();
  Logger.log('maintainCache started');
  
  var cache = recordsBufferCache.getCache();
  
  [
    recordsBufferCache.QUEUE_ENQUEUE_POINTER_KEY, 
    recordsBufferCache.QUEUE_DEQUEUE_POINTER_KEY
  ].forEach(function (key) {
    var value = cache.get(key);
    cache.put(key, value, 21600);
    Logger.log('[key='+ key + ', value=' + value + ']');
  });
  
  var executionTime = Date.now() - startTime;
  Logger.log('maintainCache took ' + executionTime + ' ms');
}

これを4時間おきに実行するようにした。

まとめ

はじめはこんな感じだった実行時間が

処理 実行時間
- Lambdaの処理全体 4807 ms
  - Google Apps Script APIの呼び出し 4741 ms
    - Google Apps Scriptの実行時間 2065 ms
    - API実行にかかるオーバーヘッド(?) 2676 ms
  - Lambda上のその他の処理 66 ms

こんな感じになりました。

処理 実行時間
- Lambdaの処理全体 1993 ms
  - Google Apps Script APIの呼び出し 1961 ms
    - Google Apps Scriptの実行時間 192 ms
    - API実行にかかるオーバーヘッド(?) 1769 ms
  - Lambda上のその他の処理 32 ms

キューの実装だけじゃなくて他のIOも色々とキャッシュ利用するロジックに変えたので、色々合わさった結果ですが、結構改善できました。（まだ遅いけど）
全体としての修正内容はこんな感じ。 github.com

あと気になるのは下記。

  • キャッシュの読み書きでロック機構が必要ではないか（基本的に更新頻度が低いので今のところ問題は起こっていない）
  • API実行のオーバーヘッド大きすぎではないか

また何かあったら書きます。

Google Spreadsheetで管理している育児記録にGoogle Formsから情報を登録する

背景

この間、こんなものを作りました。 miyataro.hatenablog.com

今はまだ子供の一ヶ月検診が終わっていなくてずっと家にいるので常にAmazon Echoが近くにあるが、そのうち外出するようになれば買物中におむつを替えたりするはず。
そのときには当然Echoはないのでその場でポチッと登録できるインターフェイスが必要になる。

という事情から、Google Spreadsheetの育児記録にサクッとイベントを登録できる画面を作った。
使用したのはGoogle Formsで、Google Spreadsheetとの連携がすごく簡単にできるので結構すんなり作れた。

実装

Google Formsの作成

育児記録を管理しているSpreadsheetからフォームを作成して編集する。 f:id:miyataro32:20180503212308p:plain

フォームの中身はこんな感じ。 f:id:miyataro32:20180503212011p:plain

Spreadsheet側のGoogle Apps Scriptへトリガー追加

フォームを作成したはいいが、フォーム専用のシートが新しく作成されてそこに送信内容が溜まっていくという仕組みになっている。今回はAlexa経由で登録したときと同じくrecordsというシートに溜まっていってほしい。
調べてみると、Google Spreadsheetに紐付いたGoogle Apps Scriptで利用可能なトリガーの中にForm submitというものがあったのでこれを使う。
Event Objects  |  Apps Script  |  Google Developers

Google Apps Scriptのイベントにはsimpleとinstallableというのがあって、simpleはonOpenのような特定のイベントをハンドルする関数の名前があらかじめ定義されており、その関数を実装すればいいやつみたい。installableなイベントは、設定画面から任意の関数に紐付けることができるらしい。
今回使うForm submitイベントはinstallableなので自分で好きな名前の関数を作ってそいつを設定画面から紐付ける感じになる。

ということで、とりあえずSpreadsheetからスクリプトエディタを開いて関数を作ってみた。（説明のために若干簡単にしてある）

function onFormSubmit(e) {
  var dateKey;
  var eventKey;
  var milkKey;
  
  var keys = Object.keys(e.namedValues);
  keys.forEach(function(key) {
    if (key.indexOf('日時') > -1) {
      dateKey = key;
    } else if (key.indexOf('イベント') > -1) {
      eventKey = key;
    } else if (key.indexOf('ミルク') > -1) {
      milkKey = key;
    }
  });
  
  var date = e.namedValues[dateKey][0] ? new Date(e.namedValues[dateKey][0]) : new Date();
  var events = e.namedValues[eventKey][0].split(/,\s*/);
  var milkVolume = e.namedValues[milkKey][0];
    
  if (events.indexOf(TYPE_NAME.unchi) > -1) {
    records.appendJournalRecordWithSpecificDate(date, TYPE.UNCHI);
  }
  if (events.indexOf(TYPE_NAME.oshikko) > -1) {
    records.appendJournalRecordWithSpecificDate(date, TYPE.OSHIKKO);
  }
  if (events.indexOf(TYPE_NAME.oppai) > -1) {
    records.appendJournalRecordWithSpecificDate(date, TYPE.OPPAI);
  }
  
  if (milkVolume) {
    records.appendJournalRecordWithSpecificDate(date, TYPE.MILK, milkVolume);
  }
}

トリガー実行時にこの関数に渡されるイベントオブジェクトeの中には以下のような値が入っている。

{
  "values": [
    "2018/05/03 21:22:27",
    "2018/05/03 21:20:00",
    "おっぱい",
    ""
  ],
  "namedValues": {
    "日時（空欄の場合は現在の時刻）": [
      "2018/05/03 21:20:00"
    ],
    "イベント（ミルクを飲ませた場合は「ミルクの量」を入力）": [
      "おっぱい"
    ],
    "ミルクの量 (mL)": [
      ""
    ],
    "タイムスタンプ": [
      "2018/05/03 21:22:27"
    ]
  },
(省略)
}

valuesとnamedValuesの両方に入力値が入っている。
どっちを使っても良かったのだが、namedValuesを使ったほうが変更に強そうだったので、namedValuesのキーから必要なものを識別してvalueを取るようなロジックを書いた。

トリガーの設定はスクリプトエディタから編集 > 現在のプロジェクトのトリガーとするとモーダルが開くので、下記のように指定する。
f:id:miyataro32:20180503221456p:plain

これでGoogle Formsから送信するとrecordsシートに情報が溜まっていくようになった。

onFormSubmitの全体はGitHubを参照 github.com

Google Formsにデフォルト値を入れられるようにする

ここまで作って動くようになったものの、妻から新しい要件が出てきた。
日付と時刻の入力はデフォルト値で現在時刻が埋まっている状態から時刻だけちょちょっと変える感じでしたいらしい。

調べてみるととりあえず初期値を入れる方法はあるらしい。 blog.nakachon.com

ただし、現在時刻のように動的に初期値を作る機能はGoogle Formsにはなさそう。

そこで、とりあえず日付と時刻に適当な初期値が入るようなurlを作成した上で、そのurlをもとにSpreadsheet上で動的に現在時刻が入るurlを作りつつそのurlへのリンクを表示し、「登録はこちらから」みたいな感じにしてみた。

まとめ

これでGoogle SpreadsheetとGoogle FormsとAmazon Echoで育児記録を管理する仕組みができた。
全体像はこんな感じ
f:id:miyataro32:20180502201647p:plain

機能的には出来ているが、Amazon Echoから操作した際の応答が若干遅いという問題が未解決のまま残っているので、もし解決できたら記事を書く予定。

Amazon echoとGoogle Spreadsheetで育児記録をつける

背景

先日第一子が産まれ、妻がスマホアプリでうんちやらおっぱいやらの記録をつけていたのだが、下記のような問題があった。

  • 妻以外の人（僕とか）は記録が見れない
  • 妻以外の人（僕とか）がおむつを変えたりミルクを上げたりしたときに記録がつけられない
  • スマホが手元にないと記録がつけられない（妻が授乳しながらスマホ取ってと言ってくること多数）

そこで、Amazon echo dotで記録をつけ、記録自体はGoogle Spreadsheetに集約することで上記の問題を解決してみた。
Google Spreadsheetはデータストアとフロントを兼ねてくれるのでかなり楽ちん（なはず）です。
ちなみに、かなりDIY感があって移植性皆無なやり方なのでアプリにして一般公開とか出来なさそう。。

要件

妻にヒアリングしたところ、下記のようなことが必要らしかった。

  • うんち、おしっこ、おっぱい、ミルクの時刻を記録して後から参照できる
  • ミルクに関してはあげた量も記録できる
  • 前回のうんち、おしっこ、おっぱい、ミルクから何時間経過したか簡単に参照できる
  • 一日あたりのうんち、おしっこ、おっぱい、ミルクの回数およびミルクの量を簡単に参照できる

ちなみに、一般的なアプリだとついている下記の機能は我が家には不要らしかったので作っていない。

  • 左右のおっぱいの区別およびそれぞれの授乳時間の記録
  • 寝た・起きた の記録

実装

全体の構成

全体の構成はこんな感じ。これは配置図というらしい。
図の中にはGoogle Formsがあるが、この記事では対象としない。 f:id:miyataro32:20180502201647p:plain

処理の流れはこんな感じ f:id:miyataro32:20180503224401p:plain

  • Alexaのカスタムスキルからは標準に従ってAWS Lambdaの関数を使う。
  • Google Spreadsheetの操作はGoogle Apps Scriptを使う。GASをかまさずにLambdaから直接spreadsheetのAPIを叩くことも出来たが、Spreadsheetの操作の実装はGoogle側に寄せてAmazon側はそれを叩くだけという構成の方がAlexa以外の他のインターフェイスを作りたくなったときなどに便利なため。

実装

Alexaカスタムスキルの作成

基本的な作成方法、Lambdaとの連携方法は下記の公式チュートリアルを見ればだいたい分かる。 developer.amazon.com

ざっくりと概念を説明すると、

  • スキルというのがAlexaにおけるアプリに相当する概念で、スキルの中にインテント(関数に相当)、インテントの中にスロット(関数の引数に相当)という概念がある。
  • Amazon Echoへの指示は、「アレクサ、スキルの呼び出し名でインテントの発話」、例えば「アレクサ、育児ノートでうんちを記録して」というように行う。
  • インテントの識別はサンプル発話をたくさん書くとそれを元にうまいことやってくれる。（うんち、うんこ、うんこ記録、うんちを記録して　みたいな感じ笑）
  • 話した内容からスロットの値を識別するときにはスロットタイプ(引数型のようなイメージ)を数値にしておけば数値として聞き取るというような動きになる。

developer.amazon.com

この部分は本当にチュートリアルの通りにやれば出来てしまうので割愛。定義をGitHubにあげてあるのでそれを貼っておく。 github.com

AWS Lambdaの関数を作成してとりあえずカスタムスキルを動かす

まずは、チュートリアルにある通りにalexa-skill-kit-sdk-factskillをベースにbabyNoteのような名前で関数を作成し、作ったスキルと関数を紐付ける。 developer.amazon.com

いったん、下記のような感じでindex.jsを書いたらAmazon Echoに対するコマンドによってLambdaの関数が動いて関数の中で作られた応答がEchoから返ってくることが確認できるようになる。

"use strict";
const Alexa = require('alexa-sdk'); // Alexa SDKの読み込み
const gasAccessor = require('./gas-accessor');

const handlers = {
    // インテントに紐付かないリクエスト
    'LaunchRequest': function () {
        console.log('Processing LaunchRequest');
        this.emit('AMAZON.HelpIntent');
    },
    // スキルの使い方を尋ねるインテント
    'AMAZON.HelpIntent': function () {
        console.log('Processing HelpIntent');
        this.emit(':ask', 'うんち、おしっこ、おっぱい、ミルクが記録できます。何をしますか？');
    },
    'RegisterUnchiIntent': function () {
        console.log('Processing RegisterUnchiIntent');
        this.emit(':tell', 'うんちを記録しました。');
    }
};

// Lambda関数のメイン処理
exports.handler = function (event, context, callback) {    
    var alexa = Alexa.handler(event, context); // Alexa SDKのインスタンス生成
    alexa.appId = process.env.APP_ID;
    alexa.registerHandlers(handlers); // ハンドラの登録
    alexa.execute();                  // インスタンスの実行
};

const handlersの中に個々のインテントに対するハンドラーを記述する。
this.emitでAlexaに喋らせることができる。第一引数が:tellの場合は単に返答するだけ、:askの場合はもう一度ユーザの回答を受付け、その回答を元に再度インテントを解釈してハンドラーが動くというような流れになる。
詳しくは第三回のチュートリアルを参照。

Alexaスキル開発トレーニングシリーズ 第3回 音声ユーザーインターフェースの設計 : Alexa Blogs

Alexaコンソールの「テスト」タブを開き、テストを有効にすることでAlexaシミュレータおよび本物のEchoデバイスでスキルを試すことが出来る。
「アレクサ、育児ノートでうんちを記録して」のように話しかけると「うんちを記録しました」と返ってくるはず。
ここまででAlexaに特有な部分はクリアできたので、中身の実装に入っていく。

Google Apps Scriptの作成

Spreadsheetの作成

まずはGoogle Spreadsheetを作成する。

こんな感じでデータを記録するシートを作成し、シート名をrecordsとした。このシートが永続化層。
図はデータが挿入されたあとのもの。
f:id:miyataro32:20180503114814p:plain

また、プレゼンテーション層もSpreadsheetで作成する。スマホアプリのSpreadsheetで見やすいように縦長に作り、シート名はdashboardとした。すごく手軽！

f:id:miyataro32:20180503114824p:plainf:id:miyataro32:20180503114817p:plain
  • 左のスクリーンショット部分のデータはrecordsの情報をもとにGoogle Apps Scriptで動的に作っている。
    イベントの登録っていうのはAlexaではなくGoogle Formsで情報登録できる画面へのリンクで、これについては別の記事で書きます。
  • 右のスクリーンショットの部分はSpreadsheetの標準機能の関数とグラフで作っている。
    回数のカウントは=COUNTIFS(records!$A:$A, YEAR($B63)&"/"&MONTH($B63)&"/"&DAY($B63), records!$C:$C, "="&C$62)みたいな感じ。

Google Apps Scriptの作成

作成したSpreadsheetの上部メニューバーからスクリプト エディタを選択することでGoogle Apps Scriptのプロジェクトが作成される。
f:id:miyataro32:20180503120711p:plain

Google Apps Scriptはプロジェクトというのが実行環境の一単位となっており、プロジェクトを特定のコンテナ(今回で言うとさっき作成したSpreadsheet)に紐付けるとExcel VBAのような使い方ができるようになるらしい。
Spreadsheetからスクリプト エディタを選択するだけでプロジェクトの作成およびSpreadsheetとの紐付けを自動でやってくれる。

プロジェクト名はbaby-noteなど、適当に設定しておく。

このスクリプト エディタを使ってソースコードを書いていく。とりあえず基本的なことを書いておく。

  • スクリプトファイルの拡張子はgs。ほとんど(node.jsとかではなく)素のJavascriptで、SpreadsheetAppのようなコンテナを操作するためのクラスが用意されているものというざっくりした認識
  • 複数のgsファイルを作ることが出来るが、特にrequire的なことをしなくても全部のファイルが読み込まれ、その中で定義した関数や変数はすべてグローバルスコープとなる模様
  • 名前空間を切って関数や変数を整理することはできる(というかほぼ必須である)が、records.getRecords()のようなグローバルスコープでない関数は外部からAPI経由で叩いたり何らかのトリガーで実行したりすることはできず、そういうことがしたい場合はグローバルスコープでfunction getRecords()のように宣言する必要がある。

recordsに対するデータ追加

recordsシートに対するCRUDはrecords.gsというファイルを作ってまとめた。また、内部でしか使用しないものはrecordsという名前空間を切ってまとめた。そのうちの一部がこれ。

var records = {};

records.getSheet = function () {
  if (!records.sheet) {
    records.sheet = SpreadsheetApp.getActive().getSheetByName('records');
  }
  return records.sheet;
}

records.appendJournalRecord = function (type, opt_parameter) {
  var startTime = Date.now();
  
  var date = new Date();
  var row = [];
  row.push("'" + date.toLocaleDateString());
  row.push("'" + date.toLocaleTimeString().replace(/[^:0-9]/g, ''));
  row.push(TYPE_NAME[type]);
  if (opt_parameter) {
      row.push(opt_parameter);
  }
  
  records.getSheet().appendRow(row);
  
  var executionTime = Date.now() - startTime;
  Logger.log('appendJournalRecordWithSpecificDate took ' + executionTime + ' ms');
};

Spreadsheetに対する単純なデータの追加、読み出しはSpreadsheetApp.getActive().getSheetByName('records')のようにして取得したSheetオブジェクトに対してappendRow、getValueすることで行う。
基本的なことはここが分かりやすかった。
qiita.com

そして、外部から使用できる関数を下記のように作成する。

function registerUnchi() {
  var startTime = Date.now();

  records.appendJournalRecord(TYPE.UNCHI); // 新しいレコードを追加
  
  var values = {unchiCount: records.countRecords(TYPE.UNCHI, new Date())}; // 今日のうんちの回数をカウントしてオブジェクトに詰める
  Logger.log('registerUnchi : ' + JSON.stringify(values));
  
  var executionTime = Date.now() - startTime;
  values.executionTime = executionTime;
  Logger.log('registerUnchi took ' + executionTime + ' ms');
  return values; // 今日のうんちの回数が入ったオブジェクトを返す
}

普通にオブジェクトを作ってreturnすることでAPIのレスポンスにデータを入れることができる。
今回はうんちを登録し、本日何回目のうんちかという情報を返すようにした。

これで作った関数をテストできる。
画面上部で関数名を選択して実行ボタンをクリックするとrecordsシートに行が追加される。初回のみSpreadsheetへのアクセスの許可が必要になる。
f:id:miyataro32:20180503134925p:plain
実行後に表示 > ログをクリックするとLoggerで出力したログを見ることができる。今のところこれが最も有力なデバッグ方法。

dashboard用の処理の話も書こうかと思ったけど長くなるので割愛。
作ったGoogle Apps Scriptの全量はこちら github.com

AWS LambdaからGoogle Apps Scriptを叩くための設定と実装

Google Apps Scriptの関数を外部から叩ける用にする設定

これが結構ややこしかった。
とりあえずここに書いてある通りにすればだいたい行けるはず。 qiita.com

OAuth 2.0 Playgroundでトークン取得後にAPIを試してみる時のrequest bodyは下記のようにする。

{
  "function": "registerUnchi",
  "parameters": [],
  "devMode": false
}

devModeはtrueにしたほうが便利なのは間違いないのだが、下記で報告されている問題にぶち当たってしまい、かなりハマった。
原因は不明だが、devModeをtrueにしていてAPIを叩くと404が返ってくる場合にはfalseにするのが手っ取り早い。 stackoverflow.com

Lambda側でGASのAPIを叩く処理を実装する

ここから先はLambdaのインラインコード編集ではなく、アクション > 関数のエクスポートで落としてきたソースコードをローカルで編集する。
npmを使いたいのと、ライブラリを組み込んだ結果サイズが増えすぎてインラインコード編集ができなくなってしまったため。

まずはターミナルを開き、ダウンロードしたソースコードのルートディレクトリでnpm install googleapis@25.* --saveを実行してgoogleのapiを叩くためのライブラリを入れる。

そして、下記のようにGASのライブラリを叩くモジュールgas-accessorを実装する。

const google = require('googleapis');
const OAuth2 = google.auth.OAuth2;

const CLIENT_ID = process.env['CLIENT_ID'];
const CLIENT_SECRET = process.env['CLIENT_SECRET'];
const ACCESS_TOKEN = process.env['ACCESS_TOKEN'];
const REFRESH_TOKEN = process.env['REFRESH_TOKEN'];
const SCRIPT_ID = process.env['SCRIPT_ID'];
const DEV_MODE = process.env['DEV_MODE'] ? /^true$/i.test(process.env['DEV_MODE']) : false;

const gasAccessor = {};

gasAccessor.executeFunction = function (functionName, callback, opt_parameter) {
    var startTime = Date.now();
    
    console.log('executeFunction started [functionName=' + functionName + ', parameter=' + opt_parameter);
    const auth = new OAuth2(CLIENT_ID, CLIENT_SECRET);
    auth.setCredentials({
        access_token: ACCESS_TOKEN,
        refresh_token: REFRESH_TOKEN
    });
    const script = google.script('v1');
    script.scripts.run({
        auth: auth,
        scriptId: SCRIPT_ID,
        resource: {
            function: functionName,
            parameters: [opt_parameter],
            devMode: DEV_MODE
        }
    }, (err, result) => {
        var turnAroundTime = Date.now() - startTime;
        console.log(functionName + ' API execution took ' + turnAroundTime + ' ms');
        if (err) {
            console.error(err);
        } else {
            console.log(result.data.response.result);
            callback(result.data.response.result);
            var callbackExecutionTime = Date.now() - startTime - turnAroundTime;
            console.log('callback execution took ' + callbackExecutionTime + ' ms');
        }
    });
};

module.exports = gasAccessor;

認証系の情報はLambdaの環境変数として受け取るようにしている。
また、executeFunctionではGASの関数名とcallback関数を引数で受け取ってGASの関数の処理が帰ってきたら任意のcallback処理を実行できるようにしてある。
GASの関数の返り値はresult.data.response.resultに入っており、これをcallback関数に渡すようにしてある。

これをindex.jsでこのように使う。

const gasAccessor = require('./gas-accessor');

const handlers = {
    'RegisterUnchiIntent': function () {
        console.log('Processing RegisterUnchiIntent');
        gasAccessor.executeFunction('registerUnchi', function (result) {
            this.emit(':tell', '本日' + result.unchiCount + '回目のうんちです');
        }.bind(this));
    }

これでregisterUnchiをAPI経由で実行して、registerUnchiから返ってきたうんち回数を含むメッセージをAlexaに渡すことができる。

index.jsが存在する階層をzipで固めてLambdaにアップロードすることでLambdaのソースコードを更新できる。
その際、zipには親のフォルダを含めないよう注意する。 stackoverflow.com 自分はいちいち気を使ってzip作るのめんどいのでシェルスクリプトを書いた。 github.com

Lambda上ではこのように環境変数を設定する。 f:id:miyataro32:20180503142823p:plain

これで、Alexaコンソールのテストで「育児ノートでうんちを記録」と入れると「本日○回目のうんちです」と返ってくるはず。
返ってこなかったときはGASのスクリプトエディタやLambdaで原因を調査する。

ちなみに、Lambdaではテストイベントというのを作ってテストをすることができる。
Alexaコンソールに表示されるこいつを f:id:miyataro32:20180503144350p:plain Lambdaのここから入力してテストボタンを押せばAlexaを介すことなくテストができて便利。 f:id:miyataro32:20180503144612p:plain

まとめ

とりあえず、これでAmazon echo dotとGoogle Spreadsheetで育児記録をつけられるようになった。
ただ、今は１ヶ月検診がまだなのでずっと家にいるから常にAmazon echo dotが使えるが、出先でおむつ交換したときにどうするかという課題がある。
何回かすでに言及したが、それについてはGoogle Formを使って解決してみたので後日まとめます。

回線速度をZabbixでモニターする

背景

最近、自宅のケーブルテレビ回線を増速しました。ただ、ネットで調べると増速しても回線品質が悪いとなかなかの評判なので、実効速度をモニターしてあまりに遅いようなら光回線とか検討しようかなーと思い、Zabbixでダウンロードの速度を計測することにしました。

自宅のマンションが無料で8Mbpsのケーブルテレビ回線を使えるから使っていたんですが、8Mbpsだと下りは1〜7.5Mbps、上りは100kbpsぐらいで結構ストレスがたまります。2000円弱で30Mbpsにできるそうなので試してみようかなと。その上は4000円弱で160Mbpsだそうで、それなら光にした方がいいかなーというところです。

Zabbixで回線速度をモニターする

外部チェックスクリプトの作成

curl -w "%{speed_download}"

#!/bin/bash

SINGLE_DL_TIMEOUT=7
ZABBIX_TIMEOUT=30

FILES_TO_DOWNLOAD=()
FILES_TO_DOWNLOAD+=(http://www.gomplayer.jp/img/sample/mp4_h264_aac.mp4)
FILES_TO_DOWNLOAD+=(http://www.gomplayer.jp/img/sample/mp4_mpeg4_aac.mp4)
FILES_TO_DOWNLOAD+=(http://ec.nikkeibp.co.jp/nsp/dl/05405/05405_08.zip)
FILES_TO_DOWNLOAD+=(http://ec.nikkeibp.co.jp/nsp/dl/05405/05405_13.zip)
FILES_TO_DOWNLOAD+=(http://wwwjp.kodak.com/JP/images/ja/digital/cameras/dc120/image031.tif)
FILES_TO_DOWNLOAD+=(http://www.sharp.co.jp/galileo/guide/movie/sample/sample2_k.mpg)
FILES_TO_DOWNLOAD+=(http://homepage1.nifty.com/trust-system/SAMPLE_koriyama.LZH)

############################

cnt=0
sum=0

dl_num=$(expr \( ${ZABBIX_TIMEOUT} - 1 \) / ${SINGLE_DL_TIMEOUT})

function calc {
  if [ ${cnt} -gt 0 ]; then
    expr ${sum} / ${cnt} 2>/dev/null
  else
    echo 0
  fi
  exit
}

trap calc EXIT

files_to_process=()
while [ ${#files_to_process[@]} -lt ${dl_num} ] && [ ${#files_to_process[@]} -lt ${#FILES_TO_DOWNLOAD[@]} ]; do
  index=$((RANDOM % ${#FILES_TO_DOWNLOAD[@]}))
  file=${FILES_TO_DOWNLOAD[${index}]}
  echo "${files_to_process[@]}" | grep "${file}" > /dev/null
  if [ $? -eq 0 ]; then
    continue;
  fi
  files_to_process+=(${file})
done

for file in ${files_to_process[@]}; do
  speed_Bps=$(curl -kL -m ${SINGLE_DL_TIMEOUT} "${file}" -o /dev/null -w "%{speed_download}" 2>/dev/null)
  speed_bps=$(expr ${speed_Bps%.*} \* 8)
  if [ $speed_bps -gt 0 ]; then
     sum=$(expr ${sum} + ${speed_bps})
     cnt=$(expr ${cnt} + 1)
  fi
done

Zabbixでアイテムを設定

f:id:miyataro32:20171011022519p:plain

PowerShellを使わずにWinRMでコマンド実行する

WinRM Python

背景

社内で使っているWindows10の共有端末のなかにバッチが置いてあって業務の中で頻繁に叩く必要がある。現状では皆がリモートデスクトップ接続して実行しており、色々鬱陶しいのでいい加減やめたかった。

ログイン情報は基本的に社内の全員が知っているぐらいの勢いなので秘匿する必要なし。

クライアントはWindows7か10でPowerShellが入ってなかったりPATH通ってなかったりもする。

手順

サーバ側

  • PowerShellを管理者権限で実行する
  • WinRMを有効化する
winrm qc
  • PowerShellのウィンドウを閉じてもう一度管理者権限で開き直す（何故か分からないが必要だった）
  • Basic認証と平文を許可する
# Basic認証
winrm set winrm/config/service/auth '@{Basic="true"}'

# 平文
winrm set winrm/config/service '@{AllowUnencrypted="true"}'

これでサーバー側は完了です。Firewallの穴あけとか別途必要な場合はあるかもしれません。

クライアントのツールを作る

今回はクライアントにPowerShell入ってない場合があるのと、PowerShell入ってたとしてもWindows7をサポートしようとすると色々面倒なのと、そもそも自分のWindows10でIPアドレス指定でHTTP経由のWinRMへの接続がうまく行かなかったのとでPythonでやっちゃったほうが早いという結論に達しました。
今回はPythonでWinRMクライアントとして動くスクリプトを作成した上で、py2exeを使ってexe化して配布しています。

Pythonでクライアントツールを作成

  • Pythonをインストール（Pythonは32bit版だとpy2exeで作る配布物を1つのexeにまとめることができるのでお薦めです）
    今回は2.7の32bit版をインストールしました。 www.python.org
  • pywinrmをインストール
pip install pywinrm

#!/usr/bin/env python
import winrm
import sys
s = winrm.Session(sys.argv[1], auth=(sys.argv[2], sys.argv[3]))
r = s.run_cmd(sys.argv[4], sys.argv[5:])
print r.std_out

python winrmclient.py 192.168.1.1 username password dir

作ったスクリプトをexeにする

from distutils.core import setup
import py2exe

option = {
  "compressed" : 1,
  "optimize" : 2,
  "bundle_files" : 1,
  "include" : ["winrm"]
}

setup(
  options = {
    "py2exe" : option
  },
  console = [
    {"script"   :    "winrmclient.py"}
  ],
  zipfile = None
)
  • exeを作成
python setup.py py2exe
  • distフォルダの中にできたexeをテスト
winrmclient.exe 192.168.1.1 username password dir

完成です。こいつをbatから呼び出して使う予定です。

Zabbixの通知をLINEに送る

bash Zabbix

背景

ちょっと前にLINE NotifyというLINEのサービスが話題になりました。 developers.linecorp.com

これを使うと非常に簡単にシステムからLINEにメッセージを送れるのですが、Zabbixのような監視サーバのアラートをLINEに送るっていう使い方は結構需要があるのではないかと思い、LINE Notifyを使ってZabbixのアラートを送るalertscriptを実装してみました。

前準備

最初にLINE Notifyの設定をする必要があります。
下記のページにアクセスし、LINEのIDでログインすると「トークンを発行する」ボタンがあります。
https://notify-bot.line.me/my/

トークンを取得し、どこかにメモしておいてください。

Zabbixの設定

詳しく知りたい方は公式ドキュメントを参照してください。 5 Custom alertscripts [Zabbix Documentation 3.0]

alertscriptの配置

トークンとメッセージのタイトル、メッセージの内容を引数に取るスクリプトを作成し、/usr/lib/zabbix/alertscriptsに配置します。
LINE Notifyとの通信にはcurlを使います。詳しくは以下のサイトを参照してください。

以下はスクリプトのサンプルです。
curlからのレスポンスをjqで読み取って終了ステータスをきちんと返すようにしましたが、Zabbixは終了ステータスを見てくれないみたいです。。

#!/bin/bash

token="$1"
subject="$2"
body="$3"

LINE_API_URI='https://notify-api.line.me/api/notify'
LOG_FILE=${0%.*}.log

function write-log() {
  echo "$(date '+%Y/%m/%d %H:%M:%S') $1" | tee -a ${LOG_FILE}
}

write-log "token=${token}"
write-log "subject=${subject}"
write-log "body=${body}"

if [ -n "${subject}" ] && [ -n "${body}" ]; then
  message=\
"${subject}

${body}"
elif [ -n "${subject}" ] || [ -n "${body}" ]; then
  message="${subject}${body}"
else
  exit 1
fi

cmd="curl -X POST -H 'Authorization: Bearer ${token}' -F 'message=${message}' ${LINE_API_URI}"
write-log "executing: ${cmd}"

response=$(eval "${cmd}" | jq -Mc ". +  {"exit_code": $?}")
write-log "response: ${response}"

exit_code=$(echo "${response}" | jq -Mc '.exit_code')
response_status=$(echo "${response}" | jq -Mc '.status')
response_message=$(echo "${response}" | jq -Mc '.message')

if [ ${exit_code} -gt 0 ]; then
  exit ${exit_code}
fi

if [ ${response_status} -ne 200 ]; then
  echo "status : ${response_status}"
  echo "message: ${response_message}"
  exit 1
fi

exit 0

メディアタイプにカスタムスクリプトを登録

  1. Zabbixにブラウザでアクセスし、[管理] - [メディアタイプ] を開き、[メディアタイプの作成]をクリックします。
  2. 以下のように項目を入力し、[追加]ボタンを押します。
項目 入力値 説明
名前 LINE Notify メディアタイプの名前
タイプ スクリプト
スクリプト名 line_notify.sh alertscriptフォルダ配下のスクリプトのファイル名
スクリプトパラメータ {ALERT.SENDTO} 第一引数。ユーザ設定で送信先に指定した値が入る。
{ALERT.SUBJECT} 第二引数。メッセージのタイトル。
{ALERT.MESSAGE} 第三引数。メッセージの内容。

ユーザーの設定

  1. Zabbixで[管理] - [ユーザー]を開き、LINE Notifyで通知を送りたいユーザーの設定画面を開きます。
  2. [メディア]タブを開き、[追加]をクリックします。
  3. [タイプ]に新しく追加したメディアタイプの名前を指定します。
    [送信先]にLINE Notifyのトークンを指定します。 その他の項目は適宜設定してください。
  4. 更新ボタンをクリックします。

後はアクション等の設定が入っていれば、これでLINEに通知が行くようになっているはずです。

まとめ

ZabbixのアラートをLINEに飛ばす方法でした。
私が以前勤めていた会社では携帯にメールで飛ばす感じだったんですが、今の世の中だとLINEに送ったほうがやりやすい職場も多いのではないかと思います。そのうちLINE Business Centerを使ったやり方も試してみたいです。