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

[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.

[annajhaveri]

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

[Ajinder31]

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.

[TomSkelton]

[TomSkelton]

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.

[annajhaveri]

@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. 

[TomSkelton]

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"

                        ]

                    }

                }

            }

        }

    }

}

[TomSkelton]

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.

[Ajinder31]

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

[annajhaveri]

@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?

[TomSkelton]

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?

[annajhaveri]

@TomSkelton are you looking to update SharePoint list based on the data from HTTP response?

[TomSkelton]

Hi Anna,

Yes I'd want the HTTP response to be used as the true source to update the sharepoint list with any changes, i.e. name change, job title change, email change, reports to change etc

[annajhaveri · Accepted solution]

@TomSkelton  understood, i would suggest you to open a separate post for this request to better work on it, as the current post that you created has been resolved, so please whichever response helped you please mark it as solution.