便利じゃなければプログラムじゃ無い!
仕事が楽になって業務効率がUPするシステムを提供!

Google Maps Geocoding API のjson形式の結果をExcelのVBAで取得する

2017/11/7 (火) • Excel, VBAコメントは受け付けていません。Read More »

img_geocode_json
前回のエントリーで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形式)と同じ個所は省略します。

  1. jsonをセット
    17~24行目:XMLHTTPオブジェクトを使いjsonを開きます。
  2. jsonを読み込む
    27行目:responseTextプロパティでjsonを読み込みます。
  3. jsonをパースする
    29~36行目:ScriptControlをセットし取得したjsonをパースします。
  4. ステータスを抽出
    51行目:通常であれば「wStatus = objJSON.status」で取得できるのですがstatusが予約語の為にエラーが発生します。
    そこで今回はCallByName関数を使ってjsonからキー「status」を抽出します。
  5. location_typeを抽出
    57行目:パースしたjsonの「results」配下をループさせます。results→geometry配下で特に予約語にはひっかからない為、以下のコードで情報を取得出来ます。
    wlocation_type = jItem.geometry.location_type
  6. 緯度・経度を抽出
    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コードでした。

タグ: , , , , , , ,

Comments are closed.