How do I add an SMS from Twilio into Azure Table Storage using Logic App

Question

I am able to receive a text message into a Logic App, via a Twilio Web Hook. as set up by following these instructions

Regarding my question here Now I need to add that text into an Azure Table. I have added a Parse JSON action What should I put in the Content and Schema?

I found that if I click inside the Content Box I am prompted to pick from a tag. I guess Body is the one.

[Update] Now I am trying to insert the Entity Directly but I cant work out how to do this in the designer. How do I expand the Entity text box so as to put the JSON in?

[Update2]

I found I could expand the Entity text box by typing in it. But how do I insert the MessageText?

[Update3]

Here is how I did the Insert Entity

I was able to get a record into the Azure storage table. But where is the body of my text message?

I can see that there was a problem running

I think I need to extract a body from the Body but I don't know how

[Update4]

Show Raw Inputs displays

{
    "host": {
        "connection": {
            "name": "/subscriptions/somenumbers/resourceGroups/mydomain.com.au/providers/Microsoft.Web/connections/azuretables"
        }
    },
    "method": "post",
    "path": "/Tables/TextMessages/entities",
    "body": {
        "Message": {
            "$content-type": "application/x-www-form-urlencoded",
            "$content": "VG9Db3VudHJ5PUFVJlRvU3RhdGU9JlNtc01lc3NhZ2VTaWQ9U000MTU4YzU1YmVkNDNjZDFiNWZmMTNiODZiNjIyNzkyNSZOdW1NZWRpYT0wJlRvQ2l0eT0mRnJvbVppcD0mU21zU2lkPVNNNDE1OGM1NWJlZDQzY2QxYjVmZjEzYjg2YjYyMjc5MjUmRnJvbVN0YXRlPSZTbXNTdGF0dXM9cmVjZWl2ZWQmRnJvbUNpdHk9JkJvZHk9VGFrZSsyJkZyb21Db3VudHJ5PUFVJlRvPSUyQjYxNDQ3NDA1NjEzJlRvWmlwPSZOdW1TZWdtZW50cz0xJk1lc3NhZ2VTaWQ9U000MTU4YzU1YmVkNDNjZDFiNWZmMTNiODZiNjIyNzkyNSZBY2NvdW50U2lkPUFDN2FhZTMxM2UwZmRlOGVkZjE5YzhjMGY5NjQ1MTgwNjYmRnJvbT0lMkI2MTQxOTU3NTQxNSZBcGlWZXJzaW9uPTIwMTAtMDQtMDE=",
            "$formdata": [
                {
                    "key": "ToCountry",
                    "value": "AU"
                },
                {
                    "key": "ToState",
                    "value": ""
                },
                {
                    "key": "SmsMessageSid",
                    "value": "SM4158c55bed43cd1b5ff13b86b6227925"
                },
                {
                    "key": "NumMedia",
                    "value": "0"
                },
                {
                    "key": "ToCity",
                    "value": ""
                },
                {
                    "key": "FromZip",
                    "value": ""
                },
                {
                    "key": "SmsSid",
                    "value": "SM4158c55bed43cd1b5ff13b86b6227925"
                },
                {
                    "key": "FromState",
                    "value": ""
                },
                {
                    "key": "SmsStatus",
                    "value": "received"
                },
                {
                    "key": "FromCity",
                    "value": ""
                },
                {
                    "key": "Body",
                    "value": "Take 2"
                },
                {
                    "key": "FromCountry",
                    "value": "AU"
                },
                {
                    "key": "To",
                    "value": "+61447405613"
                },
                {
                    "key": "ToZip",
                    "value": ""
                },
                {
                    "key": "NumSegments",
                    "value": "1"
                },
                {
                    "key": "MessageSid",
                    "value": "SM4158c55bed43cd1b5ff13b86b6227925"
                },
                {
                    "key": "AccountSid",
                    "value": "AC7aae313e0fde8edf19c8c0f964518066"
                },
                {
                    "key": "From",
                    "value": "+61419575415"
                },
                {
                    "key": "ApiVersion",
                    "value": "2010-04-01"
                }
            ]
        },
        "PartitionKey": "Twilio",
        "RowKey": "1d5a06ca-9dbd-4ba9-b514-77904710ffc3"
    }
}

[Update5]

I think I need to know how to get the body property out of @triggerBody()

[Update6]

I tried

    "body": {
        "Message": "@triggerBody()['Body']",
        "PartitionKey": "Twilio",
        "RowKey": "@guid()"
    }

but this causes an error

ie

InvalidTemplate. Unable to process template language expressions in action 'Insert_Entity' inputs at line '1' and column '1450': 'The template language expression 'triggerBody()['Body']' cannot be evaluated because property 'Body' doesn't exist. Property selection is not supported on content of type 'application/x-www-form-urlencoded'. Please see https://aka.ms/logicexpressions for usage details.'.

[Update7]

I am changing the HTTPRequest body to use JSON Schema

"$content-type": "application/json",

instead of

 "$content-type": "application/x-www-form-urlencoded",

[Later note] I did this because Azure displayed a warning message "Remember to include a Content-Type header set to application/json in your request" However it seems to work either way.

[Update8]

I was able to receive the message in Azure when I used

"Message": "@triggerFormDataValue('Body')"

You could check the `INPUTS` in the bad request screenshot by click the `Show raw inputs`. — Tom Sun - MSFT, Nov 30 '17 at 16:20
See latest update. I think I need to extract the body from "@triggerBody()" — Kirsten, Nov 30 '17 at 16:33
Investigating https://social.msdn.microsoft.com/Forums/en-US/471d7be9-58bb-47dc-bace-17269c524503/what-does-triggerbody-return?forum=azurelogicapps the value I need is stored in the body property inside @triggerBody() — Kirsten, Nov 30 '17 at 16:39
Investigating http://devslice.net/2016/03/parsing-json-data-within-logic-apps/ — Kirsten, Nov 30 '17 at 16:44

Tom Sun - MSFT · Accepted Answer · 2017-12-01T02:41:47.820

1

According to the commment, I update the answer.

If we want to insert an message to Azure table, we could contruct the table entity by inputing the json format directly. More detail please refer to the demo code and screenshot.

Note :PartionKey and Rowkey is requred for Azure table entity.

{
 "Message": "@triggerFormDataValue('Body')",
 "PartitionKey": "Twilio",
 "RowKey": "@guid()"
}

edited Dec 01 '17 at 02:41

answered Nov 29 '17 at 10:39

Tom Sun - MSFT

24,161
3
30
47

I updated my question to reflect my attempt to follow your answer. – Kirsten Nov 30 '17 at 01:01
I have updated the answer, I find that we no need to use Parse Json action, we could construct the enity directly in the insert entity action. – Tom Sun - MSFT Nov 30 '17 at 01:57
I updated my question to show my attempt to use the insert entity action. I am having trouble reproducing your screen shot. I want to construct json in the insert entity action directly – Kirsten Nov 30 '17 at 15:28
Where does the "Message Text" come from in your insert entity action directly answer? – Kirsten Nov 30 '17 at 15:38
The "Message Text" in my case, just for test. In your case you could get it from the body in your mentioned screen. – Tom Sun - MSFT Nov 30 '17 at 15:40
Thankyou, I put [Update3] in the question to show my progress. I can't find the words of my test text in the Azure table. – Kirsten Nov 30 '17 at 15:59
Thank you so much for your help. I finally got it working as shown in Update8. Can you separate your two answers so that I can mark the one that just uses the Insert Entity action as correct ( with my Update8 comment) – Kirsten Nov 30 '17 at 22:24
It would be helpful to see the code of the Insert Entity Action in the answer too. – Kirsten Dec 01 '17 at 02:36

eimajtrebor · Answer 2 · 2017-11-30T09:50:25.477

You can also do this using just Logic App actions.

You can use the Parse JSON data operation to parse the JSON coming from your Twilio response. The "Body" of your Twilio response becomes the Content for the Parse JSON action as you mention in your question.

To generate a schema for the Schema field you can click on the "Use sample payload to generate schema" link. Paste in the JSON payload you are expecting back in the Twilio response, for example:

    {
        "message: "A message from twilio"
    }

You can then use the results of the Parse JSON action to populate Azure Table Storage.

Message can be populated from the Parse JSON action; Partition Key can be hard-coded; RowKey can be calculated based on an Expression - @guid().

Thank you. I updated my question to show the Parse JSON data operation, but am unsure how to populated it. — Kirsten, Nov 29 '17 at 20:54

score 0 · Answer 3 · answered Dec 01 '17 at 14:14

0

one thing to note here is the content type of a Twilio webhook is not application/json, so you can't use parse JSON. It is application/x-www-url-formencoded. You can still parse it out, but needs to be with an expression. If you open the expression editor, getting the body of the text would be something like:

triggerFormDataValue('Body')

The FROM phone number would be

triggerFormDataValue('From')

You can see in the outputs of the trigger the different form pieces.

answered Dec 01 '17 at 14:14

jeffhollan

3,139
15
18

I changed the content type from `application/x-www-url-formencoded` to `application/json` in the HTTP Request, both worked. As per Update8 I was able to use 'Message": "@triggerFormDataValue('Body')"' In the Insert Entity action. – Kirsten Dec 01 '17 at 20:00
The reason I changed the content-type was a little popup warning showing in Azure. "Remember to include a Content-Type header set to application/json in your request" I put a screen shot in the Update7 section of my question. – Kirsten Dec 01 '17 at 21:12

How do I add an SMS from Twilio into Azure Table Storage using Logic App

3 Answers3

Linked