完成イメージと準備するもの
この回では、PART 04 のお題 を Excel VBA で実装する。ブックを開いてボタンを押すと、次の2つのシートが更新される。
- 課題一覧:課題キー・件名・状態・担当者・期限日・予定時間・実績時間・更新日時(オートフィルター付き)
- 担当者別:担当者ごとの未完了件数と予定時間の合計
用意するものは次のとおりだ。
| もの | 用途 |
|---|---|
| Excel(Microsoft 365 / 2019 / 2016 の Windows 版) | マクロの実行。32bit 版・64bit 版のどちらでもよい |
VBA-JSON(JsonConverter.bas) | JSON の解析(後述) |
| APIキー・ホスト名・プロジェクトキー | PART 02 で準備したもの |
Mac 版の Excel は、この回で使う Windows の COM オブジェクト(WinHttp・ADODB)がないため動かない。
HTTP 通信に使うオブジェクトを選ぶ
VBA から HTTP 通信をする方法は、主に3つある。
| オブジェクト | 特徴 | 評価 |
|---|---|---|
MSXML2.XMLHTTP | IE(WinINet)の仕組みを使う。サンプルが多い | GET の結果がキャッシュされて最新の値が返らないことがある。タイムアウトを細かく設定できない |
MSXML2.ServerXMLHTTP | WinHTTP を使う MSXML のサーバー向け版 | 使えるが、プロキシの設定が別途必要なことがある |
WinHttp.WinHttpRequest.5.1 | WinHTTP を直接使う。キャッシュしない。SetTimeouts でタイムアウトを指定できる | この回で使う |
どれも Windows に標準で入っているので、CreateObject で作れば参照設定は不要だ。社内のプロキシ経由でないとインターネットに出られない環境では、WinHttpRequest の SetProxy でプロキシを指定する(後述)。
JSON の解析には VBA-JSON を使う
VBA には JSON を解析する機能がない。昔は ScriptControl(JScript)で解析する方法がよく紹介されていたが、ScriptControl は 64bit 版の Office では使えない。現在の Microsoft 365 は 64bit 版が標準なので、この方法は避ける。
代わりに、VBA だけで書かれたオープンソースの VBA-JSON(MIT ライセンス)を使う。32bit・64bit のどちらでも動き、JSON をオブジェクトに変換してくれる。
| JSON | VBA-JSON での変換先 | 取り出し方の例 |
|---|---|---|
オブジェクト {...} | Scripting.Dictionary | issue("summary")、issue("status")("name") |
配列 [...] | Collection(添字は1から) | For Each x In list、list(1) |
null | Null | IsNull(issue("assignee")) で判定 |
| 数値・文字列・真偽値 | Double・String・Boolean | そのまま使える |
ブックを準備する
- 新しいブックを作り、Excel マクロ有効ブック(.xlsm) として保存する
- シート名を 「設定」 に変え、A1〜A3 に「ホスト名」「プロジェクトキー」「APIキー(任意)」と入力し、B1・B2 に値を入れる(例:
example.backlog.com、DEV) - GitHub の VBA-JSON のリリースから
JsonConverter.basをダウンロードする - Alt+F11 で VBE を開き、「ファイル」→「ファイルのインポート」 で
JsonConverter.basを取り込む - 「ツール」→「参照設定」 で Microsoft Scripting Runtime にチェックを入れる(VBA-JSON が
Dictionaryを使うため) - 「挿入」→「標準モジュール」 で新しいモジュールを追加し、名前を
BacklogFetchにする
APIキーをどこに置くか
マクロ付きブックは人に渡しやすい分、APIキーの扱いに注意が必要だ。
| 置き場所 | 評価 |
|---|---|
| VBA のコードに直接書く | 避ける。ブックを渡した相手に自分の権限ごと渡してしまう |
ユーザー環境変数 BACKLOG_API_KEY | おすすめ。ブックにキーが残らない。各自が自分のキーを設定すれば、同じブックをそのまま配れる |
| 「設定」シートのセル | 手軽だが、キーを入れたまま保存・共有しないよう注意する。共有前に必ず消す |
この回のマクロは、まず環境変数を読み、なければ「設定」シートの B3 を読む。環境変数は PART 02 の手順で設定し、設定後は Excel を開き直す。
マクロ全体
標準モジュール BacklogFetch に次のコードを貼り付ける。
Option Explicit
'
' Backlog の未完了課題を取得して「課題一覧」「担当者別」シートに展開する
' 必要なもの:標準モジュール JsonConverter(VBA-JSON)
' 参照設定 Microsoft Scripting Runtime
'
Private Const DONE_STATUS_ID As Long = 4 ' 「完了」の状態ID
Private Const PAGE_SIZE As Long = 100
Private mHost As String
Private mApiKey As String
' ===== エントリーポイント(ボタンに登録する) =====
Public Sub 課題一覧を取得()
Dim projectKey As String, project As Object, statuses As Object
Dim st As Variant, q As String, issues As Collection
On Error GoTo Failed
Application.ScreenUpdating = False
LoadSettings
projectKey = Trim$(ThisWorkbook.Worksheets("設定").Range("B2").Value)
' ① プロジェクトID
Set project = BacklogGet("projects/" & projectKey)
q = "projectId[]=" & project("id") & "&sort=created&order=asc"
' ② 「完了」以外の状態ID
Set statuses = BacklogGet("projects/" & projectKey & "/statuses")
For Each st In statuses
If st("id") <> DONE_STATUS_ID Then q = q & "&statusId[]=" & st("id")
Next
' ③ 課題を全件取得
Set issues = BacklogGetAll("issues", q)
' ④⑤ シートに展開
WriteIssues issues
WriteSummary issues
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox issues.Count & " 件の未完了課題を取得しました。", vbInformation
Exit Sub
Failed:
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox "取得に失敗しました。" & vbCrLf & Err.Description, vbExclamation
End Sub
' ===== 設定の読み込み =====
Private Sub LoadSettings()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("設定")
mHost = Trim$(ws.Range("B1").Value)
If mHost = "" Then mHost = Environ$("BACKLOG_HOST")
mApiKey = Environ$("BACKLOG_API_KEY") ' 環境変数を優先
If mApiKey = "" Then mApiKey = Trim$(ws.Range("B3").Value)
If mHost = "" Or mApiKey = "" Then
Err.Raise vbObjectError + 1, , "ホスト名または APIキーが設定されていません。"
End If
End Sub
' ===== 1回のリクエスト:429・5xx は待って再試行し、JSON を解析して返す =====
Private Function BacklogGet(ByVal path As String, Optional ByVal query As String = "") As Object
Dim http As Object, url As String, i As Long, status As Long
url = "https://" & mHost & "/api/v2/" & path
If query <> "" Then url = url & "?" & query
For i = 1 To 5
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.Open "GET", url, False
http.SetRequestHeader "Backlog-API-Key", mApiKey
http.SetTimeouts 10000, 10000, 30000, 60000 ' 名前解決・接続・送信・受信(ミリ秒)
http.Send
status = http.Status
If status = 200 Then
WaitIfLimitLow http
Set BacklogGet = JsonConverter.ParseJson(Utf8Text(http.ResponseBody))
Exit Function
ElseIf status = 429 Or status >= 500 Then
Application.StatusBar = "HTTP " & status & ":60秒待って再試行します(" & i & " 回目)"
Application.Wait Now + TimeSerial(0, 1, 0)
Else
Err.Raise vbObjectError + 513, , "HTTP " & status & ":" & ErrorMessage(http) & vbCrLf & url
End If
Next
Err.Raise vbObjectError + 514, , "再試行の上限に達しました:" & url
End Function
' ===== 全件取得(count / offset のページネーション) =====
Private Function BacklogGetAll(ByVal path As String, ByVal query As String) As Collection
Dim all As New Collection, page As Object, item As Variant, offset As Long
Do
Set page = BacklogGet(path, query & "&count=" & PAGE_SIZE & "&offset=" & offset)
For Each item In page
all.Add item
Next
Application.StatusBar = "取得中… " & all.Count & " 件"
DoEvents
If page.Count < PAGE_SIZE Then Exit Do
offset = offset + PAGE_SIZE
Loop
Set BacklogGetAll = all
End Function
' ===== 「課題一覧」シート =====
Private Sub WriteIssues(ByVal issues As Collection)
Dim ws As Worksheet, data() As Variant, issue As Variant, r As Long
Set ws = PrepareSheet("課題一覧")
ReDim data(1 To issues.Count + 1, 1 To 8)
data(1, 1) = "課題キー": data(1, 2) = "件名": data(1, 3) = "状態": data(1, 4) = "担当者"
data(1, 5) = "期限日": data(1, 6) = "予定時間": data(1, 7) = "実績時間": data(1, 8) = "更新日時"
r = 1
For Each issue In issues
r = r + 1
data(r, 1) = issue("issueKey")
data(r, 2) = issue("summary")
data(r, 3) = issue("status")("name")
data(r, 4) = AssigneeName(issue("assignee"))
data(r, 5) = IsoToJst(issue("dueDate"), True)
data(r, 6) = NullToEmpty(issue("estimatedHours"))
data(r, 7) = NullToEmpty(issue("actualHours"))
data(r, 8) = IsoToJst(issue("updated"), False)
Next
ws.Columns(2).NumberFormat = "@" ' 件名が「=」で始まっても数式にしない
ws.Range("A1").Resize(r, 8).Value = data ' 1回でまとめて書き込む(速い)
ws.Columns(5).NumberFormat = "yyyy/mm/dd"
ws.Columns(8).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(2).ColumnWidth > 60 Then ws.Columns(2).ColumnWidth = 60
End Sub
' ===== 「担当者別」シート =====
Private Sub WriteSummary(ByVal issues As Collection)
Dim cnt As Object, hours As Object, issue As Variant, who As Variant
Dim ws As Worksheet, r As Long
Set cnt = CreateObject("Scripting.Dictionary")
Set hours = CreateObject("Scripting.Dictionary")
For Each issue In issues
who = AssigneeName(issue("assignee"))
cnt(who) = cnt(who) + 1
hours(who) = hours(who) + Val(NullToEmpty(issue("estimatedHours")) & "")
Next
Set ws = PrepareSheet("担当者別")
ws.Range("A1:C1").Value = Array("担当者", "未完了件数", "予定時間の合計")
r = 1
For Each who In cnt.Keys
r = r + 1
ws.Cells(r, 1).Value = who
ws.Cells(r, 2).Value = cnt(who)
ws.Cells(r, 3).Value = hours(who)
Next
If r > 1 Then ws.Range("A1").Resize(r, 3).Sort Key1:=ws.Range("B1"), Order1:=xlDescending, Header:=xlYes
ws.Range("A1:C1").Font.Bold = True
ws.Columns("A:C").AutoFit
End Sub
' ===== 小さな関数 =====
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
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 ErrorMessage(ByVal http As Object) As String
' {"errors":[{"message": ...}]} から message を取り出す
On Error GoTo Fallback
Dim j As Object
Set j = JsonConverter.ParseJson(Utf8Text(http.ResponseBody))
ErrorMessage = j("errors")(1)("message")
Exit Function
Fallback:
ErrorMessage = http.StatusText
End Function
Private Sub WaitIfLimitLow(ByVal http As Object)
' レート制限の残りが少なければ、リセット時刻まで待つ
Dim remaining As String, resetAt As String, waitSec As Double
On Error Resume Next
remaining = http.GetResponseHeader("X-RateLimit-Remaining")
resetAt = http.GetResponseHeader("X-RateLimit-Reset")
On Error GoTo 0
If remaining = "" Or resetAt = "" Then Exit Sub
If CLng(remaining) > 1 Then Exit Sub
' PC の時計が日本時間である前提で、現在の UNIX 時間を求める
waitSec = CDbl(resetAt) - ((Now - DateSerial(1970, 1, 1)) * 86400 - 9 * 3600) + 1
If waitSec > 0 And waitSec <= 70 Then Application.Wait Now + waitSec / 86400
End Sub
Private Function IsoToJst(ByVal v As Variant, ByVal dateOnly As Boolean) As Variant
' "2026-11-05T01:23:45Z"(UTC)を Excel の日付(日本時間)にする
Dim s As String, d As Date
If IsNull(v) Or IsEmpty(v) Then IsoToJst = Empty: Exit Function
s = CStr(v)
d = DateSerial(CInt(Mid$(s, 1, 4)), CInt(Mid$(s, 6, 2)), CInt(Mid$(s, 9, 2)))
If dateOnly Then IsoToJst = d: Exit Function ' 期限日・開始日は日付だけを使う
d = d + TimeSerial(CInt(Mid$(s, 12, 2)), CInt(Mid$(s, 15, 2)), CInt(Mid$(s, 18, 2)))
IsoToJst = d + TimeSerial(9, 0, 0)
End Function
Private Function AssigneeName(ByVal v As Variant) As String
If IsObject(v) Then AssigneeName = v("name") Else AssigneeName = "(未割り当て)"
End Function
Private Function NullToEmpty(ByVal v As Variant) As Variant
If IsNull(v) Then NullToEmpty = Empty Else NullToEmpty = v
End Functionコードのポイント
レスポンスは自分で UTF-8 として読む
WinHttpRequest の ResponseText は、レスポンスヘッダーの文字コード指定を見て文字列にしてくれるが、環境によっては日本語が化けることがある。Utf8Text では ResponseBody(バイト列)を ADODB.Stream で UTF-8 として読み直しているので、確実に正しい文字列になる。
シートへの書き込みは配列で1回にまとめる
セルに1つずつ値を書くと、1,000件で数十秒かかることがある。WriteIssues では2次元配列に全件を詰めてから、Range.Value = data で一度に書き込んでいる。これだけで数秒以内に終わる。
件名を文字列として書く
件名が = や + で始まると、Excel が数式として解釈してしまう。書き込む前に件名の列の表示形式を文字列(@)にしておくことで防いでいる。
UTC を日本時間に変換する
VBA-JSON は日時を文字列のまま返すので、IsoToJst で年月日時分秒を切り出し、9時間足して Excel の日付型にしている。期限日は日付だけに意味があるので、時刻を足さずに日付として扱う(PART 04)。
担当者がいない課題
担当者が未設定だと issue("assignee") は Null になり、issue("assignee")("name") と書くとエラーで止まる。AssigneeName では、値がオブジェクト(Dictionary)かどうかを IsObject で確かめている。
ボタンに登録して実行する
- 「開発」タブがなければ、「ファイル」→「オプション」→「リボンのユーザー設定」 で「開発」にチェックを入れる
- 「設定」シートで 「開発」→「挿入」→「ボタン(フォーム コントロール)」 を選び、シート上に配置する
- マクロの登録画面で
課題一覧を取得を選んで「OK」をクリックする - ボタンの文字を「Backlog から取得」などに変える
- ボタンをクリックする。ステータスバーに件数が表示され、完了するとメッセージが出る
ブックをメールやファイルサーバーで受け取ると、Windows がマクロをブロックすることがある。その場合はファイルのプロパティで「許可する」にチェックを入れるか、社内の信頼できる場所にブックを置く。
💡 Power Query でも取得できる
Excel の Power Query(Web.Contents)でもヘッダーを付けて Backlog API を呼べる。ただし、ページネーションや 429 の待機を書くには M 言語の知識が必要になり、APIキーの扱いも難しい。ボタンで更新する用途なら、この回の VBA の方が扱いやすい。
つまずきやすい点
| 症状 | 原因と対処 |
|---|---|
| 「ユーザー定義型は定義されていません」 | 参照設定で Microsoft Scripting Runtime にチェックが入っていない |
| 「Sub または Function が定義されていません」(ParseJson) | JsonConverter.bas をインポートしていない、またはモジュール名が JsonConverter ではない |
| 環境変数の APIキーが読めない | 環境変数を設定したあと Excel を開き直していない(Excel をすべて閉じてから開く) |
| 「サーバー名またはアドレスは解決されませんでした」 | ホスト名に https:// を付けている、または社内プロキシが必要。プロキシは http.SetProxy 2, "proxy.example.co.jp:8080" を Open の後に追加する |
| 実行時エラー 13「型が一致しません」 | Null の項目をそのまま使っている。NullToEmpty や IsObject で確かめる |
| 途中で Excel が固まったように見える | 429 で60秒待っている。ステータスバーの表示を確認する |
✅ 次の記事では…
PART 07 では Python(requests)でお題を実装する。共通のクライアント関数を作り、コメントや添付ファイルの取得、pandas での Excel 出力まで扱う。