如何使用Excel VBA获取curl Api请求代码? [英] How to get curl Api request code using Excel VBA?
问题描述
我有一个API"GET"，请求代码以将我的crm的会话密钥导入Excel.
I have an API "GET" request code to get a session key from my crm into Excel.
我正试图重新利用它，以通过"PUT"将信息发送到我的crm.请求.
I'm trying to re-purpose it to send information to my crm in a "PUT" request.
我收到一个错误请求"，打开放置"，webServiceURL，False 时出错.
I get a "bad request" error when I do Open "Put", webServiceURL, False.
我执行时会获取所有数据.打开"Get"，webServiceURL，False (只是什么都没有改变).
I get all the data when I do .Open "Get", webServiceURL, False (just doesn't change anything).
Sub UpdateEstimate()
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
我以PutEstJson发送的json格式数据的副本.
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屋！
