API v2 with VBA

monday Apps & Developers
1

Attempting to push a record (item) to a board from an Excel DB using VBA. The script adds an item as intended from the try-it-yourself utility. (I built the script in the code and captured it from the function [strJSON] just to make sure it was identical.) The token (strToken) was taken from my account page, and was used in the utility as well. So, I am certain that there are no errors in the script, but just for clarity, here it is:

mutation { create_item( board_id: XXXXXXX, group_id: “topics”, item_name: “My Company”, column_values: “{"text8":"Me","date4":{"date":"2019-11-10"}}”)}

I cannot get it to rpocess the added item using VBA. My code is:

Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")

http.Open "POST", strURL, False
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", strToken
http.Send strJSON

PushToMondayPM = http.responseText
strError = http.statusText
Set http = Nothing

The responseText is empty, and the statusText says “Bad Request”.

Also, FWIW, there is conflicting information if the APIv2 URL is api.monday.com/v2 or COMPANY.monday.com/v2.

Any help or sample code that you can provide is greatly appreciated. Thanks!

Don

2

Hey @HydroCompDon!

I don’t have a lot of VBA experience so I’ll let the community handle this :slight_smile: However, I did notice something: you’ve got some strings-inside-strings in your column_values parameter. Can you ensure you’re escaping all strings and double-escaping nested strings? This will ensure your query is parsed correctly.

Here’s an example of a properly escaped string:

Set queryString = "mutation { create_item( board_id: XXXXXXX, group_id: \“topics\”, item_name: \“My Company\”, column_values: \“{\\\“text8\\\”:\\\“Me\\\”,\\\“date4\\\”:{\\\“date\\\”:\\\“2019-11-10\\\”}}\”)}"
1 Like
3

Thanks for the reply. I tried to push your “escaped” string into the try-it-yourself utility and it failed (where my example worked). Is the “escaping” different when pushed through code as I have described?

4

Hey @HydroCompDon, yes exactly. The try-it-yourself utility is automatically parsing your query as a string so you don’t need to explicitly define it as such (and if you do, it will fail).

However, when you pass a query to our server using an API call, you need to explicitly define the query as a string. Hence the double-quotes on the outside of the mutation and then " (escaped quotes) inside.

Essentially, escaping gives the server a way to differentiate pairs of quotes. For example, if we send "hello, my name is "Dipro"" to the server, it will parse "hello, my name is " as one string, ignore Dipro and then parse "" as the other string. But, if we send "hello, my name is \"Dipro\"", it will be able to differentiate the inner quote from the outer quote.

Does that help clarify?

5

Hi, everybody, Happy New Year, it is this theme closed? Or it is still open?

6

Hi @HydroCompDon ,

have you managed to find the solutions for this? I’m trying to update a pulse from VBA as well and still failing.

I have tried your method with a query string like this:

“mutation { change_column_value( board_id: XXXXXX, item_id: XXXXXX, column_id: “status”, value: “{ \“index\”: 1 }”) {id}}”

I’m getting error:

{“errors”:[{“message”:“No query string was present”}],“account_id”:XXXXX}

Any ideas why?

Thanks,

Adam

1 Like