【VBA-API】Web APIを使ってNHK番組表を取得する（NHK番組表API）

こんにちは、blueです。

今回はNHK番組表APIから当日の番組表を取得します。

これまでの記事はこちら
【VBA-API】Web APIを使ってデータを取得する(OpenWeatherMap)1
【VBA-API】Web APIを使って時間別天気予報を取得する（気象庁API）
【VBA-API】Web APIを使ってLINE通知を行う（LINE Notify）1
【VBA-API】Web APIを使って翻訳をする（DeepL API）

HTTPリクエストからレスポンスまでの流れはこれまでと変わりませんが、取得後の処理は少し躓きポイントがあります。今回はそれらを主にまとめましたので参考にしていただければ幸いです。

VBAでAPI処理を行うにはMicrosoft XML, v6.0の参照設定やVBA-JSONの導入が必要になります。
もしまだの方は以下の記事をご覧ください。

pythonですが会話形式で非常にわかりやすく書かれている書籍です。webAPIやスクレイピングを一から理解できる内容になっています。ネットからデータを取得したいと思っている方にお勧めの書籍です。

リンク

今回のWeb API
API Keyの取得
全コード
各コードの説明
今回のまとめ

今回のWeb API

今回のサイトは以下です。

NHK番組表API

このサイトでは日付やジャンル、IDなどをもとに番組リストや該当する番組の詳細情報を取得することができます。
今回は「東京」、「NHK総合」、「当日」の番組リストを取得し、Excelに出力します。

最終的なゴールは以下になります。

API Keyの取得

APIを使用する際はAPI Keyを取得する必要があります。難しくはないですが一通り説明します。

1 NHK番組表APIにて「ユーザ登録がお済みでない方はこちら」をクリックする

2 Create your account画面にて必要情報を入れ、「Create Account」をクリックする

3 メールを受領した後、指定されたアドレスからログインする

4 作成アカウントでログインできるので「Apps」をクリックする

5 「+NEW APP」をクリックする

6 App Nameを記載した後、「Enable」をクリックし、「Save」をクリックする

API Keyが取得できていればOKです。

全コード

VBAのHTTPリクエストを使ったコードは以下になります。

定数値はProgram List API | portal – NHK番組表APIを確認ください。

Sub NHK番組表ProgramListAPI()

   Const apikey As String = "apikey"          '取得したAPI Keyを入れてください
   Const area As String = "130" 　　　　　　　 '東京
   Const service As String = "g1" 　　　　　　　'NHK総合1
   Const date1 As String = "2022-12-29" 　　　'日時
   
   Dim http As MSXML2.XMLHTTP60
   Set http = New MSXML2.XMLHTTP60

   'Program List APIから取得をする
    With http
        Call .Open("GET", "https://api.nhk.or.jp/v2/pg/list/" & area & "/" & service & "/" & date1 & ".json?key=" & apikey)
        Call .send
        
        Do
            If .readyState = 4 Then Exit Do
            DoEvents
        Loop
        
        'JSONを一行でクリップボードに出力(Excelのセルは32627文字、Debug Printは10291文字までしか出力できない
        'VBAのクリップボードを使うにはMS Forms2.0 Object Libraryの参照設定必要
        Dim CB As New DataObject
        CB.SetText .responseText
        CB.PutInClipboard
        
        'JSONパース
        Dim jsonObj As Object
        Set jsonObj = JsonConverter.ParseJson(.responseText)
        
        'serviceの配列数だけループする
        Dim i As Long
        
        For i = 1 To jsonObj("list")(service).Count
            ThisWorkbook.Worksheets(1).Cells(i + 1, 1).Value = jsonObj("list")(service)(i)("start_time")
            ThisWorkbook.Worksheets(1).Cells(i + 1, 2).Value = jsonObj("list")(service)(i)("title")
            ThisWorkbook.Worksheets(1).Columns("A:B").AutoFit
        Next

    End With
End Sub

各コードの説明

HTTPリクエスト

HTTPリクエストについては以下の記事をご確認ください。

基本的にここまでのやり方は同じです。

全データ取得と出力

次にデータ解析の為、データを取得した後全出力します。

'JSONを一行でクリップボードに出力(Excelのセルは32627文字、Debug Printは10291文字までしか出力できない
        'VBAのクリップボードを使うにはMS Forms2.0 Object Libraryの参照設定必要
        Dim CB As New DataObject
        CB.SetText .responseText
        CB.PutInClipboard

全データの取得はhttpオブジェクト.responseTextで可能です。
ただこのままだと全データを出力できません。なぜなら文字数が多すぎるからです。

今回のAPIでは114000文字のデータを取得します。
ただExcelのセルには32627文字、イミディエイトウィンドウには10291文字までしか出力できません。

その為今回はVBAのクリップボード機能を使います。

クリップボード使用に関しては以下のサイトを参考にしました。
Office TANAKA – Excel VBA Tips[クリップボードを操作する]

VBAのクリップボードを使うにはMS Forms2.0 Object Libraryの参照設定が必要ですがこの機能を使うことで文字数の問題を回避することができます。

JSON解析

得られたJSONデータを解析します。
JSONデータを解析するには以下のサイトを利用します。
JSON Pretty Linter – JSONの整形と構文チェック – SYNCER

このサイトの「整形前」のところにクリップボードに格納されたデータをペーストすればOKです。

結果として以下の様に構造化されたデータを確認することができます。

このサイトの良い所はPathも表示できることです。
整形後のデータの右上にある「Path」をクリックすると、

右にPathが表示されます。

ここでのobjectに関しては今回jsonObjというオブジェクト変数を用います。

JSONパースと開始日時とタイトルの出力

        'JSONパース
        Dim jsonObj As Object
        Set jsonObj = JsonConverter.ParseJson(.responseText)
        
        'serviceの配列数だけループする
        Dim i As Long
        
        For i = 1 To jsonObj("list")(service).Count
            ThisWorkbook.Worksheets(1).Cells(i + 1, 1).Value = jsonObj("list")(service)(i)("start_time")
            ThisWorkbook.Worksheets(1).Cells(i + 1, 2).Value = jsonObj("list")(service)(i)("title")
            ThisWorkbook.Worksheets(1).Columns("A:B").AutoFit
        Next

得られた一行データをVBAの方でJSONパースします。
JSONパースについてはこちら