如何用VBA调用Microsoft Graph API？O365授权方案解析

结论先行 #

完全可以用VBA调用Microsoft Graph API！授权确实是绕不开的关键环节，我最开始也因为没处理好授权遇到了401错误，不过后来找到了适合本地简单使用的解决方案。

我的尝试与遇到的问题 #

接触到Microsoft Graph后，我先在Graph Explorer里测试了https://graph.microsoft.com/v1.0/planner/tasks接口，成功创建了Planner任务，于是想着直接在Outlook里用VBA实现这个功能。我写了下面这段宏：

Sub TaskPlannerCreation()
    Dim PlannerService As New MSXML2.XMLHTTP60
    Dim sData As Variant
    sData = " { ""planId"": ""K9Zv2QHm1U-GSAhd-PTGZfdFeOn"",""bucketId"": ""b6NVNiEIQkGZeBBzn7kWqJvAGvvs"",""title"": ""Outlook task"" } "
    With PlannerService
        .Open "POST", "https://graph.microsoft.com/v1.0/planner/tasks", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "User-Agent", "xx"
        .Send (sData)
    End With
End Sub

结果运行后直接收到了401 Unauthorized错误，显然是请求里缺少有效的身份验证令牌。

解决方案：获取Graph API访问令牌 #

2020年3月12日更新：我研究了Graph Explorer调用时的请求URL，找到了一个适合本地简单场景的令牌获取方法，亲测有效：

Function GetToken()
    Dim xml As New MSXML2.XMLHTTP60
    Dim doc As MSHTML.HTMLDocument
    Dim urltoken As String
    '复制Graph Explorer调用时的URL，额外添加prompt和domain_hint参数
    urltoken = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize?response_mode=form_post&nonce=graph_explorer&mkt=fr-FR&client_id={clientid}&response_type=token&scope=openid profile User.ReadWrite User.ReadBasic.All Sites.ReadWrite.All Contacts.ReadWrite People.Read Notes.ReadWrite.All Tasks.ReadWrite Mail.ReadWrite Files.ReadWrite.All Calendars.ReadWrite&prompt=none&domain_hint=organizations"
    xml.Open "GET", urltoken, False
    xml.Send
    If xml.readyState = 4 And xml.Status = 200 Then
        Set doc = New MSHTML.HTMLDocument
        doc.Body.innerHTML = xml.responseText
        GetToken = doc.getElementsByName("access_token")(0).Value
        sSuccess = True
    Else
        MsgBox "Error" & vbNewLine & "Ready state: " & xml.readyState & _
        vbNewLine & "HTTP request status: " & xml.Status
        sSuccess = False
    End If
    Set xml = Nothing
End Function

拿到令牌后，只需要在原来的宏里添加一行授权请求头即可：

.setRequestHeader "Authorization", "Bearer " & GetToken()

这样就能顺利通过授权，调用Graph API创建Planner任务了。

内容的提问来源于stack exchange，提问作者Romain