Answered

Parse JSON from a HTTP Web API to create/update Item in Sharepoint List

(0) Share

Report

Posted on by TomSkelton

I'm trying to build a flow that will pull employee data out of our HR system from an HTTP call and then use this data to either create new items or update an existing item in a SharePoint list.

My JSON schema is:

{

"type": "object",

"properties": {

"Employee Id": {

"type": "string"

"First Name": {

"type": "string"

"Last Name": {

"type": "string"

"Work Email": {

"type": "string"

"Location": {

"type": "string"

"Department": {

"type": "string"

"Employment Type": {

"type": "string"

"Job Role": {

"type": "string"

"Start Date": {

"type": "string"

"Reports To": {

"type": "string"

}

I'm getting the flowing error when Applying to each to create an item:

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')' is of type 'Object'. The result must be a valid array.

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

annajhaveri 8,531 Most Valuable Professional on at

Like (0)

Report

@TomSkelton does your json schema has one object or array of objects? Can you please share the example json output?

Was this reply helpful? Yes No
Ajinder31 530 on at

Like (0)

Report

Hi @TomSkelton , The JSON schema posted seems to an object instead of an Array. Apply to each action expects an array collections to iterate over.

It seems the HTTP call is only return an employee objects not list of employees. Can you confirm what was the expected out from the HTTP call? single object or array of employees.

--------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
TomSkelton 32 on at

Like (0)

Report

Was this reply helpful? Yes No
TomSkelton 32 on at

Like (0)

Report

The HTTP call is linked to a query rather than the main data set - would it be better to set this up as pulling in this manner rather than a query?

The output in the photo above does give an array back of all of the employees.

Was this reply helpful? Yes No
annajhaveri 8,531 Most Valuable Professional on at

Like (0)

Report

@TomSkelton use the output of the json in parse json -> Generate from sample. I think the apply to each needs to use the Result property from the parse json outputs.

Was this reply helpful? Yes No
TomSkelton 32 on at

Like (0)

Report

so this:

{
    "type": "object",
    "properties": {
        "body": {
            "type": "object",
            "properties": {
                "isError": {
                    "type": "boolean"
                },
                "Status": {
                    "type": "integer"
                },
                "Message": {
                    "type": "string"
                },
                "Result": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "Employee Id": {
                                "type": "string"
                            },
                            "First Name": {
                                "type": "string"
                            },
                            "Last Name": {
                                "type": "string"
                            },
                            "Work Email": {
                                "type": "string"
                            },
                            "Location": {
                                "type": "string"
                            },
                            "Department": {
                                "type": "string"
                            },
                            "Employment Type": {
                                "type": "string"
                            },
                            "Job Role": {
                                "type": "string"
                            },
                            "Start Date": {
                                "type": "string"
                            },
                            "Reports To": {
                                "type": "string"
                            }
                        },
                        "required": [
                            "Employee Id",
                            "First Name",
                            "Last Name",
                            "Work Email",
                            "Location",
                            "Department",
                            "Employment Type",
                            "Job Role",
                            "Start Date",
                            "Reports To"
                        ]
                    }
                }
            }
        }
    }
}

Was this reply helpful? Yes No
TomSkelton 32 on at

Like (0)

Report

Now getting this error:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['Result']' is of type 'Null'. The result must be a valid array.

Was this reply helpful? Yes No
Ajinder31 530 on at

Like (0)

Report

Yes @TomSkelton , Above JSON schema seems correct. Just configure the Apply_to_each action with the Result field to iterate over list of employees.

Was this reply helpful? Yes No
annajhaveri 8,531 Most Valuable Professional on at

Like (0)

Report

@TomSkelton it looks correct now, though as you are getting error can you please try using body('Parse_JSON')?['Result'] in apply to each? Also can you please share outputs part screenshot of Parse JSON from flow run?

Was this reply helpful? Yes No
TomSkelton 32 on at

Like (0)

Report

that's great - that seems to be doing the trick and populating the list.

I've set this up on a button run just for testing, but i'd like this to move to a scheduled flow.

What would be the steps to check the list for the employee ID field and only update columns if they are blank - i.e. no work email address, or if a job title changes.

And if the employee ID doesn't exist then create new item?

Was this reply helpful? Yes No