AppSheetを使って図書館などで本の貸し出しを想定した貸出管理アプリの作成方法についてまとめています。
まずは、スプレッドシートの各シートをテーブルとして読み込み、Refなどの設定をする手順です。
データベースの構成
ここでは、データベースとしてスプレッドシートを使用します。なお、「データベース＝スプレッドシート」、「テーブル＝各シート」となります。
スプレッドシート名「本貸出管理」に以下の３つのシートを作成します。
- 貸出管理マスター
- 本一覧
- ユーザー一覧
データーの保管場所
スプレッドシートは「マイドライブ」→「AppSheet」→「data」→「本貸出管理システム」の中に作成しています。
なぜ、「マイドライブ」→「AppSheet」→「data」の中に生成したかというと、AppSheetからサンプルプログラムなどを作成した場合、このdataフォルダの配下にプロジェクトごとのフォルダが生成されるためです。
貸出管理マスター
貸出管理マスターでは、いつ誰が何を借りたか、あるいは返却したかと、対象の本が今何冊あるかがわかるようになっています。
本一覧
本一覧では、書籍名、著者、発売年、全数、貸出数、残数が記録されています。
ユーザー一覧
ユーザー一覧には個人情報が登録されています。
※データは「個人情報テストジェネレーター」で機械的に生成したダミー情報です。
マスターテーブルの読み込みと設定
AppSheetにテーブルを読み込みます。
スプレッドシートの「貸出管理マスター」をクリックした状態で、「拡張機能」→「AppSheet」→「アプリを作成」をクリックします。
すると、対象のデータをベースとして自動でアプリが生成されます。
右下の「Customize with AppSheet」をクリックします。
右メニューのデータベースのアイコンをクリックすると、AppSheetを作成したときに選択していたシートの内容でテーブルが作成されていることがわかります。
自動で「No」の列がKEYとして設定されています。
KEYとは何か？
読み込んだテーブルでは「No」がKEYとして設定されています。
KEYとはテーブルにおいて、各行のデータを一意に識別するために使う項目です。一意である必要があるため、重複や空白があってはいけません。
UNIQUEID関数によるKEYの設定
ここでは、１ずつ増えていく整数をKEYに設定していますが、UNIQUEID関数を設定してランダムな8桁の数値を生成するように設定することもできます。
その場合は、「INITYAL VALUE（初期値）」に「UNIQUEID関数」をセットします。
UNIQUEID()KEYを編集不可にする ～数式でfalseを指定～
KEYは一意の値である必要があるため、書き換えを不可にしておく必要があります。
デフォルトでは「EDITABLE？」にチェックが入っており、編集可能な状態になっています。
単に、チェックを外すだけでも編集不可な状態にすることはできますが、間違ってチェックが入ってしまうことを防止するには数式で「false」を指定します。
「No」の左側にあるペンアイコンをクリックします。
すると、詳細な設定メニューが表示されます。
中段あたりに「Update Behavior（挙動の更新）」とあるので、これをクリックすると、メニューが開きます。
「Editable」という項目の右側にあるチェックマークを外したあとに、フラスコアイコンをクリックします。
すると、数式を入力する欄が出てくるのでクリックします。
「false」と入力し「Save」をクリックします。
Editable？の横に「=false」と表示されます。これが、実際のメニュー画面上での表示状態になります。
テーブルの画面に戻ってみると「EDITABLE？」のチェックボックスが消えて、入力した数式（の最初の文字だけ）が見えています。
なお、すぐ右横に「×」がありますが、これをクリックするとチェックあり状態に戻ってしまうので、クリックしないようにしましょう。
「Update Behavior」でEditableのチェックを外してから数式に「=false」を設定している場合は、「×」をクリックすると、チェック無のチェックボックスになります。
_RowNumberとは何か？
ここで、AppSheetに登録されたデータを見てみると、スプレッドシートには存在していない「_RowNumber」という列（カラム）が勝手に追加されていることがわかります。
この「_RowNumber」とは、スプレッドシートを読み込んだときに自動で追加されるもので、中身はスプレッドシートの行番号になっています。
右の方にスクロールすると「DESCRIPTION（説明）」という列に「Number of this row（現在の行番号）」と言う設営が記述されています。
なお、スプレッドシートの行は追加や削除される可能性があり、変更のない一意の値として管理することができないため、「_RowNumber」をキーとして使うことは推奨されていません。
ChangTimestamp型の設定
「入力日」はデフォルトでは「Date型」として判定されています。
ここには、自動で入力した日時（タイムスタンプ）を入れたいので、型を「ChangTimestamp」にします。
自動入力であり、マニュアルで入力する必要がないため「REQUIRE？」のチェックを外しておきます。またタイムスタンプなのでTODAY関数も不要なので削除します。
TODAY()やNOW()の削除
読み込んだ時点の列の状態によっては、「INITIAL VALUE」のところに、日付データの場合はTODAY関数、日付＋時間の場合はNOW関数が自動入力されていることがあります。
1回の登録時に貸出日と返却日に同時に日付が入ることは基本的にないので、ここでは返却日のTODAY関数を削除しておきます。
REQUIRE?の設定
てーブル読み込み時の状態では「REQUIRE?」のチェックがまばらになっていることがあります。
ここでは「ユーザー」「書籍名」は必須入力項目なのでチェックを入れます。
本一覧とユーザー一覧テーブルの読み込み
複数のシートをまとめて読み込む
続いて「本一覧」と「ユーザー一覧」テーブルをAppSheetに読み込みます。
左メニューのデータベースアイコンの中の上にある「＋」マークをクリックします。
ソースの選択画面が表示されるので「Google Sheets」をクリックします。
Googleドライブの中の対象ファイルを選択します。
「ユーザー一覧」と「本一覧」にチェックがついていることを確認して、右下の「Add 2 tables」をクリックします。
これで、すべてのシートがデータベースのテーブルとして登録されました。
警告を外す方法｜PIIとは何か？
新たに追加した「ユーザー一覧」に警告マークが出ています。警告の内容は以下のようになっています。
Table ‘テーブル(シート)名’ may contain sensitive data in column(s): メールアドレス, 住所, 携帯電話番号
これは「個人情報が含まれている可能性があるため、自動で列にPIIを設定しました」と言う意味になります。
PIIとは個人情報のことで、AppSheetではPII列に設定することで、そのカラムのデータをサーバー上に保持せずバックエンドにデータを渡すだけの働きをするようになります。
管理画面右上の「SAVE」をクリックすれば傾向は消えます。
PIIって何？PIIの設定を外したいという方は下記をご参考ください。
TYPE（データ型）の確認と修正
スプレッドシートのシートをテーブルとして登録すると、AppSheetがある程度自動でTYPEやKEY、SHOWなどの設定をしてくれます。
ただし完璧ではなく意図しない状態や最適でない状態になっていることがあるため、中身を確認します。まずはTYPE（データ型）からです。
ユーザー一覧のTYPE
ユーザー一覧テーブルのTYPEを見ると、ある程度いい感じにはなっています。
以下のカラムのTYPEを変更します。
- 氏名：「TEXT」→「Name」
- 生年月日：「Number」→「Date」
- 携帯電話番号：「Text」→「Phone」
- 郵便番号：「Phone」→「Address」
- 住所：「Text」→「Address」
本一覧のTYPE
本一覧テーブルのTYPEは以下のようになっています。
以下のTYPEを変更します。
- 筆者：「Text」→「Name」
変更したら、右上の「SAVE」をクリックして確定します。
KEYの確認と修正
KEYカラムの確認
続いてKEYの確認をします。
新たに読み込んだテーブルはいずれも「No」カラムがKEYとして登録されています。単独のテーブルであればNoはデータ識別用の一意の値なのでこれでＯＫです。
RefのためのKEY設定
後ほどでてきますが、テーブル同士を紐づけるためにRefという設定を行います。そのときに、紐づけられる側のテーブルで紐づけられるカラムはKEYに設定されている必要があります。
このため、「ユーザー一覧」テーブルでは「氏名」をキーに設定します。「本一覧」テーブルでは「タ書籍名」をキーにします。
「Ref」でテーブルを指定した場合の紐づけは、紐づけ先で「KEY」となっているカラムで行います。このため、それぞれのテーブルでカラム名が異なっていても問題ありません。
連番のidの設定｜MAX (テーブル名[カラム名])+1
今回の場合、テーブルのKEYにはNoを適用し、そのNoは1づつ増えていく値になっています。このため、追加したときに自動で1つづ増える設定にします。
「INITIAL VALUE」に以下の式を記述します。※「FORMULA」ではありません。
MAX (ユーザー一覧[No])+1こうすることで、ユーザー一覧テーブルのNoカラムの現在の最大値を取得し、それに＋1した値を設定することができます。
複数人が同時にAppSheetを利用する場合は、MAX関数を使って連番のidを設定する方法は推奨されていません。同時に登録があった場合、同じ数値が設定されてしまう可能性があるためです。
一意の値を生成してくれる「UNIQUEID関数」や「RANDBETWEEN関数」を使用します。
なお、シートの行番号に対応した「_RowNumber」は行の追加や削除で番号が変わるリスクがあるため、KEYとしての設定は推奨されていません。
FORMULAとINITIAL VALUEはどちらも数式を入力できるけど、何が違うの？と疑問に思った方は下記をご参考ください。
EDITABLEのチェックを外す（編集不可にする）
KEYは自動算出して常に一意の値になるようにしなければなりません。このため、後から変更されることがあってはいけません。
デフォルトでは「EDITABLE?（編集可能？）」にチェックが入っているので、これを外します。加えて、カラムの詳細メニューを開いて「Update Behavior」の「Editable?」の数式に「false」を設定しておくとより安全です。
右上の「DONE」をクリックして戻ると、以下のように設定が反映されます。
Ref（Relation）の設定
続いて、それぞれのテーブルを共通するカラムを指定して関連付けを行います。
データベースにおけるこの関連付けのことを「Relation（リレーション）」と言い、AppSheetにおけるカラムの型は「Ref」になります。
ユーザー名の紐づけ
①「貸出管理マスター」テーブルの「ユーザー」カラムと
②「ユーザー一覧」テーブルの「氏名」カラムの値が紐づきます。
大本となる「貸出管理マスター」テーブルの「ユーザー」カラムの編集アイコンをクリックします。
「Type」を選択する項目があるので「Ref」にします。すると、下に「Type Details」というメニューが表示されるので「Source table」で対象となるテーブル「ユーザー一覧」を選択します。
設定が完了したら、右上の「SAVE」をクリックします。
すると、紐づけしたテーブルの一番下に「Related テーブル名」といったカラムが追加されます。
これは、「貸出管理マスター」テーブルでRefの設定がされていることを示しています。
FORMULAには REF_ROWS("貸出管理マスター", "書籍名") と記載されており、「貸出管理マスター」テーブルのカラム「書籍名」とKEYが紐づいていることを示しています。
