GASだと複雑すぎる処理はAPIにしてみよう

How to create an interactive GAS application by calling the API

Published in

VELTRA Engineering

6 min readMar 4, 2019

Googleのスプレッドシートには、Google Apps Script（GAS）というExcelのマクロ（正確にはVBA）のような機能があります。ちょっとした処理を自動化したり、自前の関数を作成したりと便利な一方、下記のような点で不便を感じることもあります。

プログラミング言語がJavaScriptに限定される
基本的にWEBエディタ上でのプログラミングとなる
モジュールのライブラリ化が難しい
構成管理ができない（しようとすると工夫が必要）

そこで今回は、複雑な処理をAPIとして記述し、GASからはそのAPIを呼び出すだけというシンプルな構成で、インタラクティブな機能をスプレッドシートに提供するということに取り組みたいと思います。

サンプルアプリケーション

今回作成するのは、下記のフォーマットのスプレッドシートにおいて、「Mail Address」欄に不正な入力をされた際に、エラーダイアログを表示するというスプレッドシートです。

なお、付加機能として、左上のセルが”check”となっている場合のみチェックを行い、それ以外の場合はダイアログを表示しない制御を入れます。

APIのインタフェース仕様

今回はGASからAPIを呼び出す処理にフォーカスを当てるので、APIの詳細は説明しません。GASをコーディングする上で最低限必要なインタフェース仕様は下記のようになります。

スプレッドシートのIDとシート名を含めた、下記のようなURLのGETリクエストとする。
https://xxx/check/<spreadsheetID>/<sheetName>
チェックエラーの場合は、レスポンスにエラーメッセージを設定し、正常応答（ステータス200）する。エラーでない場合は、空で正常応答する。

なお、今回筆者は、上記のAPIをGoで作成し、Google App Engineにデプロイしました（サンプルコードはこちら）。当然ですが、このAPI自体が対象のスプレッドシートを参照する権限が必要ですので、サンプルコードと合わせて過去の記事なども参考にしていただければと思います。

GASでのコーディング

APIをデプロイし、シートを準備したら、あとはGASでコーディングするだけです。スクリプトエディタを開いて、下記のコードを貼り付けてあげましょう。エンドポイントのURLは適宜修正してください。

var endpointUrl = "https://xxx"function onEditInstalled(e){
  
  var sheet = e.range.getSheet()
  var sheetId = sheet.getParent().getId()
  var sheetName = sheet.getName()
    
  var url = endpointUrl+ "/check/"+sheetId+"/"+sheetName
  
  try {
    var resp = UrlFetchApp.fetch(url);
    var msg = resp.getContentText()
    if (msg != "") {
      Browser.msgBox(msg)
    }
  } catch(ex) {
    Browser.msgBox("server error\\nplease contact the system administrator")
  }
}

簡単に解説すると、編集時にonEditInstalled関数が呼ばれて、スプレッドシートのIDとシート名を引数のイベントオブジェクトから取得します。その後、APIを呼び出して、メッセージが返ってきたらダイアログに表示するという実装になっています。シンプルですね。

では次に、作成したonEditInstalled関数が、シートを編集したタイミングで起動されるように、トリガーを作成します。イベントソースを「スプレッドシートから」、イベントの種類を「編集時」と選択し、保存ボタンを押すと、下記のようなOAuthの同意画面が表示されるので、許可してあげます。

これでAPIを呼び出すアプリケーションの完成です。

なぜonEdit関数を使用しないのか

実は関数の名前をonEditとすると、わざわざトリガーを自分で作成しなくても、編集時にこの関数が自動で呼び出されます。これらはSimple Triggerと呼ばれていて、手動でトリガー設定しなくてよいので便利なのですが、「認可が必要なサービスにアクセスできない」といった、いくつかの制約が存在します。

今回、APIを呼び出すためにUrlFetchApp.fetch関数を利用しましたが、この関数も認可が必要なサービスです。OAuth画面にも、このアプリケーションが「外部サービスへの接続」をする旨が記載されていますね。

onEdit関数を作成し手動でトリガー登録することもできるのですが、この場合、Simple Triggerと手動で追加したトリガーの２つがあるため、１回の編集で２回実行されてしまいます。そのため、別の名前にしてあげる必要があるのです。

スプレッドシートなどのドキュメントから、ソースコードやSQLなどを自動生成するCLIツールはとても便利なため、個人的にかなり多用しています。ドキュメントが、エラーコードリストのようなエンジニア向けのものであればいいのですが、非エンジニアの方にメンテナンスしてもらうようなドキュメントの場合、ツールを使ってもらうのはかなりハードルが高いので、結局入力チェック等はエンジニア側でやることになりがちです。

そんな折、GASでAPIを呼び出して、APIがツールを動かしてしまえば、ドキュメントの編集中に、インタラクティブにチェックできてしまうのでは？と思いついて、試してみたのが今回のアプリケーションです。まだ作成したばかりですが、はたしてうまく運用できるでしょうか。乞うご期待！