需要帮助使用Excel VBA获取curl Api请求代码才能正常工作 [英] Need help getting curl Api request code using excel vba to work properly
问题描述
我有一个API GET请求代码，用于从我的crm到excel中获取会话密钥。我试图重新利用它的目的是在 PUT请求中将信息发送到我的crm，但是某些翻译不正确，因为当我执行 .Open Put，webServiceURL，False时，我一直收到错误请求错误在代码中，但是当我执行 .Open Get，webServiceURL，False时，我得到了所有数据（只是什么都没有改变。如果有人愿意帮助这位业余代码爱好者，我将对您非常满意。）我包括了到目前为止完成的代码，它给了我一个错误的请求错误。
i have an API "GET" request code i use to get a session key from my crm into excel . i'm trying to re-purpose it to send information to my crm in a "PUT" request but something isnt translating right because i keep getting a "bad request" error when i do ".Open "Put", webServiceURL, False" in the code but i get all the data when i do " .Open "Get", webServiceURL, False"(just doesnt change anything. if anyone is willing to help this amateur code enthusiast i would be very great-full to you. below i included the code i've done so far, it gives me a "bad Request " error.
Dim webServiceURL As String
Dim actionType1 As String
Dim targetWord1 As String
Dim actionType2 As String
Dim targetWord2 As String
Dim PutEstJson As String
Dim APISettings As Worksheet
Dim res As Variant
Dim allres As Variant
Dim Token As String
Dim scriptControl As Object
Token = Worksheets("API Settings").Range("e3").Value
'get token data
website = "https://cloud.servicebridge.com/api/v1.1/Estimates/25014108?sessionKey="
webServiceURL = website & Token
actionType2 = "Accept"
targetWord2 = "application/json"
actionType1 = "Content-Type"
targetWord1 = "application/json"
PutEstJson = Worksheets("API Settings").Range("k7").Value
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "Put", webServiceURL, False
.setRequestHeader actionType2, targetWord2
.setRequestHeader actionType1, targetWord1
.Send PutEstJson
allres = .GetAllResponseHeaders
res = .responseText
APIKey = Split(res, "Data")
If .Status = 200 Then
'AVAILABLE INFORMATION
'_____________________
' Debug.Print .Status
'Debug.Print .responseText
' Debug.Print .GetAllResponseHeaders
'MsgBox .GetAllResponseHeaders
'paste token data
Worksheets("API Settings").Cells(4, 3).Value = Split(res, "data")
Worksheets("API Settings").Cells(4, 4).Value = allres
Else
MsgBox .Status & ": " & .StatusText
End If
End With
End Sub```
heres a copy of the json format data i send as PutEstJson.
```curl -X PUT --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{
Location: {
Id: 0,
Name: "string"
},
UpdateLocation: false,
GeoCoordinates: {
Latitude: 0,
Longitude: 0
},
UpdateGeoCoordinates: false,
Contact: {
Id: 0,
Name: "string"
},
UpdateContact: false,
ThirdPartyBillPayer: {
Id: 0,
Name: "string"
},
UpdateThirdPartyBillPayer: false,
MarketingCampaign: {
Id: 101002,
Name: "Yard Sign"
},
UpdateMarketingCampaign: false,
JobCategory: {
Id: 21412,
Name: "Finished Estimate: Hot Lead"
},
UpdateJobCategory: true,
SalesRepresentative: {
Id: 382891,
Name: "Marvin Lamar"
},
UpdateSalesRepresentative: true,
DefaultEquipment: {
Id: 0,
Name: "string"
},
UpdateDefaultEquipment: false,
Description: "Gutter/Guard Estimate",
UpdateDescription: true,
Status: "Assigned",
UpdateStatus: false,
Branch: {
Id: 0,
Name: "string"
},
UpdateBranch: false,
Team: {
Id: 188466,
Name: "Yerry"
},
UpdateTeam: false,
ConfirmationStatus: "None",
UpdateConfirmationStatus: false,
EstimateDate: "2020-04-15T04:46:02.867Z",
UpdateEstimateDate: false,
ScheduledTime: 0,
UpdateScheduledTime: false,
EstimatedDuration: 0,
UpdateEstimatedDuration: false,
ArrivalWindow: 0,
UpdateArrivalWindow: false,
EarliestArrival: 0,
UpdateEarliestArrival: false,
LatestDeparture: 0,
UpdateLatestDeparture: false,
Notes: "string",
UpdateNotes: false,
PrivateNotes: "string",
UpdatePrivateNotes: false,
InvoiceNotes: "string",
UpdateInvoiceNotes: false,
UpdateReminder: false,
ReminderType: "None",
ReminderValue: 0,
ReminderMessage: "string",
TaxCalculation: "TaxExcluded",
UpdateTaxCalculation: false,
CustomFields: [
{
Name: "S Walk Around Done",
Value: "",
Name: "S4 Est Rating",
Value: "",
Name: "S2 I went for",
Value: "",
Name: "S3 I Feel Est. Will Close In",
Value: "",
Name: "S7 1st Follow Up Date",
Value: "",
Name: "S7 2nd Follow Up Date",
Value: "",
Name: "S7 3rd Follow Up Date",
Value: "",
Name: "Follow-up Notes",
Value: "",
}
],
UpdateCustomFields: false,
SparseUpdate: "True" ,
Version: 10,
ExternalSystemId: "string",
UpdateExternalSystemId: false,
}
' 'https://cloud.servicebridge.com/api/v1.1/Estimates/25014108?sessionKey=6550e422e843f1d94e2e8c441e05d7197f0b871' ```
推荐答案
恐怕如果没有这些工具，这将很难帮助解决问题知道您要尝试调用的API方法的详细信息。该错误表明您要发布的JSON出现问题。对我来说，一些潜在的问题是：
I'm afraid this would be very hard to help troubleshoot without knowing the details of the API's method that you are trying to invoke. The error suggests there's something wrong with the JSON you are trying to post. A few potential issues that stand out to me are these:
-
似乎有一堆
[，]，{，}JSON中缺少字符。结构格式错误，因此JSON无效，这肯定会导致您的错误。
There seem to be a bunch of
[,],{,}characters missing in your JSON. The structure is malformed and therefore invalid JSON, which could definitely cause your error.
您的JSON密钥应该放在双引号中，以构成
有效的JSON。
Your JSON keys should probably be inside double quotes to constitute valid JSON.
`{
"Id": 0,
"Name": "string"
} ...`
我注意到在结尾处有一个流氓逗号您的JSON ...
这绝对不是有效的JSON。
I notice there is a rogue comma right at the end of your JSON... this is definitely not valid JSON.
我建议您将JSON放在可以格式化JSON的文本编辑器（例如 vscode ）。这应该可以帮助您更好地了解JSON的结构。我目前还不清楚JSON结构应该是什么，所以这是我的最佳猜测。我不得不猜测丢失的 [，] ， {，} 个字符。
I would suggest putting your JSON on a text editor that can format JSON (like vscode). That should help you see the structure of your JSON better. It's very unclear to me what the JSON structure should be, so this is my best guess at what it might look like. I had to guess at where the missing [,],{,} characters should be.
{
"Id": 0,
"Name": "string",
"UpdateLocation": false,
"GeoCoordinates": {
"Latitude": 0,
"Longitude": 0
},
"UpdateGeoCoordinates": false,
"Contact": {
"Id": 0,
"Name": "string"
},
"UpdateContact": false,
"ThirdPartyBillPayer": {
"Id": 0,
"Name": "string"
},
"UpdateThirdPartyBillPayer": false,
"MarketingCampaign": {
"Id": 101002,
"Name": "Yard Sign"
},
"UpdateMarketingCampaign": false,
"JobCategory": {
"Id": 21412,
"Name": "Finished Estimate: Hot Lead"
},
"UpdateJobCategory": true,
"SalesRepresentative": {
"Id": 382891,
"Name": "Marvin Lamar"
},
"UpdateSalesRepresentative": true,
"DefaultEquipment": {
"Id": 0,
"Name": "string"
},
"UpdateDefaultEquipment": false,
"Description": "Gutter/Guard Estimate",
"UpdateDescription": true,
"Status": "Assigned",
"UpdateStatus": false,
"Branch": {
"Id": 0,
"Name": "string"
},
"UpdateBranch": false,
"Team": {
"Id": 188466,
"Name": "Yerry"
},
"UpdateTeam": false,
"ConfirmationStatus": "None",
"UpdateConfirmationStatus": false,
"EstimateDate": "2020-04-15T04:46:02.867Z",
"UpdateEstimateDate": false,
"ScheduledTime": 0,
"UpdateScheduledTime": false,
"EstimatedDuration": 0,
"UpdateEstimatedDuration": false,
"ArrivalWindow": 0,
"UpdateArrivalWindow": false,
"EarliestArrival": 0,
"UpdateEarliestArrival": false,
"LatestDeparture": 0,
"UpdateLatestDeparture": false,
"Notes": "string",
"UpdateNotes": false,
"PrivateNotes": "string",
"UpdatePrivateNotes": false,
"InvoiceNotes": "string",
"UpdateInvoiceNotes": false,
"UpdateReminder": false,
"ReminderType": "None",
"ReminderValue": 0,
"ReminderMessage": "string",
"TaxCalculation": "TaxExcluded",
"UpdateTaxCalculation": false,
"CustomFields": [
{
"Name": "S Walk Around Done",
"Value": ""
},
{
"Name": "S4 Est Rating",
"Value": ""
},
{
"Name": "S2 I went for",
"Value": ""
},
{
"Name": "S3 I Feel Est. Will Close In",
"Value": ""
},
{
"Name": "S7 1st Follow Up Date",
"Value": ""
},
{
"Name": "S7 2nd Follow Up Date",
"Value": ""
},
{
"Name": "S7 3rd Follow Up Date",
"Value": ""
},
{
"Name": "Follow-up Notes",
"Value": "test1"
}
],
"UpdateCustomFields": true,
"SparseUpdate": "True",
"Version": 11,
"ExternalSystemId": "string",
"UpdateExternalSystemId": false
}
这篇关于需要帮助使用Excel VBA获取curl Api请求代码才能正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
