Creating a Board with VBA

So I tried a small modifcation:

Sub MigrateToMonday()

Dim monday As New MSXML2.ServerXMLHTTP60
Dim response As Variant
Dim status As Variant
Dim query As String

myurl = “https://api.monday.com/v2”
mytoken = “*********”

query = " { ““mutation”” : “”@@@“”}"
query = Replace(query, “@@@”, “create_board(board_name: ““myboard””, board_kind: public){id}”)

Debug.Print query

With monday
    .Open "POST", myurl, False
    .SetRequestHeader "Content-Type", "application/json"
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Authorization", mytoken
    .Send query
    response = .responseText
    status = .status & " | " & .StatusText
End With

MsgBox query
MsgBox response
MsgBox status

End Sub

Now I’m getting a server timeout. I’m not sure it this is progress or not…really would appreciate any help folks could give me.

Hello there @MESGeorge,

If you use that query in your API Playground in monday, does it work?

I have a version that does work in the “try it yourself” area. I am able to query with the API and VBA, but the mutation is failing.

One attempt is getting me a timeout error, others give me a nebulous “Internal server error.”

Does anyone have a VBA sample they can share?

Hello @MESGeorge,

There is a mutation that is failing both in the API Playground and in VBA?

Which one?

Can you share the exact mutation and response you get?

How much time does the response take?

@Matias.Monday thanks for getting back to me.

I have tried a few different versions of the mutation, based on some feedback from monday.com support. Here are the 2 most recent they suggested I try:

{ “query”:“{mutation { create_board (board_name: “my board”, board_kind: public) { id }}”}}

{ “query”:“mutation { create_board (board_name: “my board”, board_kind: public) { id }}”}

When I take the second one listed above over to the “try it yourself” utility and paste the mutation portion of it there, it works just fine.

It takes about 30 seconds to fail. On different attempts I generally get one of three different errors"

1. Run time error -2147012894 Automation error
2. 500 Internal Server Error
3. The request timed out.

For what it’s worth, this query (with all the same VBA code otherwise), works just fine:

{“query” : “{boards(ids: xxxxxxxx){name}}”}

So I can retrieve data, just not add or update with the mutation.

I’m hoping you can help me out. Happy to ship you the current VBA code if that would help.

Hello again @MESGeorge,

Would you be able to send all of this information + the script to appsupport@monday.com so we can take a closer look from there?

Looking forward to hearing from you via email!

Cheers,
Matias

@MESGeorge Welcome to the land of doublequote-escaping hell in VBA. By the time your query hits the server, many of the required double-quotes in your query will have disappeared.

Try this in your VBA function:

query = “{”“query”“:”“mutation { create_board (board_name: "“my board "”, board_kind: public) { id } }”“}”

@jsararas Thanks for the suggestion. When I put your recommendation in, the only noticeable difference is some spaces. My most recent version produces this:

{“query”:“mutation { create_board (board_name: “my board”, board_kind: public) { id }}”}

compared to yours or this:

{“query”:“mutation { create_board (board_name: “my board”, board_kind: public) { id } }”}

I still gave yours a shot, and sadly am still getting the same 500 Internal server error. I did add a .setTimeouts to my code to avoid that error…but nothing has borne any fruit. The code takes pretty consistently 30-40 seconds and then fails.

It’s just odd that I can retrieve data with no issue, but sending the mutation is failing.

I’m using references to MS XML 6.0, I’ve tried 3.0 as well, and that fails.

Any further ideas?

First of all it looks like I neglected to use a code block, so when I copy and paste what I wrote, it ends up with left/right quotes which are wrong and will fail. Sorry about that I was in a rush. This code works for me:

query = "{""query"":""mutation { create_board (board_name:  \\""my board \\"", board_kind: public) { id } }""}"

But if the weirdo quotes were not the issue, try adding this reference: Microsoft WinHTTP Services, version 5.1. BTW what version of excel are you running?

By jove, you’ve got it. Thanks so much.

And now the REAL work begins, but getting over that hump is enormous. Can’t thank you enough!

Pleasure. I’m still surprised there isn’t more Excel/VBA action in the community. Excel Power Query + Monday = unlimited flexibility!