AppSheet

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

2024年6月19日 / 最終更新日時 : 2024年11月28日 officeの杜 AppSheet

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

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

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

1 今回利用するAppSheet他
2 そもそもリレーションとは何か？
3 AppSheetに実装する
4 関連リンク

今回利用するAppSheet他

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

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列が連結する

リレーションを実現する

子テーブルを追加する

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

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

図：シートを追加する

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

リレーションを設定する

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

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

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

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

フォームの設定

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

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

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

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

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

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

親のカラムを追加する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

標準の作り方

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

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

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

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

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

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

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

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

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

※但しこの手法の場合、シートに書き込まれるのはIDであって、項目名ではありません。表示が項目名になるだけなのでスプシを見ても項目名ではない為注意が必要です。

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

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

ドロップダウンのNewを消す

ドロップダウンを表示すると一番上に「New」という形でドロップリストを追加する項目が出てきます。しかしユーザに勝手に選択肢を追加させたくない場合には、以下のようにテーブル自体の追加権限をオフにしてしまえばオッケーです。また、スライスを作ってリストのソースとして使い、同様にRead Onlyなどにしても同様に可能です。

Dataを開き対象のテーブルを開く
右上のDBアイコンのTable Settingsをクリックする
Are updates allowed?にて、Addsをオフにする
Doneをクリックする
右上のSAVEをクリックして保存する

追加するボタンなどのフォーム上の要素も消えます。また、同じリストをほかでも使ってる場合、そちらは追加させたいとした場合には、3.にて以下のような数式を入れても良いです。対象のフォーム上でだけRead Onlyになります。

SWITCH(
  CONTEXT("VIEW"), 
  "フォームの名前", "READ_ONLY", 
  "ALL_CHANGES"
)

1

2

3

4

5

SWITCH(

CONTEXT("VIEW"),

"フォームの名前", "READ_ONLY",

"ALL_CHANGES"

)

図：Newで追加出来なくしました

図：追加権限をオフにする

図：Table Settingsはこちらから

ユーザに合わせてドロップダウンを変更する

ユーザのメアドを基準に、アプリを使うユーザに合わせてドロップダウンのメニューを切り替えたい要望は結構あります。特に申請フォーム系では、そのユーザに合わせてあげないと、ドロップダウンの項目が鬼のように膨大になってしまい使い勝手が著しく低下します。

そこでUSEREMAILでメアドを取得し、スプシの対象のシート上で一致するものだけをドロップダウンに表示する手法を作成しました。これについては以下のエントリーを参考にしてみてください。

AppSheetでユーザに合わせてドロップダウンをフィルタする

二重プルダウンを実現する

概要

１つ目のプルダウンを選択した後に、そのプルダウンの属性値を持ってフィルタした内容を2つ目のプルダウンで表示するといった仕組みがこれになります。例えば植物の分類を指定したら、2つ目のプルダウンではその分類を持ってる作物のリストが出てくるようにするといった具合です。ちょっとばかり複雑な仕掛けが必要になります。

図：柑橘類を選んでるのでみかん属だけが作物に出てくる

シートの設計

１つ目を分類、２つ目をサブ分類としてプルダウンを普通に作成します。これは前述までの方法で作成が可能です。この時のシートの設計は以下のような感じになります。

分類はIDと分類名のみのシートになります
サブ分類はID, 分類ID, 作物名という3列のシートになり、分類IDは1.のIDが入るようになっています。

よくあるDBのテーブル設計と同じです。これらを連結してるマスターのシートにはそれぞれ選んだIDのみが入るように構築しています（これが事項で重要な点になる）。プルダウンにはそれぞれの分類名や作物名が表示されるようにLabelにチェックを入れてあります。

図：分類のシート

図：サブ分類のシート

図：連結先マスターシート

フィルタして表示する仕掛け

さてこの時、フィルタして表示する仕掛けをするのは、2つ目のサブ分類のプルダウン。ここに対して分類プルダウンの選択項目に応じて、Valid Ifにて数式を構築してフィルタをしてあげる必要があります。

Dataシートにて作物マスタのサブ分類ID横の鉛筆マークをクリックする
ダイアログが出たら、Data Validityの項目を開く
Valid Ifをクリックして数式入力ダイアログを表示する
今回のケースだと以下のような数式を入力する

select(サブ分類[ID],[分類ID]=[_thisrow].[分類ID])

1

select(サブ分類[ID],[分類ID]=[_thisrow].[分類ID])

サブ分類[ID]および、2つ目の引数の頭にある[分類ID]は、サブ分類テーブル側のフィールドです
[_thisrow].[分類ID]は、作物マスタ側の分類プルダウンのIDを指定しています

こうする事で、分類IDプルダウンのIDも持つ、サブ分類側の分類IDとイコールのものを表示し、持ってくる値はサブ分類側のIDの値という命令文になります。今回、前述にあるようにこの作物マスタのサブ分類列にはIDだけをいれる仕様にしてるので、サブ分類[ID]としています。
SAVEをクリックする。
Doneをクリックしてダイアログを閉じる
右上のSAVEをクリックして保存する

これで、新規追加や既存編集時に分類のプルダウンを決めてから、2つ目のサブ分類のプルダウンを開くとフィルタされた状態で表示されるようになります。また、2つ目を決めてから再度、1つ目のプルダウンで違う分類を選ぶと、2つ目は自動的に空になるのでオカシナ組み合わせで保存されることがありません。

図：サブ分類のフィールドを編集する

図：数式でフィルタを行わせる

ルックアップを実現する

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

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

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

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

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

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

図：lookup出来ました

カレンダーを装備する

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

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

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

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

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

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