Google Apps Script （GAS）で文書作成をどれだけ効率化できるか挑戦 Part4 ～トリガー～

新年あけましておめでとうございます。加藤です。

本年もどうぞよろしくお願いいたします。

シリーズPart4です。前回の記事はこちら。

tech.arms-soft.co.jp

（１）はじめに

スプレッドシートで条件付き書式を使うと見やすくなってよいのですが、ドキュメントの作成作業中に条件式を手で追加していく作業がとても面倒くさいのが悩みです。。。

作業の中でセルや行を増減させたりコピー＆ペーストしたりしていくと条件式に設定している参照行や範囲がだんだんズレてしまっていって、意図しない動きになってしまったときには悲しみに暮れました。

「代わりにGASが使えたりする…？そうすればもっとスマートに作業ができるのかも…？」と思い至りましたのでチャレンジします。

（２）GASのトリガー（イベントハンドラ）について

今回のポイントはイベントハンドラです。GAS公式ではトリガーと呼ぶので以降はそう呼びます。

GASには2種類のトリガーが用意されています。

Simple Triggers：ドキュメントを開いた時・編集した時などの基本的なトリガー。関数名も固定。
Installable Triggers：あらかじめ用意されているイベントの中から目的や用途に合わせて自作の関数をトリガーに設定できる。（例：ユーザ認証が必要な処理、定期的なイベント処理など）Simple Triggersよりも柔軟。

Installable Triggersはここではひとまず置いておいて、Simple Triggersに注目していきます。

（３）Simple Triggersについて

Simple Triggersに用意されているトリガーの関数は以下の通りです。

https://developers.google.com/apps-script/guides/triggers/?hl=ja

onOpen(event) ファイルを開いた時
onEdit(event) スプレッドシートを編集した時
onInstall(event) アドオンがインストールされた時
doGet(event) 外部からHTTP GET リクエストを受信した時
doPost(event) 外部からHTTP POST リクエストを受信した時

冒頭をナナメ読みですが、これだけのトリガーが用意されていることがわかりました。（ファイルを開いた時に動くイベントハンドラonOpen(event）は前回の記事のおまけで少しだけ触れています。）

今回はスプレッドシートの編集時にアクションを起こしたいので、onEdit(event)を使っていきます。

練習用にこのようなToDoリストをざっと作ってみました。

やりたいことは以下の2つです。

進捗を「作業中」に設定すると、セルの背景色が黄色になる
進捗を「完了」に設定すると、1行がグレーアウトされる

（４）関数作成

例のごとくスクリプトエディタを開いてonEdit()を作成しました。

// 編集した時に実行されるトリガー
function onEdit(event){
  
  Logger.log(event);
  
  var status = {
    working : '作業中',
    complete : '完了'
   }
  // 「進捗」列の列番号
  var status_column = 4;
    
  // (1)編集対象の取得
  // イベント発生時に開いているシートの選択
  var editSheet = event.source.getActiveSheet();
  // 編集対象範囲を取得
  var editRange = event.source.getActiveRange();
  
  // 編集された列番号を取得
  var columnIndex = editRange.getColumn();
  // 編集された行番号を取得
  var rowIndex = editRange.getRow();
  
  // （2）判定 - 進捗 
  // '作業中'
  if(editSheet.getRange(rowIndex, status_column).getValue() == status.working){
    // 背景色を設定 getRange(行番号,列番号,行数,列数)
    editSheet.getRange(rowIndex, status_column, 1, 1).setBackgroundColor('yellow');
    // '完了'  
  } else if(editSheet.getRange(rowIndex, status_column).getValue() == status.complete) {
    // 背景色を設定 getRange(行番号,列番号,行数,列数)
    editSheet.getRange(rowIndex, 1, 1, editSheet.getLastColumn()).setBackgroundColor('gray');
  } else {
    editSheet.getRange(rowIndex, 1, 1, editSheet.getLastColumn()).setBackgroundColor('white');
  }
}

「え、急に出てきたけれどevent.sourceとは一体…？？」と困惑しました。トリガーのパラメータであるeventオブジェクトには操作したイベントに関連する情報が格納されています。

https://developers.google.com/apps-script/guides/triggers/events

logを出してみると中身がわかりやすいです。操作したユーザーや編集前・編集後のValueなどが出ています。

eventの中身はトリガーによって少し違うようです。

onEdit()のeventオブジェクトには source と range が入っています。

sourceは編集対象のSheetオブジェクト、rangeは編集対象のRangeオブジェクトでしたので、それぞれgetActiveSheet()やgetActiveRange()で取得することができました。

Rangeに対してsetBackgroundColor()で背景色を設定すれば、条件付き書式もどきの完成です。

（４）注意点・今後の課題

公式ドキュメントにも記載がありますが、Simple Triggersには制限事項があります。

https://developers.google.com/apps-script/guides/triggers/#restrictions

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

トリガーはスクリプトやAPIで変化したものについてはイベント検知しない。

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

認証機能が必要なものはSimple Triggersでは不可能。

They cannot run for longer than 30 seconds.

30秒越えて実行はできない。

また、「進捗」とは別に「期日」が過ぎていたらフォントを赤字にする…というのも今回一緒に実装してみたかったのですが、ひとつのonEdit()内で処理を切り分けようとしたせいか、うまくいきませんでした…。

あまり一般的な方法ではないのでしょうか…？調査も含めて、次回チャレンジしてみたいです。

今回は以上です。