連載第5回目の目的
この連載第5回目では、前回に引き続きExcel VBAによるGitHubリポジトリの操作について取り上げます。前回は、GitHubについて理解し、必要なリポジトリの準備、そしてAPIの動作確認まで行いました。今回は、それらを一覧表示するExcelワークシートを作成していきます（図1）。
-
図1：完成サンプル
完成サンプルのソースコード
https://github.com/wateryinhare62/mynavi_excelvba_webservice
なお、本連載では動作確認をWindows 10 Pro（64bit）、Microsoft 365（Excel 16.0、VBA 7.1）で行っています。旧バージョンや単体のExcelで試す場合にはご注意下さい。
ワークシートの準備
APIが認証付きでうまく使えることを確認できたら、ワークシートを用意しましょう。今回は、issueの取得を行っていきますから、上部に所有者名とリポジトリ名、そして取得開始のボタンを配置します。以下では細かな位置やサイズの指示は特に行いませんので、完成サンプルを見ながら各自で調整して下さい。
所有者名とリポジトリ名を用意する
所有者名とリポジトリ名をセルに入力しておきます。値として、読者の所有者名とリポジトリ名「mynavi-ew-github」を入れておきます。セルにある所有者名とリポジトリ名をもとにissueをAPIを通じてデータを取得し、ワークシートに反映させます。
取得開始のボタンを配置する
続けて、取得を開始するボタンを配置します。［開発］タブをクリックし、［コントロール］グループの［挿入］ボタンをクリックすると、コントロール一覧がメニューとして表示されます（［開発］タブが表示されていない場合は連載第1回を参照）。ここから、「フォームコントロール」カテゴリにある［ボタン（フォームコントロール）］をクリックします。マウスカーソルの形状が「＋」に変わって、好きな位置に好きな大きさのボタンを配置できるようになります。位置は、所有者名の右側にします（図2）。
-
図2：ボタンを配置する
マウスボタンのリリースと同時に「マクロの登録」ダイアログが開きますので、［新規作成］ボタンをクリックして、ボタンクリック時に呼び出されるイベントハンドラを作成します（図3）。イベントハンドラ名の初期値は「ボタン1_Click」ですが、［マクロ名］欄で名称を変更することもできます。Visual Basic Editorが起動して、イベントハンドラが作成されたことを確認できます（図4）。
-
図3：「マクロの登録」ダイアログ
-
図4：イベントハンドラが作成された
必要に応じて、ボタンのテキストも「取得開始」などに変更しておきます。ここまでの作業状態を確認しましょう（図5）。
-
図5：ボタン等が配置された
VBA-JSONを準備する
GitHub APIは、結果をJSONデータで返します。VBAからJSONデータを使いますので、そのために必要なライブラリを準備します。第3回と同様に、「VBA-JSON」というサードパーティのライブラリを使用します。VBA-JSONは、今回のテーマでもあるGitHubで公開されています。第3回の記事を参照して、「Microsoft Scripting Runtime」への参照設定を有効にすることを含めた、VBA-JSONの準備を行って下さい。
ここで、ブックを保存しておきます。ブック名は何でもよいですが、形式を「Excel マクロ有効ブック (*.xlsm)」にしてください。マクロを有効にしておかないと、VBAのスクリプトを実行できないからです。このあとも、適当なタイミングでブックを保存して下さい。
Webサービスにアクセスする
今回は、GitHubへのAPI経由でのアクセスを試みます。そこで、拡張性を考慮してWebサービスへのアクセスは共通化しましょう。まずは、そのスクリプトを書いていきます。
ボタンの配置でスクリプトを関連付けた時点で、すでに標準モジュールのModule1が作成されていますので、ここにスクリプトを追加していきます。Webサービスにアクセスするスクリプトは、KickWebServiceという関数にします。それをリスト1に示します。すでにある「End Sub」の下に追加します。
［リスト1］KickWebService関数
Private Function KickWebService(ByVal EndPoint As String) As String
' アカウント名とトークンを変数に入れておく
Dim User As String, Token As String
User = "xxxxx" '(1)
Token = "ghp_xxxxx" '(2)
' URLを作成する
Dim Url As String
Url = "https://api.github.com/" & EndPoint '(3)
' BASE64エンコードされたアカウント名とトークンを作成する
Dim Node As Object '(4)
Set Node = CreateObject("MSXML2.DOMDocument.3.0").createElement("base64")
Node.DataType = "bin.base64"
Node.nodeTypedValue = ConvertToBinary(User & ":" & Token)
' リクエストを送信する
Dim http As Object '(5)
Set http = CreateObject("MSXML2.XMLHTTP")
With http
.Open "GET", Url, False
.setRequestHeader "Accept", "application/vnd.github.v3+json"
.setRequestHeader "Authorization", "Basic " & Replace(Node.text, vbLf, "")
.send
' リクエスト結果を取得する
KickWebService = .responseText '(6)
End With
End Function
引数に、APIのパスとクエリ引数を受け取って、結果としてJSON文字列を返す関数です。
あらかじめ、（1）と（2）でGitHubアカウントとトークンを変数に入れておきます。ハードコーディングは好ましくないので、本来なら外部ファイルにあるものを読み込んで使用したり、あるいはユーザに都度入力させるべきでしょう。ここでは、サンプルとして単純化しています。
（3）で、URLを作成しています。
（4）からは、認証のためのBASE64エンコーディングされたアカウントとトークンを作成しています。なお、文字列をバイナリに変換するConvertToBinary関数については、この連載では関係が薄いため掲載を割愛しますので、サンプルファイルを参照して下さい。
（5）からは、WebサービスなどHTTPでアクセスする準備と実行をしています。オブジェクト作成後、HTTPメソッドとURLを指定してOpenメソッドを呼び出し、setRequestHeaderメソッドで
・Acceptヘッダ（リクエストの送信元が受け取れるデータのタイプ、GitHub APIで指定を推奨されている）
・Authorizationヘッダ（認証の方式とユーザ名とパスワードの情報）
を設定した後、sendメソッドで実際の呼び出しを行います。
なお、Openメソッドの構文はリスト2のとおりで、3番目の引数以降は省略できます。3番目の非同期取得フラグの既定値はTrueで、この場合はOpenメソッド呼び出し後にすぐにメソッドから戻ってきますが、Falseではレスポンスがあるまで待ちます。今回の例ではAPIが軽量で待ち時間もほとんどないので、Falseを指定してレスポンス返るのを待っています。Trueの場合には、Openメソッド呼び出し後に、レスポンスが返ってくるまで待つスクリプトを記述する必要があります。ユーザ名とパスワードは、ページが認証を求める場合に必要ですが、今回の例ではAuthorizationヘッダにて直接設定しているため、省略しています。
Openメソッドの構文
Open(HTTPメソッド, URL, 非同期取得フラグ, ユーザ名, パスワード)
呼び出した結果はResponseTextプロパティで取得できます。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返します（6）。
［NOTE］BASE64エンコーディングとは?
BASE64エンコーディングとは、データを64種類の英数字のみで構成するフォーマットです。仕様上、7ビットのデータしか扱えない通信に用いられます。多くはマルチバイト文字やバイナリデータをメールやWebで扱うために使用されます。今回のサンプルでは、一部に記号を含む「アカウント名＋トークン」をAcceptヘッダの内容とするために、このBASE64エンコーディングを行っています。
取得結果を取得する
では、issueのスクリプトを書いていきましょう。
取得結果の構造
その前に、スクリプトを読む前提知識となる、issue取得結果のJSONデータの構造をリスト2に示します。
［リスト2］取得結果のJSONデータの構造
[ …取得結果はissueの配列となる
{
…中略…
"number": 5, …issueの番号
"title": "プロモーション", …issueのタイトル
"user": { …issueの作成者の情報
"login": "wateryinhare62", …アカウント
…中略…
},
"labels": [ …ラベルの情報の配列
{
"id": 3763658323,
"node_id": "LA_kwDOGu0pNM7gVN5T",
"url": "https://api.github.com/repos/wateryinhare62/mynavi-ew-github/labels/help%20wanted",
"name": "help wanted", …ラベルの名称
"color": "008672", …ラベルの表示色
"default": true,
"description": "Extra attention is needed"
}
],
"state": "open", …ステータス：オープンかクローズ
"locked": false,
"assignee": null,
"assignees": [ …担当者の情報の配列
…中略…
],
"milestone": { …マイルストーンが設定されている場合、その情報
…中略…
"number": 4, …マイルストーンの番号
"title": "プロジェクトX完成", …マイルストーンの名称
"description": "いよいよ完成！", …マイルストーンの説明
"creator": { …マイルストーン作成者の情報
…略…
},
…略…
"due_on": "2022-06-30T07:00:00Z", …締め切り日時
"closed_at": null
},
"comments": 0, …コメント数
"created_at": "2022-01-25T10:48:23Z", …作成日時
"updated_at": "2022-01-25T10:48:23Z", …更新日時
"closed_at": null,
"author_association": "OWNER",
"active_lock_reason": null,
"body": "テストで問題なければ完成だ！", …本文
"reactions": { …リアクションの情報
…略…
},
…略…
}, …次のIssueへ
…略…
]
ポイントは、まずissueの配列となっていることです（配列でない場合、何らかのエラーが発生しています）。検索結果はページ単位で返され、1ページあたりのデフォルトの項目数は30個です（クエリのperPage引数で最大100個まで増やせる）。実際のリクエストでは、どのページを取得しにいくのか？ということをクエリのpage引数で指定してやります。項目数は、配列の要素数をCountプロパティを使って取得します。
取得結果には、issueに関係するほとんど全ての情報が含まれます。担当者はassignees、ラベルはlabels、マイルストーンはmilestoneのぞれぞれのノードに含まれますから、その下位ノードから必要な情報を取得することができます。assigneesとlabelsは配列なので、要素数だけ値の取得を繰り返す必要があります。milestoneが設定されていない場合はnullが値として入っていますので、null判定を行って値を取り出す必要があります。
取得スクリプト
取得結果を踏まえて、ワークシートから所有者名とリポジトリ名を取り出し、取得した結果をワークシートに書き込むスクリプトをボタン1_Clickの中に追加します（リスト3）。
［リスト3］結果取得＆ワークシートへの書き込み
Dim Row As Integer, Col As Integer
Dim Owner As String, Repository As String
' 所有者名とリポジトリ名をワークシートから取得する
Col = 2
Row = 3: Owner = Cells(Row, Col) '(1)
Row = 4: Repository = Cells(Row, Col)
' 取得結果の最大数と1ページあたりのデータ数を変数にセットする
Dim MaxResult As Integer, PerPage As Integer
MaxResult = 1000: PerPage = 100 '(2)
Dim Page As Integer, Count As Long, TotalCount As Long
Page = 1: Count = 1: Row = 7: Col = 1 '(3)
For Page = 1 To MaxResult / PerPage '(4)
' GitHub APIを呼び出す
Dim Result As String
Result = KickWebService("repos/" & Owner & "/" & Repository & _
"/issues?state=all&per_page=" & PerPage & _
"&page=" & Page) '(5)
' JSONデータが戻ってこない場合は処理を中止する
If Left(Result, 1) <> "[" And Left(Result, 1) <> "{" Then '(6)
MsgBox ("取得結果が不正です。")
Exit For
Else
' JSON文字列をディクショナリ形式に変換する
Dim Json As Object
Set Json = JsonConverter.ParseJson(Result) '(7)
Dim i As Integer, j As Integer, Items As Integer
' 取得データ数が0だったら処理を終了する
Items = Json.Count
If Items = 0 Then
Exit For
End If
' Dictionaryが返ってきていれば認証エラー等であるので処理を中止する
If TypeName(Json) = "Dictionary" Then
MsgBox ("認証エラーです。")
Exit For
End If
Dim str As String
For i = 1 To Items
' 番号、issue作成日、更新日をセルに設定する
Cells(Row, Col + 0) = Json(i)("number") '(8)
Cells(Row, Col + 1) = Left(Json(i)("created_at"), 10)
Cells(Row, Col + 2) = Left(Json(i)("updated_at"), 10)
' 締め切り日をセルに設定する
Dim Today, DueOn
Today = CDate(Format(Date, "yyyy/mm/dd")) '(9)
DueOn = Null
str = "なし"
If Not IsNull(Json(i)("milestone")) Then
str = Left(Json(i)("milestone")("due_on"), 10)
DueOn = CDate(str)
End If
Cells(Row, Col + 3) = str
' 締め切り日と実行日を比較して背景色を決める
Dim Color '(10)
Color = vbWhite
If Not IsNull(DueOn) Then
If DueOn < Today Then
Color = rgbLightSlateGray
Else
If DateDiff("d", Today, DueOn) < 7 Then
Color = rgbSalmon
Else
If DateDiff("d", Today, DueOn) < 30 Then
Color = rgbPaleGoldenrod
End If
End If
End If
End If
' ラベルをセルに設定する
str = "" '(11)
For j = 1 To Json(i)("labels").Count
If str <> "" Then
str = str + ","
End If
str = str + Json(i)("labels")(j)("name")
Next
Cells(Row, Col + 4) = str
' タイトルと内容をセルに設定する
Cells(Row, Col + 5) = Json(i)("title")
Cells(Row, Col + 6) = Json(i)("body")
Cells(Row, 1).Interior.Color = Color
' issueのオープン、クローズに応じて文字色を設定する
Dim TextColor '(12)
If Json(i)("state") = "open" Then
TextColor = vbBlack
Else
TextColor = rgbDarkGray
End If
Range(Cells(Row, 1), Cells(Row, 7)).Font.Color = TextColor
' 次のデータへ
Row = Row + 1
Count = Count + 1
Next
End If
Next
' 最大行数までクリアする
Range(Cells(Row, 1), Cells(Row + MaxResult, 7)) = "" '(13)
Range(Cells(Row, 1), Cells(Row + MaxResult, 7)).Interior.ColorIndex = 0
（1）では、所有者名とリポジトリ名をそれぞれOwner変数とRepository変数に取得しています。（2）では、取得する最大リポジトリ数（1,000）をMaxResultに、リクエスト1回あたりの取得数（デフォルトから変更して100）をPerPageにセットしています。（3）のPageとCountは、それぞれ取得ページ、取得項目数のカウンタです。
（4）で、1ページ目からループを回しますが、ひとまず最大ページ数（MaxResult / PerPage）までとしています。（5）で、APIを呼び出しています。エンドポイントは「repos/オーナー名/リポジトリ名/issues」、クエリ引数はオープン/クローズ関係なく全issue対象、ページあたりの項目数とページ番号からなる「state=all&per_page=100&page=1」などとなります。結果がJSONデータで返ってこなかった場合には、（6）でエラーメッセージを出してループを中止しています。
（7)でJSON文字列を変換し、結果の配列の要素数が0であればここでループを中止しています。変換結果が配列でなくディクショナリ形式であった場合には、何らかのエラーが発生していますので、エラーメッセージを表示してループを中止しています。
（8）以降では、項目がある場合は、その中のいくつかのプロパティを取り出してセルにセットしています。（9）はmilestoneに関する処理を行っています。（10）は締め切り日が設定されている場合、今日の日付と締め切り日の間に応じたセル背景色を決める処理を行っています（白＝デフォルト、超過＝灰、7日以内＝赤、30日以内＝黄）。
（11）では、ラベルの設定があればそれをすべてセルに設定しています。
（12）では、issueがオープンか（クローズされていないか）調べて、テキスト表示色の設定を行っています（オープン＝黒、クローズ＝濃いグレー）。
最後に（13）において、項目数の変化に対応するためにセルのクリアを行っています。
これでワークシートの形を整えて、冒頭の図1のような状態になれば完成です。IssuesやMilestonesをいろいろ変えて試してみて下さい。
まとめ
今回は、前回に引き続きGitHub APIをプロジェクト管理に応用するというサンプルを通じて、JSONデータの解析の手順を紹介しました。GitHub APIで扱える機能はIssues関係だけではありませんので、読者が自身のリポジトリを持っていれば、それを操作するサンプルに応用できるのではないかと思います。
次回は、Amazon PAからのデータ取得について取り上げたいと思います。
WINGSプロジェクト 山内直著/山田祥寛監修
＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中
Google Chrome徹底解説 - フル機能を使い倒そう 第5回 意外と知らない「戻る」「進む」「再読み込み」の使い方
