Skip to main content
Question

Formula Text to format JSON for webhook

  • April 9, 2019
  • 0 replies
  • 124 views
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?
    This topic has been closed for replies.

    • ChayceDuncan2
    • Quickbase Alumni
    • April 9, 2019
    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
    • Author
    • Quickbase Alumni
    • April 9, 2019
    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
    • Quickbase Alumni
    • April 9, 2019
    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
    • Author
    • Quickbase Alumni
    • April 9, 2019
    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
    • Author
    • Quickbase Alumni
    • April 12, 2019
    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"
    Terms & ConditionsAccessibility statement