Table of Contents
This post and video will show how to combine Power Automate and Office Scripts to convert Excel file to CSV format and store in SharePoint. The approach shown only requires a few lines of code in the Office Script and it is leveraging standard connectors (no license required) and it doesn’t use any third-party app.
During the video, we go over common challenges such as dynamic data ranges, shared script management in SharePoint and pagination limits.
In one of our previous blogs, we demonstrated how to create dynamic approvals that are sent in a single request at the same time, to learn more about this, check it out.
Use case description
For this example, we’re working with a file that can have “N” number of rows (we don’t know how many rows we will get) and 5 columns. The file looks as follows:
The file is received in XLSX format and we want Power Automate to convert it into CSV format and save the output in SharePoint. For this video and blog, we’re working with SharePoint lists, but the same logic can be applied regardless of the data source being referenced, such as SQL or Dataverse.
Solution description
Preparing the Excel File and Creating the Office Script
- The Excel file is uploaded to SharePoint.
- We use the “Automate” tab to create the Office Script.
- We create an Office Script dynamically convert the flat data range into a table with a fixed name (“DataTable”) – the getUsedRange function is leveraged to handle the dynamic number of rows the file may have.
The script is saved and stored in OneDrive by default, but we move it to SharePoint.
Create the Power Automate Flow
- Add triggerwhen a new file item is created in SharePoint folder (or any other trigger depending on your needs).
- Use the “Run script from SharePoint library” action to dynamically run the previously created Office Script on the newly uploaded Excel file.
- Use the “List rows present in a table” Excel action to read the rows from the dynamically created table.
- We have to enable Pagination with a row limit (e.g., 1,500) to accommodate large datasets beyond the default row limit for the Excel action.
- Using the Select action, columns are customized to exclude unwanted system-generated columns and optionally rename headers in the CSV output.
Finally, we add “Create file” SharePoint action to save the generated CSV in the same folder as the original Excel file.
Step-by-step walkthrough
How our Power Automate Development services can help
Combining Office Scripts and Power Automate creates an end-to-end automation that can be applied to a wide range of use cases. Using our Power Automate and Power Platform consulting services, we have implemented many automations that combine different tools from the Microsoft ecosystem. If you’d like some help working with Excel Online files and Office Scrips, contact us, we’ll be more than happy to have a conversation and see how we can be of help for your team.
