🐥
Spreadsheet as a DB を運用するときのアレコレ

2024/12/04に公開
タイトルだけみて「けしからん！説教してやる！」と思った方もいると思いますが、まあそういう世界線があっても良いじゃないですか。
ちなみに、Google の製品としてはしばらくまえに正式名称として「Google Sheets」になりましたね。Spreadsheet は、表計算ソフトの一般名称、かのExcelも、Spreadsheetです。

そんなことはどうでも良くて、今回は Google Sheets を使った話です。
#p2bhaus P2B Haus Advent Calendar 2024 4日目の記事です。
2日目はちゃんさん、3日目はいくおさんでした。みんなありがとうな！

 Spreadsheet は同時編集管理画面付きデータシートSpreadsheet 偉いと思います。なんたって同時編集管理画面が付属していてそこそこちゃんとデータを集約することができるあれだけのものを無料で提供しているのですから。

(とはいえGoogle Workspaceを契約しているので別に無料ではないのだけど... バンドルされているから実質無料ではある)
Spreadsheet をつかってデータを管理するメリット・デメリットはもう言うまでもないので省略したいのですが、一応書いておきます。
メリット
WebUIやアプリで管理画面のついた簡易DBが構築できる
誰でも簡単に更新できる

スクリプト(Google Apps Script = GAS)やAPI経由で外部連携できる
非エンジニアにとっても比較的簡単に扱える

デメリット
データが壊れやすい、トランザクションを扱えない
リレーショナルなデータを構築できない(しにくい)

したがって、以下のような特徴があるものに対して使うのであれば、なかなか悪くない代物です。
DBを参照するタイミイングがコントロールできる
ユーザーなどから直接参照されず、必ずバッチなどの実行タイミングでのみ参照される
つまり、編集中や、壊れているタイミングには参照されない (されたとして、不具合が生じても許される)

データが多少壊れても良い(直せる)
リレーショナルなデータがある程度あっても良いが、せいぜい2次元程度であり、n:nのリレーショナルテーブルなどを構築する必要がない程度のデータ
で、そういうものを扱いたいシーンってどういうユースケースかなぁ、と考えると、そう、飲食店の店舗運営ですよね。

飲食店の店舗で扱うメニューなどのデータは、特にこういったケースに向いていることは皆さん想像に難くないと思います。

 P2B HausでビールメニューマスタP2B Haus では、Spreadsheetにビールマスタを集約することにより、以下のようなことを実現しています。
(これは、東大のICTと産業の授業で講義させていただいたときのスライドから抜粋。スライドは→ 「ソフトウェアxスタートアップから見た飲食と配送の世界」)
POSレジ連携用のCSV出力(GASで整形)
Slackへの情報投稿 (これもGASで、、)
インスタ等に投稿されるデータのテンプレの生成
在庫情報の店舗内共有

Webサイトの「本日のクラフトビールタップリスト」や「アーカイブ」の出力
印刷用メニュー(PDF)の生成
この記事では、データマスタの作り方のみを話すので、その活用については、また別記事で取り上げたいと思います...。

 マスターデータにはIDを振って集約するというわけで、さっそくデータを見に行ってみましょう。原価等が載ってる部分はマスクしてます(O~Q列などにも価格情報が入っているがhideしてます)。知りたかったら P2B Haus 2号店を一緒にやりましょう。
このシートは、ご覧の通り、P2Bで扱っているビールのマスタを管理しています。
ID: WebページのURLにもなるID。POSレジのIDにそのまま使われる。一意のID。
New: =IF(ISBLANK(AI1507),IF(AG1507 <= 3, 1 , ""), "") みたいな感じ
Tap No: 1~12 のタップ番号 or 99 在庫
ブルワリー、銘柄、国・街、スタイル、提供グラス形状、度数、IBU: ビールの基本情報
ブルワリーは、別途 BreweryMaster Sheet が存在し、国・街などの情報はそちらに記載があり、このシート上はただのViewとして存在している

ビアフライト対象、平日の飲み放題対象、XS展開かどうかのフラグ
基本は価格により自動決定するが、たまに手動で変更するケースがある

Kegサイズ、価格・単価・税抜き単価:
税抜き単価を記入すると、ブルワリーごとの送料・返送料を加算したビール単価を出力し、それを樽のサイズで割ることによりml単価を出す
=AK1507 + VLOOKUP(D1507,BreweryMaster!B:I,8,FALSE)

ml単価に、S/M/Pint の各グラスサイズを掛け、原価率で割ることで、提供価格を生成する

説明: 印刷用メニューやWEBに掲載されるスタッフコメント
on_tap/out_tap: 開栓日、抜けた日
などなど。
つらつらと説明しちゃったけどこの説明いらなかったな...
とにかくなにが大事かというと、
ID は大事。ちゃんと振っておこう。
活用のしやすい Spreadsheet としにくいやつの差は ID の有無と言っても過言ではない

でもIDでリレーション貼ると、シートがすごく見づらく管理しにくくなってしまうので別にブルワリーマスタとの結合はIDでしてない、、ブルワリー名でしてます
価格のようなものは、計算の途中結果のカラムを用意する
例えばml単価のような、複数カラムで参照したくなるようなものは計算結果のカラムを作っておくと便利

ここはあまり語ることがないですね。

 用途ごとに非正規化したView的シートを用意するデータベースとして設計する場合、メニューマスタとブルワリーマスタの他に、ホップやビアスタイルなどのマスタも用意したくなるところではありますが、複雑なシート結合には向いていないので、どのデータを非正規化しておくか、というところがシート設計のポイントになるかと思います。
そういう意味では、今回はビールのマスタとブルワリーマスタしか作っていないので、非常に非正規化されたテーブルということになります。
で、今回はビールマスタのフォーカスしているのでこれだけなのですが、

実はフードメニューも、マスタがあります。
用途ごとに非正規化したView的シートというのは、例えば、以下の様な話をしています。
メニューマスタには、原価やメニューの表示・非表示フラグなど、マスタとして欲しいデータを整備する
別途シートとして、POSレジ連携用のCSVを生成するためのシート(CSVに必要なデータのみが表現されたカラムをもつシート) を作り、メニューマスタから参照する
例えば、マスタは、以下のように、有効無効のフラグをもつテーブルになっているのですが、
CSV用のシートを別途用意すると良いよ、という話です。
A:1 は =QUERY('メニューマスタ'!A:B, "SELECT B where A = 1", 1) となっていて、メニューマスタから有効なメニューのみを取得し、メニュー名などはすべてそのIDを元に参照する構造にしておくと =IF(ISBLANK(A2),"", VLOOKUP(A2,'メニューマスタ'!B:G,2,FALSE))、とっても便利に使えます。

 データを壊さないためのアレコレSpreadsheetは店舗スタッフもいじるため(TapNoや開栓日は、現場運用で入れてもらっている)、この際に、壊れてはいけないデータが壊れないようにしたいですよね。例えばID欄消しちゃう、とか。

 保護機能を使うProtect Sheets and Ranges の機能をつかって、
IDなどは保護しておきましょう。
警告付きでいじれるようにしたり、特定の人のみ編集可能なようにしておくこともできます。

 値の重複はすぐ気付けるようにする例えば、IDや、このシートで言うTap.Noは、数値の被りがあってほしくありません。

Conditional Formatting の機能を使って、
=COUNTIF(C:C,C:C)>1 みたいな condition を設定しておくと、Tap no を誤って同じ設定にした場合などにはすぐ気づくことができるようになります。

 まとめSpreadsheet as a DB とか言ったら怒られるかなぁ、と思って検索したら結構やってる人がいました。
Googleスプレッドシートを駆使してバックエンドDBを構築する こちらのナビタイムさんの記事なんかはより実践的でガチでためになりました。
Spreadsheetの活用はほどほどに。
このシートをどう使っているかなどはまた別の記事で紹介したいと思っております

DB化したいなぁ
誰かインターンとかで(r
飲食店がそんなものに払うお金はないので自分でやります...


というわけで、P2B Haus に飲みに来た際はぜひメニューをみながらこれはどの値から取ってるのかなぁ、などと感じてくださいね。