完成イメージと準備するもの
ブックの「設定」シートにリポジトリ名を入れてボタンを押すと、「コミット履歴」シートが次の内容で更新される。
| 日時 | 作者 | SHA | メッセージ | ファイル数 | 追加 | 削除 | URL |
|---|---|---|---|---|---|---|---|
| 2019/01/29 01:19 | Tim Hall | 06b6c45 | Update license | 1 | 1 | 1 | https://github.com/… |
| 2019/01/29 01:05 | Tim Hall | 1e49ba8 | v2.3.1 | 2 | 1 | 1 | https://github.com/… |
| 2019/01/29 01:04 | Tim Hall | e0f1a77 | Update vba-utc to v1.0.6 | 2 | 16 | 5 | https://github.com/… |
ファイル数・追加・削除の3列は発展課題で、設定で有効にしたときだけ埋まる。用意するものは次のとおりだ。
| もの | 用途 |
|---|---|
| Excel(Microsoft 365 / 2019 / 2016 の Windows 版) | マクロの実行。32bit 版・64bit 版のどちらでもよい |
VBA-JSON(JsonConverter.bas) | JSON の解析。今回のお題のリポジトリそのものだ |
| アクセストークン(任意) | 公開リポジトリなら、なくても動く(1時間60回まで) |
HTTP 通信には WinHttp.WinHttpRequest.5.1、JSON の解析には VBA-JSON を使う。この組み合わせを選ぶ理由(MSXML2.XMLHTTP のキャッシュの問題や、64bit 版 Office で ScriptControl が使えないことなど)は、Backlog 情報取得シリーズの PART 06 で詳しく説明しているので、ここでは手順だけを示す。Mac 版の Excel では、これらの COM オブジェクトがないため動かない。
ブックを準備する
- 新しいブックを作り、Excel マクロ有効ブック(.xlsm) として保存する
- シート名を 「設定」 に変え、下の表のとおりに A 列と B 列を入力する
- GitHub の VBA-JSON のリリースから
JsonConverter.basをダウンロードする - Alt+F11 で VBE を開き、「ファイル」→「ファイルのインポート」 で
JsonConverter.basを取り込む - 「ツール」→「参照設定」 で Microsoft Scripting Runtime にチェックを入れる
- 「挿入」→「標準モジュール」 で新しいモジュールを追加し、名前を
GitHubCommitsにする
| セル | A 列(見出し) | B 列(値) | 省略したとき |
|---|---|---|---|
| 1行目 | リポジトリ | VBA-tools/VBA-JSON | 省略不可 |
| 2行目 | ブランチ | (例:develop) | デフォルトブランチ |
| 3行目 | 開始日 | (例:2018/1/1) | 最初のコミットから |
| 4行目 | 最大件数 | (例:300) | 全件 |
| 5行目 | 変更行数も取得 | TRUE または FALSE | 取得しない |
| 6行目 | トークン(任意) | (環境変数がないときだけ使う) | トークンなし |
トークンをどこに置くか
マクロ付きブックは人に渡しやすい分、トークンの扱いに注意が必要だ。このマクロは、まずユーザー環境変数 GITHUB_TOKEN を読み、なければ「設定」シートの B6 を読む。どちらもなければ、トークンなしで呼ぶ。
| 置き場所 | 評価 |
|---|---|
| VBA のコードに直接書く | 避ける。ブックを渡した相手に自分の権限ごと渡してしまう |
ユーザー環境変数 GITHUB_TOKEN | おすすめ。ブックにトークンが残らない。各自が自分のトークンを設定すれば、同じブックをそのまま配れる |
| 「設定」シートのセル | 手軽だが、入れたまま保存・共有しないよう注意する |
環境変数は PART 02 の手順で設定し、設定後は Excel をすべて閉じてから開き直す。
マクロ全体
標準モジュール GitHubCommits に次のコードを貼り付ける。
Option Explicit
'
' GitHub REST API でコミット履歴を取得して「コミット履歴」シートに展開する
' 必要なもの:標準モジュール JsonConverter(VBA-JSON)
' 参照設定 Microsoft Scripting Runtime
'
Private Const API_BASE As String = "https://api.github.com"
Private Const API_VERSION As String = "2022-11-28"
Private Const PER_PAGE As Long = 100
Private mRepo As String
Private mToken As String
' ===== エントリーポイント(ボタンに登録する) =====
Public Sub コミット履歴を取得()
Dim ws As Worksheet, query As String
Dim maxCount As Long, withStats As Boolean, commits As Collection
On Error GoTo Failed
Application.ScreenUpdating = False
' ① 設定を読む
Set ws = ThisWorkbook.Worksheets("設定")
mRepo = Trim$(ws.Range("B1").Value)
If Not mRepo Like "?*/?*" Then
Err.Raise vbObjectError + 1, , "リポジトリは「所有者名/リポジトリ名」の形式で入力してください。"
End If
maxCount = Val(ws.Range("B4").Value)
withStats = (UCase$(Trim$(CStr(ws.Range("B5").Value))) = "TRUE")
mToken = Environ$("GITHUB_TOKEN") ' 環境変数を優先
If mToken = "" Then mToken = Trim$(ws.Range("B6").Value)
' ② 条件を組み立てる
query = "per_page=" & PER_PAGE
If Trim$(ws.Range("B2").Value) <> "" Then
query = query & "&sha=" & WorksheetFunction.EncodeURL(Trim$(ws.Range("B2").Value))
End If
If IsDate(ws.Range("B3").Value) Then
query = query & "&since=" & JstToIsoUtc(CDate(ws.Range("B3").Value))
End If
' ③ 全ページを取得してシートに展開する
Set commits = GetCommits(API_BASE & "/repos/" & mRepo & "/commits?" & query, maxCount)
WriteCommits commits, withStats
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox commits.Count & " 件のコミットを取得しました。", vbInformation
Exit Sub
Failed:
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox "取得に失敗しました。" & vbCrLf & Err.Description, vbExclamation
End Sub
' ===== 全ページの取得(Link ヘッダーの rel="next" をたどる) =====
Private Function GetCommits(ByVal url As String, ByVal maxCount As Long) As Collection
Dim all As New Collection, page As Object, item As Variant, nextUrl As String
Do While url <> ""
Set page = GitHubGet(url, nextUrl)
For Each item In page
all.Add item
If maxCount > 0 And all.Count >= maxCount Then Exit Do
Next
Application.StatusBar = "取得中… " & all.Count & " 件"
DoEvents
url = nextUrl
Loop
Set GetCommits = all
End Function
' ===== 1回のリクエスト:JSON を解析して返し、次のページの URL を nextUrl に入れる =====
Private Function GitHubGet(ByVal url As String, ByRef nextUrl As String) As Object
Dim http As Object, i As Long, status As Long
nextUrl = ""
For i = 1 To 3
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.Open "GET", url, False
http.SetRequestHeader "Accept", "application/vnd.github+json"
http.SetRequestHeader "X-GitHub-Api-Version", API_VERSION
http.SetRequestHeader "User-Agent", "excel-vba-commit-history"
If mToken <> "" Then http.SetRequestHeader "Authorization", "Bearer " & mToken
http.SetTimeouts 10000, 10000, 30000, 60000 ' 名前解決・接続・送信・受信(ミリ秒)
http.Send
status = http.Status
If status = 200 Then
nextUrl = NextLink(HeaderOrEmpty(http, "Link"))
Set GitHubGet = JsonConverter.ParseJson(Utf8Text(http.ResponseBody))
Exit Function
ElseIf (status = 403 Or status = 429) And HeaderOrEmpty(http, "X-RateLimit-Remaining") = "0" Then
Err.Raise vbObjectError + 512, , "レート制限に達しました。" & _
Format$(ResetTimeJst(http), "hh:nn") & " 以降に実行してください。" & _
IIf(mToken = "", vbCrLf & "トークンを設定すると上限が1時間5,000回になります。", "")
ElseIf status >= 500 Then
Application.StatusBar = "HTTP " & status & ":5秒待って再試行します(" & i & " 回目)"
Application.Wait Now + TimeSerial(0, 0, 5)
ElseIf status = 404 Then
Err.Raise vbObjectError + 513, , "HTTP 404:リポジトリ名が違うか、非公開リポジトリを読む権限がありません。"
Else
Err.Raise vbObjectError + 514, , "HTTP " & status & ":" & ErrorMessage(http)
End If
Next
Err.Raise vbObjectError + 515, , "再試行の上限に達しました:" & url
End Function
' ===== 「コミット履歴」シート =====
Private Sub WriteCommits(ByVal commits As Collection, ByVal withStats As Boolean)
Dim ws As Worksheet, data() As Variant, c As Variant, r As Long
Dim files As Long, additions As Long, deletions As Long
Set ws = PrepareSheet("コミット履歴")
ReDim data(1 To commits.Count + 1, 1 To 8)
data(1, 1) = "日時": data(1, 2) = "作者": data(1, 3) = "SHA": data(1, 4) = "メッセージ"
data(1, 5) = "ファイル数": data(1, 6) = "追加": data(1, 7) = "削除": data(1, 8) = "URL"
r = 1
For Each c In commits
r = r + 1
data(r, 1) = IsoToJst(c("commit")("author")("date"))
data(r, 2) = c("commit")("author")("name")
data(r, 3) = Left$(c("sha"), 7)
data(r, 4) = FirstLine(c("commit")("message"))
If withStats Then ' コミット1件につき1リクエスト
GetStats c("sha"), files, additions, deletions
data(r, 5) = files: data(r, 6) = additions: data(r, 7) = deletions
Application.StatusBar = "変更行数を取得中… " & (r - 1) & " / " & commits.Count
DoEvents
End If
data(r, 8) = c("html_url")
Next
ws.Range("B:D").NumberFormat = "@" ' SHA やメッセージを数値・数式にしない
ws.Range("A1").Resize(r, 8).Value = data ' 1回でまとめて書き込む(速い)
ws.Columns(1).NumberFormat = "yyyy/mm/dd hh:mm"
ws.Range("A1").Resize(1, 8).Font.Bold = True
ws.Range("A1").Resize(r, 8).AutoFilter
ws.Columns("A:H").AutoFit
If ws.Columns(4).ColumnWidth > 60 Then ws.Columns(4).ColumnWidth = 60
End Sub
' ===== コミット1件の変更ファイル数・追加行数・削除行数 =====
Private Sub GetStats(ByVal sha As String, ByRef files As Long, _
ByRef additions As Long, ByRef deletions As Long)
Dim d As Object, dummy As String
Set d = GitHubGet(API_BASE & "/repos/" & mRepo & "/commits/" & sha, dummy)
files = d("files").Count
additions = d("stats")("additions")
deletions = d("stats")("deletions")
End Sub
' ===== 小さな関数 =====
Private Function NextLink(ByVal linkHeader As String) As String
' <https://...&page=2>; rel="next", <https://...&page=3>; rel="last" から next の URL を取り出す
Dim part As Variant, p1 As Long, p2 As Long
For Each part In Split(linkHeader, ",")
If InStr(part, "rel=""next""") > 0 Then
p1 = InStr(part, "<"): p2 = InStr(part, ">")
If p1 > 0 And p2 > p1 Then NextLink = Mid$(part, p1 + 1, p2 - p1 - 1)
Exit Function
End If
Next
End Function
Private Function HeaderOrEmpty(ByVal http As Object, ByVal headerName As String) As String
' ヘッダーがないと GetResponseHeader はエラーになるので、空文字を返す
On Error Resume Next
HeaderOrEmpty = http.GetResponseHeader(headerName)
End Function
Private Function ResetTimeJst(ByVal http As Object) As Date
' X-RateLimit-Reset(UNIX 時間・UTC)を日本時間にする
ResetTimeJst = DateSerial(1970, 1, 1) + Val(HeaderOrEmpty(http, "X-RateLimit-Reset")) / 86400 + TimeSerial(9, 0, 0)
End Function
Private Function ErrorMessage(ByVal http As Object) As String
' {"message": "...", "documentation_url": "..."} から message を取り出す
On Error GoTo Fallback
ErrorMessage = JsonConverter.ParseJson(Utf8Text(http.ResponseBody))("message")
Exit Function
Fallback:
ErrorMessage = http.StatusText
End Function
Private Function Utf8Text(ByVal bytes As Variant) As String
' レスポンスのバイト列を UTF-8 として文字列にする
Dim st As Object
Set st = CreateObject("ADODB.Stream")
st.Type = 1: st.Open: st.Write bytes ' 1 = バイナリ
st.Position = 0: st.Type = 2: st.Charset = "utf-8" ' 2 = テキスト
Utf8Text = st.ReadText
st.Close
End Function
Private Function IsoToJst(ByVal s As String) As Date
' "2019-01-28T16:19:54Z"(UTC)を Excel の日付(日本時間)にする
IsoToJst = DateSerial(CInt(Mid$(s, 1, 4)), CInt(Mid$(s, 6, 2)), CInt(Mid$(s, 9, 2))) _
+ TimeSerial(CInt(Mid$(s, 12, 2)), CInt(Mid$(s, 15, 2)), CInt(Mid$(s, 18, 2))) _
+ TimeSerial(9, 0, 0)
End Function
Private Function JstToIsoUtc(ByVal d As Date) As String
' 日本時間の日付を "2017-12-31T15:00:00Z" の形式(UTC)にする
JstToIsoUtc = Format$(DateAdd("h", -9, d), "yyyy-mm-dd\Thh:nn:ss\Z")
End Function
Private Function FirstLine(ByVal s As String) As String
Dim p As Long
p = InStr(s, vbLf)
If p > 0 Then s = Left$(s, p - 1)
FirstLine = Replace(s, vbCr, "")
End Function
Private Function PrepareSheet(ByVal sheetName As String) As Worksheet
On Error Resume Next
Set PrepareSheet = ThisWorkbook.Worksheets(sheetName)
On Error GoTo 0
If PrepareSheet Is Nothing Then
Set PrepareSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
PrepareSheet.Name = sheetName
End If
If PrepareSheet.AutoFilterMode Then PrepareSheet.AutoFilterMode = False
PrepareSheet.Cells.Clear
End Functionコードのポイント
User-Agent を付ける
GitHub の API は、User-Agent ヘッダーのないリクエストを拒否する。WinHttpRequest は既定の値を付けるが、何のプログラムからの呼び出しかがわかるように、明示的に設定している。
ページネーションは Link ヘッダーで判断する
続きのページがあるとき、レスポンスの Link ヘッダーに次のページの URL が入る(PART 03)。NextLink でこの URL を取り出し、空になるまで GetCommits が繰り返す。VBA には Python の r.links のような機能がないので、カンマで区切って rel="next" を含む部分を探している。
最後のページでは Link ヘッダーに rel="next" がなく、1ページに収まる場合はヘッダー自体がない。GetResponseHeader は存在しないヘッダーを指定するとエラーになるため、HeaderOrEmpty で包んでいる。
SHA を文字列として書く
短縮した SHA は、1234567 のように数字だけになったり、12345e6 のように指数表記と解釈される並びになったりすることがある。そのままセルに書くと、Excel が数値に変換して、後者は 1.2345E+10 と表示されてしまう。書き込む前に B〜D 列の表示形式を文字列(@)にして防いでいる。メッセージが = で始まる場合に数式として解釈されるのも、同じ設定で防げる。
UTC を日本時間に変換する
API の日時は UTC の文字列で返る。IsoToJst で年月日時分秒を切り出して Excel の日付型にし、9時間足している。逆に、「設定」シートの開始日は日本時間として入力されるので、JstToIsoUtc で9時間引いて UTC の文字列にしてから API に渡す。
レート制限はメッセージで知らせる
Python 版では短い待ち時間なら待って再試行したが、Excel で数十分も固まるのは使いにくい。このマクロは、レート制限に当たったら再開できる時刻をメッセージで表示して終了する。
発展:変更ファイル数と行数
「設定」シートの B5 を TRUE にすると、コミットごとに詳細の API(/commits/{sha})を呼び、ファイル数・追加行数・削除行数の列を埋める。
⚠️ コミットの数だけリクエストが増える
100件のコミットなら、一覧1回+詳細100回で101回のリクエストになる。トークンなし(1時間60回)ではまず足りないので、トークンを設定し、開始日や最大件数で範囲を絞って使う。履歴全体の行数を集計したい場合は、次回のローカル方式の方が向いている。
ボタンに登録して実行する
- 「開発」タブがなければ、「ファイル」→「オプション」→「リボンのユーザー設定」 で「開発」にチェックを入れる
- 「設定」シートで 「開発」→「挿入」→「ボタン(フォーム コントロール)」 を選び、シート上に配置する
- マクロの登録画面で
コミット履歴を取得を選んで「OK」をクリックする - ボタンの文字を「GitHub から取得」などに変える
- ボタンをクリックする。ステータスバーに件数が表示され、完了するとメッセージが出る
プライベートリポジトリの場合
🔒 この回のマクロで変わる点
- コードの変更は不要。対象リポジトリの Contents: Read-only を付けたトークン(PART 02)を、環境変数
GITHUB_TOKENに設定する - ブックを複数人で使う場合は、各自が自分のトークンを自分の PC の環境変数に設定する。1人のトークンをシートに入れて配ると、その人の権限を全員に渡すことになる
- トークンがない、または権限が足りないと
404が返り、マクロは「リポジトリ名が違うか、非公開リポジトリを読む権限がありません」と表示する - GitHub Enterprise Server の場合は、定数
API_BASEをhttps://ホスト名/api/v3に変える
つまずきやすい点
| 症状 | 原因と対処 |
|---|---|
| 「ユーザー定義型は定義されていません」 | 参照設定で Microsoft Scripting Runtime にチェックが入っていない |
| 「変数が定義されていません」(JsonConverter) | JsonConverter.bas をインポートしていない、またはモジュール名が JsonConverter ではない |
| 「レート制限に達しました」 | トークンなしの上限(1時間60回)。社内から同じ IP アドレスで出ている場合は、他の人の分も合算される。トークンを設定する |
| HTTP 401:Bad credentials | トークンの値が違う、または期限切れ。環境変数を設定したあと Excel を開き直したかも確認する |
| 「サーバー名またはアドレスは解決されませんでした」 | 社内プロキシが必要。http.Open の次の行に http.SetProxy 2, "proxy.example.co.jp:8080" を追加する |
SHA が 1.2345E+10 のように表示される | 表示形式を文字列にする前に書き込んでいる。NumberFormat = "@" を値の書き込みより前に置く |
✅ 次の記事では…
PART 07 では、同じ Excel VBA からローカルクローンに対して git log を実行する。API を使わないので、トークンもレート制限も関係なく、変更行数まで一度に取れる。