如何使用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中尝试了此API https://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
error code 401
2020年12月3日更新:找到的解决方案在调用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屋!