Error when uploading a csv to sharepoint list. 'Expected Array but got Object'

[s_raja]

Hello all,

Seeking advice on my dilemma, I am building a flow which will take a csv that is created daily, and upload it into a sharepoint list.

I have gotten quite far by using a combination of other posts on this forum, and the flow actually worked when I was using test data, however I am facing an error from the Parse to JSON function (the second last step).

The error happens between the 'parse JSON' and 'create item in sharepoint list' function blocks (see images below), and the exact error is listed here 

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

 

Resources:

Parse CSV - Get Rows from CSV file using Power Automate - YouTube

Parse CSV script - Blog-Resources/Get Rows from CSV file using Power Automate.js at main · cfernandes-muhimbi/Blog-Reso...

Automated upload flow - Solved: Re: Daily extract of content of csv to SharePoint ... - Power Platform Community (microsoft....

This flow is a combination of these two tutorials.

I am open to reworking the entire thing, but would prefer not to.

This is my current flow, I have not included any of the first few blocks, because I have to initialize 38 variables, and the trigger for this flow is, "when a new file is created in a sharepoint folder", but I have been doing this manually while testing.

  

the expression for 'Yesterday Date' is: 

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')

This calculates yesterdays date to pull files from my sharepoint 

the expression for 'Compose 2' is: 

trim(base64ToString(outputs('Get_file_content_using_path')?['body']['$content']))

This converts the file to strings, and cleans up and unnecessary spaces.

I have also included test data as well as my JSON schema, which is below:

{

    "body": [

        {

            "reportjobs": {

                "JobId": {

                    "type": "string"

                },

                "Ref": {

                    "type": "string"

                },

                "Type": {

                    "type": "string"

                },

                "Category": {

                    "type": "string"

                },

                "Contact": {

                    "type": "string"

                },

                "ContactId": {

                    "type": "string"

                },

                "ContactGoupId": {

                    "type": "string"

                },

                "Postcode": {

                    "type": "string"

                },

                "Location": {

                    "type": "string"

                },

                "JobContactLatitude": {

                    "type": "string"

                },

                "JobContactLongitude": {

                    "type": "string"

                },

                "Resource": {

                    "type": "string"

                },

                "Asset": {

                    "type": "string"

                },

                "PlannedStart": {

                    "type": "string"

                },

                "PlannedEnd": {

                    "type": "string"

                },

                "Duration": {

                    "type": "string"

                },

                "Status": {

                    "type": "string"

                },

                "StatusId": {

                    "type": "string"

                },

                "StatusDate": {

                    "type": "string"

                },

                "RealStart": {

                    "type": "string"

                },

                "RealEnd": {

                    "type": "string"

                },

                "RealDuration": {

                    "type": "string"

                },

                "DueDate": {

                    "type": "string"

                },

                "DrivingDur": {

                    "type": "string"

                },

                "Description": {

                    "type": "string"

                },

                "DrivingDist": {

                    "type": "string"

                },

                "PrintName": {

                    "type": "string"

                },

                "CustNote": {

                    "type": "string"

                },

                "ResNote": {

                    "type": "string"

                },

                "Actioned": {

                    "type": "string"

                },

                "JobPO": {

                    "type": "string"

                },

                "JobContractId": {

                    "type": "string"

                },

                "Created": {

                    "type": "string"

                },

                "Scheduled": {

                    "type": "string"

                },

                "CurrentFlag": {

                    "type": "string"

                },

                "Assistants": {

                    "type": "string"

                },

                "Result_Id": {

                    "type": "string"

                },

                "ContactParentId": {

                    "type": "string"

                },

                "StatusComment": {

                    "type": "string"

                }

            },

            "type": "array"

        }

    ]

}

Any help is appreciated, thanks.

[AnthonyAmador]

Hi @s_raja 

To create the item you can select the dynamic values of the Parse JSON action.

Hope this helps

Best regards. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[s_raja]

Hi @AnthonyAmador 

I have already added all the dynamic values into the create item function, my main problems lie with both the json schema and the output from the script being run.

But thanks for the attempt.

[s_raja · Accepted solution]

The JSON schema was the issue, but has been resolved now. Follow this tutorial:

https://www.youtube.com/watch?v=fI9S5781P3o