外部 API で自身を編集する GAS と「変更時」トリガの組み合わせによる大量トリガ発火事件

タイトルでオチているので「そりゃそうだろ」という話なのですが、一回自分の手でこの組み合わせを使ってちょっと大きめのやらかしをしてしまったので戒めのための記事です。

Google Spreadsheet（以下スプレッドシート）と Google App Script（以下 GAS）の組み合わせは浸透しつつある技術ですが、そこで見事に頭から突っ込んでしまった落とし穴について、技術的な本質は保ちつつ程よくデフォルメ化された事件例を用いながら紹介していきます。

概要

• 変更時トリガを設定したスクリプト内で外部 API 処理を行い、その API 処理内にスクリプトを持っているスプレッドシート自身を編集する処理が含まれるケースが対象。
• 外部 API を実行するスクリプトの完了までは「変更時」トリガの対象範囲内として扱われてそのスクリプトによるシートの編集によって新たに「変更時」トリガは発火しないが、外部 API 処理でシートに戻ってきて編集する操作は新たな「変更時」トリガの対象となる。
• 変更内容の制御を誤ると、外部 API 処理から戻ってきた編集操作によって新たにトリガが発火し、そのトリガ実行によってさらに行われた外部 API 処理から戻ってきた編集操作によってまた新たなトリガが発火し……と自己触媒的な大炎上トリガ実行に繋がる。

以下、事件が生じるまでの作業に沿って説明をしますので、ジェットコースターに乗ったような気分で読んでいただければと思います。

状況：申込が生じた都度外部 API を使用したい

何らかのサービスで申込情報がスプレッドシートに記録されるようになっている状況を考えます。
今回は内容としてシンプルに申込者の名前と申込番号、申込者からの備考メッセージを受け取るとしましょう。備考は空欄でも OK としておきます。
この申込は使用している外部サービスによって申込のごとに 3 項目が同時に埋まります。

各行の 3 項目について外部 API に JSON 形式で渡すスクリプトを作ることを考えます。新しい申込で 3 項目が埋まったタイミングでスクリプトを走らせたいので、スクリプトはトリガ条件「変更時」で動くものを想定してスクリプトを作ります。今回は申込サービスが Google フォームではないので「フォーム送信時」が使えないが致し方なし……。

送信したかどうかをスプレッドシート側で記録しておく必要があるので、列の最後に「通知送信」という列を用意しています。

スクリプトで行ごとにメールを送信したらその行の「通知送信」に 1 をいれるようにして、「変更時」に「通知送信」に値が入っていない行の情報について API を実行して最後に「通知送信」に 1 を入れるスクリプトを用意します。

また、今回利用する外部 API の先で諸々の処理が完了したら該当行の「送信完了」列に 1 を入れていくようにします。最終列の一つ左なので、 該当行にtransferredcolumn から -1　した列に 1 を入れます。

function onSentNewForm(e){
  const mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');

  // 最終行・列数を取得
  const lastRow = mySheet.getLastRow();
  const lastColumn = mySheet.getLastColumn();
  const transferredColumn = lastColumn - 1;

  // 「通知送信」列を配列にする
  const transferred = mySheet.getRange(2, transferredColumn - 1, lastRow-1, 1).getValues();
  const transferred_array = Array.prototype.concat.apply([],transferred);

  // transferred が blank な行を メール送信
  transferred_array.map(function(value, index, array){
    if(array[index] == ""){
      let rowNumber = index + 2;
      // ここで JSON フォーマットや API 処理（今回は省略）
      // 転送済みの投稿の transferred を 1 にする
      mySheet.getRange(rowNumber,transferredColumn - 1).setValue(1);
    }
  });
}

他で似たようなスクリプトを作っていたので、それをコピペして直す必要があるところは全て直し終わったと思います。

既存の申込にはフラグを入れておいて、これで準備完了。トリガをセットして次の申込を待ちます。

……新しい申込がきてその通知が届きました。しかし、API 使用通知が 1 件だけにとどまらずどんどん飛んでくることに気がつきます。スプレッドシートを確認しにいくと次のようになっていました。

原因は気づいた方も多いかと思いますが、「通知送信」列を参照しようとしているところ誤ってさらにもう一つ左の「備考」列を参照してしまっています。
これによって既に送信されている行に関しても備考欄が埋まっていない場合は処理が走ってしまい、その行に対する外部 API による「送信完了列」の上書きによってさらに「変更時」トリガが実行され……という悪循環が起こってしまったわけですね。

今回例で見せたものはとても小規模のものですが、実際に起こした事件では 2500 行程度に対してこの API による変更での「変更時トリガ」発火問題を生じさせてしまい、スプレッドシートの情報を別シートに退避させて鎮火するまでに 6000 回ほど API を叩いてしまいました。

「変更時」トリガが走らない対象範囲

さて、今回は実行する GAS と API の 2 つの方法でスプレッドシートに変更が加えられる状況でした。
GAS では「送信通知」列（のつもりで「備考」列）を、API では「送信完了」列をそれぞれ編集します。

ただ、トリガ実行の履歴を見てみると、「変更時」トリガで実行されたスクリプトの自身を変更する操作（今回は mySheet.getRange(rowNumber,transferredColumn - 1).setValue(1)）によって新たなトリガが発火している様子は見られません。

他にもいくつか実験をしてみたのですが、どうやら 「変更時」トリガによって実行するスクリプトで行われた変更に対しては「変更時」トリガがさらに発火しない ことがわかりました。
当たり前といえば当たり前ですが、ここら辺はお行儀よくなっているのですね。

しかし、一方で 外部 API を経由してまた元のスプレッドシートを変更するとなると話は別で、自身が実行したとはいえ一度 GAS の管轄外となった処理については「変更時」トリガの対象範囲外 となるそうです。

外部 API による変更による「変更時」トリガ発火を防ぐための対策

もちろん小規模テストでちゃんと動作確認をするとか、レビューをして複数人で確認するというコード管理上の対策もあるのですが、GAS 上での対策案も記しておきます。

今回の問題は外部 API が元になったスプレッドシート自身を変更することで発火するトリガ実行が問題でした。
この状況では最初の申込発生時による本来想定しているトリガ実行と、外部 API によるスクリプトを実行したくないけど動いてしまうトリガ実行の 2 種類のトリガ実行が生じます。

少々力業ですが、API を叩く処理の後に別シートに送信ログを記録するようにします。そして、スクリプトの序盤に最新の送信ログから特定の時間が経過していなければスクリプトを終了するように条件分岐を入れます。

諸々列の指定など間違っていたところも修正した完成版がこちら↓。

function onSentNewForm(e){
  const mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
  const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('送信ログ');

  // 最新の送信が 2 分以内であれば終了
  let currentTime = new Date();
  let previousEntryTime = new Date(logSheet.getRange(logSheet.getLastRow(), 1).getValue());
  const logInterval = 2;
  if( logSheet.getLastRow() > 1 && (currentTime - previousEntryTime) < logInterval * 60 * 1000 ){
    Logger.log('%d 分以内に送信履歴があるため、スクリプトを終了します。', logInterval);
    return;
  }

  // 最終行・列数を取得
  const lastRow = mySheet.getLastRow();
  const lastColumn = mySheet.getLastColumn();
  const transferredColumn = lastColumn - 1;

  // transferred を配列にする
  const transferred = mySheet.getRange(2, transferredColumn, lastRow-1, 1).getValues();
  const transferred_array = Array.prototype.concat.apply([],transferred);

  // transferred が blank な行を JSON で転送
  transferred_array.map(function(value, index, array){
    if(array[index] == ""){
      let rowNumber = index + 2;
      // ここで JSON フォーマットや API 処理（今回は省略）
      // 転送済みの投稿の transferred を 1 にする
      mySheet.getRange(rowNumber,transferredColumn).setValue(1);
      // ログシートに送信ログを追加
      let time = new Date();
      var lastLogRow = logSheet.getLastRow();
      logSheet.getRange(lastLogRow + 1, 1).setValue(time);
      logSheet.getRange(lastLogRow + 1, 2).setValue(mySheet.getRange(rowNumber,2).getValue());
    }
  });
}

ログは今回は日時と申込番号を記録するようにしました。

トリガ実行をしてから 40 秒ほど経過してから外部 API の立場になって「送信完了」列に 1 を入れたら以下のように最新の送信履歴時間による制御ができていました。

まとめ

もちろん今回の事件は参照する列を間違えたことによる事故で、小さなテスト環境で試してレビューをするという管理上の問題が大きくあるところではありますが、実験と検証を重ねていく中で「変更時」トリガの対象範囲がかなり明確になってきました。

外部 API を経由してスプレッドシートを変更するケースでの「変更時」トリガの取り扱いにはくれぐれもご注意ください（自戒）。