GitHubのプロジェクト管理ワークシートを作成しよう【GitHub API】

連載第5回目の目的

この連載第5回目では、前回に引き続きExcel VBAによるGitHubリポジトリの操作について取り上げます。前回は、GitHubについて理解し、必要なリポジトリの準備、そしてAPIの動作確認まで行いました。今回は、それらを一覧表示するExcelワークシートを作成していきます（図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）。

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

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

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」の下に追加します。

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 WithEnd 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(HTTPメソッド, URL, 非同期取得フラグ, ユーザ名, パスワード)

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

［NOTE］BASE64エンコーディングとは?BASE64エンコーディングとは、データを64種類の英数字のみで構成するフォーマットです。仕様上、7ビットのデータしか扱えない通信に用いられます。多くはマルチバイト文字やバイナリデータをメールやWebで扱うために使用されます。今回のサンプルでは、一部に記号を含む「アカウント名＋トークン」をAcceptヘッダの内容とするために、このBASE64エンコーディングを行っています。

取得結果を取得する

では、issueのスクリプトを書いていきましょう。

その前に、スクリプトを読む前提知識となる、issue取得結果のJSONデータの構造をリスト2に示します。

[　…取得結果は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）。

Dim Row As Integer, Col As IntegerDim Owner As String, Repository As String' 所有者名とリポジトリ名をワークシートから取得するCol = 2Row = 3: Owner = Cells(Row, Col) '(1)Row = 4: Repository = Cells(Row, Col)' 取得結果の最大数と1ページあたりのデータ数を変数にセットするDim MaxResult As Integer, PerPage As IntegerMaxResult = 1000: PerPage = 100 '(2)Dim Page As Integer, Count As Long, TotalCount As LongPage = 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 ObjectSet Json = JsonConverter.ParseJson(Result)'(7)Dim i As Integer, j As Integer, Items As Integer' 取得データ数が0だったら処理を終了するItems = Json.CountIf Items = 0 ThenExit ForEnd If' Dictionaryが返ってきていれば認証エラー等であるので処理を中止するIf TypeName(Json) = "Dictionary" ThenMsgBox ("認証エラーです。")Exit ForEnd IfDim str As StringFor 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, DueOnToday = CDate(Format(Date, "yyyy/mm/dd"))'(9)DueOn = Nullstr = "なし"If Not IsNull(Json(i)("milestone")) Then str = Left(Json(i)("milestone")("due_on"), 10) DueOn = CDate(str)End IfCells(Row, Col + 3) = str' 締め切り日と実行日を比較して背景色を決めるDim Color'(10)Color = vbWhiteIf Not IsNull(DueOn) Then If DueOn < Today ThenColor = rgbLightSlateGray ElseIf DateDiff("d", Today, DueOn) < 7 ThenColor = rgbSalmonElseIf DateDiff("d", Today, DueOn) < 30 Then Color = rgbPaleGoldenrodEnd IfEnd If End IfEnd If' ラベルをセルに設定するstr = "" '(11)For j = 1 To Json(i)("labels").Count If str <> "" Thenstr = str + "," End If str = str + Json(i)("labels")(j)("name")NextCells(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 = vbBlackElse TextColor = rgbDarkGrayEnd IfRange(Cells(Row, 1), Cells(Row, 7)).Font.Color = TextColor' 次のデータへRow = Row + 1Count = Count + 1Next End IfNext' 最大行数までクリアする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記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中