Power Automate - Excel Process Automation
This company approached us as they were having to manually format and update Excel workbooks daily, taking a considerable amount of time out of their day, doing the same task day upon day. We at Synapx engaged with the stakeholders, considering their current actions for the Excel workbooks, and future ones that could arise and looked to automate this, taking seconds, rather than hours, liberating them from this task.
Power AutomateWritten by
Published on
The Challenge
A Financial Services client received two Excel workbooks in their inbox daily. They needed to manually format these workbooks to prepare them for uploading to a database via a webpage. While they used some basic Excel formulas to assist with the data transformation, these formulas frequently broke whenever the Excel file formats changed. This manual process took the client around one hour each day.
Our Approach & Solution
Synapx addressed this issue by setting up an O365 Shared Mailbox and instructing the client to route the relevant emails to this mailbox. We then implemented a Power Automate flow that triggers automatically whenever a new email is received. When the flow detects either of the Excel files in the email, it uploads the files to a SharePoint Library, runs an Office Script to generate a new sheet in the workbook with the data formatted as required, and then writes the data directly to the database. After the process is complete, the flow sends an email from the Shared Mailbox to notify client stakeholders, including a link to the updated Excel workbook in the SharePoint Library.
The Outcome
This solution streamlined the client’s workflow, eliminating the need for manual data formatting and significantly reducing the time spent on this task each day. The automated process is reliable and resilient to changes in the Excel file formats, ensuring consistent and accurate data uploads to the database.
