How to use an array variable of JSON objects to either update a sharepoint list or create an XLSX file.

[LocalsXOnly]

Literally started using power automate on Monday so I'm a super beginner. Here is my problem

Context:

Below is the use case of what i'm trying to accomplish overall. Each item below will be labeled with "(Completed" if I've already achieved that task. I just wanted to include everything to provide a high level view.

Use case:

1. Automated message is sent to my work email every day with an csv attachment (Complete)

2. I would like to use power automate to move the email into a shared mailbox using outlook rules (Complete)

3. Once the email has been received in the shared mailbox, I want to create a flow that takes the CSV file and does the following:

• Deletes first two columns (Completed)
• Renames the last three columns (Completed)

4. I then want to take the contents and either:

• Create an item in a SharePoint list that is updated every time the email is received OR
• Convert to XLSX format and replace the previous file (using the same name) every time the flow is triggered.

What i've completed:

I was able to do everything above except for step 4. The only way I was able to do steps 1-3  was to convert the contents of the csv file into JSON objects and then delete the first two properties and rename the remaining three.

Where i'm at:

I have an array of JSON objects with the following format (Achieved through Parse JSON schema):

[
{
"type": "array"
"properties":{
"App":{
"type": "string"
}
"Application O":{
"type": "string"
}
"BAPP":{
"type": "string"
}
}
}
]

What can I do with this array of JSON objects to essentially get them in a SharePoint list? I figure converting the JSON is probably way to difficult for my experience level. Any suggestions, I'm pretty much open to anything at this point. 

[DamoBird365]

Hi @LocalsXOnly 

You can easily create an xlsx from csv with an excel office script. I’ve got a video to share with you here https://youtu.be/9J6ThPWGaG0. If you would rather do json to excel, I have an example that takes the json body from a list or Dataverse table to excel here https://youtu.be/4g8Lh0gzEnc also using office scripts. (I like office scripts btw). 

Please let me know if this helps and how you get on?

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

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[LocalsXOnly]

Thanks @DamoBird365 !

Your video helped me out alot!

The only issue i'm having now is when I insert the rows from the new  xlsx file into my SharePoint site, that every field value has double quotes wrapped around it. Any suggestions for fixing this? I'm assuming a replace function is necessary but I'm afraid i'll mess up the formatting of your excel script. Do you think I should do this towards the beginning or near the end?

[DamoBird365 · Accepted solution]

Hi @LocalsXOnly 

Your csv must already be quote encapsulated? I.e “each of the field values”. Quote encapsulation is used when a field might have the separator in the string, like an address line I.e “flat b, 62 some street”.  If you remove the quotes, your single field becomes 2 fields, flat b and 62 some street. If that doesn’t apply to your data, you could apply replace(csv body, ‘“‘,’’) before passing the csv content to the script. If it does apply to you, then maybe a change to the excel script but I don’t know that answer right now.  I would suggest it’s done after the csv split expression but I’m not in front of a Pc to test.

Hopefully that gives you something to try.

Damien