Google スプレッドシートで変化があったらメールで通知する

問題

Google スプレッドシートに新しい値が追加された際、前回の値と比較して一定以上の変化があったらメールで通知したい。

解決法

スプレッドシートに対してGoogle Apps Scriptを割り当て、新しい値が追加される毎に前回の値との差分を求め、閾値を超えるようであればメールで通知するようにする。

材料

• Google スプレッドシート
• Google Apps Script

これは、IoTツールキット「MESH」のタグの1つ「温度・湿度タグ」から「IFTTT」を経由して1分間毎にスプレッドシートに記録されたデータの例です。このように、センサで取得されたデータに限らず、新しい値が追加される度に新しい行が追加され、チェックの対象とする値が特定の列にあることがわかっていれば、どのようなデータに対しても適用できるでしょう。

Triggered At	Temperature
January 8, 2017 at 03:47PM	23
January 8, 2017 at 03:48PM	22.9
January 8, 2017 at 03:49PM	22.6

MESHアプリでの設定例

IFTTTでの設定例

実装

1. スプレッドシートを開く
2. ［ツール］メニュー→［スクリプト エディタ…］でスクリプトエディタを開く
3. スクリプトエディタに以下のスクリプトを入力し、保存する
4. ［リソース］メニュー→［現在のプロジェクトのトリガー］で「時間主導型」（time based）のイベントとして適切な間隔でonTimeBasedTriggerを実行するように指定する
5. スプレッドシートに新しい行を追加して動作を確認する

トリガー設定時の画面例（この例では動作確認用に1分ごとに設定している）

サンプル

function onTimeBasedTrigger(event) {
  // 閾値
  const threshold = 5.0;

  // 現在ブラウザで開いているシートを取得する
  var sheet = SpreadsheetApp.getActiveSheet();

  // スクリプトのプロパティから前回処理した行を取得
  var scriptProperties = PropertiesService.getScriptProperties();
  var processedRow = scriptProperties.getProperty('processedRow');

  // もしプロパティに登録されていなければ値を初期化
  if (processedRow == null) {
    processedRow = 1;
    scriptProperties.setProperty('processedRow', processedRow);
  }

  // もし行数が2未満の場合には最小限のデータが揃っていないので以下の処理は行わない
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    return;
  }

  // 前回から増えた行数を求め、増えていなければ以下の処理は行わない
  var updatedRows = lastRow - processedRow;
  if (updatedRows < 1) {
    return;
  }

  // 前回最後に処理した行とその後に更新された行から値を取得する
  var updatedValues = sheet.getRange(processedRow, 2, updatedRows + 1, 1).getValues();

  // 値の間でそれぞれ差分を求める
  for (var row in updatedValues) {
    // 最初の行は前回の値なのでそれ以降であれば以下を実行
    if (row > 0) {
      // 前回の値と今回の値
      var lastValue = updatedValues[row - 1][0];
      var value = updatedValues[row][0];

      // 前回と今回の差分値を求める
      var differenceValue = value - lastValue;

      // 差分値の絶対値が閾値以上であれば
      if (Math.abs(differenceValue) >= threshold) {
        // 変化が起きたことをメールで送信する（差分値は小数点以下第1位までに四捨五入）
        MailApp.sendEmail('yourname@yourdomain',
          '温度変化に関する通知です',
          '温度に' + (Math.round(differenceValue * 10) / 10) + '度の変化がありました');

        // 短時間に繰り返しメールを送信することを避けるためループを抜ける
        break;
      }
    }
  }

  // 処理の終わった行の3列目に'done'を書き込む
  sheet.getRange(lastRow, 3).setValue('done');

  // 前回処理した行の値を更新する
  scriptProperties.setProperty('processedRow', lastRow);
}

ディスカッション

スプレッドシートには、編集時や値の変更時に関数を呼び出す仕組み「Simple Triggers」があらかじめ用意されています。しかしながら、これらのイベントはウェブブラウザからスプレッドシートを編集した時のみ発生し、今回のように別のウェブサービスからスプレッドシートを更新した場合には発生しません。このため、1分ごとにスプレッドシートをチェックし、必要に応じて追加された行に対して処理を実行するようにしています。

また、このサンプルではどこまで処理したのかを記録するためにPropertiesServiceを利用し、最初にgetProperty(key)で読み取り、最後にsetProperty(key)で更新しています。これは、Google Apps Scriptがサーバサイドで実行され、実行が終了した後は保持されないため、関数の外に変数を記載しても、その値が保持されないためです。例えば、以下のようなスクリプトを書いてtest()を実行すると、毎回変数aの値は123.0になります。

var a = 123;

function test() {
  Logger.log(a);
  a = a + 1;
}

認証で使用するキーなど、毎回変化しないものには使えますが、スクリプトの実行間で保持したい値に関してはPropertiesServiceなどを利用してスクリプト外で保持する必要があります。

リファレンス

• Google Apps Scriptウェブサイト
• 掌田津耶乃『Google Apps Scriptパーフェクトガイド』Amazon Services International, Inc.（2016年）