こんにちは！ atama plusでエンジニアをしているnaoshiです。
この記事では、短期間の検証用プロトタイプ開発において、Google スプレッドシートとBigQueryを活用した事例について紹介します。
とくに、スプレッドシート×BigQueryを採用した理由、また開発中に直面した問題点とその解決策について説明します。
プロトタイプ開発などでスプレッドシート×BigQueryを活用する方の参考になれば幸いです。
BigQueryの簡単な紹介
BigQueryはGoogle Cloudが提供するデータウェアハウスで、大規模なデータを高速かつ低コストで処理でき、分析用途においてよく使われています。
atama plusではプロダクトのデータの一部をBigQueryに転送しており、それをもとに自由に分析可能な環境が整備されています。
プロトタイプの開発背景
atama plusのアプリチームでは「デュアルトラックアジャイル」を採用しており、ディスカバリーとデリバリーという2つのトラックを並行して進めています。
Webエンジニアは、他の職種と協力しながら仕様策定から議論に参加し、プロトタイプの構築などによってディスカバリーに貢献します。
（参考：デュアルトラックアジャイルって結局何なの？）
あるディスカバリーにおいて、どのようなツールが良いのかを現場で使って探るため、プロトタイプを用いた検証をすることになりました。
検証開始は1ヶ月後で、それまでにツールのプロトタイプをエンジニア2名で開発する必要がありました。
プロトタイプ開発にスプレッドシート×BigQueryを採用した理由
チームでは、検証プロトタイプを開発するにあたり、以下の2つの方針が検討されました。
- 既存プロダクトに新機能として組み込む
- プロダクト外で独立したPoC環境を構築する
まず、既存プロダクトに新機能を追加する案が考えられましたが、以下の理由から難しいと判断しました。
-
短期間での開発が困難である
1ヶ月という限られた時間内での開発が厳しいと予想されました。 -
どう組み込むべきかの検討が困難である
検証段階では、どの既存プロダクトに組み込むべきかを決定するための情報が不足していました。 -
改善のスピードが制限される
既存プロダクトのリリースサイクルに依存するため、迅速に改善できないリスクがありました。
これらの理由から、既存プロダクト外で独立した検証環境を構築する方針が採用されました。
次に、プロダクト外でのPoC環境を作る際に、私たちはスプレッドシート×BigQueryの組み合わせを選択しました。その理由は次の通りです。
-
プロトタイプの表形式デザインに適している
作りたいプロトタイプが表形式のデザインだったため、スプレッドシートが適していました。 -
社内での利用実績がある
スプレッドシート×BigQueryの組み合わせは、社内ですでに利用実績があり、設定や運用に慣れている点が大きな利点でした。 -
データ連携が容易である
スプレッドシートはBigQueryと簡単に連携でき、データ更新の自動化も容易に実現できるため、プロトタイプの要件に合致していました。
（参考：Google スプレッドシートで BigQuery データを使ってみる）
以上の理由から、私たちはスプレッドシート×BigQueryを活用して、プロトタイプの開発を進めることにしました。
スプレッドシート×BigQueryを活用したプロトタイプの問題点とその解決策
スプレッドシート×BigQueryの開発を進める中で、パフォーマンスとメンテナンスコストの観点での問題点が見つかりました。
ここでは、それら問題点と解決策について紹介します。
パフォーマンスの低下
このプロトタイプはダッシュボード的な利用を想定していました。
ユーザがパラメータを変更・入力するたびに、シート内のデータが再取得・再計算され、再描画が必要でした。
開発を進めるうちに、こうしたデータ更新や再描画が原因で、スプレッドシートの動作がかなり重くなる問題に直面しました。
特に、参照範囲が広い関数やセルが連鎖的に参照されている箇所でパフォーマンスの低下が顕著でした。
この問題に対しては、以下の対策を講じました。
参照範囲を限定する
スプレッドシートでは、以下のように数式の参照範囲を限定しない書き方ができます。
以下の例では、SUM関数がA1セルからはじめて、A列をすべて参照しています。
=SUM(A1:A)
しかし、複数の複雑な数式でこのような参照範囲の指定をすると、パフォーマンスが低下してしまいました。
そこで、以下のように書き換えました。
以下の例では、SUM関数がA1セルからA1000セルまでを参照しています。
=SUM(A1:A1000)
このように参照範囲を限定することで、パフォーマンスを改善できました。
セルの連鎖的な参照の削減
以下のように、セルが連鎖的に参照している箇所がありました。
A1 に =2024/01/01
A2 に =A1+1
A3 に =A2+1
…
A1000 に =A999+1
何気なく書いてしまっていましたが、この連鎖が長くなると、最後の値を表示するためにすべての参照を再評価する必要があり、パフォーマンスが低下してしまっていました。
そこで、以下のように書き換えました。
A1 に =2024/01/01
A2 に =2024/01/02
A3 に =2024/01/03
…
A1000 に =2026/09/27
セルの連鎖的な参照をなくすことで、パフォーマンスを改善できました。
メンテナンスコストの増加
私たちのチームでは、開発したスプレッドシートを複数の現場で活用するために、開発用のスプレッドシートを複製し、各現場に配布する予定でした。
複数のスプレッドシートを運用する際に、機能追加やバグ修正を行うと、スプレッドシートの数だけ作業が必要となるため、メンテナンスコストが大幅に増加します。
この問題を解決するために、以下の対策を講じました。
追加開発と使用期間の方針決定
スプレッドシートの複製・配布後は、バグ修正を除いて原則として大規模な追加開発を行わないという方針をチーム内で決定しました。
また、スプレッドシートは検証期間のみ使うという方針をチーム内で決定しました。
これにより、配布後のスプレッドシートに対するメンテナンス負担を大幅に軽減できました。
BigQueryでのクエリ管理
もう1つの重要な対策として、可能な限りクエリはBigQuery側で管理し、スプレッドシート側での修正を最小限に抑える方針としました。
この方針について、BigQueryのテーブルAとテーブルBのデータを組み合わせてスプレッドシートに表示する方法を例にして説明します。
まず、スプレッドシートにテーブルAとテーブルBを直接読み込み、VLOOKUP関数などを使用して結合・加工する方法があります（図1の1）。
この方法では、将来的にバグが発生した場合、配布したすべてのスプレッドシートを修正しなければならず、非常に手間がかかります。
一方で、BigQuery上でテーブルAとテーブルBをJOINして加工したテーブルCをあらかじめ作成し、そのテーブルCをスプレッドシートに読み込んで表示する方法もあります（図1の2）。
このアプローチであれば、将来の修正が必要な際にはテーブルCを直すだけで済みます。
この対策も、配布後のスプレッドシートに対するメンテナンス負担の軽減につながりました。
図1 クエリの管理方法
まとめ
最終的に、スプレッドシート×BigQueryを使用することで、短期間でプロトタイプを開発できました。
私達チームは、以下の理由からスプレッドシート×BigQueryを採用しました。
- 既存プロダクトに縛られない短期間での開発と改善ができる
- プロトタイプの表形式デザインに適している
- 社内での利用実績があり、設定や運用に慣れている
- データ連携が容易で、自動化が実現できる
しかし、開発を進める中で、次第に以下のような問題が発生しました。
- パフォーマンスの低下
- メンテナンスコストの増加
これらのデメリットに対して、パフォーマンスを一定水準に保ちつつ、メンテナンスコストを極力抑え、無事に1ヶ月でプロトタイプを完成させることができました。
この記事が、プロトタイプ開発などの際にスプレッドシート×BigQueryを活用する方の参考になれば幸いです。
atama plus 株式会社では一緒に働く仲間を募集しています！
Discussion