Formula Text to format JSON for webhook

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
I'm trying to make a JSON webhook sendinblue and for the emails to use I use a formula text that grabs all the emails related and formats them for the JSON like this

[{"email":"me@example.com"}, {"email":"you@example.com"}]

So the above is the result of the formula text and this is in my json like this.
{
  "to":[EmailToJSONRemoveBlanks]
}
(Theres more to that JSON but the rest isn't relevant for this post)

If I paste the raw result of the formula text into this it works fine but when I use this it says invalid json format. I'm not sure if the webhook is getting messed up with the array [] brackets and thinks its a field or what. Any thoughts?
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Where is it saying your JSON is invalid? Is the actual Webhook failing when you try and run it and the logs say that? 

I know this is only a screenshot - and so starting with my simplest observation first - do you have a comma after your [EmailToJSONRemoveBlanks]?

So
{
  "to":[EmailToJSONRemoveBlanks], //Rest of JSON }


Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
Hi and thanks for replying! I do have a comma and you are correct. The webhook error is in the log that says invalid json. If I paste the resulting text from the formula directly into the webhook it works fine. I've even tried generating the entire json mesage from a formula and that doesn't work either. 
Strange. the only thing that comes to mind then is maybe try swapping out " with ' in your JSON and the formula? Might be something strange with the actual raw formula value being passed in to the webhook that isn't the same when you copy it. So in your actual json change from "to" over to 'to' and in your formula change what I assume are \" to ' as well. Kind of grasping at straws since I don't know of a way to actually see what the webhook is trying to send

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
It did not like single quotes. I tried doing the entire json message with a static email and single quotes and it said invalid json so it requires double quotes it looks like
Best case might be to submit an actual ticket to QB then and see if they can pull the logs of what the webhook is actually trying to send. I personally can't think of anything else and it sounds like you've exhausted other options that come to mind.

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
Yeah I opened a ticket and they referred me to here but my last message was to ask if I could see the message being sent. We'll see what they say. Thanks for taking the time to look into it with me!
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
Yeah I saw a request to add what a webhook sends  when it throws an error on uservoice so gave it 3 votes as that would be very very helpful. I'll try the quotes and if it works I'll follow up. Thanks for the suggestion!
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
So this is all I could get from support. If anyone else wants to see JSON message as it is after the webhook fills in fields and sends it off go to uservoice and vote! I'm thinking if you want to make a formula field with JSON within it then it may be possible by adding these formats somehow but I'm not sure how to go about it

ActionWorkFlows" : [
{
"actionId" : 2,
"active" : true,
"cause" : {
"queryString" : "",
"triggerFields" : [],
"triggerOnAllFields" : true,
"typeId" : 1,
"typeName" : "OnRecordChange"
},
"description" : "",
"effect" : {
"format" : "JSON",
"headers" : {
"Content-Type" : "application/json",
"api-key" : "XXXXXXXXXX"
},
"isComplete" : true,
"template" : "{\r\n \"to\":[22],\r\n \"replyTo\": {\"email\": \"email@email.com\", \"name\": \"Chuck\"},\r\n \"templateId\":1,\r\n \"params\": {\r\n \"balance\": \"[23]\",\r\n\"last_payment_date\":\"[24]\"\r\n }\r\n}",
"typeId" : 3,
"typeName" : "DoWebHook",
"url" : "https://api.sendinblue.com/v3/smtp/email",
"verb" : "POST"
},
"label" : "Past Due Notice",
"lastRunDate" : "2019-04-09 15:08:43",
"lastRunDateMS" : 1554822523098,
"owner" : "email@email.com",
"when" : "a"