完成イメージと準備するもの

ブックの「設定」シートにリポジトリ名を入れてボタンを押すと、「コミット履歴」シートが次の内容で更新される。

日時作者SHAメッセージファイル数追加削除URL
2019/01/29 01:19Tim Hall06b6c45Update license111https://github.com/…
2019/01/29 01:05Tim Hall1e49ba8v2.3.1211https://github.com/…
2019/01/29 01:04Tim Halle0f1a77Update vba-utc to v1.0.62165https://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 オブジェクトがないため動かない。

ブックを準備する

  1. 新しいブックを作り、Excel マクロ有効ブック(.xlsm) として保存する
  2. シート名を 「設定」 に変え、下の表のとおりに A 列と B 列を入力する
  3. GitHub の VBA-JSON のリリースから JsonConverter.bas をダウンロードする
  4. Alt+F11 で VBE を開き、「ファイル」→「ファイルのインポート」 で JsonConverter.bas を取り込む
  5. 「ツール」→「参照設定」 で Microsoft Scripting Runtime にチェックを入れる
  6. 「挿入」→「標準モジュール」 で新しいモジュールを追加し、名前を 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 に次のコードを貼り付ける。

VBA(標準モジュール 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回)ではまず足りないので、トークンを設定し、開始日や最大件数で範囲を絞って使う。履歴全体の行数を集計したい場合は、次回のローカル方式の方が向いている。

ボタンに登録して実行する

  1. 「開発」タブがなければ、「ファイル」→「オプション」→「リボンのユーザー設定」 で「開発」にチェックを入れる
  2. 「設定」シートで 「開発」→「挿入」→「ボタン(フォーム コントロール)」 を選び、シート上に配置する
  3. マクロの登録画面で コミット履歴を取得 を選んで「OK」をクリックする
  4. ボタンの文字を「GitHub から取得」などに変える
  5. ボタンをクリックする。ステータスバーに件数が表示され、完了するとメッセージが出る

プライベートリポジトリの場合

🔒 この回のマクロで変わる点

  • コードの変更は不要。対象リポジトリの 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 を使わないので、トークンもレート制限も関係なく、変更行数まで一度に取れる。

→ PART 07 — Excel VBAでローカルクローンを操作する へ