Forum Discussion

ChuckGrigsby's avatar
ChuckGrigsby
Qrew Cadet
7 years ago

Formula Text to format JSON for webhook

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?
Formulas and functions
NotificationsEmail

8 Replies

Sort By
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    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
  • ChuckGrigsby's avatar
    ChuckGrigsby
    Qrew Cadet
    7 years ago
    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. 
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    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
    • ChuckGrigsby's avatar
      ChuckGrigsby
      Qrew Cadet
      7 years ago
      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
    • ChayceDuncan2's avatar
      ChayceDuncan2
      Qrew Cadet
      7 years ago
      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
    • ChuckGrigsby's avatar
      ChuckGrigsby
      Qrew Cadet
      7 years ago
      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!
  • ChuckGrigsby's avatar
    ChuckGrigsby
    Qrew Cadet
    7 years ago
    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!
  • ChuckGrigsby's avatar
    ChuckGrigsby
    Qrew Cadet
    7 years ago
    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"