How to get curl Api request code using Excel VBA?

Question

I have an API "GET" request code 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.

I get a "bad request" error when I do Open "Put", 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

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'

You have PutRQST = part1 & part2 & part3 & part4 before you populate those variables. — Tim Williams
– Tim Williams, Commented Apr 24, 2020 at 1:00
Hey thanks,i thought i had fixed all the little things like this but i gues not. anyway it still gave me a bad request error after i fixed that. do you see anything else wrong or i could just something i could do better? — Yerry Balenzuela
– Yerry Balenzuela, Commented Apr 24, 2020 at 2:09

ArcherBird · Accepted Answer · 2020-04-24 13:59:59Z

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:

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.
Your JSON keys should probably be inside double quotes to constitute valid JSON.
```
`{
   "Id": 0,
   "Name": "string"
} ...`
```
I notice there is a rogue comma right at the end of your JSON... this is definitely not valid 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
}

Hey Thank you soo much... with your advice i was able to make it work... the json was a bit picky like you said and what i got to work in curl did need a little editing .

Collectives™ on Stack Overflow

How to get curl Api request code using Excel VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related