SpreadSheetとGASを使った実装に要件やテストを加えながらインターフェイスを更新した記録

SpreadSheet上のデータ及び操作を元にGASを使って通知等行いたい場合、動作確認が何かと面倒な状況になりがちです。

編集操作にてデータが変わった時の動作を確認したい場合、以下2つのケースが最低でも必要になってきます。

• セル編集時
• 編集後のデータ変更時

ただ、これらはセル操作が必須となり、手動テストと変わりありません。できれば複数パターンを自動で実践したい。割とよくありそうな編集遷移ですが、同じことを何度も繰り返してしまいそうなので備忘録として書き出してみました。

当初の想定処理

まずはテストを考えず、編集で入力されたデータをチェック、問題なければ整形して通知としました。

onEditはGAS組み込みの関数です。oauthScopesの追加設定を要求されない限りは編集時の自動処理で問題なく使えます。

セル編集を介さないテスト

組んだ直後は特に問題ありませんでしたが、ValueValidateにていくつかのパターンが出てきたあたりからやや煩わしくなります。 現状ではこれらにセル操作は必須で、とても手間のかかる手動テストと化しています。できれば複数パターンを自動で実践したい。そこでセル選択を介さずにデバッグメニューだけで処理が通るようにしてみました。

ワークフローではナンノコッチャという感じなので、実際のコードの一部を例にしてみます。

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var changed_cell = e.range.getA1Notation();
  update_check(sheet, changed_cell)
}

function auto_test() {
  var sheet = SpreadsheetApp.getActiveSheet();
  update_check(sheet, "B2");
}

ValueValidateにonEditで渡しているパラメータと等価構成になるように組みました。ValueValidateからNoticeまではupdate_check内に隠蔽された形となります。必要に応じてauto_test側でupdate_checkに色々渡すとよい感じです。

そしてこれで一段落かと思いましたが、今度はセルを特定しない状態でのチェックもやっておきたいと思い至りました。セルの編集が必要かどうか全体をチェックして、必要であれば通知にて編集を促すといった流れです。

特定セルを指定させない

難儀そうな要件にも見えますがシンプルです。

変更された箇所の実装例としては以下の通り。auto_test側の定義を少し変更します。

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var changed_cell = e.range.getA1Notation();
  update_check(sheet, changed_cell)
}

function auto_test() {
  var sheet = SpreadsheetApp.getActiveSheet();
  update_check(sheet, "B2");
}

function daily_auto_check() {
  var book = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXX');
  book.getSheets().forEach( function(sheet) {
    update_check(sheet)
  })
}

function update_check(sheet, changed_cell=null) {
  ....
  if (changed_cell != null) {
     ....
  }
}

update_checkにて変更セルの未指定が可能になりました。また、change_cellが定義されている場合だけDataParse以前の処理が走るようにもしています。

グローバル変数でフラグを指定しつつ、if分岐にてフラグを元に処理を変更して検証する、という手段を極力回避しつついくつかの検証方法にも耐えうるようなインターフェイス設計としてみました。

あとがき

インターフェイスについて都度最適化を行いましたが、内部処理に関しては適切なフローと言い難い状況となっています。複数の要求を問題なく通す1つのプロセスよりも、要求に応じて完全に処理を切り替えたほうが後々不具合も出難いはずです。

内部設計の最適化は要件次第でもあり、複雑化する駅路線の乗り換えのようなものともいえます。今回一部ピックアップした処理の本体は他に代用し難いものであるため、今後も継続して改良をしていこうと思います。