Google Apps Script を書く際の考察

Google スプレッドシートはプログラマー以外にとって素晴らしいプラットフォームである

もしこのスクリプトを自分の目的のために書いていたとしたら、Googleスプレッドシートを使うことは決して考えなかっただろう。コマンドラインスクリプトとして書くだろう。しかし、プログラムを書くときは常に、どのようにデプロイされるかを考えなければならない。この場合、私の友人はオーディオビジュアル技術に非常に精通しているものの、毎日コマンドラインを使用するプログラマーではない。また、彼はWindowsを使用しており、それは私がもはや慣れていない環境である。（コマンドラインが良くなったと聞いているが、基準が非常に低かったため、ミミズでも飛び越えられるほどだった。）しかし、多くのコンピュータユーザーと同様に、彼は常にGoogle スプレッドシートを使用している。

スプレッドシートは、多くの企業がいくつかのExcelスプレッドシートに依存していることを指摘する私たちプロのプログラマーから多くの批判を受けている。私たちは目を丸くしながらそう言うが、そのようなスプレッドシートは、複雑なコード、モジュール性の低さ、テストされていない、バージョン管理がない、管理なしでユーザーのコンピュータに存在していることがよくある。スプレッドシートは、不向きなタスクに無理やり使用されることが多く、しばしば、寄せ集めのデータベース、VLOOKUPが複雑に絡み合ったテーブルとして機能している。

しかし、これらのスプレッドシートが広く使用されているのには、正当な理由がある。表形式のメタファーは、構造化されたデータを扱うためのシンプルで効果的な方法である。例示的プログラミングを使用することで、ユーザーは数式を操作しながらすぐに結果を確認できる。Googleスプレッドシートを使用すると、スプレッドシートを他の人と簡単に共有できるため、複数の人が共有データを保存する共通の場所を簡単に持つことができる。スキルに基づく障壁を本能的に嫌う人間として、私はソフトウェア開発者が他の専門家がスプレッドシートで行っていることにあまり関与していないことにしばしば不満を感じてきた。

他の多くの人々と同様に、スプレッドシートは友人がこのタスクを実行するのに自然な環境になる。Googleスプレッドシートを使用すると、新しいメニューを作成し、メニュー項目にスクリプトを簡単にバインドできるため、彼は私のスクリプトを簡単に実行し、スクリプトが生成するデータを確認し、追加データをシートに直接追加できる。シートを簡単に共有できるので、問題が発生した場合にスクリプトを更新したり、データを確認したりできる。彼は自分のマシンにソフトウェアをインストールしたり、最新の状態に保つ必要がない。

もし私がバスにひかれたとしても、彼は簡単にシートを他の人と共有でき、他の人は簡単にシートを編集および実行できる。コードはJavaScriptなので、プログラマーが扱うのに広く知られている言語である。

一番難しいのは認証

スクリプトの「ビジネスロジック」は非常に簡単だった。彼のSlackチャネルのメンバーのリストを、Patreonまたは彼の独自の別のリストに登録されているメンバーと比較する。次に、その比較を使用して、Slackに追加または削除する人々のリストを生成する。必要なのは、リストからメールを取得し、いくつかの差集合演算を行うことだけだった。

難しいのは、REST URLからフェッチするという意味ではなく（これも簡単）、スクリプトがこのデータを取得する権限があることをサービスに納得させることだった。どちらのサービスも認証にOAuthを使用しているが、Google Apps Scriptが提供するライブラリを使用しても、プラグアンドプレイのエクササイズであるとは言えない。

最終的に、認証を半分回避し、完全に回避することになった。Slackには、Slackデータにアクセスするためのアプリを作成し、必要な承認を与えると、Webサイトで簡単なアクセストークンが付与されるという優れたメカニズムがある。このアプリケーションでは、そのアクセストークンをスクリプトに直接入れることができた。通常、それはセキュリティ上の好ましくない行為だが、この場合、スクリプトはダウンロードするデータと同じスプレッドシート内にある（そして、そのデータは非常に機密性が高いわけではない）。これにより、OAuthが提示する複雑さのほとんどを回避できた。

Patreonデータは、認証がより厄介で、より機密性の高いデータだった。そのため、ここでは認証を回避した。Patreonウェブアプリを使用すると、ユーザーはデータをCSVファイルにダウンロードできる。そこで、友人にそれを行い、データをスプレッドシートにインポートするように依頼した。

ここで、Googleが認証フロー全体を簡素化する絶好の機会がある。リモートサービスでfetchメソッドを呼び出すだけで、インフラストラクチャが認証フローを調査してプログラミングする必要がないようにする必要がある。

Google のドキュメントはゼロではない

それが、私がそれについて言える最善のことだ。すべてのクラスとそのメソッドのリストがある。それらを読み通すことで、通常は何かを理解することができた。しかし、それ以外にはあまりなく、このような簡単なタスクでさえ、私が望むよりも厄介な場所に導かれた。

スプレッドシートの整理

私はスプレッドシートでプログラムをあまりしないので（Rで一般的なデータの整理とグラフのプロットをしている）、十分に構造化されたスプレッドシートがどのようなものかについて強い意見を持つほど経験がない。スプレッドシートをうまく設計する方法に関するアドバイスがどこかにあるかもしれないが、見つけることができなかった（もし良い記事を知っている人がいれば、教えてください）。

それが不足していることを考えると、私はいつもの本能に従った。最初の本能は、最小限の操作でデータをローカルストアにダウンロードすることだ。そのため、Slackからデータをダウンロードするスクリプトは、必要なフィールドを選択してスプレッドシートの1ページにダンプすることしか行わなかった。同様に、Patreonデータのページは、PatreonからのCSVファイルの単純なアップロードを想定している。これらのページはどちらも、リフレッシュされるとページ全体をクリアして置き換えるように構築されている。3番目のページには、手動で管理された例外のリストが含まれているだけだった。これら3つのシートはすべて純粋なデータシートであり、単一のテーブル、1行目の見出し、数式は含まれていない。比較スクリプトは、これら3つのデータシートから読み取り、（単純な）アプリケーションロジックを実行し、2つのリストを別の出力シートに出力する。

それはまさに、コマンドラインアプリで個別のテキストファイルを使用する方法である。ユーザーはダウンロードされた生のデータを確認できる。毎回ダウンロードすることなくアプリケーションロジックを実行（およびテスト）できる。テストデータを含むシートを設定できた。シートとコードの間には、データの明確な一方通行の流れがある。

行を追加するのに appendRow を使わないこと

Slackからダウンロードするコードを最初に実行したとき、ひどく遅かったのでがっかりした。せいぜい1000行程度しかなかったかもしれないが、1秒に1行程度の速度でスプレッドシートに追加された。それには我慢できたが、あまり良くなかった。もっと速い方法があるはずだと確信していた。

APIを調べてみると、多くのスプレッドシート操作がスプレッドシート内の範囲の定義に依存していることがわかった。私はSheet.appendRowを使用して新しい行を追加していたが、範囲（シート全体でもよい）を定義すれば、代わりにRange.setValuesを使用できることがわかった。そうすると、行の追加が事実上瞬時に行われた。ドキュメントやWebの他の場所でこれを試すヒントは見つからなかった。このようなドキュメントの不足は、人々がこのプラットフォームをもっと広く利用する上での障壁となるため、重要である。

API 検索で複数の値を検索できるようにする

上記で述べたように、認証に関する複雑さのため、PatreonデータにRESTインターフェイスを使用することは最終的にしなかった。しかし、CSVダウンロードを支持するもう1つの理由があった。Patreon APIには、キャンペーンの支援者であるすべてのユーザーを教えてくれるリソースが含まれており、これらのユーザーについては、Patreon IDと名前が提供された。しかし、Slackリストと照合するには、メールも必要だった。それは、IDでインデックス付けされたリソースを取得することで調べることができた。ただし、数百人のユーザーに対してそれを行う必要があり、それぞれに個別のGETが必要になる。

APIデザイナーへのメッセージはこれだ。IDでリソースに関する情報を検索する機能を提供する場合は、一度に複数のIDのデータを提供する機能をサポートすること。[1]

アプリケーションロジックとスプレッドシートの入出力を分離するのが好きだ

スプレッドシートからデータにアクセスする方法は、スプレッドシートの列と行の規則（例：セル「B22」または範囲「A2：E412」）を使用することである。これは、プログラマーがスプレッドシート内のセルを操作するという観点から問題を考えているため、多くのスクリプトタスクに適している。

私はむしろ、基本的なJavaScriptデータ構造の形式でデータを好むため、物事を異なる視点で考える傾向がある。特に、JavaScriptのコレクションパイプライン演算子をそれらに使用できるため。

それを考慮して、これはシートからデータを抽出し、JavaScriptオブジェクトの配列として返すために書いた便利な関数である。

extractData(sheetName, firstCol, lastCol, mapper) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  const numRows = sheet.getLastRow()
  const range = `${firstCol}1:${lastCol}${numRows}`
  return sheet.getRange(range).getValues().map(mapper)
}

次に、次のようなコードで使用できた

const mapper = row => ({email: row[3], name: row[0], slackId: row[1]})
slackData = this.extractData("raw-slack-download", "A", "D", mapper)

配列に変換すると、結果を把握するのは簡単だった。ただし、lodashにアクセスできなかったため、単純なArray.difference関数を作成する必要があった。

開発環境が簡素なのは理解できる

JavaScriptを書くために、スプレッドシートでメニュー項目を選択して、簡素なテキストエディターに入力するだけだ。それは私が慣れ親しんでいる快適な場所ではないが、一晩の滞在には適している。

もしもっと高度なことをしている場合は、より良い環境を構築することを検討するだろう。1つの可能性は、Emacsの素晴らしいTrampモードを使用してスクリプトを編集できるかどうかを確認することだ（これにより、リモートファイルをローカルファイルのように編集できる）。さらに良いのは、ローカルファイルをGoogleドライブと同期して、ソースコードをgitリポジトリに保持できるようにする方法だろう。しかし、このような単純なタスク、約150行のコードの場合、これが可能かどうかを調べる価値はなかった。

まとめ

Googleスプレッドシートで簡単なアプリケーションをホストすることは、さまざまな簡単なタスクに適したデプロイプラットフォームだ。ユーザーは自分のマシンに何かをインストールすることなくコードを実行したり、使い慣れた環境でデータを入力したり、同僚との簡単な共有をサポートしたりできる。あまり議論されているプラットフォームではないが、覚えておくべきプラットフォームだ。特に、簡単なシェルスクリプトになるが、ユーザーがコンソールウィンドウやテキストファイルに慣れていないタスクの場合。