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
The Financial Services client would receive 2 different Excel workbooks to their personal inbox each day. They would format both workbooks into the required format to allow them to upload the data to their database via a webpage. There was some basic Excel formulas to help with the data transformation, but this often broke when the Excel file changed format. The end-to-end process would take the client approximately 1 hour each day.
Our Approach & Solution
Synapx setup an O365 Shared Mailbox and asked the client to get the emails directed there. We then setup a Power Automate flow to trigger, each time an email is received. If either of the Excel files are detected within the email, the flow would upload the files to a SharePoint Library, run an Office Script to create a new sheet in the Excel workbook with the data in the required format, finally writing the data directly to the database. The flow would then notify client stakeholders via an email from the Shared Mailbox that the flow had ran, along with a link to the Excel workbook uploaded to the SharePoint Library.
The Outcome
Synapx setup an O365 Shared Mailbox and asked the client to get the emails directed there. We then setup a Power Automate flow to trigger, each time an email is received. If either of the Excel files are detected within the email, the flow would upload the files to a SharePoint Library, run an Office Script to create a new sheet in the Excel workbook with the data in the required format, finally writing the data directly to the database. The flow would then notify client stakeholders via an email from the Shared Mailbox that the flow had ran, along with a link to the Excel workbook uploaded to the SharePoint Library.
