Google Maps Geocoding API のjson形式の結果をExcelのVBAで取得する
前回のエントリーでxml形式のGoogle MapのGeocoding APIを利用する方法についてアップしましたが今回はjson形式の結果をVBAで取得します。
今回は一般的なScriptControlを使った方法でトライしています。VBAでJScriptの機能を利用し、jsonをパースする方法でコードを書きましたが･･･結構制約があるので正直おすすめしません･･･。
今回のコードはScriptControlを使っている為、officeが64bit版の場合動作しません。
それと後述しますがプロパティを利用する際、VBAの予約語と重複している場合はエラーになって使えません。
Geocoding APIではこの予約語にひっかかり、エラーに悩まされました･･･。
1.Google Maps Geocoding APIの使い方
json形式でリクエストをする場合は「outputFormat」に「json」を設定します。
具体的には以下の通りのURLでリクエストする事になります。
http://maps.googleapis.com/maps/api/geocode/json?address=東京都中央区日本橋
Geocoding APIから返ってくるjson
上記のURLを実行すると以下のjsonが返ってきます。
{
"results" : [
{
"address_components" : [
{
"long_name" : "日本橋",
"short_name" : "日本橋",
"types" : [ "political", "sublocality", "sublocality_level_1" ]
},
{
"long_name" : "中央区",
"short_name" : "中央区",
"types" : [ "locality", "political" ]
},
{
"long_name" : "東京都",
"short_name" : "東京都",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "日本",
"short_name" : "JP",
"types" : [ "country", "political" ]
},
{
"long_name" : "103-0027",
"short_name" : "103-0027",
"types" : [ "postal_code" ]
}
],
"formatted_address" : "日本、〒103-0027 東京都中央区日本橋",
"geometry" : {
"bounds" : {
"northeast" : {
"lat" : 35.6844046,
"lng" : 139.778675
},
"southwest" : {
"lat" : 35.6780347,
"lng" : 139.7704029
}
},
"location" : {
"lat" : 35.680366,
"lng" : 139.7716695
},
"location_type" : "APPROXIMATE",
"viewport" : {
"northeast" : {
"lat" : 35.6844046,
"lng" : 139.778675
},
"southwest" : {
"lat" : 35.6780347,
"lng" : 139.7704029
}
}
},
"place_id" : "ChIJU_JmlleJGGARa7vrQMGllR4",
"types" : [ "political", "sublocality", "sublocality_level_1" ]
}
],
"status" : "OK"
}
利用するキーは以下の通りです。
・ステータスコード：status
・地域に関する補足データ：results → geometry → location_type
・緯度：results → geometry → location → lat
・経度：results → geometry → location → lng
冒頭でも書きましたがステータスコード「status」と緯度・経度で使われているプロパティ「location」が問題のVBAの予約語になります。
2.VBAサンプルコード
実際にExcelに組み込むサンプルコードを紹介します。
ボタンに組み込むコードと文字変換についてはxml形式のコードと同じな為、割愛させていただきます。
■ジオコード結果（json）から緯度、経度、ステータスを取得してカンマ区切りで返す
Function GeoCoding_LatLang(ByVal adress As String) As String
'GoogleMaps API json形式でジオコードを取得
'戻り値：緯度(lat),経度(lng),ステータスをカンマ区切り
Dim HttpReq As MSXML2.XMLHTTP60
Dim DomDoc As MSXML2.DOMDocument60
Dim URL As String
Dim js As Object
Dim objJSON As Object
Dim jsonTxt As String
Dim strGeocode As String
'Google Maps Geocoding API
URL = "https://maps.googleapis.com/maps/api/geocode/json?address=" & UrlEncodeUtf8(adress)
'XMLHTTPオブジェクトをセット
Set HttpReq = New MSXML2.XMLHTTP60
With HttpReq
.Open "GET", URL, varAsync:=False '非同期モードで通信を開始
.send 'リクエストを送信
If .Status <> 200 Then Exit Function 'リクエストが成功しなかったら終了
Set DomDoc = New MSXML2.DOMDocument60
End With
'jsonを取得する
jsonTxt = HttpReq.responseText
Set js = CreateObject("ScriptControl")
js.Language = "JScript"
'jsonにパースする関数を追加
js.AddCode "function jsonParse(s) { return eval('(' + s + ')'); }"
'追加した関数を実行して、結果を変数に格納する
Set objJSON = js.CodeObject.jsonParse(jsonTxt)
Dim wStatus As String
Dim wlat As String
Dim wlng As String
Dim wlocation_type As String
Dim wGeometry As Object
Dim wLocation As Object
Dim jItem As Variant
Dim wCount As Long
wCount = 0
'ステータス コード（status）を取得する
wStatus = CallByName(objJSON, "status", VbGet)
'結果が複数あった場合はループさせる
For Each jItem In objJSON.results
'地域に関する補足データ（location_type）を取得する
wlocation_type = jItem.geometry.location_type
'※※※ 緯度・経度を取得する ※※※
'geometryをオブジェクトにセットする
Set wGeometry = CallByName(jItem, "geometry", VbGet)
'locationをオブジェクトにセットする
Set wLocation = CallByName(wGeometry, "location", VbGet)
'緯度を取得する
wlat = CallByName(wLocation, "lat", VbGet)
'経度を取得する
wlng = CallByName(wLocation, "lng", VbGet)
wCount = wCount + 1
Next
'ステータスの状態をチェック
Select Case wStatus
'ジオコード成功の場合
Case "OK"
strGeocode = wlat & "," & wlng & ","
If wlocation_type = "ROOFTOP" Then strGeocode = strGeocode & "OK"
If wlocation_type = "APPROXIMATE" Then strGeocode = strGeocode & "位置情報は近似値です"
If wlocation_type = "RANGE_INTERPOLATED" Then strGeocode = strGeocode & "ジオコーディング出来ません"
If wlocation_type = "GEOMETRIC_CENTER" Then strGeocode = strGeocode & "-"
'以下ステータスがOKでは無く問題があった場合
'緯度、経度は空白で返す
Case "ZERO_RESULTS"
strGeocode = ",,住所から緯度経度を出力出来ませんでした。"
Case "OVER_QUERY_LIMIT"
strGeocode = ",,クエリ数が割り当て量を超えています。"
Case "REQUEST_DENIED"
strGeocode = ",,リクエストが拒否されました。"
Case "INVALID_REQUEST"
strGeocode = ",,照会条件（address、components、latlngのいずれか）がありません。"
Case "UNKNOWN_ERROR"
strGeocode = ",,サーバーエラーでリクエストが処理できませんでした。"
End Select
'結果（results）が複数ある場合
'緯度、経度は空白で返す
If wCount >= 2 Then
strGeocode = ",,住所を確認して下さい。複数の住所候補があります。"
End If
'結果を返す
GeoCoding_LatLang = strGeocode
Set objJSON = Nothing
Set js = Nothing
Set wGeometry = Nothing
Set wLocation = Nothing
End Function
VBAコードの補足
※前回のエントリー（xml形式）と同じ個所は省略します。
- jsonをセット
17～24行目：XMLHTTPオブジェクトを使いjsonを開きます。 - jsonを読み込む
27行目：responseTextプロパティでjsonを読み込みます。 - jsonをパースする
29～36行目：ScriptControlをセットし取得したjsonをパースします。 - ステータスを抽出
51行目：通常であれば「wStatus = objJSON.status」で取得できるのですがstatusが予約語の為にエラーが発生します。
そこで今回はCallByName関数を使ってjsonからキー「status」を抽出します。 - location_typeを抽出
57行目：パースしたjsonの「results」配下をループさせます。results→geometry配下で特に予約語にはひっかからない為、以下のコードで情報を取得出来ます。
wlocation_type = jItem.geometry.location_type - 緯度・経度を抽出
61～70行目：緯度・経度はresults→geometry→location配下にありますが「location」が予約語にあたります。
その為、一旦「geometry」をオブジェクトに格納し（61行目）、更にgeometryから「location」を抽出してオブジェクトに格納しています（64行目）。
そしてCallByName関数を使って「lat」、「lng」を取得するステップを踏みました。
他にスマートな方法もあるかも知れませんがとりあえず今回はこれで対応しています。
【要注意】Google Maps Geocoding API のポリシーと使用制限
Google Maps Geocoding APIは実際にGoogleマップに結果を表示するときにのみ併用で使えるもので、それ以外はポリシーで禁止されています。あくまでGoogle Maps Geocoding APIの使い方やjsonファイルのVBA操作の参考程度でご利用下さい。
また、Google Maps Geocoding APIには使用制限があります。無料で使えるのは1日に2,500回または1 秒に50回のリクエストまで。
このリクエスト数を超えた場合は従量制で課金されることになりますのでご注意下さい！詳しくは公式サイトをご確認下さい。
以上、今回はGoogle Maps Geocoding APIを使ってjsonを取得する為のVBAコードでした。
今回のサンプルファイルは以下のリンクからダウンロード可能です。
