如何使用VBA调用Microsoft Graph API？

Question

问题

是否可以使用VBA代码调用Microsoft Graph API？

如果是，如何处理 O365 授权？我已经看到很多主题说在Microsoft Azure中创建一个应用程序以获取令牌，但我很惊讶我必须这样做以进行简单的本地使用。

我尝试了什么

在发现Microsoft Graph之后，我在Graph Explorer中尝试了这个API https://graph.microsoft.com/v1.0/planner/tasks

我能够在计划器中创建任务！

因此，在我看来，可以从直接在Outlook中执行的VBA代码调用此API。

我在Outlook中创建了此宏：

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)

我有一个授权错误

错误代码 401

12-03-2020更新：找到的解决方案，可以在调用图形资源管理器时获取图形API令牌分析URL（非常适合我）：

Function GetToken()

    Dim xml As New MSXML2.XMLHTTP60
    Dim doc As MSHTML.HTMLDocument
    Dim urltoken As String

'copy paste the URL you see when calling Microsoft Graph Explorer and add prompt + domain_hint parameters
    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

因此，使用 VBA 调用图形 API 是可能的:)

Answer 1

因此，您显示的代码仅部分正确。这是我发现实际有效的方法。（这是您提供的内容，因为我实际上找到了一个 Json 解析器来比 innerHTML 方法更好地处理数据，我还必须使用不同版本的 MSXML，因为您引用的那个不适合我。

Function GetToken()
    Dim xml As New MSXML2.XMLHTTP60
    Dim doc As MSHTML.HTMLDocument
    Dim urltoken As String

    'copy paste the URL you see when calling Microsoft Graph Explorer and add prompt + domain_hint parameters
    urltoken = "https://login.microsoftonline.com/{tenent id}/oauth2/v2.0/token"

    xml.Open "POST", urltoken, False

    xml.Send("client_id={clientid}&scope=https://graph.microsoft.com/.default&grant_type=client_credentials&client_secret=(cleint secret}")

    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