Excel for Mac Issue JSON POST

[SimonKalp]

Hi there,

I have a fairly complex worksheet that works across Excel Windows and Mac. I generate a JSON body as a string and POST it to my web service. Under Windows it works perfectly with no errors. It was working on the Mac version when i tested it but recently it has stopped working (most likely since the latest Excel updates). The problem appears to be the at the JSON body is corrupted as my web service sees no valid body and consequently fails trying to convert the object.

I have exported and re-imported all the classes and modules to no avail.

On the Mac i am on Mojave 10.14.6 and the Excel version is 16.29.1

Has anyone seen this issue or have any ideas on how to resolve?

Thanks

Simon

[zgrose]

What did your POST body look like before and what does it look like now so we can see this corruption.

[FredNorthcutt]

I just had a problem with POST on mac only works on windows. If there was an apostrophe in the text it would fail on the curl. I made a small change in my version to send you if you want to include it. I tried multiple things but this is what I came up with. Basically changes the case statement and added ' -> 39 -> \u0027 in the json_Encode of webhelpers. Case 34 ' " -> 34 -> \" json_Char = "\""" Case 39 ' Fix Fred Northcutt ' ' -> 39 -> \u0027 ' Fix Fred Northcutt json_Char = "\u0027" ' Fix Fred Northcutt Case 92

Attached is the updated WebHelpers.bas file. I had to upload it as a txt file so it will have to be renamed. Not sure if this will fix your problem but it sounds similar. Best of luck.

WebHelpers.bas.txt

[chentao1006]

I just had a problem with POST on mac only works on windows. If there was an apostrophe in the text it would fail on the curl. I made a small change in my version to send you if you want to include it. I tried multiple things but this is what I came up with. Basically changes the case statement and added ' -> 39 -> \u0027 in the json_Encode of webhelpers. Case 34 ' " -> 34 -> " json_Char = """" Case 39 ' Fix Fred Northcutt ' ' -> 39 -> \u0027 ' Fix Fred Northcutt json_Char = "\u0027" ' Fix Fred Northcutt Case 92

Attached is the updated WebHelpers.bas file. I had to upload it as a txt file so it will have to be renamed. Not sure if this will fix your problem but it sounds similar. Best of luck.

WebHelpers.bas.txt

Thank you. This solved my problem.

[skywalker-sr]

I've run into this exact same issue, which was showing up as a curl error about not being to resolve the host. As mentioned above, it is specific to Mac and shows up only when you have an apostrophe within your json payload. It is caused by the fact that on a Mac, the underlying curl tool is used to make the request, and uses single quotes for each value passed to the curl command in the shell. The presence of an apostrophe in your json payload will be interpreted by the shell as the end of the data value and everything after is interpreted as the url for your endpoint - which will of course be nonsense and cause a host error.

I'd implemented a fix by changing the code in WebClient.PrepareCurlRequest function to use double quotes for the json string and then escape the double quotes within the json itself. @chentao1006's fix is more elegant though so I'm rolling mine back and going with that. Thanks!

[chentao1006]

I've run into this exact same issue, which was showing up as a curl error about not being to resolve the host. As mentioned above, it is specific to Mac and shows up only when you have an apostrophe within your json payload. It is caused by the fact that on a Mac, the underlying curl tool is used to make the request, and uses single quotes for each value passed to the curl command in the shell. The presence of an apostrophe in your json payload will be interpreted by the shell as the end of the data value and everything after is interpreted as the url for your endpoint - which will of course be nonsense and cause a host error.

I'd implemented a fix by changing the code in WebClient.PrepareCurlRequest function to use double quotes for the json string and then escape the double quotes within the json itself. @chentao1006's fix is more elegant though so I'm rolling mine back and going with that. Thanks!

It's @FredNorthcutt 's fix. Thank him. :)

[skywalker-sr]

Kudos, good vibes and high fives to @FredNorthcutt!

[FredNorthcutt]

Thank you! Glad it helped! FredOn Oct 20, 2023, at 3:30 AM, skywalker-sr @.***> wrote: Kudos, good vibes and high fives to @FredNorthcutt!

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>