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

问题描述

我有以下JSON POST示例代码，试图将其转换为Excel的VBA:

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

我一直收到以下答复:

{
  "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屋！