Convert SharePoint List Excel Attachments to JSON in Power Automate
Excel XLSX might be the best format for displaying data to another (human) viewer, but it’s certainly not the best format for processing data in programmatic workflows.
Open Office XML files are large and complex, often carrying vector graphs, bulky multimedia objects, and layer upon layer of styling instructions. Plain-text data types like JSON are inherently light-weight, simpler to use, and widely supported by apps and enterprise app integration platforms (like Power Automate) alike.
Convert Excel SharePoint List Uploads to JSON Automatically
If we’re asking users to upload Excel workbooks to a SharePoint list, we can instantly make that data usable in a Power Automate flow by converting it directly to JSON. Converting tabular data like Excel (or CSV) to JSON is extremely straightforward, and it gives us the opportunity to compose, parse, filter, and select spreadsheet data in interesting ways.
Power Automate Walkthrough
In this article, we’ll walk through a relatively simple flow that converts Excel data (specifically, an example Company Expenses spreadsheet) to JSON in Power Automate when an Excel document is uploaded to a SharePoint list. We’ll use the Cloudmersive Document Conversion connector to handle our data conversion.
This flow will automatically break down tabular data from an Excel spreadsheet into a JSON array. Information from this array will be dynamically usable across dozens of subsequent flow actions.
In summary, our flow will:
- Trigger when new items are added to a specific SharePoint list
- Check those items for attachments
- Retrieve attachment content
- Verify the content is Excel XLSX format
- Convert the Excel content to JSON
- Parse the JSON into a sample-generated schema
- Utilize the structured JSON in a subsequent action (in this case, an Approval)
Getting Started
To begin, we’ll select the option to create an Automated cloud flow with the SharePoint When an item is created trigger.
We’ll then configure our trigger action by selecting the SharePoint Site Address our list lives on & then selecting the List Name itself.
Get Attachments & Attachment Content
The When an item is created trigger produces a list item ID which we can use to configure subsequent SharePoint actions in our flow.
In this case, we’ll use two SharePoint actions back-to-back — Get attachments and Get attachment content — which collectively bring list item attachment content into our flow.
The Get attachments action produces a File Identifier value which the Get attachment content action uses to retrieve file bytes.
We’ll notice that Power Automate places our Get attachment content action in a For each control; this is to account for the possibility of multiple files attached to the same list item.
Filter Out Non-Excel Files
Before we make our Excel to JSON conversion, we’ll first include a Condition that checks if files contain an XLSX extension before moving forward.
This is simple enough; we’ll simply place our condition before our Get attachment content action in the For each control, and we’ll check if DisplayName contains an .xlsx string.
We’ll then click & drag the Get attachment content action into the True branch of our Condition.
Convert Excel to JSON
We’ll now incorporate our Excel to JSON conversion action acter our Get attachment content action.
We’ll first search the Power Automate connector library for Cloudmersive connectors, and we’ll locate the Cloudmersive Document Conversion connector (with the green logo).
We’ll click “See more” to view the full actions list, and from there, we’ll search for an action named Convert Excel XLSX to JSON conversion (pardon the redundant naming).
After we select this action, we’ll need to create our Cloudmersive Document Conversion connection. We can do that by creating a free account on the Cloudmersive website (this gives us a free API key with up to 800 API calls/month and no commitments).
Once our connection is created and authorized, we can pass Attachment content into the Input file parameter and then pass our DisplayName value into the file name parameter.
Create a JSON Schema
At this point, we’ve checked if our SharePoint list item has Excel document attached, and we’ve converted Excel documents to JSON. We can now use a sample payload to create a schema for future flow runs (note — this assumes spreadsheets attached to this particular list follow a standard structure).
To create a sample payload, we’ll save & test our flow with a file that follows our reporting template.
For example, my Company Expenses documents all have set pre-set columns labeled as Month, Department, Expense Category, Amount (USD) and Description.
When I run a flow with one of these documents, I can copy the JSON from the Excel to JSON conversion API response and use that as a sample schema in the Parse JSON action (from the Data Operation connector).
As we can see, the Parse JSON action utilized my sample payload to generate a schema for my Company Expenses spreadsheet format.
Utilize JSON in an Approval
As mentioned before, we’ll use our parsed JSON data in an Approval action as an example in this flow. We’ll ask our flow to Start and wait for an approval after our Parse JSON action.
We can set this up however we want; I’ll be using the Approval type called Approve/Reject — First to respond in my example flow.
We’ll use our parsed JSON to create one dynamic approval for each column/row combination in the original Excel spreadsheet. This means the stakeholder responsible for handling expense approvals can look at each individual company expense without needing to peruse the original Company Expenses spreadsheet.
Test the Final Product
Now that we’ve parsed our converted JSON content into a schema, we’ll save our flow and run one final test. This test should send one Approval to our flow stakeholder per row of the original Excel spreadsheet.
Since we elected the Start and wait option, our flow will remain in limbo until a stakeholder approves or rejects each expense.
Since I sent this Approval to myself, I can see exactly what I’m approving or rejecting in my approval email.
After hitting “Approve” or “Reject” on this Approval, I’ll receive the next one, and then the next one after that, and so on.
Conclusion
In this walkthrough, we learned how to automatically convert SharePoint list Excel attachments to JSON, parse our JSON into a schema, and utilize our data array in a common downstream flow action.
We can use this flow any time we’re parsing data from Excel documents with standardized columns.
