如何使用 VBA 调用 Microsoft Graph API? [英] How to call Microsoft Graph API using VBA?

问题描述

问题

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

Is it possible to call Microsoft Graph API using VBA code?

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

If yes, how to handle O365 authorization? I have seen plenty of topics saying to create an application in Microsoft Azure to get a token but I am surprised I must do that for a simple local use.

我的尝试

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

After discovering Microsoft Graph, I tried this API in Graph Explorer https://graph.microsoft.com/v1.0/planner/tasks

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

I was able to create a task in planner!

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

Consequently, in my mind, it was possible to call this API from VBA code executed directly in Outlook.

我在 Outlook 中创建了这个宏:

I created this macro in 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 更新:找到了在调用 Graph Explorer 时获取 Graph Api 令牌分析 URL 的解决方案(非常适合我):

UPDATE on 12-03-2020 : Solution found to get a Graph Api token analysing URL when calling Graph Explorer (works perfectly for me) :

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 :)

So using VBA for calling Graph API is possible :)

推荐答案

所以你展示的代码只是部分正确.这是我发现实际工作的内容.(这是您提供的内容，因为我实际上发现了一个 Json 解析器比innerHTML 方法更能处理数据，我还不得不使用不同版本的 MSXML，因为您引用的那个版本对我不起作用.)

So the code you show is only partially correct. Here is what I found to actually work. (This is with what you provided as I actually found a Json parser to work with the data better than the innerHTML methods, I also had to use a different version of MSXML since the one you reference wasnt working for me.)

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

这篇关于如何使用 VBA 调用 Microsoft Graph API?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！