【GASで監視】シートが編集されたことを任意のタイミングでメール通知する

標準機能では即時と1日1回しか選べない「更新時の通知」を、任意のタイミングで行うGoogle Apps Scriptです。

こんな悩みを解決します

• シートが更新されたら通知してほしいが、更新の都度通知されるのは多すぎるし1日1回じゃ少なすぎる。
• 任意のタイミングで通知してほしい。

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• （1つ目の関数）シートを編集したときに、編集したユーザーのメールアドレスと変更前後の値をスクリプトプロパティに記録します。
• （2つ目の関数）スクリプトプロパティからログを取り出し、メールで通知します。メール送信後はスクリプトプロパティをクリア（削除）します。

使い方

1.事前準備

スクリプトの「事前設定エリア」を編集します。
変更通知を送りたいメールアドレスを入力してください。カンマ区切りで複数指定することも可能です。

2.トリガーの設定

まずは1つ目の「編集時に変更内容をスクリプトプロパティに記録する」関数について設定します。
編集をトリガーに動作してほしいのでこのような内容になります。

• 実行する関数: recording_to_property
• デプロイ時に実行: HEAD
• イベントのソース: スプレッドシートから
• イベントの種類: 編集時

次に2つ目の「スクリプトプロパティのログをメール通知する」関数についてもトリガーを設定します。
通知してほしい間隔で自由に設定しましょう。2時間おきの場合はこんな設定になります。

• 実行する関数: notify_edit_log
• デプロイ時に実行: HEAD
• イベントのソース: 時間主導型
• 時間ベースのトリガーのタイプ: 時間ベースのタイマー
• 時間の間隔: 2時間おき

3.動作確認

準備が終わったので問題なく動くか試してみましょう。
シートを編集してみます。

変更通知メールが届きました！
（テストのためトリガーを1分おきにしています）
空白のセルに書き込んだのでログの最後の部分（変更前の値）は空欄になっています。

では今のセルを書き換えてみます。

今度の通知メールには変更前の値も含まれていますね。

4.注意点

関数「notify_edit_log」では、メール送信後にスクリプトプロパティを全削除しています。
何らかの値をスクリプトプロパティに保存している場合、それも消してしまいますのでご注意ください。

また、プロパティの読み書きや最大サイズに制限があります。
読み書き: 50,000回/day（無償ユーザ）、500,000回/day（有償ユーザ）
サイズ: 9KB/件、500KB/合計（無償有償問わず）
詳しくはQuotas for Google Services | Apps Script | Google Developersをご確認ください。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00138/index.html
 */

function recording_to_property(e) {
  // セルの値が変わったときに実行される関数

  // 編集されたセルの値を取得
  const value = e.value;

  // 変更前の値を取得
  const old_value = e.oldValue;

  // 日時を取得
  const time = new Date().getTime();

  // 編集したユーザーのメールアドレスを取得
  const user = Session.getActiveUser().getEmail();

  // 配列にまとめる
  const data = [user, value, old_value];

  // スクリプトプロパティに接続
  const script_property = PropertiesService.getScriptProperties();

  // プロパティをセットする。keyに時間（UNIXTIME）、valueにJSON形式に変換したメールアドレス、編集前後の値を入れる。
  script_property.setProperty(time, JSON.stringify(data));

}


function notify_edit_log() {

  // ########## 事前設定エリア ここから ##########

  // 変更通知の送信先メールアドレスを入力してください。
  const mail_to = '***************';

  // ########## 事前設定エリア ここまで ##########


  // スクリプトプロパティにアクセスする
  const script_property = PropertiesService.getScriptProperties();

  // すべてのプロパティを取得する
  const properties = script_property.getProperties();

  // オブジェクトになっているので配列に変換する
  const array = Object.entries(properties);

  // プロパティが1件もない = 前回のメール通知から編集されていない場合は何もせず終了する
  if (array.length == 0) {
    return;
  }

  // メール通知する内容を格納する配列
  const logs = [];

  // プロパティの数だけループ
  for (let i = 0; i < array.length; i++) {

    // key（[0]）はUNIXTIMEが入っている。文字列になっているのでNumberで数字にしてnew Dateする。
    let datetime = Utilities.formatDate(new Date(Number(array[i][0])), 'JST', 'yyyy-MM-dd HH:mm:ss');

    // value（[1]）に編集したユーザーのメールアドレス、変更前、変更後の値が入っている。JSON形式のためparseして配列に戻す。
    let data = JSON.parse(array[i][1]).join(' / ');

    // 1件のログを1行にまとめて配列に入れておく
    logs.push(datetime + ' : ' + data);

  }

  // メール本文を組み立てる
  const message = 'スプレッドシートの編集履歴をお知らせします\n\n[日時] [編集ユーザー] [変更後の値] [変更前の値]\n' + logs.join('\n');

  // メールの送信処理
  GmailApp.sendEmail(mail_to, 'スプレッドシート変更通知', message);

  // スクリプトプロパティの削除処理
  // メール送信後は不要になるのでクリアする
  script_property.deleteAllProperties();

}

関連するアプリ