Excel+VBA+JSON+Put Requests

Question

I don't fully understand how to properly package the category IDs I need to send to the API.

Sub testing()
  Dim sc As Object
  Set sc = CreateObject("ScriptControl")
  sc.Language = "JScript"

  Dim strURL As String: strURL = "https://api-sandbox.site.com/v1/customers/111111?api_key=xxxxxxxxxxxxx"

  Dim strRequest
  Dim XMLhttp: Set XMLhttp = CreateObject("msxml2.xmlhttp")
  Dim response As String

  XMLhttp.Open "PUT", strURL, False
  XMLhttp.setrequestheader "Content-Type", "application/json;charset=UTF-8"
  XMLhttp.send strRequest
  response = XMLhttp.responseText

End Sub

Objective: Take a set of category IDs and push them to a specific client. My current focus is just to gain understanding on how to do this with a single case. As always any information is GREATLY appreciated.

https://api-sandbox.site.com/v1/customers/clientID?api_key=xxxxxxxxxxxxx

The API uses JSON to TX/RX. The format (from my understanding) for the categories would need to be:

{"categoryIDs" : [ 1, 2096, 2008, 2009 ]}

An example session from the API:

PUT /v1/customers/2938293/locations/39483?api_key=xxxxxxxxxxxxx HTTP/1.1
Host: api.site.com
Content-Type: application/json;charset=UTF-8

{
  "zip": "92886",
  "phone": "7147147147",
  "countryCode": "US",
  "state": "CA",
  "locationName": "Backpack Brigade",
  "isPhoneTracked": false,
  "specialOfferIsDeal": false, 
  "specialOffer": "Check out our new Summer Backpacks!",
  "folderId": "0",
  "city": "Yorba Linda",
  "id": "123",
  "customerId": "140149",
  "categoryIds": [
    90,
    833
  ],
  "suppressAddress": false,
  "address": "4345 Bastanchury Road",
  "websiteUrl": "http://backpackbrigade.com/",
  "hours":"2:12:00:PM:5:00:PM,3:12:00:PM:5:00:PM,4:12:00:PM:5:00:PM,6:12:00:PM:5:00:PM,7:12:00:PM:5:00:PM",
  "additionalHoursText": "Sunday by Appointment",
  "description": "Best Backpack Store in Southern California!",
  "twitterHandle": "backpackbrigade",
  "logo": { 
    "url": "http://cms.site.com/cms/328812732-backpack.png",
    "description": "Picture of a backpack" 
  },
  "displayLatitude": 33.8991997,
  "displayLongitude": -117.8437043,
  "emails":["admin@backpackbrigade.com"]
}

I can't say with certainty, but the code I provided is for handling the response mostly and it would appear that I'm not packaging anything to send with the PUT request.

Do the category IDs I'm sending need to be packaged as an object or is a string ok?

Answer 1

For anyone looking to use JSON and EXCEL I highly recommend this website as a resource: http://ramblings.mcpher.com/Home/excelquirks/json

The modules that Bruce Mcpherson has put together here makes PUT requests, sorting and saving data from JSON API calls simple and straight forward.

I hope this helps anyone that is in the same situation I was!