How to update subitems using API calls in VBA

Platform discussions
, , , ,
1

Hi

I’m trying to create subitems via the monday api in VBA but coming up with some issues with formatting the JSON payload for the api request. I have no problems retrieving items so something is working, but making changes is causing my some headaches. I should also say, I can do this perfectly in python but my workplace won’t allow me to have that, or spend any money on other codeless environments such as Zapier, so i’m stuck with Excel and VBA.

I use an externally sourced JsonConverter that allegedly sorts out any VBA issues with escaping quotes.

My code:

Sub PostMondaySubitems()
Dim http As Object
Dim url As String
Dim apiKey As String
Dim mutation As String
Dim colVals1 As String, colValsJson As String
Dim body As String

' --- Column values as JSON strings ---
colVals1 = "{""color_mkw58znm"":""AR"",""numeric_mkrz1ykt"":""12"",""numeric_mkrz1crk"":""9""}"

' --- Escape quotes for GraphQL ---
colValsJson = ConvertToJson(colVals1)

' --- Build mutation string ---
mutation = "mutation { " & _
           "s1: create_subitem(parent_item_id: ""9945618885"", item_name: ""Net CV"", column_values: " & colValsJson & ") { id } " & _
           "}"
    
' --- Wrap mutation in JSON body ---
body = "{""query"":""" & mutation & """}"
body = ConvertToJson(body)

' --- Debug ---
Debug.Print body

' --- Send request ---
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", Constants.API_URL, False
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", Constants.API_KEY
http.send body
Debug.Print ("status: " & http.Status & ", response: " & http.responseText)

'Debug.Print body          ' Check final JSON
Debug.Print ("status: " & http.Status & ", response: " & http.responseText)

End Sub

The output:

“{“query”:“mutation { s1: create_subitem(parent_item_id: “9945618885”, item_name: “Net CV”, column_values: “{\“color_mkw58znm\”:\“AR\”,\“numeric_mkrz1ykt\”:\“12\”,\“numeric_mkrz1crk\”:\“9\”}”) { id } }”}”
status: 400, response: {“errors”:[{“message”:“Invalid GraphQL request”,“extensions”:{“code”:“INVALID_GRAPHQL_REQUEST”,“details”:“Expected object, received string”}}],“extensions”:{“request_id”:“a0adc561-c784-92d2-b838-a6a016fd9aa3”}}

I have tries many combinations of quotes and slashes, double quotes etc but never arrived at a correctly formatted request.

Many thanks in advance, this is driving me nuts!