AppSheetでユーザに合わせてドロップダウンをフィルタする
別件のワークフローアプリを作ってる中で「このドロップダウンから選ぶの大変」と言うケースがありました。AppSheetのドロップダウンは検索がそのまま出来るのですが、100も200も毎回出てくると、必ず検索をするという手間が生じることになります。これはスマートじゃない。
しかし実際にユーザに必要なのはそのうちのいくつかでしか無いので、これをテーブル設計で何とかして、ユーザに合わせてフィルタした結果をプルダウンに表示させたい（そうすれば検索する必要が無くなる）。というのが今回のテーマです。
目次
今回利用するアプリ等
今回はスプレッドシート側でフィルタ元のリストをQuery関数でよしなに作成する必要があります。また今回のサンプルは、ユーザ一覧と所属してるグループアドレス一覧をシートに用意してありますが、これらを手でメンテは大変なので、GASでAdmin SDKを使ってユーザ一覧やグループアドレスをトリガーにて自動で取得してくるようにしておくと尚便利です。
AppSheetサンプルをコピーした場合、データは自身のメアドが入っていないので必ずまずはテーブルデータを整備する必要があります（でないとドロップダウンは真っ白のまま）
スプレッドシートの準備
personとgroupシート
はじめからリストシートのような内容を作ればいいじゃないか？とお思いの人は考えがちょっと欠落してる。それが言えるのが少数のリストだからこそ言えるわけで、例えば今回のようなユーザ1 : リストNといった中で、リストNからユーザに紐付くものだけを取り出したいとなった場合の元テーブルがlistテーブルなのですが、そのリストは膨大なものになります。
これはデータベースの正規化と呼ばれる過程の１つで、ユーザ一覧とグループアドレス一覧という別々のものを用意して結合し、１枚のテーブルを作ったほうが楽なのです。つまりメンテナンス性。ということで、それぞれ別に用意します。
- personシート：通常のユーザの一覧。IDを基準にgroupシートと連結をします。
- groupシート：IDとpersonシートのIDを親IDとして、対象ユーザが所属してるグループアドレスを列挙していきます。
図：単純にユーザの一覧だけを格納しておく
図：ユーザのIDにひも付けてグループを列挙
listシート
さて、前述の2つのシートをpersonのID = groupの親IDとで連結して1枚のテーブルにする。結合すること自体は実はAppSheetでも出来るのですが、問題はその結果できあがったものをプルダウンに使おうとするとうまいこといかない。であるならば無理にAppSheetで構築するのではなくスプシ上でQuery関数を使って結合したものをテーブルとして取り込めばいい。無理にAppSheetで完結させようとすると沼ります。
以下のような数式で結合したものを、さらにQuery関数で列を選択することで目的の2列のシートが完成します。
|
1 2 |
//結合して欲しい列だけ表示 =query(query({group!A2:C8,iferror(arrayformula(vlookup(group!B2:B8,person!A2:D5,{2,3},false)))}),"select Col3,Col5") |
Col3がグループアドレス（ドロップダウンは１列目を表示しようとするため）、Col5がメアド（これをスライスでフィルタする対象とする）という形で抽出することが出来ます。personやgroupにあとは値を追加していくだけで自動的にlistシートも変化するので便利です。
結合する最初のQuery関数の数式はarrayformulaとvlookupも利用してるためやや複雑ですが、このテクニックも普段使用ではとても便利なものなので、身につけておくと応用の幅が広がります。
図：リストを関数で構築する
AppSheetでドロップダウンを作る
今回はドロップダウンなので、前回の記事のリレーションシップとドロップダウンの実装の応用編になります。以下のエントリーをご覧頂いてから本エントリーにチャレンジすると理解が深まります。
リストを用意する
テーブルの追加
スプシのmainシートからアプリを作成すると、mainシートだけが取り込まれた状態でスタートします。AppSheetの開発画面でまずは前述のlistシートだけをテーブルとして新規に取り込みます。
- Dataの右上のプラスボタンをクリックする
- 左サイドバートップにある「Add new Data」をクリックする
- Add dataにて「Google Sheets」をクリックする
- 対象のスプレッドシートを選択する
- シート一覧が出てくるので、追加するシートにだけチェックを入れて Add 1 Tableをクリックする
- あとはテーブルのTypeを設定したりすればOK（メアドなどはemailタイプに変更しておく）
- また、_RowNumberがKeyになってしまってるのでチェックを外し、同じ行内の他のチェックも外す。
- 一方、グループアドレスのKeyにはチェックを入れておく必要があります（こうしないとドロップダウンで利用出来ない）
- 右上のSAVEをクリックして保存する
この時、グループアドレスやメアドがセンシティブなデータということで、PII?という項目にチェックが入れられてしまい、プレビュー時に中身が表示されなくなります。よってこのチェックを外しておく必要があります。
図：PIIの項目はチェックを外す
図：Keyはグループアドレスにつける
スライスを追加する
このままではまだリストとして利用が出来ません。使っているユーザに合わせてこれをフィルタする必要があるので、ドロップダウン用のスライスを用意してあげます。
- Dataのlistテーブルの横にカーソルをもっていき＋ボタンをクリックする
- Create a new slice for listというボタンが出てくるのでクリックする
- Slice Nameはドロップダウン専用とでも命名します。
- Row filter conditionではクリックする
- 数式として、以下のようなものをセットする。USEREMAIL関数を利用します。
1[メアド] = USEREMAIL()
※メアドが現在利用してるユーザのメアドとイコールという条件になります。 - Slice Actionsでは、Compose Email(メアド)を選択します。
- Update ModeはRead Onlyのみオンにしておきます。
図：これでlistがユーザのメアドでフィルタされる
最後にリレーションを張る
実は最初にリレーションを張ってから、色々と作業をしていたのですが不可解なエラーが発生し、もう一回前述のスライスに対してmainテーブルの該当項目からリレーションを張ったらエラーが消えたという現象がありました。
AppSheetは細かい点でこういったオカシナエラーに遭遇することがあり、再度やり直してからSAVEをクリックすると通るみたいなエラー処理が甘い部分がありユーザが戸惑う部分があります。ユーザの設定が間違ってるのではなく、AppSheet側がきちんと把握できていないといアプリ側の問題になるので、「設定は正しいハズなのにオカシナエラーが出る」なんて時はもう一回やり直してみるのが大事です（煮詰まるポイント）。
よって、今回はリレーションをmainからスライスしたリストに貼るのは最後に行います。
- スライスしたドロップダウン専用を元にリレーションを張ります。
- 親であるmainテーブルの送信先という場所のTypeをRefにする。
- Source Tableは新規に作成したスライスのドロップダウン専用を指定する
- Is a part of?についてはチェックを外しておく。
- Input modeについては、Dropdownを選択します。
- Doneをクリックする
- 右上のSAVEをクリックする
図：スライスに対してリレーションを張る
使ってみる
実際に、プレビュー画面にて、新規にレコード追加をクリックし送信先をタップしてみると、全部のグループアドレスではなく、自分のメアドでフィルタされたグループアドレスのみが出てくるようになりました。但しこの代わりに検索窓がなくなってる点に注意が必要です。
選択すると送信先に選んだグループアドレスが格納されます。
図：フィルタされたドロップダウン
スプシの関数と組み合わせる利点
今回のサンプルは特にGoogle Apps Script連携は行っていませんが、前述のようにpersonとgroupのシートにはGASとトリガーで自動的にGoogle Workspaceのディレクトリから値を引っ張ってきて自動メンテにするとノーメンテになります。
また、無理にAppSheetでどうやって実現しようか？を考えるよりも、使い慣れたスプレッドシートの関数（FilterやQueryなど）があるわけなので、そちらで組み立てたほうが全然楽に構築出来ますので、スプシの恩恵は最大限利用しましょう。
特にAppSheetのリレーションはちょっとわかりにくい機能でもあり、スライスしたものをさらにスライスみたいなクエリでやるようなことは出来ないのと、ドロップダウンでは表示する列を選ぶ機能が無いため、必ず1列目に対象の値が来るように組み替える必要があります（組み替えて途中にID列を配置してしまうと、Keyに設定しようとすると出来なかった）。
