Create a GitHub project management worksheet [GitHub API]

5th purpose of the series

In the fifth installment of this series, we will focus on the operation of the GitHub repository by Excel VBA.Last time, I understood GitHub, prepared the necessary repositories, and checked the operation of the API.This time, we will create an Excel worksheet that lists them (Fig. 1).

完成サンプルのソースコードhttps://github.com/wateryinhare62/mynavi_excelvba_webservice

In this series, the operation confirmation is confirmed by Windows 10 Pro (64bit), Microsoft 365 (Excel 16.0, VBA 7.I am doing it in 1).Please be careful when trying with the old version or a single Excel.

Preparation of worksheets

If you can confirm that the API can be used well with authentication, prepare a worksheet.This time, we will acquire iSSUE, so we will place the owner's name, the repository name, and the button to start acquiring at the top.In the following, we do not give any detailed instructions or size, so please adjust yourself while looking at the completed sample.

Enter the owner's name and repository name in the cell.As a value, put the reader's owner name and the repository name "MyNavi-ew-github".Based on the owner's name and repository name in the cell, the data is obtained through the API and reflects it in the worksheet.

Continue to place the button to start acquiring.Click the Development tab and click the [Insert] button in the [Control] group to display the control list as the menu (see the 1st Series if the [Development] tab is not displayed).From here, click [Button (Form Control)] in the "Form Control" category.The shape of the mouse cursor will change to " +", and you will be able to place buttons of your favorite size in your favorite position.The location is on the right side of the owner name (Fig. 2).

The "Macro Registration" dialog opens at the same time as the mouse button release. Click the [Create] button to create an event handler called when clicking the button (Fig. 3).The initial value of the event handler name is "button 1_click", but you can also change the name in the [Macro name] column.You can check that Visual Basic Editor has been launched to create an event handler (Fig. 4).

If necessary, change the text of the button to "Start acquisition".Let's check the working status so far (Fig. 5).

The GitHub API returns the result in JSON data.Since JSON data is used from VBA, prepare the library required for that.As in the third, use a third-party library called "VBA-JSON".VBA-JSON is released on GitHub, which is also the theme.Refer to the 3rd article to prepare VBA-JSON, including enabling the reference settings for "Microsoft Scripting Runtime".Here, save the book.The book name may be anything, but the format is "Excel macro effective book (*).Please make it XLSM).If you do not enable the macro, you will not be able to run the VBA script.After this, please save the book at the right time.

Access Web service

This time, I will try to access GitHub via API.Therefore, access to web services is common in consideration of expandability.First, I will write the script.When the script is associated with the arrangement of the button, the standard module Module1 has already been created, so we will add a script here.The script that accesses the web service is a function called Kickwebservice.List 1 shows it.Add it under the existing "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

It is a function that receives the API path and query argument as an argument and returns the JSON character string as a result. In advance, put the gitHub account and token in the variable in (1) and (2). Hard coding is not desirable, so you should read and use something in the external file or input to the user each time. Here, it is simplified as a sample. In (3), we are creating a URL. From (4), we have created a Base64 encoded account and token for authentication. Please refer to the converttobernary function that converts strings into binaries, so please refer to the sample file because this series is not relevant. From (5), we are preparing and execution to access with HTTP, such as web services. After creating an object, specify the HTTP method and URL to call the Open method, and in the setRequeStheader method ・ ・ Accept header (Data type that can be received by the request source, Github API is recommended to specify specified in the Github API) ・ Authorization Header (authentication. After setting the method, username and password), the actual call is performed in the Send method. The syntax of the OPEN method is as shown in List 2, and can be omitted after the third argument. The default value of the third asynchronous acquisition flag is true, and in this case it returns from the method immediately after calling the Open method, but in False, wait until there is a response. In this example, the API is lightweight and there is almost no waiting time, so I am waiting for the response to the response. In the case of true, you need to describe a script to wait until the response is returned after calling the Open method. User names and passwords are required when the page requires authentication, but in this example, it is omitted because it is set directly by the Authorization header.

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

The result of the call can be obtained in the Responsetext property.If this is the sky, there is some error, but this is returned as a function return value (6).

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

Get acquisition results

Let's write an ISSUE script.

Before that, List 2 shows the structure of the json data of the ISSUE acquisition result, which is a prerequisite knowledge to read the script.

[　…取得結果は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へ…略…]

The point is that it is an ISSUE array first (if it is not an array, some error occurs). The search results are returned on a page, and the number of default items per page is 30 (up to 100 by query perpage arguments). Which pages do you get at the actual request? I will specify that by the page argument of the query. The number of items is obtained by using the Count Properties. The acquisition results include almost all information related to ISSUE. The person in charge is included in ASSIGNEES, the label is Labels, and the milestone is included in each node, so you can get the necessary information from the lower nodes. Since assignees and labels are arrays, it is necessary to repeat the value by the number of elements. If Milestone is not set, null is included as a value, so it is necessary to make a NULL judgment and extract the value.

Based on the acquisition results, take out the owner's name and repository name from the worksheet, and add a script to write the obtained result in the button 1_click (List 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をいろいろ変えて試してみて下さい。

summary

This time, we introduced the JSON data analysis procedure through a sample of applying the GitHub API to project management.The functions that can be handled with the GitHub API are not only Issues -related, so if you have your own repository, I think it can be applied to the sample that operates it.Next time, I would like to take up the data acquisition from Amazon PA.

WINGSプロジェクト 山内直著/山田祥寛監修＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中