【AppSheet】郵便番号で住所を自動入力する方法を実例で解説(自動検索機能)

AppSheetを使っていると、よく住所の入力機能などである郵便番号を入力すると都道府県や市区町村を自動入力してくれる機能を使いたいときがあります。

もちろんAppSheetでも実現することができます。

ここでは、AppSheetのビューで郵便番号を入力したら該当する都道府県と市区町村を自動で入力する方法について解説しています。

全体の流れ
郵便番号と住所のデータを入手
分割してAppSheetに読み込ませる
データ参照先のテーブル
バリデーションの設定（半角文字で数字とハイフンのみ許可する）
バーチャルカラムの作成（自動検索機能の実装）
カラムに検索結果を自動入力
1. FORMULAにバーチャルカラムを指定する
2. ビューの確認

全体の流れ

AppSheetのビューで郵便番号を入力したら該当する都道府県と市区町村を自動で入力する全体の流れは次のようになっています。

郵便番号自動検索機能の実装の流れ

郵便番号と住所のデータを入手
分割してAppSheetに読み込ませる
バリデーションの設定（半角文字で数字とハイフンのみ許可する）
バーチャルカラムの作成（自動検索機能の実装）
カラムに検索結果を自動入力

ざっくりまとめると、AppSheetに郵便番号と都道府県、市区町村のデータを持ったカラムを作成し、SELECTを使った検索でひっぱります。

郵便番号と住所のデータを入手

データのダウンロード

まずは郵便番号と住所のデータを入手します。

郵便局の公式ホームページに郵便番号データダウンロードページがあります。

CSVファイルで「読み仮名データの促音・拗音を小書きで表記しないもの」をクリックします。

右下にある「全国一括」をクリックします。

※160MBを超えているのでCSVファイルの中では比較的重いです。

UTF-8への変換

ken.all.zipというフォルダがダウンロードできたら、解凍します。

中のKEN_ALL.CSVファイルは文字コードがShift-JISのため、これをUTF-8に変換します。

ローカルのExcelやメモ帳で開きます。

ファイルメニューから「名前を付けて保存」を選択します。

ファイル名で拡張子「.csv」を指定し、ファイルの種類を「すべてのファイル(*.*)」、エンコードを「UTF-8」にします。

これで「保存」をクリックします。

注意点

直接スプレッドシートに変換しないでください。文字化けします。

※日本語の文字コードがShift-JISのため。UTF-8に変換すればスプレッドシートでも正しく表示されます。

文字列としてデータを抽出する

※注意点：冒頭の0が消える

郵便番号のデータを扱う上で注意しなければいけないことがあります。それは0始まりの郵便番号が存在するということです。

例えば、北海道の札幌市関連の郵便番号は「0600041」のように冒頭に0がつきます。

こういった0始まりの数値を含んだCSVを通常どおりExcelやスプレッドシートで開くと型が数値として認識され冒頭の0が消えてしまいます。

このままデータを使用すると入力された郵便番号と一覧の値が違ったものになってしまいます。

▼メモ帳で確認すると郵便番号は文字列として保存されていることがわかります。

文字列としてスプレッドシートに移管する

補足

ここではスプレッドシート上で変換する方法を紹介しますが、データ数が重く非常に時間がかかります。

処理が重くて進まない場合はローカルのExcelで文字列データとして読み込むか、データを分割して処理していくなどで対応可能です。

まずは、UTF-8に変換したCSVファイルをGoogleドライブに保存し、「アプリで開く」から「Googleスプレッドシート」を選択します。

この状態では郵便番号の0が消えています。

郵便番号のC列を選択した状態で、上部メニューの「123」→「カスタム数値形式」を選択します。

「0000000」（0を7つ）入力し「適用」をクリックします。

これで冒頭の0が表示されます。

参考

カスタム数値形式で使用した「0」が何を意味しているのかや詳細については下記をご参考ください。

カスタム数値形式の0,#シャープの意味とは何か？

不要なデータの削除

現時点では行124,683 x列15とかなり大量のデータです。このため、列郵便番号が正しく表示されるようになったら、不要なデータを削除します。

その前に、簡単に郵便番号ファイルのデータ構成を紹介しておきます。各列のデータは以下のような意味を持っています。

各列のデータ

全国地方公共団体コード（JIS X0401、X0402）………　半角数字
（旧）郵便番号（5桁）………………………………………　半角数字
郵便番号（7桁）………………………………………　半角数字
都道府県名　…………　半角カタカナ（コード順に掲載）　（※1）
市区町村名　…………　半角カタカナ（コード順に掲載）　（※1）
町域名　………………　半角カタカナ（五十音順に掲載）　（※1）
都道府県名　…………　漢字（コード順に掲載）　（※1,2）
市区町村名　…………　漢字（コード順に掲載）　（※1,2）
町域名　………………　漢字（五十音順に掲載）　（※1,2）
一町域が二以上の郵便番号で表される場合の表示　（※3）　（「1」は該当、「0」は該当せず）
小字毎に番地が起番されている町域の表示　（※4）　（「1」は該当、「0」は該当せず）
丁目を有する町域の場合の表示　（「1」は該当、「0」は該当せず）
一つの郵便番号で二以上の町域を表す場合の表示　（※5）　（「1」は該当、「0」は該当せず）
更新の表示（※6）（「0」は変更なし、「1」は変更あり、「2」廃止（廃止データのみ使用））
変更理由　（「0」は変更なし、「1」市政・区政・町政・分区

データ量が多いと重くなるので不要な行を削除します。ここでは以下の４つのデータのみにします。

抽出するデータ

郵便番号
都道府県名
市区町村名
町域名

削除したことでデータ量が半分ほどになり少し軽くなりました。

タイトル行の挿入

AppSheetで読み込ませるために、一番上にタイトル行を挿入します。

不要な文言（「以下に掲載がない場合」と「～丁目」の削除）

デフォルトのデータでは「町域」の行に「以下に掲載がない場合」や「～丁目」といった文言が表示されています。

AppSheetでデータを参照したときにこれらのデータが表示されてしまうため、削除します。

「以下に掲載がない場合」の削除

「ctrl + h 」で置換メニューを表示し、「以下に掲載がない場合」を空文字に置換します。

「～丁目」の削除

デフォルトでは丁目が（1~19丁目）のようにひとまとまりで入力されています。

何丁目かはユーザーに直接入力してもらいたいのでこの文字列を削除します。

「ctrl + h 」で置換メニューを表示し、「以下に掲載がない場合」を空文字に置換します。検索に(（.*）)を入力し、「正規表現を使用した検索」にチェックを入れます。

(（.*）)

「すべて置換」をクリックすれば完了です。

分割してAppSheetに読み込ませる

データの分割

AppSheetでスプレッドシートを読み込むことができる最大の行数は「100,000」行という制限があります。

https://support.google.com/appsheet/answer/10104789?hl=ja

郵便番号の行数は「124,683」で上限を超えています。このためデータを2分割します。

スプレッドシートのコピーを作成してシート名を「郵便番号一覧2」とします。

区域的にはだいたい長野と岐阜が半分ぐらいでエリアも別れるので、岐阜県を境にデータを2分割すると調度いいかとおもいます。

「郵便番号一覧」は59,706行、「郵便番号一覧2」は64,979行になります。

https://www.cas.go.jp/jp/seisaku/doushuu/kuwari/dai4/siryou6.pdf

以上で郵便番号検索のもととなるデータの作成は完了です。

AppSheetに読み込ませる

スプレッドシートの「拡張機能」→「AppSheet」→「アプリを作成」でAppSheetアプリを作成します。

既にアプリがある場合は、「Data」 → 「＋アイコン」をクリックします。

Google Sheetsから対象のスプレッドシートを選択し追加します。

この郵便番号の一覧は基本的にビューでユーザーが追加、編集、削除することがないため、インポートするときは「Read-Only」にしておきます。

データベースにテーブル追加されます。

カラムの設定

ビューに表示することも編集することもないため、以下のように設定します。

KEYは「郵便番号」です。

データ参照先のテーブル

郵便番号一覧テーブルのデータを参照するテーブルとして、ここでは「収入先一覧」というテーブルを使用します。（テーブル名やテーブルの構成は各自で好きに作成してください）

ポイントとして「郵便番号」「都道府県」「市区町村」「住所１」「住所２」というカラムを作成します。型はどれも「Text」とします。

ビューでこのテーブルにレコードを追加しようとするとUIは以下のようになります。

ここで「郵便番号」を入力したら「都道府県」「市区町村」「住所１」が自動で入力されるようにします。

バリデーションの設定（半角文字で数字とハイフンのみ許可する）

「Valid If」と「Invalid value error」の設定

「郵便番号」の入力は半角整数またはハイフンのみとする必要があるため、入力制限（バリデーション）を設定します。

AppSheetで入力するデータに制限を設定するには「Data Validity」の「Valid If」を使います。なお、下の「Invalid value error」は入力規則に合わなかったときに表示するエラーメッセージです。

カラムの編集画面を開いて、「Valid If」に以下の数式を入力します。ついでに「Invalid value error」にエラーメッセージ「半角の数値とハイフンのみを使用してください」も入力しておきます。

len(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  [_this],"0",""),
  "1",""),
  "2",""),
  "3",""),
  "4",""),
  "5",""),
  "6",""),
  "7",""),
  "8",""),
  "9",""),
  "-",""),
)=0

MEMO

現時点で、AppSheetのビューの入力制限を行う際に、正規表現を使うことはできません。このため数式でなんとか記述する以外に方法はなさそうです。

今後のAppSheetのアップデートに期待します。

文字数制限のバリデーション｜Type Details

なお、カラムの型がTextの場合、カラムの設定で「Type Details」の「Maximum length（最大文字数）」と「Minimum length（最少文字数）」を指定することができます。

ビューに入力された値がこの文字数制限を外れるとエラーメッセージが表示されます。

※エラーメッセージは「Data Validity」の「Invalid value error」に入力した値が表示されます。

例えば「000-0000」（8文字）と「000000」（7文字）のみの入力を許可する場合は以下のように設定します。

入力したバリデーションに合わせて「Data Validity」の「Invalid value error」も編集します。

"半角の数値とハイフンのみを使用してください（例:123-4567）。文字数は8文字で入力してください。"

ビューの確認

ビューで設定した入力規則とエラーメッセージが適用されているか確認します。

文字数が足りないとNGです。

全角文字を含む場合もエラーになります。

123-4567のように郵便番号の形式で入力すればエラーメッセージは表示されません。

数式の解説

len(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  [_this],"0",""),
  "1",""),
  "2",""),
  "3",""),
  "4",""),
  "5",""),
  "6",""),
  "7",""),
  "8",""),
  "9",""),
  "-",""),
)=0

Valid Ifに入力した以下の数式は「0~9」と「-」のみの入力を許可するというものです。

使っている関数は、substituteとlenの２つのみです。

指定した文字列のみを許可するバリデーション（len関数とsubstitute関数）

指定した文字列のみ入力を許可するという最少のバリデーションの数式は以下になります。

len(
 substitute([_this],"入力を許可する文字列","")
)=0

substitute関数

まず一番初めに実行している処理が以下のsubstitute関数です。例えば、半角数値の「0」のみ許可する場合は以下のように記述します。

substitute([_this],"0","")

これは[_this]で現在のカラム（収入先一覧テーブルの郵便番号カラム）でビューで入力された値です。

この「0」を置換して「」（空文字）にするという処理になります。

例えば「000」と入力すれば、処理結果は「」です。「0123A00」だと「123A」になります。

len関数

len関数は引数で与えられた値の文字数をカウントする関数です。

以下の数式はカウントした文字数が0の場合はtrue、0以外の場合はfalseになります。

len(
 substitute([_this],"0","")
)=0

つまり、入力値が「000」のように入力を許可する文字列のみの場合はsubstituteで処理されあた結果が「」（何もなし）になります。

この文字数は0なので、lenの処理結果はtrueになります。

一方、「00A」や「100」のように1文字でも違いう値が入っていた場合はsubstituteの置換結果が「Ａ」や「1」となり、文字数は1以上となります。このため len()=0 の結果はfalseになります。

substituteで置換を繰り返す

入力を許可する文字列を複数指定する場合は、その分だけsubstituteを繰り返します。

len(
  substitute(
  substitute(
  substitute(
  [_this],"入力を許可する文字列１",""),
  "入力を許可する文字列２",""),
  "入力を許可する文字列３","")
)=0

「0~9」と「-」のみの入力を許可する

「0~9」と「-」のみの入力を許可する場合は、合計で10個のsubstituteを記述します。

len(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  substitute(
  [_this],"0",""),
  "1",""),
  "2",""),
  "3",""),
  "4",""),
  "5",""),
  "6",""),
  "7",""),
  "8",""),
  "9",""),
  "-",""),
)=0

数式が正しいか確認する（便利なデバッグ機能）

substituteとlenを使った入力規則の設定はやってること自体は簡単なのですが、数式が長く正しく入力できているかの確認が大変です。

そんなときは「Valid If」に数式を入力したときに表示される下側の表示を使います。

構文のエラーチェック

緑の丸のチェックマークが出ているときは、数式の構文は合っているという状態です。閉じカッコが足りない場合や関数の使い方が間違っているときはエラーメッセージが表示されます。

エラーメッセージが出たときはメッセージの内容にそって関数を修正します。

数式の処理結果のテスト

数式の構文に間違いがなくても、記述した数式で意図した結果が返ってきているかまではわかりません。

そんなときは右端にある「Test」をクリックします。

すると、対象のテーブルに登録されているデータの各行ごとに、処理結果を表示してくれます。

左端の青い文字が各行に数式を適用したときの処理結果です。

下記のように「Y」と表示されている場合は「Yes」つまり「true」ということです。

処理結果の下に表示されているプルダウンマークをクリックすると数式の中の処理を1つずつ適用したときの結果を確認することができます。

このデバッグ機能はとても重宝します。

バーチャルカラムの作成（自動検索機能の実装）

バーチャルカラムの作成

「郵便番号」カラムに入力された郵便番号を元にSELECT関数を使って「郵便番号一覧」テーブルのデータを引っ張ってきて「都道府県」や「市区町村」「住所1」に値を入力するように設定します。

ここで重要なのが、SELECT関数を通常のカラムのFormulaに設定してもデータが動的に更新されないということです。通常のカラムとはスプレッドシートと連動しているカラムのことです。

このため、AppSheet上のみのカラムであるバーチャルカラムを作成します。

対象のテーブルを選択し、右上の「＋」アイコンをクリックします。

参考

バーチャルカラムについてもう少し詳しく知りたい方は下記をご参考ください。

【AppSheet】FORMULAとINITIAL VALUEの違いは何か？いつどう使えばいいかを実例で解説｜バーチャルカラム（Virtual Column）とは何か？

都道府県のバーチャルカラムを追加

App formulaの設定

バーチャルカラム名を「_都道府県」として、App formulaに以下の式を入力します。

IF([郵便番号]="","自動で入ります",
  IF(
    ANY(SELECT(郵便番号一覧[都道府県],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))<>"",
    ANY(SELECT(郵便番号一覧[都道府県],substitute([_THISROW].[郵便番号],"-","")=[郵便番号])),
    ANY(SELECT(郵便番号一覧2[都道府県],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))
  )
)

MEMO

ファイル名冒頭の「_（アンダースコア）」はバーチャルカラムであることを意味しています。他にも「VC都道府県」といったカラム名の書き方もあります。

右上の「Done」と「SAVE」をクリックして保存します。

追加したバーチャルカラムは一番下に表示されます。

本番ではビュー上で見せる必要がないためSHOW?のチェックを外します。ここでは確認のためチェックを入れたままにしています。

ビューで確認する

ビューで確認すると郵便番号が何も入力されていない状態では指定した「自動で入ります」が表示されています。

郵便番号を入力してEnterキーをクリックします。

すると、都道府県に郵便番号でヒットした都道府県名が入ります。

市区町村のバーチャルカラムを追加

都道府県と同様に、市区町村のバーチャルカラムを追加します。

バーチャルカラム名は「_市区町村」、App formulaの数式は以下のようにします。

IF([郵便番号]="","自動で入ります",
  IF(
    ANY(SELECT(郵便番号一覧[市区町村],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))<>"",
    ANY(SELECT(郵便番号一覧[市区町村],substitute([_THISROW].[郵便番号],"-","")=[郵便番号])),
    ANY(SELECT(郵便番号一覧2[市区町村],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))
  )
)

バーチャルカラムが一番下に追加されます。

こちらもビューで確認しておきます。

郵便番号を入力すると該当する市区町村名が表示されます。

町域のバーチャルカラムを追加

都道府県と同様に、町域のバーチャルカラムを追加します。

バーチャルカラム名は「_町域」とします。App formulaの数式は以下のようにします。

IF([郵便番号]="","自動で入ります",
  IF(
    ANY(SELECT(郵便番号一覧[町域],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))<>"",
    ANY(SELECT(郵便番号一覧[町域],substitute([_THISROW].[郵便番号],"-","")=[郵便番号])),
    ANY(SELECT(郵便番号一覧2[町域],substitute([_THISROW].[郵便番号],"-","")=[郵便番号]))
  )
)