vba - 如何使用 VBA 调用 Microsoft Graph API？

Question

问题

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

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

我试过的

发现 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)

我有一个授权错误

error code 401

2020 年 3 月 12 日更新:
找到了在调用 Graph Explorer 时获取 Graph Api token 分析 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 来调用 Graph 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