json - 使用 Excel VBA 访问 Zendesk Web API(并在其中进行身份验证)

Question

我正在尝试将 Web API 与 Excel VBA 一起使用。
在 API 说明中写到:
使用 curl

curl https://{subdomain}.zendesk.com/api/v2/users/create_or_update.json \
  -d '{"user": {"name": "Roger Wilco", "email": "roge@example.org"}}' \
  -H "Content-Type: application/json" -X POST \
  -v -u {email_address}:{password}

链接到 API 本身(创建或更新用户) https://developer.zendesk.com/rest_api/docs/support/users#create-or-update-user
这是我的代码:

Public Function PostJsonRequest() As String
    Dim strURL As String
    Dim strParse() As String
    Dim jsonStr As String
    Dim hreq As Object
    Dim tixScript As Object
    
    On Error GoTo Er   
 
    Set hreq = CreateObject("MSXML2.XMLHTTP")        
    strURL = "https://subdomain.zendesk.com/api/v2/users/create_or_update"
    hreq.Open "POST", strURL, 0, "username/token", "token"
    
    hreq.setRequestHeader "User-Agent", "Chrome"
    hreq.setRequestHeader "Content-Type", "application/json"
    hreq.setRequestHeader "Accept", "application/json"
    hreq.setRequestHeader "-v -u {MyEmail}:{MyPassword}"

    jsonStr = "-d '{""user"": {""name"": ""Roger Wilco"", ""email"": ""roge@example.org""}}'"
    hreq.Send jsonStr

    MsgBox hreq.responseText
    Exit Function
    
    Er:
    MsgBox "Error - " & Err.Number & " - " & Err.Description    
End Function

在电子邮件和密码行中，我收到此错误:

Error - 450 - Wrong number of arguments or invalid property assignment

Answer 1

这无效 hreq.setRequestHeader "-v -u {MyEmail}:{MyPassword}"改为尝试基本身份验证

hreq.setRequestHeader "Authorization", "Basic dXNlcjpwYXNzd29yZA=="

在哪里 dXNlcjpwYXNzd29yZA==是 base64 编码的 {MyEmail}:{MyPassword}字符串。
例如:

Dim username As String
username = "user123"

Dim password As String
password = "abc123"

hreq.setRequestHeader "Authorization", "Basic " & EncodeBase64(username & ":" & password)

base64 编码函数的工作方式如下:

Private Function EncodeBase64(ByVal plainText As String) As String
    Dim bytes() As Byte
    Dim objXML As Object 'MSXML2.DOMDocument60
    Dim objNode As Object 'MSXML2.IXMLDOMNode
    
    bytes = StrConv(plainText, vbFromUnicode)
   
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    EncodeBase64 = objNode.Text
    
    Set objNode = Nothing
    Set objXML = Nothing
End Function

还要确保只发送没有 -d '…' 的 JSON 部分:

jsonStr = "{""user"": {""name"": ""Roger Wilco"", ""email"": ""roge@example.org""}}"

最后是一个比问题更美观的东西:

hreq.setRequestHeader "User-Agent", "Chrome"

将您的用户代理字符串设置为伪造一个真正的用户代理，对于当前的 chrome，它看起来像:

Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36

例如，伪造用户代理是为了让网站认为您正在使用 Chrome 浏览器。对于 API，我猜这不是必需的，因此您可以将其设置为通用的内容，例如:

hreq.setRequestHeader "User-Agent", "MyVBAProject Version x.y.z Windows 10 using MSXML2.XMLHTTP"

清楚地向网站显示您是哪种类型的应用程序。
至少不要设置为 "Chrome"因为这只是令人困惑，因为 Chrome 永远不会使用该用户代理。