A demo for Power Automate — an integration of capturing specific data from worksheets and sending it to stakeholders accordingly
Power Automate is a useful automated flow to help you with periodic routine tasks. This article will cover a process that starts with reading an Excel file locally (or you can make it from DB, too), capturing some raws from it, writing the captured rows to a new file, and sending the new file to stakeholders. The whole process is automated, and the task can be done in just a few minutes. You must have the experience of having raw data containing sales numbers of different regions, and you want to send the number to regional managers accordingly. You can build a dashboard, but the managers usually need raw data to follow up or discuss with their direct reports. So, in the above situation, you must create different files for different managers. But the truth is these are a routine task that takes time. In this case, Power Automate will be your good friend to save you time and deliver reports efficiently.
Star with Power Automate control panel
It is easy to learn how to use the panel. On the left-hand side, they are the actions(such as capturing data from the web, opening Excel, closing files, connecting to DB, etc. ) that you can use for your case. The middle shows the actions that connect to complete your process. You can edit your actions here, and all the error messages can also be found here. The right-hand side shows the variables that trigger the data flow or actions and the input/output file used in the process.
Define variables in the process
Let’s get into our scenario: reading an Excel file locally (or you can make it from DB, too), capturing some raws from it, writing the captured rows to a new file, and sending the new file to stakeholders.
Below is the whole process
- So, start by reading an Excel file. You need to launch an Excel application, so we have the first action — to launch Excel. Select from the left-hand side area and edit action details. In the details, you can find that this action finally generates a variable called “excelinstance” , which is like what we create while coding. We can pass this variable to other actions or capture values to move to the next step. You can also edit some settings while clicking edit in the middle area.
2. The next step is to read data from the selected Excel file. When you select the read from Excel worksheet action from the left-hand side area, you need to set the Excel instance so that Power Automate can find where to capture the data; the rest of the details are like using Excel to define the captured scope.
3. Launch a new Excel file. This file will be the one you try to send to the stakeholders. You can see here we have the second instance(excelinstance2). This instance helps the power automate to differentiate different Excel files.
4. Write the captured content to the new file. You see that we captured data (exceldata) from excelinstance, and wrote it to excelinstance2.
5. Save the new Excel file. You can either save it in your local folder or in the cloud folder.
6 and 7, to close both Excel files
8. Launch the Outlook application. This action also generates an instance called “OutlookInstance”. This instance will be the trigger for sending emails.
9. Set your email receiving lists. This action drives an email to your stakeholders. You can attach the new Excel file (Excelinstnace2) to whom it may concern, and customize your email title and body.
Below is a demo video of running a whole process and automatically sending an email to stakeholders. Copilot enables more powerful applications on the Microsoft platform, and I will introduce more applications on Power Automate. Stay tuned.
