この記事はエムスリーAdvent Calendar 2023の17日目の記事です。
こんにちは、エムスリーエンジニアリンググループ/ BIR(Business Intelligence and Research) チーム の遠藤(@en_ken)です。
皆さんGoogleスプレッドシート使っていますか？
エンジニアだとあまり使わない方もいるかも知れませんが、BigQueryとの連携が非常に容易なため、 データをビジネスサイドに見せたり分析したりする際には便利なツールです。
BigQueryをバックエンドと考えると、スプレッドシートは簡易なフロントエンドのフレームワークとも捉えられます。 ちょっとしたビューの変更はセルで行えるため、 BIRでは業務を整理する際のプロトタイピングとして、 あるいはビジネスサイドの要件でExcel的なインタフェースが好ましいときのアプリケーションの作成方法として スプレッドシートを利用するケースがあります。
関数を組み合わせるのはシンドイ
BigQueryのデータを表示したいだけであれば、BigQuery側のクエリでデータを整形してビューとしてそのまま表示すればよいですが、 「BigQueryのデータ」+「スプレッドシート上の入力項目」を組み合わせてビューとして整形する場合にはスプレッドシート上にロジックが必要になります。 ロジック実装の選択肢はスプレッドシート関数 or Goolgle App Script(GAS)が選択肢になると思います。
GASの方がJavaScript準拠で書くことができ、claspを使えばローカル実行やバージョン管理も可能なのでエンジニアフレンドリーではある一方、 実行にはトリガー操作が必要なので、入力項目を即時反映できるリアクティブなビューを作りたい場合は関数を選択することになります。
しかしながら、そこそこ複雑なロジックを組んでデータ整形する場合、いわゆるExcel準拠の関数を組み合わせて実現するのは結構大変そうです。スプレッドシートの関数は仕様上、複数を組み合わせようとすると入れ子になっていくので可読性が破綻しがちです。
QUERY関数の誘惑
Googleのスプレッドシートには、独自拡張の関数としてQUERYがあります。
QUERY関数は独自の魔改造された、でもなんだかSQLっぽいクエリ言語でクエリを書くことができる関数です。
「第1引数で範囲を指定するからfrom句がない」
「列名はasとかでは書けなくてlabel句での記載が必要」
など奇妙な点はあるもののなんかSQLっぽい、関数組み合わせて頑張るよりはだいぶエンジニアに優しそうに見えます。 やってみると確かに簡易なクエリは見知った感じで書けますし、これひとつあればデータをほしい形に整形できるので可読性も良さそう。
そんな一見良い落としどころ感に誘われて、私はQUERYを使って実装を進めていってしまいました。 ところがQUERYの森深くに誘われると、大きな困難に直面します。
QUERY関数の奇妙な仕様
select句に文字列を書くとヘッダ行が勝手に入る
QUERY関数は第3引数はヘッダ行の認識させるためのパラメータになっており、 参照範囲からヘッダ部分を抜いた上でQUERYの第3引数を0にすれば、 参照範囲の内容からselectして出力するだけであれば、ヘッダは出力されません。
ところが、select句に文字列を記載すると、自動的に以下のようなヘッダ行が現れます。 なにか制約によるものなのだと思いますが、よくわかりません。
ただ、一応これには回避方法があり、label句でヘッダに表示されている名前を空文字で置き換えると自動で出力されるヘッダを消すことができます。
でもなんか気持ち悪いですね。 複数ある場合は、ひとつひとつ置き換えの記述を書く必要があります😇
select句に空文字は記述できない
select句に空文字を記載するとエラー(N/A)になります。
エラーメッセージも出ないため、最初なんだかわかりません😇
おそらく空文字を許容すると前述の自動で生成されるヘッダ名が空文字になってしまうため許容されないのだと推察されますが、結構困ります。
select句に同じ文字列を記述できない
空行以外なら問題ないかというとそんなことはなく、 select句に同じ文字列を複数書いた場合もエラーになります。
同じ文字列を許容すると、前述の自動で生成されるヘッダの列名が被ってしまうことを避けるための仕様のようです。
全部別の文字列にすればいけます😇
select句に同じ列は2回記述できない
上記諸々の問題を回避する方法は、select句には文字列は書かず、出力したい文字列を参照範囲に含めておくことです。 空文字を出力したい場合は、参照範囲に空文字を含めておけば、ヘッダの出力は回避しつつ空文字を出力できます。
ところが、空文字を複数列で出したい場合に、同じ列をselect句に記載するとエラーになります。
同じ文字列を複数出したかったら出す数だけの列を作って参照範囲に含め、別々の列を使いましょう😇
空行を出力できない
これはスプレッドシート(というかExcel)関数の思想に準拠したものなのだと思いますが、クエリの結果が0件のとき、関数はN/Aになります。そのため、参照先のデータが変わると、さっきまで動いていたクエリがN/Aになる現象が発生します。
IFERRORで囲むことでエラーは処理できますが、何らか出力自体は必要なため、完全に空行を返す(何も返さない)ということができません。
空文字だけの行は消える
with句のようにクエリ結果にさらにクエリしたい場合はQUERYを入れ子にすることで実現できます。(あれ？ 入れ子にしたくなくてQUERYを選んだ気がしましたね😇)
union句のように複数のSQL結果を結合したい場合も同様に入れ子で実現できます。
しかし、このクエリで前述のように片方の内側のクエリ結果が空行を返しN/Aになると、当然全体も失敗してしまいます。 ちなみに、入れ子にしてエラーになった場合、全く何のエラーかわかりません。メンテナンスする上では最悪ですね😇
そこで個別にIFERRORでラップするわけですが、前述の通り、エラー時に何も返さないことができないため、空文字のみのゴミ行が結果に入ってきそうです。
とやってみるとcount=2で後者のQUERYの結果のみなので、空文字のみを返している行は結果からは消えています。つまり、空文字のみの行を返せば実質何も返さないのと同じ動作になるようです。 labelで空文字に書き換えるとヘッダが消える挙動と言われてみれば一貫性は取れてますが、なんか気持ち悪いですね😇
文字列・数値混在時、文字列が少数派だと空文字に変換される
少し毛色が違いますが、こちらの記事にあるとおり、文字列と数値が混じった列があった場合、クエリ結果の文字列が少ないと勝手に空行になります。
カウント1(C列)は文字列が少ないので勝手に空行になっていいますが、カウント2(D列)は文字列のほうが多いので空行にはなりません。混乱する挙動ですね😇
普通のSQLではデータ型が混在している列を扱うことはないと思うので、異常なケースではありますが、であればクエリ結果にデータ型が混在していたらエラーにして振ってくれた方がまだ親切な気がします。
あまり困るユースケースがなさそうとはいえ、私のユースケースではこれも引っかかりました😇
まとめ
ということで、QUERY関数の言語仕様はかなりとっつきにくく、それなりのビューを表現するために大量のワークアラウンドを求められます。また、スプレッドシートのエラー出力はとっつきにくい言語仕様と戦うにはあまりに貧弱です。 ハマりどころが多すぎるので、使用するならシンプルなクエリが限度だと思います。
個人的な結論として、QUERY関数は業務で利用するようなスプレッドシートに組み込むものではないと思います。一見便利だなと思って使いだしても、かなり早い段階でメンテナンスコストとの損益分岐点がやってきます。
QUERYが入れ子しはじめたら勇気を持って撤退しましょう。 せっかく作ったのに？ 典型的なコンコルド効果です。 GASで実装するほうが確実に良い選択です。リアクティブなビューは諦めて作り直しましょう。
正しい撤退判断ができないでいると、次にこんなスプレッドシートをメンテナンスする扉を開けてしまうのは、あなたかも知れません……🕶
We are hiring!!
上記のスプレッドシートを改善してくれるエンジニアを募集してます😇
嘘です。これは私が責任を持ってどうにかします。
BIRでは今後アンケートシステムの大規模なリニューアル・リアーキテクチャを行っていく予定になっています。興味がある方は、以下のURLからカジュアル面談にぜひご応募ください!
