Microsoft Flow: Insert JSON into a Sharepoint Table

After writing about how to insert data from a CSV file into a Sharepoint Table using Microsoft Flow of the Power Automate platform this post is about inserting JSON data into a Sharepoint table. Inserting JSON into a Sharepoint table is interesting, because besides CSV JSON is the most commonly used data exchange format and if you e.g. get data from a webservice it will most likely be in JSON.

The example which will be used here is to put the attendance data of a work week into a Sharepoint table so that it can later be used in a PowerBI Desktop report.

The JSON is very simple and is an array of an object with the following attributes:

• Date: Date of the attendance count
• Attendance: Amount of the attendance count  [ { "Date": "2020-12-01", "Attendance": 5 }, { "Date": "2020-12-02", "Attendance": 4 } ]

The Sharepoint list will have the same attributes and the ID has to be shown, too.

The flow to get the data from the JSON and storing it in the Sharepoint list looks as follows:‌

1. When a file is modified: This is the trigger, once a file is modified in a folder the flow will run
2. Get the file content: This action will get the content of the file which has been changed
3. Compose: In this action a string based on the file contents will be composed. This is necessary as the file content is in a Base64 format.
4. Parse JSON: In this action the JSON will be parsed into an array
5. Initialize variable: In this action an array is initialized and the parsed JSON is set as content
6. Get item: This action is getting all items of the Sharepoint list
7. Apply to each Delete items of list: This action is looping over all items in the Sharepoint list and delets them
8. Apply to each Insert data: This action is looping over all items of the JSON file (or better the array created out of it) and stores it in the Sharepoint list

The interesting action here are Compose, Parse JSON, Initialize Variable und Apply to each Insert data. I will get into details for these, if you want to learn about the other actions you can check it out here.

The compose action

In the compose action the content from the file will be converted into a JSON string. Since the content of the Parse JSON action is a base64 string, it needs to be converted:

base64toString(outputs('Get_file_content')?['body/$content'])

The parse JSON action

The parse JSON action is used to parse the JSON string into a JSON object.

The schema for parsing is the following:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "date": {
                "type": "string"
            },
            "attendance": {
                "type": "integer"
            }
        },
        "required": [
            "date",
            "attendance"
        ]
    }
}

The Initialize Variable action

In this action the parsed JSON object will be converted into a Flow array so that it can be used at a later stage of the process.

The Apply to each Insert data action

In the Apply to each Insert data action all data will be inserted into the Sharepoint list. First an Apply to each action will be started for the ATTENDANCE_LIST array which has been initialized earlier. And then the single items will be inserted in the list. Here I am converting the date to a string, which is not preferred but for my use case made sense.

The Date formula is

string(item()?['date'])

The Attendance forumale is

int(item()?['attendance'])

That's it folks. Hope this helps you to use Microsoft Flow of the Power Automate framework to get some awesome stuff done.