「仕事完了！ でも報告メールは面倒くさい」――GASで実現する報告メール自動化術：働く価値を上げる“VBA／GAS”術（14）（3/3 ページ）

[高橋宣成，プランノーツ]

イベントオブジェクト

　トリガーで関数が呼び出された際に、イベント発生に関する情報を含むオブジェクト「イベントオブジェクト」を受け取れます。このイベントオブジェクトは、一般的に「e」という仮引数が用いられます。例えば、インストーラブルトリガーの「スプレッドシートから」→「編集時」で設定したトリガーで動作した場合は、以下のプロパティを持つオブジェクトを受け取れます。

• source：スクリプトがバインドしているSpreadsheetオブジェクト
• range：編集のあったRangeオブジェクト
• value：編集後のセルの値（単一セルのときのみ）
• oldValue：編集前のセルの値（単一セルのときのみ）

　関数onChangeStatus(e)の以下の部分は、編集のあったRangeオブジェクトと、その編集後の値を取得しています。

var cell = e.range;
var status = e.value;

Rangeオブジェクトのシート、列番号、行番号を取得する

　続いて以下の部分を見ていきます。

var sheet = cell.getSheet();
var column = cell.getColumn();
var row = cell.getRow();

　変数cellはRangeオブジェクトです。Rangeオブジェクトに対して、「getSheet()」メソッドを使うと、そのセル範囲が含まれるSheetオブジェクトを取得できます。

Rangeオブジェクト.getSheet()

　今回の場合、イベントオブジェクトの「source」プロパティから、配下のSheetオブジェクトをたどれますが、シートが複数あった場合、シート名またはシート順を指定する必要があります。一方で、Rangeオブジェクトが含まれるSheetオブジェクトは一意に決まるので、その他の情報がなくても確定できます。

　また、「getColumn()」「getRow()」メソッドは、そのRangeオブジェクトの列番号、行番号を取得するメソッドです。

Rangeオブジェクト.getColumn()

Rangeオブジェクト.getRow()

　Rangeオブジェクトが範囲である場合は、最も左上にあるセルの情報を取得します。これら取得した行番号、列番号を使用して、トリガーを起動すべき対象が範囲内であるか判定します。範囲内かつその編集後の値が「完了」であれば、別の関数sendEmail(row)を呼び出します。

配列からNo.とタスク名を取り出す

　続いて、関数sendEmail(row)を見ていきましょう。まず、冒頭の部分です。

  var FIELDS = {
    no: 0,
    task: 2,
    status: 4
  }
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var index = row - 1;
  var myNo = values[index][FIELDS.no];
  var task = values[index][FIELDS.task];

　オブジェクト「FIELDS」は、VBAでいう列挙型のような仕組みを実現し、後ほど取り扱う二次元配列の「列番号にあたるインデックス」を指定するためのものです。これを作ることで、後ほどToDoリストのテーブルの列構成に変更があったとき、この部分を変更するだけで対応できるので、メンテナンス性が高まります。

　以降の部分で、FIELDSを用いてNo.、タスクの値をそれぞれ取得しています。なお、関数onChangeStatus(e)から受け取った行番号「row」をマイナス1しているのは、配列のインデックスが1からではなく、0から始まるためです。

Gmailでメールを送信する

　その後の処理で、宛先「recipient」、件名「subject」、本文「body」を生成します。GASでは、この3要素さえあれば簡単にメールを送信できます。

　以下のように、GmailサービスのグローバルオブジェクトGmailAppの「sendEmail()」メソッドを使います。

GmailApp.sendEmail(宛先, 件名, 本文)

インストーラブルトリガーの設置

　これでスクリプトが完成するので、続いてトリガーを設置します。スクリプトエディタのツールバーにある「現在のプロジェクトのトリガー」アイコンをクリックします。この動作は、メニューの「編集」→「現在のプロジェクトのトリガー」でもいいです。

図3　現在のプロジェクトのトリガー

　すると、「G Suite Developer Hub」の画面が開くので、右下の「＋トリガーの追加」をクリックします。

図4　G Suite Developer Hub

　トリガーの追加画面が開くので、各項目を下記のように選択して「保存」します。

• 実行する関数を選択：onChangeStatus
• イベントのソースを選択：スプレッドシートから
• イベントの種類を選択：編集時

図5　トリガーを追加

　これで、トリガーの設置は完了です。設置したトリガーを確認できます。

図6　設置したトリガー

　なお、イベントトリガーの動作では、スプレッドシートやGmailへのアクセスを許可できません。そのため、この時点で一度、手動で実行して、アクセス許可を通します。

図7　手動でスクリプトを実行

全体を確認

　解説は以上です。再度、ToDo完了メール送信ツールのスクリプトを確認しましょう。まず、トリガーで呼び出されて実行される関数onChangeStatus(e)です。

function onChangeStatus(e){
  var cell = e.range;
  var status = e.value;
  var sheet = cell.getSheet();
  var column = cell.getColumn();
  var row = cell.getRow();
  var lastRow = sheet.getLastRow();
    
  if(column === 5 && row >= 2 && row <= lastRow && status === '完了'){
    sendEmail(row);
  }
}

　以下が、ステータスが「完了」となったときを条件に呼び出される関数sendEmail(row)です。

function sendEmail(row){
  
  var FIELDS = {
    no: 0,
    task: 2,
    status: 4
  }
  
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var index = row - 1;
  var myNo = values[index][FIELDS.no];
  var task = values[index][FIELDS.task];
  
  var recipient = '[e-mail]';
  var recipientName = '皆様';  
  var subject = '';
  subject += 'タスク完了メール: [' + myNo + '] ';
  subject += task;
  
  var body = '';
  body += recipientName + '\n';
  body += '以下のタスクが完了しました\n';
  body += '[' + myNo + '] ' + task;
  
  GmailApp.sendEmail(recipient, subject, body);
  
}

最後に

　今回は、GASでToDoリストの完了メール送信ツールを作成しました。トリガーを使うことで、スプレッドシートを編集後、スクリプトを自動的に起動できます。他にもバリエーション豊かなトリガーが利用可能です。特に、時限式のトリガーは、クラウド環境のGASならではの機能で、定期的にスクリプトを実行させたいときに非常に便利です。ぜひ、活用してみてください。

著者プロフィール

高橋宣成

プランノーツ 代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。

書籍紹介

ExcelVBAを実務で使い倒す技術

高橋宣成著 秀和システム 1800円（税別）

動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。

詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜

高橋宣成著 秀和システム 2600円（税別）

Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。