AppSheetでドロップダウンとリレーションシップを装備する

前回のエントリーでAppSheetにて植物図鑑の大枠が作成できました。しかし、実際にはちょっと機能不足。ということで、各植物に「栽培日誌」として記録を追加できるようにしたり、作業項目をドロップダウンで選んで登録出来るようにしたい。その結果をカレンダーで一覧にしたい。

ということで、今回はRefと呼ばれるリレーションシップ機能とカレンダービューの追加の２つを取り組んでみたいと思います。

今回利用するAppSheet他

• AppSheetサンプル
• 植物図鑑v2 - Google Spreadsheet

今回のリレーションは以下の前回記事の続きであるので同じプロジェクトを利用します。ここにリレーションで各項目の明細を追加したり、明細で利用する値でドロップダウンの設定、およびカレンダービューを追加したいと思います。

AppSheetでGemini連携植物図鑑を作ってみた【GAS】

そもそもリレーションとは何か？

いきなりリレーションと言っても、これを即理解できる人は、AccessのデータベースやExcelのPower Queryを使ってる人だけだと思います。また、例え話でVLOOKUP関数を出す事例もありますが、厳密には異なります（vlookupはあくまで1:1で対応するだけで且つ、特定の値を引っ張ってきてるだけ。リレーションは1:Nで対応します）。

このリレーションとは

• 例えば、請求書というレコードに複数ぶら下がる請求明細というレコードをつなぐもの
• 請求書１に対して、N個の明細を連結し表示したり、ドロップダウンメニューを構築するためにも利用します。
• 引っ張ってくるだけじゃなく、登録時に自動で親側のIDを用いて請求明細に追記してくれる。

よって、利用するためには2つ以上のシートが必要になります。

また、その際の書き込み側のスプレッドシートもこれを考慮した列を用意しなければならず、結構な挫折ポイントの１つになると思います（Access挫折問題の根幹がコレ）。ですが、請求書と請求明細の関係にもあるように、この仕組みは非常によく利用するテクニックであるため、出来るようになるとアプリの作成の幅が広がります。

今回のアプリで言えば、植物図鑑であるため個々の植物のデータに対して、日々登録する作業日誌を登録・連結していくために必須なのです。こうしないと、作業日誌側で個々の植物と連結してるものがどれなのか？が不明となり引っ張ってこれなくなったり、連携書き込みが出来ません。

図：リレーションの概念図

図：画面イメージでの概念図

AppSheetに実装する

スプレッドシートの設計

スプレッドシートを取り込んで子テーブルとして取り込む前に、スプレッドシートに設計が必要になります。親テーブルはすでに存在しているわけですが、子テーブルにも前述にもあるように「考慮した列の追加」が必要になります。

• 親テーブルのスプレッドシートのシートにはユニークなID列が必要です。ID列を設けて、Initial Valueは「UNIQUEID関数」を追加して自動割当しておきましょう。
• 子テーブルのスプレッドシートのシートにも同様にユニークなID列が必要です。
• 子テーブルの親ID列⇔親テーブルのID列を連結させて、親でレコードが入ると、子の親IDに親テーブルのID列の値が自動で入るようになる。

どんなスプシであっても、リレーションを使ってアプリを作るならばこの作業を行う必要性があります。

図：ID列は自動でユニークなIDが入るようにする

図：子テーブルはID列は自動入力、親ID列が今回の肝

図：子テーブルの特定列と親テーブルのID列が連結する

リレーションを実現する

子テーブルを追加する

まずは、前述のテーブル設計を施したシートを追加する必要があります。以下の手順で子テーブルになるシートを追加します。

1. AppSheetの開発画面トップを開きます。
2. 左サイドバートップにある「Add new Data」をクリックする
3. Add dataにて「Google Sheets」をクリックする
4. 対象のスプレッドシートを選択する
5. シート一覧が出てくるので、追加するシートにチェックを入れて　Add 1 Tableをクリックする
6. あとはテーブルのTypeを設定したりすればオッケ。ID列は自動的にUNIQUEIDが入れられていました。

図：シートを追加する

図：シートを選択してる様子

リレーションを設定する

さて、いよいよリレーションを設定するわけなのですが、親テーブル側ではなく新規に追加した子のテーブルの側に対して行います。また、その設定をする列は「親IDが入る列」に対して行うので、以下のような手順で作成します。

1. Dataに於いて追加した子テーブルを開く
2. 親IDの列を見つける
3. TypeがTEXTになっているハズなので、ここをRefに変更する
4. ダイアログが出てくるので、Type DetailsのSource Tableに於いて親となるテーブルを指定する
5. Is a part of?は、親を削除したら子も削除するかどうか？の設定になるので、今回のケースで言えばここはチェックを入れる
6. 右上のDoneボタンをクリックする
7. 右上のSAVEをクリックして保存する
8. Dataを開いて再度子テーブルを開く
9. 子テーブルにRelated xxxxという列が新規に追加されており、一番右側にスクロールしDescriptionを見る
10. ここの説明文は不要なので空にして保存してしまう。

SHOWフラグをオフにして非表示にしても良いのですが、その場合合わせてSearchableのフラグもオフにしないとエラーになります。また、エラーになった場合、一度SHOWをオンにして保存し、もう一度SHOWをオフにして保存しないとエラーが解消しない謎のバグがあります。

図：リレーションを貼る画面

フォームの設定

実はAppSheetは親に対してリレーションを貼った時点ですでに既存の親テーブルに対して、詳細画面上でRelated xxxxxという列が新規に自動で追加されて、尚且つ、フォームのレコードの詳細がテーブル形式で自動で追加され、入力用のフォームもきちんと自動で用意してくれます。

この自動でできたテーブル形式の明細欄に対して設定を行います。

1. 右側のプレビュー画面に於いて、一度親のテーブルの一覧を開いておく。
2. 親のテーブルから特定のレコードをタップして、詳細画面を開く
3. 下の方にRelated xxxxという場所が見つかるので、明細欄ではなくRelatedにカーソルを合わせた時の鉛筆ボタンをクリックする。
4. Edit Columnをクリックする
5. Column Nameを直接書き換えても良いのですが、Display NameにてこのRelatedという文字列を書き換える
6. Doneボタンをクリックする。
7. 今度は、3.に於いて明細欄にカーソルを合わせて鉛筆ボタンをクリックする
8. Edit Viewをクリックする
9. デフォルトだとテーブル形式なのですが、View TypeをギャラリーやDeckなどに変更することも出来る。
10. 右上のSAVEをクリックして保存する

これでフォームの明細のAddをクリックすると、明細を新規追加する画面になります。明細のレコードをタップすると明細の詳細な内容が出てくるようになります。

図：明細フィールドが自動で追加される

親テーブルの項目を表示する

親のカラムを追加する

前述のようにリレーションを張って、フォームに子テーブルの内容を表示は出来ますが子テーブルの詳細を表示した際に欠けてる項目があります。それがこのままでは対象の明細が何に紐ついてるのか？（今回のケースで言えば植物の名前）がわからない点。しかし子テーブルにはその列は存在しない。こんな時に親テーブルの植物名を持ってくることができます（Accessで言えば選択クエリのような状態）。

以下のような手順で子テーブルに対して親の表示したい項目を持ってくることが可能です

1. Dataに於いて子テーブルの編集画面を出す
2. テーブル項目の右上部にある「Add Virtual Column」をクリックする
3. ダイアログが出たらAdd Formulaの欄をクリックする
4. Data Explorerタブをクリックする
5. リレーションを張っている親に該当するテーブルの一番右のアイコンをクリックすると中身が展開されます。
6. 持ってきたい列のInsertをクリックする（今回は植物名）
7. 数式にカラム名が入るのでSaveをクリックする

ちなみにこの列は親の列になるのでフォーム上では編集不可となります。

図：親の列を追加する作業

図：もってくる列を指定する

図：親の列が追加されました

クエリを制する者はAccessを制する

フォームの並び順を変更する

テーブルに列は追加できたので、該当の編集フォームや表示する明細にも自動的に表示してくれるのですが、問題はその項目は一番うしろに表示されてしまいます。これを表示される列の一番前に持ってきたい。ということでフォーム2箇所を直します。

1. 右側のプレビュー画面に於いて個別の植物の編集画面を開く
2. 下の方に追加したであろう対象の子テーブルの明細のテーブル部分にカーソルを移動して鉛筆アイコンをクリック
3. Edit Viewをクリックする
4. xxxx_Inline (system generated)という編集画面になるので、Column orderをManualに変更する
5. 今回は植物名を親から追加してるので、それを掴んで一番上に持ってきます。
6. 右上のSAVEをクリックする
7. 今度はその明細の中身をクリックして明細の編集画面を開く
8. 上部のメニューすぐ下、明細エリアにカーソルを移動して、右上の鉛筆アイコンをクリックする
9. xxxx_Detail (system generated)という編集画面になるので、
10. Column orderをManualに変更する
11. 今回は植物名を親から追加してるので、それを掴んで一番上に持ってきます。
12. 右上のSAVEをクリックする

図：列の並び順を変更することが出来ます

図：明細エリアのここをクリック

図：明細の編集画面も並び順を変える

図：親の列が一番上に移動出来ました

ドロップダウンを実装する

標準の作り方

個別レコードの入力フォームに於いて、テキストで直接入力させるのではなく、ドロップダウンメニューとして既定の値から選択してもらうことが可能です。この時にもリレーションを使用する必要があります。

1. 1列だけでタイトル行有りのシートを作成し、ドロップダウンの項目を列挙しておく
2. 前述までの手法同様にテーブルにインポートしておきリレーション設定をすることになる。
3. 前回とは異なり、今回は子テーブルの特定の列をドロップダウンにするので親である子テーブルの該当箇所のTypeをRefにする（新規に追加したリストのテーブルは弄らない）
4. Source Tableは新規に追加したリストのテーブルを指定する
5. リストのテーブルは親が削除されたからといって連動で削除されては困るので、Is a part of?についてはチェックを外しておく。
6. Input modeについては、Dropdownを選択します。
7. Doneをクリックする
8. 右上のSAVEをクリックする

これで、該当の明細の入力欄がドロップダウンに変わり、リストはリレーションを貼ったリストテーブルの中身がずらっと出てきます。一つ選んで保存すると、そのテキスト内容で追加しておいた子テーブルの中に反映するようになります。

この画面から新規でリストに追加もできますが、基本はあらかじめ使いそうなものはメンテナンスしておく必要があります。

図：ドロップダウンに変更され、リストが出てきた

1列目以外の値をドロップダウン

標準の作り方の場合、1列目がドロップダウンの選択項目として出てきます。しかし、ドロップダウンのテーブルがID / 項目名 みたいな通常のテーブル構造である場合、選択項目として表示したい内容は2列名以降にあることになります。しかし、UI上ではそれを指定する場所がない・・・

ということで、2列目の項目名をドロップダウンの選択項目として出したい場合には、ドロップダウンテーブルの「項目名として表示したいフィールドのLABELにチェックを入れる」になります。

1つのテーブル上でラベルしていを出来るのは、画像系が1つ、それ以外が1つの合計2つまでのようです。

図：項目として表示したいものにLABELオン

図：IDじゃなく項目名が出てきた

ルックアップを実現する

現在別のアプリの作成過程の中で、ドロップダウンと同じ手法で今度は別のテーブルの値を表示する方法を実現してみます。入力された値を元に別のテーブルから別の列の値を取ってきて表示するためには、以下の手順で実装します。以下の方法はVirtual Columnとして表示してるだけなので、親テーブルの元のスプシに持ってきた値を書き込むことはしません。

※この設定をすると入力欄は必然的にドロップダウンになるので注意。今回は自分の用途ではここはバーコードリーダにしてるのでスキャンすると子テーブルから取ってくるみたいな使い方をしています。

1. データを追加する親テーブル側の手で入力する欄のTypeをRefにする
2. 連携先は値を取ってくる子テーブルを指定する
3. 続けてDataの親テーブルを開いて、右上のAdd Virtual Columnをクリックする
4. Column Nameを適当に入力する
5. App Formulaをクリックして、Data Explorerをクリックする
6. 取って来たい子テーブルの対象の列をクリックする
7. Saveをクリックする

この状態で例えば、1.の欄でドロップダウンから選んだり、カメラからバーコードでスキャンして取得して入ると、6.の値が引っ張られてフォームに表示される仕組みです。フォーム上では一番うしろに挿入されてるので、フォーム上のViewで表示順番を入れて変えてあげれば尚良いでしょう。

プレビューの新規追加画面では6.のフィールドが見えない状態なのですが、値を選択したりスキャンすると表示されるようになります。また、このVirtual Columnは編集不可フィールドなので注意。

図：リレーションで別テーブルから値を持ってくる

図：lookup出来ました

カレンダーを装備する

今回新規にリレーションとして追加した子テーブルには、日付と作業した内容を記述するような列を設けています。この日付欄を使って、作業内容のタイトルを用いて、カレンダーで一覧に出来たら後で見返す時に便利です。そこでカレンダービューでビューを追加してみようと思います。

まずはカレンダービューを新規に追加します。

1. AppSheetの開発画面を開く
2. 左サイドバーより、Viewを開く
3. Primary Navigation横の＋をクリックして、Create a new Viewをクリックする
4. View Nameを適当に入力する（例：カレンダー）
5. for this dataは、今回は前述で新規に追加した子テーブルを指定します。
6. view typeは、カレンダーを指定します。
7. PositionをLaterにします（これでトップ画面下にボタンが表示される）。
8. View Optionでは、今回は日付欄が1個しか無いのでStart DateもEnd Dateも同じ日付欄を指定します。もう一個ある場合は期間で表示されるようになります。
9. Default Viewは自分はMonthで指定しました。
10. DisplayのIconを指定しておきましょう。
11. 右上のSAVEをクリックする

これでトップ画面にカレンダービューを追加することが出来ました。ただ、通常はメインの親テーブルの子に対して作ることはあっても、その更に明細に対してカレンダーを作っても、親がどれなのか？わからなくなるので、子までのものに作るのが一般的です（さらに親に移動するロジックが必要）。

図：カレンダービューを追加した様子

図：カレンダービューの様子

関連リンク

• AppSheet で請求書アプリ（第3回）Refでリレーションシップを設定
• AppSheet実践編シリーズ⑯～絶対覚えたい！！Refってなに？～
• 【AppSheet】プルダウンやボタン選択の作り方
• Google AppSheetでプルダウンを使う
• How to disable add button on a related table view after some times
• AppSheetやってみた#3〜選択入力項目で「Enum」と「Ref」を使い分けてみる
• AppSheet実践編シリーズ⑦～ビュータイプ活用術を学ぼう！！～
• AGRIHUB機能紹介
• ExcelのVLOOKUP関数のような動きはできますか？