在 VBA 中发送 JSON POST 请求 [英] Sending JSON POST request in VBA

问题描述

我有以下 JSON POST 示例代码，我正在尝试将其转换为 VBA for Excel:

I have the following JSON POST sample code which I am trying to convert to VBA for Excel:

POST /services/shipper/orders HTTP/1.1
Content-Type: application/json
User-Agent: Mozilla 5.0
Host: qa.etowertech.com
X-WallTech-Date: Tue, 06 Jan 2018 21:20:27 GMT
Authorization: WallTech test5AdbzO5OEeOpvgAVXUFE0A:79db9e5OEeOpvgAVXUFWSD

这是我想出的代码:

With JsonHTTP
             .Open "POST", "http://qa.towertech.com/services/shipper/orders", False
             .setRequestHeader "RequestName", "application/json"
             .setRequestHeader "Accept", "application/json"
             .setRequestHeader "User-Agent", "Mozilla 5.0"
             .setRequestHeader "Host", "qa.etowertech.com"
             .setRequestHeader "X-WallTech-Date", "Tue, 06 Jan 2018 21:20:27 GMT"
             .setRequestHeader "Authorization", "WallTech test5AdbzO5OEeOpvgAVXUFE0A:79db9e5OEeOpvgAVXUFWSD"
             .send (body)
End With

我不确定 POST/services/shipper/orders HTTP/1.1 输入应该放在哪里

I wasn't sure where the POST /services/shipper/orders HTTP/1.1 input should go

我不断收到这样的回复:

I keep getting this response:

{
  "status": "Failed",
  "errors": [
    {
      "code": 100004,
      "message": "System internal error"
    }
  ],
  "data": null
}

我收到的当前回复:

{
  "status": "Failed",
  "errors": [
    {
      "code": 401,
      "message": "Authorization information is invalid."
    }
  ],
  "data": null
}

我只是想我错过了签名(请参阅下面的说明)但不确定如何呈现它以及它在请求中的确切位置?

I just figured I missed out on the Signature (see below instruction) but wasn't sure how to render it and where it goes exactly in the request?

X-WallTech-Date

EEE, dd MMM yyyy HH:mm:ss zzz

授权

WallTech <Access Token>:<Base64 Encoded HMAC SHA-1 Hash>

签名字符串

<HTTP Verb> + "\0x000A" + <X-WallTech-Date Header> + "\0x000A" + <Full URL>

推荐答案

解决方案如下:

url = "http://qa.towertech.com/services/shipper/orders"
token = "xxxxxx" ' API token goes here
auth = Base64_HMACSHA1("POST" & Chr(10) & timestamp & Chr(10) & url, key)

With JsonHTTP
             .Open "POST", url, False
             .setRequestHeader "RequestName", "application/json"
             .setRequestHeader "Accept", "application/json"
             .setRequestHeader "User-Agent", "Mozilla 5.0"
             .setRequestHeader "Host", "qa.etowertech.com"
             .setRequestHeader "X-WallTech-Date", timestamp
             .setRequestHeader "Authorization", "Walltech " & token & ":" & auth
             .send (body)
End With

问题是 Base64 函数中缺少密钥，因此 auth 字符串编码不正确

The problem was the secret key was missing from the Base64 function so the auth string was incorrectly encoded

希望这能帮助其他有同样经历的人！

Hope this helps anyone else having the same!

这篇关于在 VBA 中发送 JSON POST 请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！