Google Apps Scriptの中で地味ながら、その活用の幅が実に広いものとして、スクリプトトリガーがあります。いわゆる時限式で作動させるための仕組みなのですが、スクリプトトリガーは、スクリプトエディタの画面から入り、手動で登録するのが通常のフローです。しかし、「スクリプト内でテンポラリで時間トリガーを設置したい」であったり「トリガーの設置し直し」など、スクリプトエディタにいちいち入らず設定したいシーンがボチボチあります。そういった場合には、スクリプトからトリガーの設置や削除が出来ると便利です。二重に登録してしまったりすると、二回発動したり、片方しか発動しなかったりするので、慎重に設置をしましょう。

これらトリガーは大きく分けて4種類あり、1.時限作動式 2.開いた時 3.編集時 4.フォーム送信時を設置することが可能です。主に使うのは時限作動式と、フォーム送信時の2つになります。但し、このスクリプトトリガーはちょっと癖があるので、そこだけは注意しとかなければなりません。

難易度:


使用するクラス、メソッド

トリガーの設置

スプレッドシート編

スプレッドシートでは、時間ベーストリガーの他にも、スプレッドシート特有のトリガーを設置する事が出来ます。使用することの出来る特有のトリガーは、「onChange」、「onEdit」、「onFormSubmit」、「onOpen」の合計4つとなります。それぞれ、「変更時」「編集時」「フォーム送信時」「起動時」の4つに該当します。以下に各トリガー設置のスクリプトを記載します。

onChange(変更時)

変更時とは、構造変更や内容変更が発生した時に発動するトリガーです。VBAで言う所のAfterUpdateイベントと言えます。

onEdit(編集時)

編集時とは、編集時に毎回発動するトリガーです。VBAで言う所のBeforeUpdateイベントと言えます

onOpen(起動時)

起動時とは、そのスプレッドシートが開かれた時に発動するトリガーである。通常はメニューの登録などでよく使われている。

フォーム編

殆ど、スプレッドシートと同じですが、Google Formで主に使うのは以下の1つのみ。このトリガーはスプレッドシート側でも使えなくはないですが、このトリガーをフォーム側ではなくスプレッドシート側で使うのは推奨しません。スプレッドシート側で使ったケースで、データがおかしく登録されたり、順番が狂ったり。。

制御するならフォーム側で使うべきです(なので、フォームで送信時に自動応答メール等実装する場合は、フォーム側でこのトリガーを使って装備しましょう)

onFormSubmit(フォーム送信時)

フォーム送信時とは、Googleフォームでユーザがフォームを送信する時に発火するトリガーです。このトリガーを使って処理を行う場合には、複数ユーザ同時送信であったり、処理のバッティングが起きないように、排他制御が必須になります。

時限作動式編

スクリプトトリガーで最も使用する機会が多く、また、設置できるパターンの多いトリガーです。結構細かく時間トリガーを設置することが出来ますが、あまりにも短い時間にバンバンデータを取り込むようなトリガーを設置してしまうと、GoogleのサーバーにBANを食らったり、正常に動作しなくなることも考えられるので、設置に当たっては気をつけましょう。また、このトリガーが設置されたものは、例えファイルがゴミ箱に行こうともトリガーは動き続けますので、捨てる場合には、トリガーを削除してから捨てるようにしましょう。

ここでこのトリガー使用上の注意点です。

  1. これらのトリガーは、すべて±15分のラグを持って作動するようなので、分単位での綿密なトリガー作動は期待してはいけません。
  2. 時間ベーストリガーの場合、SpreadsheetAppなどを使う時にはOpenByIdを利用するようにしましょう。getActiveSpreadsheetではエラーになる事があります。
  3. 同様の理由でgetuiなども無意味ですのでメッセージボックス関係などのコードは外しておきましょう。

ミリセカンド後に実行

単位がミリセカンド(1/1000秒)なので、通常は数秒後という形で設定して使う時間トリガーです。下記の例では10秒後に実行するよう設定しています。

特定日に実行

単位が特定日のスポット実行なので、日付型でデータを受け取っておき、atに続けて引数で渡してあげます。

特定日に実行(深夜に実行)

特定日に実行のものと殆ど同じですが、こちらは、深夜付近で実行がされるトリガーです。atDateに続けて引数で渡してあげます。

○○時に実行

引数で取った数字(時間)に従って、その時になったら発動するトリガー。このトリガーは、この後に出てくる別のトリガーと組み合わせて使用します。下記ではeverydaysと組み合わせて使用しています。サンプルの例の場合、3日置きに午前5時〜6時の間でトリガーが発動します。

毎日○○時間毎に実行

引数で取った数字(時間)に従って、毎日その時間にトリガーが発動します。下記の例では、毎日12時にスクリプトが実行されます。

毎分○○ごとに実行

引数で取った数字(分)に従って、○○分毎にトリガーが発動しつづけます。指定できる引数は、1分・5分・10分・15分・30分が指定可能です。

指定週毎に指定曜日の指定時刻に実行

everyWeeksで毎週なのですが、引数に指定した数字にて、2週毎といった指定ができます。また、この指定オプションは、必ず、指定時刻とどの曜日なのか?を指定するatHourとonWeekDayの指定が必要です。

タイムゾーンの指定

Googleドキュメント全般には、それぞれプロパティを見るとわかるのですが、アメリカ合衆国になっていたりすることがあります。スクリプトでもそれがあり、場合によっては、変更しておかないと妙な挙動をするスクリプトを作る羽目になります。このオプションは、そのタイムゾーンを指定することの出来るメソッドです。

トリガーの修正と削除

トリガーの修正・削除は、いずれもまずは削除から始まります。そして、修正だけはこの後に改めて設置をするメソッドを発行することになります。しかし、スクリプトトリガーは設置者以外のトリガーが見えなかったりするので、トリガー削除ルーチンがとても便利です。このルーチンの後に設置などのルーチンをつなげてあげれば、トリガーの設置し直しが完了するわけです。

トリガー設置時に、getUniqueIdでトリガーのIDを取得可能です。これをスクリプトプロパティに格納しておく事で、後で指定のIDのトリガーのみを削除する事が可能になります。次のコードがその特定のIDのトリガーのみを削除するコードです。但し、スクリプトプロパティに入った値は文字列となってしまうので、deleteTriggerに引数としてtriggerIdを渡す場合には、事前にNumber(triggerId)といった形で数値型にしておく必要があります。

しかし、このルーチンは、triggerIdが引数で必要であるため、自分の場合、引数をなくして、以下のようにし「トリガー全削除ルーチン」に改造して使います。

但し、当たり前ですが、トリガー全削除ルーチンは、根こそぎ全部削除してしまいますので、キメ細かくトリガーセッティングをしているケースでは、何か別の仕組みが必要になってくると思います。自分の場合、別にトリガーをワンボタンで設置する為のルーチンを用意しておいてあります。

もっと柔軟にトリガーを設置してみる

一見すると便利そうなスクリプトトリガーという機能ですが、実はめちゃめちゃ融通が効きません。細く設定できそうなトリガーの条件設定なのですが、モノすごくアバウトにしか設定できません。いい事例が、毎日10:30に発動とか、そういったことが出来ません。30分っていう設定は、分トリガーにしかなく、他は時間トリガーしかないわけです。そうなると、時間トリガーしかなく、割りと大きな情報収集トリガーを2本発動させた後にPDF化して送信というスクリプトを書くとなると、3時間後とかになってしまいます。そこで、これを何とかしようというのが今回の目的。

※尚、2本の情報収集トリガーは同時に発動すると、データが壊れるので時間を空けて挙げなければならない。

解決したい課題(自分の事例)

  • データは洗い替えで収集するので、毎回データ取得前には、データをクリアする必要性がある。(clearsheet関数を作成)
  • 取得したデータに対してFilterでフィルタしたものをPDFにしているので、日付データを毎日設定し直す。(filterday関数を作成
  • 日付と時刻指定トリガーを3つ設置する(本日の10:00, 本日の10:30, 本日の11:00)

作成するコードとトリガー

  • 全トリガー削除関数を作成する
  • データのクリア、日付の変更、全トリガーの削除、全トリガーの設置し直し(settingAllTrigger関数)という一連の作業を行うルーチン(specialdays関数とする)を作成する。このルーチンをトリガーとして、毎日AM1:00に発動するように設置する。この関数の中に、settingAllTrigger関数が最後に呼び出されるように含まれている)。
  • 上記ルーチンを実行するトリガー(settingAllTrigger関数とする)を作成する
  • ややこしいですが、settingAllTrigger関数もまた、全トリガー削除のルーチンの対象になります。
  • 但し、onOpen関数は毎回設置されていないとちょっと困る事情があるので、settingAllTrigger関数に含めて置きます。
  • 特定の日時でのトリガー設置の為に日付を構築する関数を用意してあげる。

で、結果的には・・・

毎日、AM1時にspecialdays関数が発動して全トリガーとデータの消去が行われた後、settingAllTrigger関数が呼ばれて新たに、以下のトリガーが設置されます
  1. onOpenのトリガー
  2. specialdays関数のトリガー(毎日AM1:00で設定)
  3. 本日の日付と特定時刻で設定されているAM10:00発動のデータ収集トリガー1本目
  4. 本日の日付と特定時刻で設定されているAM10:30発動のデータ収集トリガー2本目
  5. 本日の日付と特定時刻で設定されているAM11:00発動のPDF化&メール送信トリガー
の5本が、毎日設置され直されて継続していくわけです。こうすることで、非常に柔軟性のあるトリガーを設置して細かく挙動をコントロールすることが可能になります。

ソースコード

  1. このスクリプト群は、一度そのスプレッドシートのIDをスクリプトプロパティに格納しておく必要があるので、最初の1回だけsetup()を実行する必要性があります。
  2. 当たり前ですが、最初の1回だけは、手動でトリガー群を設置しないといけないので、settingAllTrigger()を実行する必要性があります。以降は、トリガーが勝手にやってくれるので、必要ありません。
  3. 特定の日付のトリガー用に、trigger1~3の変数を用意して、日付と時刻をセットしてあげます。

特定の日時のトリガーを作る上でのポイント

下記は、今日の日付の10:40の日付型の値をtrigger1変数に受け取るコードです。時刻部分を、ユーザに入れさせるように、何かギミックを用意するのも悪くありませんね。

下記は、受け取ったtrigger1の値をもとに、Script Triggerのat()をつかって、特定の日付・時刻を指定しています。

特定の日時で毎日トリガーを実行する

前述のトリガーは特定の日時なので、2021年2月10日10:30に実行といった事が出来ますが、その後のこのトリガーはもう使えません。使い切りのトリガーです。そこで、これにさらに一工夫を加えて毎日10:30に実行といったような形に改造をしようと思います。ポイントは

  1. 毎日特定のトリガーを設置するトリガーを用意する
  2. 毎日実行する本体のトリガーは実行したら自身を設置したトリガーを削除する

という点。よってこのトリガーの設置には2個のトリガーが必要になります。

  • setCustomTriggerは最初の1回のみ実行して、exeCustomTriggerを呼び出すようにセット(時刻は朝の5時にセット)
  • exeCustomTriggerが毎日実行される関数。当日の10:40分に実行されるsilentgetという関数を呼び出すトリガーをセットします。
  • この時、トリガーIDをスクリプトプロパティに格納しておく
  • silentget関数は毎日10:40分に実行後にセットされたトリガーを削除する
  • 実行後にスクリプトプロパティに格納されてるトリガーIDに基づいてトリガーを削除しています。

これで毎朝5時にトリガー発動で朝10:40分に発動するトリガーがセットされ、10:40分後にそのトリガーは削除されるという繰り返しが実現できます。

特定のセルを編集したら1分後にセルをクリアする

onEditとトリガーを駆使して、特定のセルを編集したら、1分後にそのセルの中身をクリアするといったコードを作ってみます。今回の場合、onEditのトリガーを予め設置しておく必要があるため、deleteTrigger()でトリガー削除をしてしまうと、onEditのトリガーまで消えてしまいます。

ですので、トリガー設置時にtrigger_idを取得して、特定のトリガーだけを削除しつつこの課題を実現してみます。予め、シートのトリガーに対して、onEditを編集時として設置しておく必要があります。

  • スプレッドシートが開かれていなくても、トリガー発動で消せるように、ssidを設定しておきます。
  • onEditで特定のシートの特定のセルだけ編集時にトリガー設置するように条件分岐
  • createTrigger()では、既に仕込み済みのクリアするトリガーを予め削除するようにしています。
  • トリガーは1分後にclearcell()を呼び出します。またこの時、trigger_idを取得し、スクリプトプロパティに格納しておく。
  • deltrigger()ではスクリプトプロパティに格納されているtrigger_idを持って、特定のトリガーのみを削除します。
  • clearcell()ではclearcontentでセルをクリア後に、トリガーを削除するようにしています。

図:事前にonEditのトリガーを仕込んでおく

営業日ベースでトリガーを発火させる

実際の業務の現場ではトリガーで自動処理は非常に便利な反面、休日にまでせっせとトリガーが発動して無駄に動作してしまうのは面倒です。そこで、「営業日ベースで指定の時刻に発火するトリガー」が必要になります。土日祝は処理を実行せず、またカレンダーにはないお休みの日なども対応させて、営業日にだけ動かせるようにしてみようと思います。

サンプルになるスプレッドシートはこちらです。

事前準備

自分のアカウントに日本の休日のカレンダーを追加しておきましょう。以下の手順で追加しておきます。

  1. Googleカレンダーを開く
  2. 左下のその他のカレンダーの横にある+ボタンをクリックし、カレンダーに登録をクリックする
  3. カレンダーに追加に、「ja.japanese#holiday@group.v.calendar.google.com」を追加する
  4. これで日本の祝日がカレンダーに表示されたと思います。

また、サンプルスプレッドシートを使う場合には、シートを開き、メニューにある「セットアップ」から初期化を実行し、同様にトリガー設置も実行しましょう。

スプレッドシートのお休みリストには日付と名前付きで土日祝以外の休みの日を記載します。

ソースコード

  • トリガー発火で実際に自動作業をする関数はexecuteWorkに記述します
  • トリガーでの呼び出しは基本毎日○時で発火でセットします。
  • トリガーで呼び出される関数であるworkdaychk関数で、土日か?祝日か?スプレッドシート記載の日付か?を判定し、どれにも合致しない場合、営業日として判定し、executeWorkを実行します。
  • Googleカレンダーの日本の祝日カレンダーに該当の日があった場合には祝日として判定して処理を終了させます。

今回は土日祝以外の日で休みとする場合には、お休みリストにある日付に記載して処理をしていますが、このリスト自体もGoogleカレンダーに専用のカレンダーを用意して、イベントを登録し、そのカレンダーIDを取得して日本の祝日と同様の処理を行えば、お休みリストをカレンダー上で管理が出来て便利です。

専用カレンダーで管理する場合、そのカレンダーIDが必要です。

専用のカレンダーを用意する

Googleカレンダーを開いて、お休みリスト用のカレンダーを用いて処理を行わせたい場合には、以下の手順でカレンダーを用意してカレンダーIDを取得します。スクリプトは自分自身の権限で動かしてるので共有等は不要です。

  1. Googleカレンダーを開く
  2. 左下のその他のカレンダーの横にある+ボタンをクリックし、新しいカレンダーを作成をクリックする
  3. お休みリストと名前をつけて、カレンダー作成をクリックする
  4. マイカレンダーに作られるので、開いてみると「カレンダー設定」があるのでクリックする
  5. 右サイドの下のほうにカレンダー統合があり、そこにカレンダーIDが記述されてるのでコピーする

前述のworkdaychk関数のうち、スプレッドシートに記載の日付と一致するものがあるか判定の部分を日本の祝日と同じコードに変えてあげる

  • oyasumiに取得したカレンダーIDを記述する
  • 当日にイベントが記載されていればその日は土日祝以外のお休みとして判定される

カレンダーには会社のお休みとして適当なイベントを登録しておくと良いでしょう。専用のカレンダーなので余計なイベントを入れてしまうと、休日判定されてしまうので注意。

図:新規にカレンダーを用意しておく

図:カレンダーIDを取得する

Class構文のStaticメソッドをトリガーで設置する

現在のGoogle Apps ScriptはV8 Runtimeに対応してるので、ES2019までのJavaScript構文が使えるようになりました。その結果として、スクリプトトリガーでも、Class構文の中にあるstaticメソッドで定義した関数をスクリプトからであれば、設置することが可能になっています。まだ、Google Apps Scriptのトリガー設置画面上からは設置は出来ません。

参考事例は以下の通りです。以下の例であれば括弧なしでmailman.tomatoと指定して設置すれば良いです。

 

図:スクリプトではStaticメソッドをトリガーで設置できる

トリガーに引数を渡す手法

スクリプトトリガーは実はデフォルトでは「関数の引数」を渡す事ができません。あくまでも関数を呼び出すだけで()をつけて引数を付けて、トリガーを設置する事が出来ません。そのため手動で設置する場合には、トリガー引数は設置が出来ないのです。

しかし、スクリプトから設置する場合には、ちょっとしたテクニックを使う事でスクリプトトリガーの関数に引数を設定する事が可能です。

  • スクリプトトリガー設置時に、スクリプトトリガーのID、引数、繰り返し使用フラグを生成する(settingTrigger関数にて)
  • 上記で取得した情報を、スクリプトプロパティにトリガーのIDをキーとして、引数と繰り返し使用フラグをJSONとして保存
  • トリガーで関数を呼び出された時に、呼び出された関数(TriggerFunction)からhandleTriggerdを呼び出してあげる
  • TriggerFunctionの引数にeventがついていますが、これをつけることで、トリガーの情報(そのトリガーのIDなど)が取得できます。これを元にスクリプトプロパティからTriggerFunctionに渡したい引数情報や継続使用フラグを取り出します。
  • handleTriggerdでは、継続使用フラグがTrueの時にはトリガーデータを返し、falseの場合はトリガー削除を実行する
  • deleteTriggerByUidにてスクリプトトリガーを削除すると同時にスクリプトプロパティも削除する
  • 同じ関数でもトリガー固有のIDはすべて違うので、実行時にトリガーIDに基づいて、異なる引数で複数同時にトリガーを設置することは可能です。

図:スクリプトプロパティに格納されたトリガー引数

図:実行数からログを確認。トリガーも無事削除されてる

ポイント

トリガー対象となっているスクリプト内では、自動的にそれらが動くわけなのですが、通常とはちょっとだけ異なる挙動になるのと、タイムアウトの5分を意識して作らなければなりません。もし、スクリプトの実行が失敗しますと、サーバーからメールが飛んできます。

  • スクリプトトリガー作動時のユーザは設置者の権限となります。故に、メールアドレスなども設置者のメアドがログに残ることになります。
  • 複数の人間が同じトリガーを設置してはなりません。また、他の人からは他の人間が設置したスクリプトトリガーは見えませんので要注意です。タブってトリガーが発動されることになります。
  • 値を他のシートからかき集めて、集計するタイプのものは、タイムアウトに注意!
  • 集計したさらに先に、PDFを作成して格納するようなルーチンを書く場合には、スクリプトトリガーの発動時間を当たり前ですが、ずらしておく必要性がある。でないと、集計されきってないのに、空のPDFが作成されたりする。
  • 複雑な関数を自分で作成し、スプレッドシート内で関数として使っている場合、「読込中・・・」のままになり、これをPDF化するような自動化を行うと、全く帳票として使い物にならないものが生成されるので、なるべくなるべく、スプレッドシート上で使用する関数は標準の関数を使用すること(自作関数はどうしてもスピードでは圧倒的に負ける)。
  • 時間トリガーはあくまでも指定した時間~時間のどこかで実行されるものなので、確実に何時何分という指定はできない。
  • 通常は、getActiveSpreadsheetなどで取得しているシートは、openByIdにしておくこと。もちろん、自分自身のIDを取得するような関数も用意しておいて、予めスクリプトプロパティに格納し、呼び出す仕組みが望ましい。でないと、書き込みなどが出来ずに終わってしまう。
  • スクリプトトリガーの設置では、引数をつけた関数の呼び出しが出来ないので、引数付きで呼び出したい場合には、引数付きの関数を呼び出す関数を作成しておいて、それをセットするようにすると良いでしょう。

実行日付がオカシイ場合

スクリプトトリガーがオカシナ時間に実行されるという場合には、タイムゾーン設定を疑う必要があります。GoogleスプレッドシートにはGASも含めて2個のタイムゾーン設定が存在しており、この設定がオカシイ場合、実行時間に大幅なズレが生じる可能性があります。以下の2点をチェックしましょう。

スプレッドシートのタイムゾーン

これはスプレッドシートそのものに設定されてるタイムゾーンです。大昔から存在するもので、主にシート状での関数などの挙動に関する日付等を担当しています。よって、スクリプトトリガーに直接影響があるかといったら微妙なのですが、トリガーで日付の書き込みなどを行う関数を取り扱う場合に影響が出るかもしれません。

以下の手順で確認しましょう。

  1. メニューより「ファイル」→「Googleスプレッドシートの設定」を開く
  2. タイムゾーンが「GMT+9:00 Tokyo」になっていればOK

かなり初期の頃この値がUSになっていたことがあり、自動でセルに日付を挿入した際に、USの時間が書き込まれてオカシイ挙動だった時代があります。

図:ここが狂ってるケースは多分今は殆どない

スクリプトエディタのタイムゾーン

さて問題は、スクリプトエディタ側にもこのタイムゾーンの設定があるのだけれど、この設定自体は表に出ていない点。スクリプトエディタのタイムゾーン設定はappsscript.jsonに入っており、デフォルトで非表示になっているため。以下の設定変更で表示し、中身を確認する必要があります。

  1. スクリプトエディタを開く
  2. 左サイドバーのプロジェクトの設定を開く
  3. appsscript.json マニフェストファイルをエディタで表示するにチェックを入れる
  4. あらためて、コードに戻るとファイルの項目に、appsscript.jsonが表示される
  5. 開くと中にtimeZoneの項目があり、ここが「Asia/Tokyo」になっていればOK

この設定はスクリプトトリガーの実行自体や、スクリプトでのnew Date()などの結果に影響を与えるのですが、なぜかAmerica/New_Yorkになってる人がいるようで、必ず治すようにしましょう。

図:この設定をオンにする

図:appsscript.jsonの設定は影響します

共有してみる: