如何使用VBA调用Microsoft图形API?

Question

问题

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

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

我尝试了什么

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