○連載第3回目の目的

この連載第3回目では、Excel VBAによるWebサービスの呼び出しとJSONデータの解析についての手順を学びます。連載第2回目では、XML形式のデータを返すWebサービスを用い、データの解析を行っていましたが、今回はJSON形式のデータに対して解析を行います。Webサービスの多くは、取得できるデータをJSON形式で提供していますので、似たようなケースでは同様の手順でのデータ取得などに応用できるでしょう。

題材としては、気象庁の提供する天気予報APIを用います。このAPIは、公式にはWebサービスとして公開されているものではないですが、利用規約を守ることで誰でも無料で気象情報のデータを入手することができます。今回はこれを使用して、指定した気象区の天気予報を自動取得するExcelワークシートを作成します(図1)。

完成サンプルのソースコード

https://github.com/wateryinhare62/mynavi_excelvba_webservice

[NOTE]JSONについて

JSON(JavaScript Object Notation)とは、テキストベースのデータ交換フォーマットのひとつです。その名称からわかるように、JavaScriptにおけるオブジェクト記法をそのまま用いています。現在ではJavaScriptに限らず、多くのプログラミング言語で使用することができます。Key: Value形式の値や配列として、データの階層や繰り返しなどを表現します。WebサービスにおいてJSONは重要な位置を占めます。今回のWebサービスはJSON形式で取り込みますので、ぜひ心に留めておきましょう。

なお、本連載では動作確認をWindows 10 Pro(64bit)、Microsoft 365(Excel 16.0、VBA 7.1)で行っています。旧バージョンや単体のExcelで試す場合にはご注意下さい。

○天気予報APIについて

ここでは、気象庁の提供する天気情報APIを使います。APIと書いていますが、正式なAPIでない(外部における利用を想定せず、利用法を公開していない)ことに注意してください。とはいえ、予報区のコードをURLに含めて直接アクセスすることで、誰でも利用できます。結果はJSON形式のデータで得られます。気象庁のデータの利用については、『政府標準利用規約(第2.0版)に準拠』しています。下記の利用規約に目を通して、問題ないと判断される場合にのみ利用して下さい。

気象庁ホームページ利用規約:

https://www.jma.go.jp/jma/kishou/info/coment.html

天気予報APIにアクセスするURLの形式は以下のとおりです。

概観を取得:

https://www.jma.go.jp/bosai/forecast/data/overview_forecast/<予報区コード>.json

詳細を取得:

https://www.jma.go.jp/bosai/forecast/data/forecast/<予報区コード>.json

概観は、予報日時、対象エリア、ヘッドライン、予報本文からなるシンプルなデータです。詳細の方は、複数の日時とエリアに渡って、天候・風・波・気温・降雨確率、湿度などの情報からなるやや複雑なデータです。今回は、両方を使っていきます。

<予報区コード>には、全国の予報区に割り当てられているコードを指定します。たとえば東京予報区の場合には、130000を指定します(必ず6桁にすること)。予報区とコードの一覧は、サンプルの「予報区コード」シートを参照してください。

○JSONデータを見てみる

まずは、APIで取得できるJSONデータを見てみましょう。Webブラウザで上記のURLを開いてみます。ここでは、東京予報区(130000)を指定してみました(図2)。

Webブラウザによっては、すべてのデータが改行もインデントもなく表示されることがあります。ここではGoogle Chromeを使いましたが、そのままでは見にくい表示となってしまい、データの構造を検討したい場合に不便です。そこで、まずはJSONデータを見やすく整形して表示できるようにしてみましょう。Google Chromeの場合は、拡張機能「JSON Viewer」を入れておきます。まずは、下記URLから「Chromeウェブストア」をChromeで開き、検索ボックスに「JSON Viewer」と入れて表示される候補から「json viewer」をクリックします(図3)。

Chromeウェブストア:

https://chrome.google.com/webstore/category/extensions?hl=ja

見つかったJSON Viewerについて、アイコン画像をクリックします(図4)。

JSON Viewerについて詳細が表示されますので、[Chromeに追加]をクリックします(図5)。

「JSON Viewerを追加しますか?」という確認が表示されるので、[拡張機能を追加]をクリックすればJSON Viewerが追加されます(図6)。

なお、Chromeウェブストアは、Chromeのウインドウ右上にある三点メニューから[その他のツール]−[拡張機能]を選んで拡張機能の一覧を表示させ、ウインドウ左下にある[Chromeウェブストアを開きます]をクリックしても開くことができます。

これで、改めて東京予報区のJSONデータを見てみましょう。今度は、インデントや改行が適宜加わって、非常に見やすいものとなりました(図7)。

JSON Viewerは、これに限らずあらゆるJSONデータの確認に役立つので、ぜひ活用しましょう。

○ワークシートの準備

APIが使えることを確認できたら、ワークシートを用意し、上部に天気予報を取得したい予報区のコード、取得開始のボタンを配置します。以下では細かな位置やサイズの指示は特に行いませんので、完成サンプルを見ながら各自で調整して下さい。

○予報区コードを用意する

天気予報を取得したい予報区のコードを入力しておきます。初期値として東京予報区の130000を入れておきます。セルにある予報区のコードをもとに、天気予報をAPIを通じて取得し、ワークシートに反映させます。

○取得開始のボタンを配置する

続けて、天気予報の取得を開始するボタンを配置します。[開発]タブをクリックし、[コントロール]グループの[挿入]ボタンをクリックすると、コントロール一覧がメニューとして表示されます([開発]タブが表示されていない場合は連載第1回を参照)。ここから、「フォームコントロール」カテゴリにある[ボタン(フォームコントロール)]をクリックします。マウスカーソルの形状が「+」に変わって、好きな位置に好きな大きさのボタンを配置できるようになります。位置は、予報区コードの右側にします(図8)。

マウスボタンのリリースと同時に「マクロの登録」ダイアログが開きますので、[新規作成]ボタンをクリックして、ボタンクリック時に呼び出されるイベントハンドラを作成します(図9)。イベントハンドラ名の初期値は「ボタン1_Click」ですが、[マクロ名]欄で名称を変更することもできます。Visual Basic Editorが起動して、イベントハンドラが作成されたことを確認できます(図10)。

必要に応じて、ボタンのテキストも「取得開始」などに変更しておきます。ここまでの作業状態を確認しましょう(図11)。

○VBA-JSONを準備する

今回はVBAからJSONデータを使いますので、そのために必要なライブラリを準備します。ここでは、「VBA-JSON」というサードパーティのライブラリを使用します。VBA-JSONはGitHubで公開されていますので、下記URLをWebブラウザで開きます。

VBA-JSON(GitHub):

https://github.com/VBA-tools/VBA-JSON

ページが開けたら、ページ下端までスクロールし、「Installation」欄にある[latest release]をクリックします(図12)。

ページが切り替わりますので、「VBA-JSON 2.3.1」のAssetsのうち、[Source code (.zip)]をクリックしてダウンロードを行います(図13。最新版のバージョンは変化する可能性があります)。

Zipファイルがダウンロードされたフォルダ(「ダウンロード」フォルダなど)で、Zipファイルを解凍します。ファイルが数個できますが、このうちJsonConverter.basをVisual Basic Editorのプロジェクトエクスプローラーにドラッグ・アンド・ドロップし、「標準モジュール」内に表示されればOKです(図14)。

さらに、VBA-JSONはDictionary型を使うため、「Microsoft Scripting Runtime」への参照設定を有効にしておきます。Visual Basic Editorにて、[ツール]メニューの[参照設定]から「参照設定 – VBA Project」を開きます。[参照可能なライブラリ ファイル]から「Microsoft Scripting Runtime」を見つけて、チェックが入っていなければチェックを入れます。そして[OK]ボタンをクリックすれば終了です(図15)。

ここで、ブックを保存しておきます。ブック名は何でもよいですが、形式を「Excel マクロ有効ブック (*.xlsm)」にしてください。マクロを有効にしておかないと、VBAのスクリプトを実行できないからです。このあとも、適宜ブックを保存して下さい。

○Webサービスにアクセスする

今回は、天気予報の概観と詳細の取得を試みます。そこで、拡張性を考慮してWebサービスへのアクセスは共通化しましょう。まずは、そのスクリプトを書いていきます。

ボタンの配置でスクリプトを関連付けた時点で、すでに標準モジュールのModule1が作成されていますので、ここにスクリプトを追加していきます。Webサービスにアクセスするスクリプトは、KickWebServiceという関数にします。それをリスト1に示します。すでにある「End Sub」の下に追加します。

[リスト1]KickWebService関数

Private Function KickWebService(ByVal Detail As Boolean, _

ByVal Code As Long) As String

Dim Url As String, Path As String, CodeStr As String

If Detail Then (1)

Path = “forecast”

Else

Path = “overview_forecast”

End If

CodeStr = Format(Code, “000000”) (2)

Url = “https://www.jma.go.jp/bosai/forecast/data/” _

& Path & “/” & CodeStr & “.json”

Dim http As Object (3)

Set http = CreateObject(“MSXML2.XMLHTTP”)

With http

.Open “GET”, Url, False

.send

KickWebService = .responseText (4)

End With

End Function

引数に、概観と詳細のどちらを取得するかという論理値(Trueで詳細を取得)Detail、そして予報区のコードCode(Long型)を受け取って、結果としてJSON文字列を返す関数です。

URL中のパスは、概観と詳細で変化するので、それをDetailの値に応じて切り替えています(1)。また、予報区コードは6桁でなければならないので、必ず6桁の文字列になるようにFormat()で変換しています(2)。

VBAでは、WebサービスなどHTTPでアクセスする必要のあるとき、MSXML2.XMLHTTPというモジュールのオブジェクトを使用します。オブジェクト作成後、HTTPメソッドとURLを指定してOpenメソッドを呼び出し、必要なリクエストパラメータを設定した後(ここでは不要なので省略しています)、sendメソッドで実際の呼び出しを行います(3)。

なお、Openメソッドの構文はリスト2のとおりで、3番目の引数以降は省略できます。3番目の非同期取得フラグの既定値はTrueで、この場合はOpenメソッド呼び出し後にすぐにメソッドから戻ってきますが、Falseではレスポンスがあるまで待ちます。今回の例ではAPIが軽量で待ち時間もほとんどないので、Falseを指定してレスポンス返るのを待っています。Trueの場合には、Openメソッド呼び出し後に、レスポンスが返ってくるまで待つスクリプトを記述する必要があります。ユーザ名とパスワードは、ページが認証を求める場合のオプションです。

[リスト2] Openメソッドの構文

Open(HTTPメソッド, URL, 非同期取得フラグ, ユーザ名, パスワード)

呼び出した結果はResponseTextプロパティで取得できます。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返します(4)。

○天気予報概観を取得する

では、天気予報概観の取得スクリプトを書いていきましょう。ワークシートから予報区コードを取り出し、取得した天気予報概観をワークシートに書き込むスクリプトをボタン1_Clickの中に追加します(リスト3)。

[リスト3]天気予報概観取得&ワークシートへの書き込み

Dim Row As Integer

Dim Code As Long

Dim Result As String

Dim Json As Object

Row = 3

Code = Cells(Row, 3) (1)

Result = KickWebService(False, Code) (2)

If Left(Result, 1) “[” And Left(Result, 1) “{” Then

MsgBox (“天気予報詳細の取得結果が不正です。”)

Else

Set Json = JsonConverter.ParseJson(Result) (3)

Row = 5

Cells(Row, 2) = Json(“publishingOffice”) (4)

Cells(Row + 1, 2) = Json(“reportDatetime”)

Cells(Row + 2, 2) = Json(“targetArea”)

Cells(Row + 3, 2) = Json(“headlineText”)

Cells(Row + 4, 2) = Json(“text”)

End If

天気予報概観は、ルートに要素を5つ持つだけのシンプルな構造のJSONデータですので、処理も非常に単純です。ワークシートを参照し、この場合は3行・3列から予報区コードを読み取り(1)、そのコードを渡してWebサービスを呼び出し(2)、その結果が空でない場合に限りJSONデータを解析してJSONオブジェクトを取得し(3)、5つの値を取得してワークシートに書き込みます(4)。ここまでの状態で取得開始ボタンをクリックすると、天気予報概観を取得して、図16のように書き込まれます。

概観のJSONデータは、リスト3のようにキー文字列を与えるだけで簡単に取得できます。これは、各要素がルート直下にあるのでシンプルな指定で済んでいるのですが、配列や階層を持つ場合にはもう少し複雑な指定になります。これについては次の天気予報詳細の取得で紹介します。

○天気予報詳細を取得する

天気予報概観の取得ができましたので、続けて天気予報詳細を取得します。詳細データにはいくつかの種類があり、それらが同一のJSONデータに含まれていますが、今回はもっとも単純な天候、風、波のみからなるデータのみを使います。リスト3に続けて、取得した天気予報詳細をワークシートに書き込むスクリプトをボタン1_Clickの中に追加します(リスト4)。

[リスト4]天気予報詳細取得&ワークシートへの書き込み

Result = KickWebService(True, Code)

If Left(Result, 1) “[” And Left(Result, 1) “{” Then

MsgBox (“天気予報詳細の取得結果が不正です。”)

Else

Set Json = JsonConverter.ParseJson(Result)

Set Json = Json(1)(“timeSeries”)(1)

Row = 12

For Area = 1 To Json(“areas”).Count

Cells(Row, 1) = Json(“areas”)(Area)(“area”)(“name”)

For TimeDefine = 1 To Json(“timeDefines”).Count (4)

Cells(Row, 2) = Json(“timeDefines”)(TimeDefine)

Cells(Row, 3) = Json(“areas”)(Area)(“weathers”)(TimeDefine)

Cells(Row, 4) = Json(“areas”)(Area)(“winds”)(TimeDefine)

If Json(“areas”)(Area).Exists(“waves”) = True Then (5)

Cells(Row, 5) = Json(“areas”)(Area)(“waves”)(TimeDefine)

Else

Cells(Row, 5) = “―”

End If

Row = Row + 1

Next

Next

While Area