いよいよExcel VBAによりYouTube Data APIをプログラムで操作する解説に入ります。とは言うものの僕はExcel VBAに超詳しいというわけではないので、VBA言語についての解説はほとんどせず、極めてシンプルなサンプルの解説に留めたものになります。それでも今後本格的にプログラミングを行ってく上での取っ掛かりとしては十分な内容になっています。
なぜExcel VBAか
僕がYouTube APIを操作する上でプログラミング言語としてExcel VBAを選んだ理由は、以下の2点です。
- Excelもしくはofficeがインストールされているパソコンであれば特に開発環境を準備する必要はない
- 大量のデータをExcelシートで管理するので、当然ながらExcelシートと親和性の高い言語であるため
ということなんですが、僕は元々仕事外の家のパソコンでもOffice365を導入しているため、とてもとっつきやすい言語であったということが大きかったです。
今回の解説の構成
今回の記事は以下の2段階で解説していきます。
- HTTP通信を行うオブジェクトを使ってリクエストURLを送信し、受信したデータを文字列領域に取り込む
- 文字列領域に取り込んだ受信データからJSONコンバータを使って必要な項目を取り出す
HTTP通信を行うFunction
VideoIDとAPIキーを引数として受けとり、そこから情報を取得するリクエストURLを作成し、Youtubeからの戻りデータを文字列データとして戻す機能をExcel VBAのFunctionとして切り出しました。そのソースは以下のように物凄くシンプルなものになります。
' Youtube API(Video)を呼び出す（引数はVideoIDとAPIキー、戻り値はJSON文字列）
Function KickYouTubeVideo(ByVal VideoID As String, ByVal APIKey As String)
' URLを作成する
Dim Url As String
Url = "https://www.googleapis.com/youtube/v3/videos?part=id,snippet,contentDetails,liveStreamingDetails,player,recordingDetails,statistics,status,topicDetails&id=" _
& VideoID & "&key=" & APIKey
' HTTP通信オブジェクトを準備する
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
With http
' URLリクエストを送信する
.Open "GET", Url, False
.Send
' URLリクエストの応答を戻り値としてセットする
KickYouTubeVideo = .responseText
End With
End Function
このFunctionを呼び出すサンプルも作ってみました。
Sub Sample01()
Dim Wk_VideoID As String
Dim Wk_APIKey As String
Wk_VideoID = Cells(3, 2).Value
Wk_APIKey = Cells(3, 3).Value
MsgBox KickYouTubeVideo(Wk_VideoID, Wk_APIKey)
End Sub
実行イメージはこんな感じです。
B3セルにVideoIDを設定して、C3セルにAPIキーを設定してマクロ（Sample01())を登録した実行ボタンを押すと
YouTubeからの応答がMSGBOXに表示されました。MSGBOXの仕様なのか、概要欄の途中で切れてしまっていますが、テストとしてはこれで十分でExcel VBAでYouTube APIの操作ができることがわかりました。それにしても、ここで使ったXMLHTTPオブジェクトは強力であり、これだけのコードでURLリクエストを発行して、応答データが得られるのは個人的にはかなりのインパクトがありました。このオブジェクトが発行するリクエストURLはYouTube APIでなくてもよいわけで、そう考えると世界中のあらゆるWebページから様々なデータを取得できるので、アイディア次第でものすごいことが出来そうな気がしています。こうして、Webから情報収集することをWebスクレイピングといいますが、これを応用すれば、Webスクレイピングの達人になれるかもしれません。
JSONコンバーターを使う
取得した文字列からJSONコンバータを使って情報を取り出すのですが、これには少し事前準備が必要です。
事前準備
JSONコンバータであるVBA-JSONをダウンロードしてExcelファイルにインポートします。
下記のGithubにアクセスして
ここから最新版のzipファイルをダウンロードします。
ダウンロードしたzipファイルを解凍して、現れたファイル”JsonConverter.bas”をVBAのプロジェクトエクスプローラーにドラッグ＆ドロップします。以下の状態になればOKです。これでVBA－JSONのインポートは完了です
もうひとつ、JSONコンバータを使うにあたって、Dictionary型を使用するためにMicrosoft Scripting Runtimeライブラリへの参照設定が必要になります。VBAエクスプローラにて「ツール」「参照設定」を開いて、
Microsoft Scripting Runtimeにチェックを入れてOKボタンを押したら事前準備は完了です。
JSONコンバータを使ったサンプルプログラム
ソースコードは先ほどのHTTP通信のサンプルの続きで、YouTubeからの応答データをJSONコンバータに渡して、必要な項目のデータを抽出します。
Sub Sample02()
Dim Wk_VideoID As String
Dim Wk_APIKey As String
Dim Result As String
Dim Json As Object
Wk_VideoID = Cells(6, 2).Value
Wk_APIKey = Cells(3, 3).Value
Result = KickYouTubeVideo(Wk_VideoID, Wk_APIKey)
Set Json = JsonConverter.ParseJson(Result)
Cells(6, 3) = Json("items")(1)("snippet")("title")
Cells(6, 4) = Json("items")(1)("snippet")("description")
Cells(6, 5) = Left(Json("items")(1)("snippet")("publishedAt"), 10)
Cells(6, 6) = Json("items")(1)("contentDetails")("duration")
Cells(6, 7) = Json("items")(1)("statistics")("viewCount")
End Sub
このサンプルもすごくシンプルです。
KickYouTubeVideoの戻り値の文字列ResultをJsonConverter.ParseJsonセットしてしまえば、あとはJSON形式の階層構造と項目名さえわかれば簡単に必要な情報を取得することが出来ます。このサンプルを見れば、JSON形式が人間とコンピュータ両方にとって、いかに優れたデータ表現形式であることが容易に理解できると思います。
実行イメージは
B6セルにVideoIDを設定して、C3セルにAPIキーを設定してマクロ（Sample02())を登録した実行ボタンを押すと
C6～G6セルに動画情報を取得することができました。これが1個出来てしまえは、何百個、何千個作るのも容易いのがプログラミングのすごいところとなります。
まとめ
このように、Excel VBA からリスエストURLを発行し、取得したデータからJSONコンバータを使って必要なデータを取り出す方法を示しました。プログラム・ソースコードとしてはとても短く簡単に実現できることがお分かりいただけたと思います。また、これらの方法はYouTube Data APIに限らず様々なWeb APIでも活用可能となります。これで、VideoIDさえわかればいくらでも情報取得できるようになりましたが、次回はチャンネル内のすべでのVideoIDを取得する方法について説明します。
コメント