Excel 上で施設・店舗名のリストから自動で住所リストをつくる（Google Maps API 活用）
概要
先日、住所が入ってない施設リストに住所情報を入れなきゃいけなくなり、数百件の施設リストなので1件1件検索するのもバカらしく、自動化できないかと思った次第です。やってみたら案外簡単だったので整理してシェア。
- Google Maps API の Places API を叩いて住所リストをつくれる（ただし、月ごとの回数上限を超えると費用が発生する点には注意）
- Excel（※ Mac 版は無理）上で、コーディングなしで処理できる
Google Maps API の Places API を叩く
Google Maps API に Places API という施設検索の API があったので、そこを叩いて情報を取ってみることにしました。具体的に言うと、Places API の Place Search にある Find Place requests に施設名のデータを飛ばして、formatted_address（住所データ）を取得しました。
Excel（Windows 版の 2013 以降のみ）では API 叩いて XML を呼んできて必要な項目のみぶっこ抜けるので（後述）、Places API でも XML で呼ぶと良い感じです。施設名を飛ばして住所を呼んでくるには、API キーの取得をやった上で、以下のような URL にアクセスすればよいということになる。なお、施設名も住所も日本語（language=ja）という想定。
https://maps.googleapis.com/maps/api/place/findplacefromtext/xml?language=ja&input=[施設名]&inputtype=textquery&fields=formatted_address&key=[各自の API キー]
- Google Maps API の API キーの取得あたりは以下が参考になりました。キーの制限は外部から悪用されないように必ずやるべき。特に必要なければ、基本的には自分が今使っている IP アドレスからのみ使えるように制限すれば十分なはず。
https://www.zenrin-datacom.net/business/gmapsapi/api_key/ - Google Maps API は毎月一定額の無料利用分がある従量課金制となっているので、呼び出すのが一定回数を超えてしまうと料金が発生してしまいます。この回数は呼び出す項目（fields）の数に応じて増えていくので、呼び出す項目は最小限必要な項目に絞ったほうが良い（ここでは formatted_address のみにしている）。数百件の住所リストを作る程度であれば、変に何度も呼び出したりしない限りは課金ラインにはかからないはず。なお、料金表は下記にあります。今の使用感だと、施設数×2リクエストぐらい発生するイメージ。
https://cloud.google.com/maps-platform/pricing/sheet/?hl=JA - その他、Places API の技術情報は下記にあります。
https://developers.google.com/places/web-service/search
Excel の 関数で API 叩いて住所情報を抜き出す
叩くべき API の URL は先ほどのとおり。それを Excel 上で以下のような形で叩けば、住所リストを得られます。なお、Mac 版 Excel では WEBSERVICE 関数が使えないのでできない。
- A 列に施設名があるとする。
- B 列には施設名ごとに URL が入るようにする。先ほどの URL を A 列の施設名で各行穴埋めできるように、「&」で文字列結合すればよい。
→（B 列）=「URL の前半部」&「A 列」&「URL の後半部」的なこと - C 列では WEBSERVICE 関数でデータ取得する。これで C 列に XML のデータがまるごと入る。
→（C 列）= WEBSERVICE(B 列) - D 列では、FILTERXML 関数で C 列のデータ（XML まるごと全部）から住所部分のみを抜き出す。
→（D 列）= FILTERXML(C 列, //FindPlaceFromTextResponse/candidates/formatted_address)
あと最後は取得したデータの修正。
- 冒頭に郵便番号等が入ってるのは、RIGHT 関数とか使って適当に整形すれば OK。概ね同じ形式なので、文字数指定して切ってしまえる。
- 体感では 1 割程度のデータは何らかの理由で正しくデータが入らない（空欄になったり、変な場所が出たりする）ので、ここは手作業での修正が必要になる。施設名の表記ゆれ等であれば、施設名を微修正すれば正しく入ることもある。それでもおかしい場所はもう手作業で検索して入れ直したほうが速いですね。
- C 列（WEBSERVICE 関数）は残しておくと変なタイミングで API 叩きに行ってしまう（= 想定外の課金に繋がる）可能性があるので、概ね問題なくなったら値貼り付けしておくほうが無難と思います。
これで、施設名や店舗名のリストがあるときの住所リスト作成をずいぶん効率化できるはず。数件〜数十件であればシート作成等の手間のほうが多い（実験にはよいかも）けど、数百件となると手作業検索では1〜2人日かかる作業になってしまうので、こういう形でやれると結構な時短になるんじゃないでしょうかね。
