😶‍🌫️

情シスはお金をかけずに台帳を作りたいからスプシを頑張る

2024/04/18に公開

Google Workspace

はじめに

情シスの皆様、台帳は何で作っていますか？
弊社はすべてGoogleスプレッドシート（以下スプシ）で作っています。
システムを内製するかという気持ちもたまに湧くものの、メンテナンスできる人が辞めると終了のリスクがあります。スプシなら誰でもできる！

しかしスプシを単体のデータ一覧として使っていくと負債が積み上がりすぎます。
少しはRDBっぽく使いたいし、権限もいい感じにしたい。
やっていきます。

初歩編　関数以外にやれること

シート保護

スプシは（権限があれば）編集可能状態で開かれますし、不用意にキーを押してしまうと更新＆自動保存されます。
情シスの生命線である台帳が意図せず更新されては困りますから、保護しておきます。
更新できるGWSユーザーを指定することもできるものの、これをやると負債化しやすいです。
体制やメンバーの性格にもよりますが、シートに対して「この範囲を編集するときに警告を表示する」の設定をしておくだけで十分だと感じます。

データの入力規則

まずは入力ブレを防ぎたいので、選択肢項目はすべて「データの入力規則」を設定します。
取り回しがいいように、データを入れるシートとは別シートにマスタデータ一覧的なものを作って運用しましょう。
これがあとで活きてきます。

条件付き書式

アカウント台帳や機器台帳など、開始日と終了日がある台帳は数多いです。
終了日が入った行は自動でグレーアウトされるようにしておきましょう。
人間の目で見やすいことも大事です。

フィルタ表示

たとえば「PC台帳で在庫のPCだけ見たい」「アカウント台帳で退職処理待ちの人だけ見たい」というような利用シーンがあります。
そういう場面を想定して、あらかじめフィルタ表示をいくつか作っておきましょう。必要な時に切り替えるだけでいいので焦らずに済みます。
なお、「フィルタ」ではなく、「フィルタ表示」のほうです。

中級編　関数を使おう

個別の関数の説明はヘルプに譲り、よく使うものを挙げます。

vlookup

めちゃめちゃお世話になるやつ。同じスプレッドシート内のデータを参照するやつです。
vlookupは、条件に当てはまる値が存在しない場合に #N/A エラーを返してくるので、iferror を噛ませておくと景観がよくなります。

iferror

上でも出てきた、数式の結果がエラーだった時に代替テキストを出してくれるやつ。
この存在を知るまでは if(iserror(hoge),"error", "not error") みたいな書き方をしていてめちゃくちゃ長くなっていました。早く知りたかった！

isblank

特定のセルが空かを判定するやつ。
単独では使いませんが、ifやvlookupと合わせると登場回数がぐんと上がります。

if、ifs

条件分岐するやつ。
ここまでに出てきた関数を組み合わせると
「社員マスタで退職日が空の社員だけ、別シートのメールアドレスを参照して表示する」
というようなことができます。

countif、countifs

特定の条件に合うデータだけカウントしたいやつ。情シスが絶対使うやつ。
countifを覚えられなくても、後述のqueryが分かれば乗り切れますけど、知っておいて損はないです。
「ライセンス台帳で、割り当て先が空のものだけカウントする」というような場面で使います。

importrange

他のスプシのデータを参照できるやつ。Excelにはないすごいやつ。
これを使いこなせればスプシがRDBに一歩近づきます。スプシを使う上での必修関数です。

前出の「データの入力規則」の設定をするときも、別シートから値を参照したいことがよくあります。
たとえば「スマホ台帳のプルダウンに、アカウント台帳で管理しているメールアドレスを出したい」というような場合です。そういう時に使いましょう。
このとき「アカウント台帳のデータをコピってスマホ台帳に貼る」をやってはいけません。自殺行為です。importrangeで参照しましょう。

類似の関数としてimportxmlもあります。こちらはHTMLやCSVなどのデータを読み込めるものです。大変便利ですが、台帳にはあまり使わないので割愛します。

query

SQLライクに条件を書いて、データ群から任意のデータを取り出せるやつ。importrangeと組み合わせれば別スプシのデータも操作できます。最強です。
他シートからデータを持ってくるとき、importrange単独だと細かい条件を指定できません。importrangeで取ってきたデータをqueryで絞ることで便利に使えます。

数ある台帳を一つのスプレッドシートにまとめることはできません。
また、別シートにあるデータをコピペで持ってきてしまうとメンテナンス漏れの温床になります。
適切な規模でスプレッドシートを分け、importrangeやqueryで相互参照するのが基本形です。

arrayformula

これを知ると世界が広がるarrayformulaパイセン！
スプシの全部の行に vlookup("A1",hoge!A:B,2, false) を書いたけど、行が増えたら数式もコピーしなきゃ…
念のためまだデータを入れていない行にも数式をコピっておいたら #N/A が出てきちゃうから数式に isblank を入れておかなきゃ…
みんな通る道ですよね？
arrayformulaパイセンにお任せです。数式一つ入れておけばいい感じにしてくれます。

応用編　関数トラブルシューティング

queryの結果、複数のデータがひとかたまりで出てきてしまう

元データは複数行に分かれていたはずが、一つのデータとして固まって出てきてしまうことが稀にあります。
以下はおかしくなっている例です。データの1行目がヘッダと合体して一つのセルに出ています。

QUERY(データ, クエリ, [見出し])

query関数の第3引数は省略可能となっていますが、省略すると上記のようにおかしくなることが多いです。第3引数を明示すれば解決します。

運用編　権限を意識する

ひとつのスプレッドシートの中にはたくさんのデータが入っています。
「そのうち一部のデータは情シス外に見せたいけど、見せてはいけないデータも混ざっている」という場合は多々あると思います。
RDBであれば、テーブルをそのまま見せるのではなく、項目をピックアップしたビューを作って公開することになるでしょう。
スプシでこれをやるには、

マスタースプシ（情シスのみ編集可）
公開用スプシ（情シスのみ編集可、社員閲覧可）
に分け、公開用スプシからマスタースプシをimportrangeで参照すればOKです。

このやり方は権限面でとても便利ですが、マスター側の行追加・削除をすると参照データも崩れる（なんなら見せてはいけないものが見えてしまう）というリスクがあります。
マスタースプシ側にREADMEシートでも作り、注意事項として書いておきましょう。