LoginSignup
0
1

More than 1 year has passed since last update.

@sunwed

Directions API（REST API）をVBAで使う

Last updated at Posted at 2022-05-29

はじめに

初投稿の非エンジニア事務職です。Directions API（Googleのサービス）で検索した結果をVBAで取得する方法を記載しています。あまり需要はなさそうですが、何らかの理由でVBAからAPIを使う場合にお役立てください。

対象読者

  • VBAはそこそこ不自由なく使えるよ
  • APIね… 聞いたことあるけどよく分からん

投稿のきっかけ

先日、業務で二地点間の距離を数百件ほど調べる必要がありました。色々調べた結果、DirectionsAPIとVBAを使って二地点間の距離検索結果をExcelにまとめられるようになりました。どんな技術記事でも価値があるという考え方に基づいて、ちょっとでも刺さる人がいればいいなと思い投稿します。

とりあえずコードを確認したい！という場合はこちらをクリックしてスキップしてください。

検索方法について

二地点間の距離を数百件ほど調べるにあたり、最初はスクレイピング（後述）しようと考えていました。ただ、社内の情報部門の人に聞いたところ、「スクレイピングは推奨されるものではなく、提供されているならAPIを使うべき」と知りました。そこからAPIについて調べ、GoogleマップのAPIにたどり着きました。無料枠も十分で検索内容も良さそうだったため、使うことにしました。

スクレイピング（ウェブスクレイピング）

「こする」という意味の動詞 scrape から来ています。ウェブサイトからプログラムを使って情報を（こすり）取ってくることを指します。法的にはグレーゾーンと言われていて、サービス利用規約で明確に禁止されていることもあります。例えばTwitterの規約には次の記載があります。

Twitterによる事前の同意がないまま本サービスのスクレイピングをすることは明示的に禁止されています

また、多くのアクセスを短時間に集中させてしまうと、場合によってはDoS攻撃とみなされてしまう可能性があります。実際、システムへの負荷を考慮した悪意のないアクセスであっても、逮捕勾留に至ってしまった事件（岡崎市立中央図書館事件）が起きています。

よって、スクレイピングは最終手段であり、もし対象のウェブサイトがAPIを提供している場合はそれを用いるのが基本となっています。APIにもアクセスの制限回数などが設定されていることが多いですが、その範囲内であれば全く問題ありません。

REST API とは

システムの機能を外部から利用できるようにした仕組みで、ウェブサイトにアクセスするのと同じように「注文内容を書いたURLで欲しい情報にアクセスできる仕組み」と、非エンジニア的にはこんな風に理解しました。

本記事のような情報収集はもちろん、巷でよく聞くAPI連携（ウェブサービス間の機能の貸し借り）に使われています。と呼ばれています。身近な例としては、食べログの店舗地図でのGoogleマップ使用が挙げられます。食べログ側はGoogleの公開しているAPIを使うことにより、自前で用意せずとも地図情報を載せることができています。

似たような言葉があるので、以下に整理しておきます。

  • API （Application Programming Interface）
    システムの一部機能を、外部から利用できるようにした仕組み一般のこと。呼び出す側は、呼び出される側の内部のことがよく分からなくても、決められた方法に従うことで機能を呼び出すことができます。

  • Web API
    APIのうち、インターネットで（HTTPで）情報をやり取りできるようにしたもの。詳しいことは、長岡 賢一さんの記事（Web APIとは何なのか｜Qiita ）をご覧ください。ものすごく分かりやすいです。

  • REST API（RESTful API とも言われる）
    Web API のうち、REST（Representational State Transfer）という考え方を基に作られたもの。現在の主流なWeb APIのため、単にAPIと略されることが多いです。
    ポイントは、提供される情報（リソース）がURLで表現できる点です。難しいことが分からない事務職であっても、とにかくURLの作り方が分かれば、簡単にウェブサイトと同じ感覚で情報にアクセスできてしまう、シンプルかつ便利な仕組みです。
    詳しくはこちらの記事（RESTful APIとは何なのか｜Qiita ）を参照してください。

API / Web API / REST API という階層になっていますね。

HTTP（HyperText Transfer Protocol）とは

クライアントとサーバとが情報をやり取りする際の約束事（プロトコル）のことです。URLの先頭に付いているhttp[https]は、「HTTPに従って通信してますよ」という意味です。REST API もこれに則っています。

  • クライアント：　ブラウザなど、情報を要求（リクエスト）する側
  • サーバ　　　：　クライアントからの要求を処理して、情報提供など応答（レスポンス）してくれる側

イメージは下図の通りです。

APIの使い方

大まかな流れは次の通りです。

  1. APIの仕様（リクエストの方法、レスポンスの意味など）を確認する
  2. 仕様の通りにサーバにリクエストする
  3. レスポンスを受け取る
  4. 受け取った情報を解析していい感じに使う

具体例

実際に触ってみるとAPIがどんなものなのか分かりやすいです。郵便番号APIを使って郵便番号から住所を調べてみましょう。フリーのものがいくつか公開されていますが、今回はこちらを使わせてもらいます。とてもシンプルですが、他のAPIにも応用が利きます。

リクエスト

一般的にURLの作り方は以下の通りです。

  • ベースのURL（固定） ＋ リクエストパラメータ（具体的な要求内容）
  • リクエストパラメータはパラメータ名＝内容の形式にし、複数の場合は&で繋ぎます。

今回のAPIの場合は以下の通りです。

  • ベースのURL：https://zipcloud.ibsnet.co.jp/api/search
  • リクエストパラメータは下表の通り
パラメータ名 項目名 必須 備考
zipcode 郵便番号 ○ 7桁の数字。ハイフン付きでも可。完全一致検索。
callback コールバック関数名 - JSONPとして出力する際のコールバック関数名。UTF-8でURLエンコードした文字列。
limit 最大件数 - 同一の郵便番号で複数件のデータが存在する場合に返される件数の上限値（数字）　※デフォルト：20

以上を踏まえると、「〒062-0934」の住所を調べるリクエストはこちらです。
https://zipcloud.ibsnet.co.jp/api/search?zipcode=0620934

これを試しにブラウザで開いてみましょう。直接クリックするか、ブラウザの検索窓にコピペしてEnterしてみてください。

レスポンス

すると、以下の文字列が返ってきます。この応答の文字列の形式をJSON（後述）といいます。この構造化された文字列から必要な情報を抽出していくわけです。

{
	"message": null,
	"results": [
		{
			"address1": "北海道",
			"address2": "札幌市豊平区",
			"address3": "平岸四条",
			"kana1": "ﾎｯｶｲﾄﾞｳ",
			"kana2": "ｻｯﾎﾟﾛｼﾄﾖﾋﾗｸ",
			"kana3": "ﾋﾗｷﾞｼ4ｼﾞｮｳ",
			"prefcode": "1",
			"zipcode": "0620934"
		}
	],
	"status": 200
}

JSON（JavaScript Object Notation）とは

データの表現方法のひとつです。REST APIの応答はJSONであることがほとんどです。

JavaScript Object Notation（JSON、ジェイソン）はデータ記述言語の1つである。軽量なテキストベースのデータ交換用フォーマットでありプログラミング言語を問わず利用できる[1]。名称と構文はJavaScriptにおけるオブジェクトの表記法に由来する。
https://ja.wikipedia.org/wiki/JavaScript_Object_Notation

"address1": "北海道"のように、key（項目名）: value（値）という形式でデータを表現します。そのままでも読めなくはないですが、以下のツールで構造を可視化すると分かりやすくなります。

先ほどのレスポンスは以下のようになります。
image.png

Directions API および Google Maps Platform のセットアップ

別記事で投稿しました。こちらで設定を済ませてください。

VBAでの実装

事前知識の説明が概ね終わりましたので、VBAで具体的にどうすればよいか、という話に入っていきます。以降はこちらの記事を参考にしています。

先に結論のコードを記載しておきます。出発・到着地点と距離・時間をイミディエイトウィンドウに出力するシンプルなコードです。骨組みが分かるよう、ループ処理等は入れていません。次の2つの設定（後述）をしておけばコピペで動きます。

  1. VBA-JSONのインポート
  2. 参照設定
Sample Code
Sub GettingDirections()
    
    Dim params As Dictionary
    Set params = New Dictionary

    '日本語部分はURLエンコードしておかないとエラーになります
    With params
        .Add "origin", WorksheetFunction.EncodeURL("札幌駅")
        .Add "destination", WorksheetFunction.EncodeURL("新千歳空港")
        .Add "key", YOUR_API_KEY  'は自身のAPIキーに置き換えてください
    End With
    
    Dim joinedParams As String
    Dim i As Long
    For i = 0 To params.Count - 1
        joinedParams = joinedParams & params.Keys(i) & "=" & params.Items(i) & "&"
    Next i
    
    Dim requestURL As String
    requestURL = "https://maps.googleapis.com/maps/api/directions/json?" & joinedParams
    
    Debug.Print requestURL

    'APIにリクエストを送信
    Dim httpRequest As XMLHTTP60   '「Microsoft XML, v6.0」を参照設定
    Set httpRequest = New XMLHTTP60
    
    With httpRequest
      .Open "GET", requestURL
      .send
    End With

    Do While httpRequest.readyState < 4
        DoEvents
    Loop

    'JSONパース
    Dim jsonObj As Dictionary  '「Microsoft Scripting Runtime」を参照設定
    Set jsonObj = JsonConverter.ParseJson(httpRequest.responseText)
        
    '結果の出力
    'http://kimihiro-n.site44.com/json_dump/index.html
    Debug.Print jsonObj("status")

    Debug.Print jsonObj("routes")(1)("legs")(1)("start_address")
    Debug.Print jsonObj("routes")(1)("legs")(1)("end_address")

    Debug.Print jsonObj("routes")(1)("legs")(1)("distance")("text")
    Debug.Print jsonObj("routes")(1)("legs")(1)("distance")("value")

    Debug.Print jsonObj("routes")(1)("legs")(1)("duration")("text")
    Debug.Print jsonObj("routes")(1)("legs")(1)("duration")("value")

End Sub

VBEの設定

①VBA-JSONのインポート

VBAでJSONを解析するのに便利な「VBA-JSON」というモジュールが Github で公開されていますので、ありがたく使わせていただきましょう。

以下にアクセスし、最新版（Latest）の「Source code (zip)」をダウンロードします。

image.png

ダウンロードしたzipを解凍し、「JsonConverter.bas」をVBEのプロジェクトエクスプローラーにドラッグ&ドロップします。以下の状態になればインポート完了です。
image.png

②参照設定

以下2つのライブラリ（再利用可能な部品の集まり）を参照できるよう設定します。

  • Microsoft XML, v6.0
    HTTPリクエストするのに必要
  • Microsoft Scripting Runtime
    Dicitonary型の変数（JSONの解析結果の形式）を使うのに必要

VBEの「ツール」から「参照設定」を開きます。
image.png
「Microsoft XML, v6.0」と「Microsoft Scripting Runtime」にそれぞれチェックを付け、「OK」をクリックして完了です。
image.png

コード解説

リクエストURLの作成

まずURLに必要なパラメータをキーと値のペアでDictionary（連想配列）型の変数に格納しています。Dictionary.Add キー名, 値でペアを追加できます。

    Dim params As Dictionary
    Set params = New Dictionary

    '日本語部分はURLエンコードしておかないとエラーになります
    With params
        .Add "origin", WorksheetFunction.EncodeURL("札幌駅")
        .Add "destination", WorksheetFunction.EncodeURL("新千歳空港")
        .Add "key", YOUR_API_KEY  '自身のAPIキーに置き換えてください
    End With

URL内に含まれる日本語は、リクエストする前にあらかじめでエンコードしておく必要があります。ワークシート上でURLを作る際もご注意ください。ワークシート上ではENCODEURL関数でエンコードできます。
image.png

paramsに格納したキーと値を結合し、ベースのURLと合体します。わざわざ連想配列を用意していたのは、パラメータが増えても以下のコードをいじらなくて済むためです。

    Dim joinedParams As String
    Dim i As Long
    For i = 0 To params.Count - 1
        joinedParams = joinedParams & params.Keys(i) & "=" & params.Items(i) & "&"
    Next i  

    Dim requestURL As String
    requestURL = "https://maps.googleapis.com/maps/api/directions/json?" & joinedParams
    
    Debug.Print requestURL

リクエストの送信とレスポンスの受信

VBAでHTTP通信をする際は、IXMLHTTPRequestオブジェクトを使います。「Microsoft XML, v6.0」の参照設定をすることで使うことができます。

まずIXMLHTTPRequestオブジェクト（コード内ではXMLHTTP60という名前）のインスタンスを生成します。

    Dim httpRequest As XMLHTTP60   '「Microsoft XML, v6.0」を参照設定
    Set httpRequest = New XMLHTTP60

次にopenメソッドで、送信時のメソッド（GET、POSTなど）と送信先URLを設定し、sendメソッドでリクエストを送信します。なお、今回は送信時のメソッドがGETのため引数はありませんが、POST（データ送信）の場合は送るデータを引数とします。

    With httpRequest
      .Open "GET", requestURL
      .send
    End With

レスポンスの受信までは次のコードで待機します。処理の進行に伴ってreadyStateプロパティが整数0から4まで増えていき、最後の4が「レスポンス受信完了」を意味します。

    Do While httpRequest.readyState < 4
        DoEvents
    Loop

XMLHttpRequest の中身については以下を参照してください。

JSONのパース

次のコードから VBA-JSON の機能を使っています。

ここでは、レスポンスの文字列（JSON）をパース（parse／解析）しています。サーバーからのレスポンスはそのままではただの文字列ですので、プログラム内で必要な値が取り出せるようにkeyとvalueで扱えるオブジェクトに変換しておきます。

パース処理には VBA-JSON のParseJsonという便利な関数を使います。引数には JSON の形式で書かれた文字列が必要なので、IXMLHTTPRequestオブジェクトのresponseTextプロパティでレスポンスの文字列を取得し関数に渡しています。

解析結果はDictionary型変数に格納します。

    'JSONパース
    Dim jsonObj As Dictionary  '「Microsoft Scripting Runtime」を参照設定
    Set jsonObj = JsonConverter.ParseJson(httpRequest.responseText)

値の取り出し方

基本的な書き方はDictionary(キー名)です。こちらで使われている例をお借りして具体的に説明します。

例
Dim Json As Dictionary
Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

変数Jsonの内容を見やすくすると以下の通りです。

変数 Json
{
    "a": 123,
    "b": [
        1,
        2,
        3,
        4
    ],
    "c": {
        "d": 456
    }
}

"a"の値を取り出す場合は次の通りです。シンプルですね。

Json("a") '-> 123

"b"のような配列の値を取り出す場合はインデックスで指定します。

インデックスは 1 始まりです

Json("b")(1) '-> 1
Json("b")(4) '-> 4

最後に"c"の中に入れ子になっている"d"の値を取り出す際は、次のように書きます。

Json("c")("d") '-> 456

値の取り出し

では例で確認したことを応用して、実際にAPIのレスポンスから値を取得してみましょう。

ステータス

ここまでの処理でjsonObjという変数にレスポンスの文字列が格納されています。まずはAPIの結果から検索の成否を表すstatusを抽出してみましょう。

    Debug.Print jsonObj("status")

OKとイミディエイトウィンドウに表示されたでしょうか。OK以外であれば、同じリクエストをブラウザ等で確認したり、以下ドキュメントを参照したりして解決してください。

出発地点と到着地点

続いて出発地点と到着地点を取得してみましょう。ここからは入れ子になっていますので、箇所の指定が少し複雑になります。一つ一つ読み解いていくのも時間がかかるので、先ほども登場したThe Fastest JSON visualizer を使います。

窓にJSONを入力して「Visualize JSON」ボタンを押すと、構造が可視化され、かつ、欲しい値の指定方法も分かります。
image.png

APIをブラウザからリクエストして結果をコピペし、ツールで可視化します。その中から"start_address"と"end_address"を探します。以下のマーカー部分がJavascript用のパスになっていますので、これを活用します（※他に良い方法があればご教示ください）。
image.png
"start_address"の場合はdata['routes']['0']['legs']['0']['start_address']ですね。書式が異なるので、次の通り加工します。

  • dataを削除
  • 記号を置換
    • [] -> ()
    • '' -> ""
  • インデックスに1を加算（0 -> 1）

すると("routes")(1)("legs")(1)("start_address")となります。これでイミディエイトウィンドウに出力できるようになりました。

    Debug.Print jsonObj("routes")(1)("legs")(1)("start_address")
    Debug.Print jsonObj("routes")(1)("legs")(1)("end_address")

同じ方法で距離や時間も出力できます。見た目はややこしい印象ですが、覚えてしまえば簡単だと思います。

    Debug.Print jsonObj("routes")(1)("legs")(1)("distance")("text")
    Debug.Print jsonObj("routes")(1)("legs")(1)("distance")("value")

    Debug.Print jsonObj("routes")(1)("legs")(1)("duration")("text")
    Debug.Print jsonObj("routes")(1)("legs")(1)("duration")("value")

おまけ

①結果のテキストファイルへの保存

ブックの保存先フォルダ直下のresultフォルダにjsonファイル（テキストエディタで開けます）を保存するコードです。リクエストURLrequestURLとレスポンスhttpRequest.responseTextを書き込んでいます。テキストファイル操作方法の詳細は以下ページを参照してください。

    Dim saveDir As String
    saveDir = ThisWorkbook.Path & "\result\"
    
    'resultフォルダがなければ作成する
    If Dir(saveDir, vbDirectory) = "" Then
        MkDir saveDir
    End If
    
    'http://officetanaka.net/excel/vba/file/file11.htm
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .WriteText requestURL, 1
        .WriteText httpRequest.responseText, 1
        .SaveToFile saveDir & Format(Now, "yyyymmdd-hhmmnn") & ".json", 2
        .Close
    End With

②ワークシートに出力

ブックの1シート目の1行目にヘッダーがあり、2行目に検索結果を入力するコードです。行をループさせれば複数件の結果記入にも応用できますね！

    With Sheets(1)

        .Cells(2, 1) = jsonObj("status")
    
        .Cells(2, 2) = jsonObj("routes")(1)("legs")(1)("start_address")
        .Cells(2, 3) = jsonObj("routes")(1)("legs")(1)("end_address")
    
        .Cells(2, 4) = jsonObj("routes")(1)("legs")(1)("distance")("text")
        .Cells(2, 5) = jsonObj("routes")(1)("legs")(1)("distance")("value")
    
        .Cells(2, 6) = jsonObj("routes")(1)("legs")(1)("duration")("text")
        .Cells(2, 7) = jsonObj("routes")(1)("legs")(1)("duration")("value")
        
    End With

image.png

おわりに

記事の内容について誤りやご意見ありましたらコメント等いただければ幸いでございます。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1