Excel VBAとGoogle Maps APIを用いて複数地点間の移動時間を一気に求めてみた。
どうも、一年ぶりくらいですかね、、、？
資格勉強が嫌すぎて、現実逃避がてらこの記事を書いています
タイトルにある通り、ど素人の僕がGoogle Maps APIを使う機会があったので何をしたか、そしてどうやってするかについてご紹介します〜！
【やりたいこと】
複数の地点同士の移動時間を一気にExcel上で計算がしたい。
要は以下の表みたいな物を楽して作りたいんです。
例えば、京都駅ー静岡駅だと移動に120分かかるってことがこちらの表でわかります。
こんな表、Google mapや、乗り換え検索で一つ一つ調べればすぐできるじゃん？っていう方もいらっしゃるかと思います。
確かにこの表であれば15回[ =(6*6-6)/2]Googleさんにお聞きすればいいのですが、今回僕が作りたいのは600箇所以上の移動距離をまとめた表です。
600箇所だとだいたい、、、(600*600-600)/2=359700
36万回Googleさんに聞かないといけないわけで
一つ調べるのに10秒とかで計算しても1000時間
流石に人間やめたくなります。
そこで、Google Maps APIのご登場です。
なんと、上の計算をExcel関数とAPIを使えば一瞬でできます。
【やりかた】
やり方はこちらの記事を参考にしつつ、これだけだと動かなかったのでエンジニアの友人に質問して少しだけコードをいじりました。
環境はこんな感じのポンコツっぷりです。
・Excel 2007
※今回のやり方はwindowsでしかできません
step2で行うMicrosoft XML. v6.0の実行環境がwindowsのみっぽいのです。
Step1：Google Maps APIを動かすためのKEYを取得
Step2：Excel VBAの参照設定をMicrosoft XML. v6.0に設定
Step3：VBAの標準モジュールにコードを貼り付け
Step4：Excelでユーザー定義関数を用いて表を作る
以上です！！
では、詳細にいきます。
Step1：Google Maps APIを動かすためのKEYを取得
こちらはわかりやすく解説しているサイトがたくさんあるので参考にたサイトを貼っときますね。
ちなみに今回は特に公開するわけでもないので、キーの制限までは多分不要、、？
Step2：Excel VBAの参照設定をMicrosoft XML. v6.0に設定
1, まずVBAのエディターを開きます。
リボンの開発＞Visual Basic
2, 参照設定で「Microsoft XML. v6.0」を選択
僕の場合、参照設定で「Microsoft XML. v6.0」が見当たらなかったので新しくMicrosoftのサイトからインストールしました。↓
インストールしたあとexcelは再起動した方がいいかも
Step3：VBAの標準モジュールにコードを貼り付け
1, VBAの標準モジュールを開く
2,以下のコードを標準モジュールに貼り付ける
以下のコードを標準モジュールに貼り付けてください。
その際コード中の「ここにAPIのキーを入れる」の代わりにStep1でで取得したKEYをぶち混んでください。
ーーーーーーー以下、コードーーーーーー
Function DGMAP(origin As String, destination As String, distance As Boolean) As String
‘http://stackoverflow.com/questions/10116301/google-maps-api-for-time-and-distance-data-access-vba
Dim sXMLURL As String
sXMLURL = "https://maps.googleapis.com/maps/api/directions/xml?origin=" _
& origin & "&destination=" & destination & "&sensor=false&key=ここにAPIのキーを入れる”
Dim objXMLHTTP As MSXML2.ServerXMLHTTP60
Set objXMLHTTP = New MSXML2.ServerXMLHTTP60
With objXMLHTTP
.Open "GET", sXMLURL, False
.setRequestHeader "Content-Type", "application/x-www-form-URLEncoded"
.Send
End With
'Debug.Print objXMLHTTP.ResponseText
Dim domResponse As DOMDocument60
Set domResponse = New DOMDocument60
domResponse.LoadXML objXMLHTTP.ResponseText
Dim ixnStatus
Set ixnStatus = domResponse.SelectSingleNode("//status")
If ixnStatus.Text = "OK" Then
Dim ixnDistance, ixnDuration
Set ixnDistance = domResponse.SelectSingleNode("/DirectionsResponse/route/leg/distance/text")
Set ixnDuration = domResponse.SelectSingleNode("/DirectionsResponse/route/leg/duration/text")
End If
If IsEmpty(ixnDistance) Then
DGMAP = "Empty"
Exit Function
End If
'距離（True) または時間（False)
If distance = True Then
DGMAP = left(ixnDistance.Text, InStr(1, ixnDistance.Text, " ") - 1)
Else
DGMAP = ixnDuration.Text
End If
Set domResponse = Nothing
Set objXMLHTTP = Nothing
End Function
ーーーーー以上、コードーーーーーー
コード入力後、一旦保存してVBAを閉じます。
Step4：Excelでユーザー定義関数を用いて表を作る
さて、あとは表を作るだけです。
今回は以下のユーザー定義関数を用いて作成します。
=DGMAP ([origin], [destination], TRUE: distance/ FALSE: time)
第1引数（出発地）：[origin]
第2引数（目的地）：[destination]
第3引数（距離or時間）：TRUE→距離、FALSE→時間
今回は移動時間が調べたいので第3引数はfalseを用います。
E3のセルに
=DGMAP(B3,E2,false)
と入力
すると、数秒待つと時間が出ます！！
あとは
=DGMAP($B3,E$2,false)
このように参照セルを固定させてオートフィルを使えば一気に表を埋めれちゃいます！！
以上！！簡単！！
注意点
Excelの再計算機能とかで何回も再計算されるとAPIの無力枠超えたりするでの気をつけましょ〜
あとこちらの表だと、左下半分は上右半分と同じ結果なので、計算を全部させると無駄になっちゃうので一工夫した方がいいかと思います。
