cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianmacmoore
Frequent Visitor

Forms responses buried in string

Hi, I'm using "Send an HTTP request to SharePoint" to retrieve details from an MS Forms to store in our SQL database. Form info and questions took a bit of doing but are now stored. The final piece is retrieve all the responses to the form, which ought to be straight forward also, however the response is this form:

 

 

 

 

{
    "statusCode": 200,
    "headers": {
    },
    "body": {
        "value": [
            {
                "submitDate": "2021-11-09T14:08:18.4473907Z",
                "responder": "john.doe@company.com",
                "answers": "[{\"answer1\":\"2021-11-15\",\"questionId\":\"r1e240ea107f946a4ab17c413f6e38e89\"},{\"answer1\":\"Technologies\",\"questionId\":\"r2e8f37ea949e443ab45892898c3617d3\"}]"
            }
        ]
    }
}

 

 

 

 

Notice that the answers key has the array of dictionaries inside a string. I've tried the obvious ways of parsing, but have gotten no where. This is my action:

ianmacmoore_0-1660960114765.png

I have a table in SQL dedicated just for responses, so if each line was one of these answers, the questionId can be used to join on another table and voila the form can be completely reconstructed. But I'm not seeing how to get there. Thanks!



1 ACCEPTED SOLUTION

Accepted Solutions
ianmacmoore
Frequent Visitor

I figured it out. Needed to use a combination of Select action and json(). Hopefully this will help someone.
image (1).png

 
 

 

View solution in original post

3 REPLIES 3
vayinapurapu1
Resolver II
Resolver II

It can be retrieved in couple of ways. 1 using select operator and other ways is to use Parse JSON. Below is the link that would give some idea

https://www.c-sharpcorner.com/article/query-values-from-json-outputs-in-powerautomate/

 

Cheers,

Vinay. 

ianmacmoore
Frequent Visitor

I got it to run using body() as suggested in the post, but it still chokes reading the answers key. Below shows attempt to split. "The provided value is of type 'Array'." Yeah, no kidding.

I'm abandoning this and just parsing everything SQL because this is garbage. But if you see something I'm doing wrong feel free to chime in.

ianmacmoore_1-1661185516853.png

ianmacmoore_2-1661185536096.png

 

ianmacmoore
Frequent Visitor

I figured it out. Needed to use a combination of Select action and json(). Hopefully this will help someone.
image (1).png

 
 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,858)