How to use Power Automate to streamline routine tasks

Robotic Process Automation tools are one of those areas where an imposing name and overly complicated use cases have made a helpful tool seem out of reach of the masses. However, simplified web-based automation tools are putting the capabilities of RPA within reach of most users who would like to automate repetitive tasks.

Tools like Microsoft’s Power Automate, Zapier, Automate.io and IFTTT are part of an emerging set of web-based tools that can automate tasks in cloud applications. One significant benefit of Microsoft’s Power Automate, formerly known as Microsoft Flow, is that it’s included with a Microsoft 365 subscription and works well with Microsoft’s Office, OneDrive and Sharepoint.

If you’re interested in these tools, start by automating a simple task and then add new embellishments as your competence grows. In my case, I was looking for a way to automate the various approval emails for articles I write for TechRepublic, which I track in a fairly unassuming Excel spreadsheet.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

While it only takes me a few minutes to create my approval emails and update the spreadsheet manually, it’s a task that I complete several times each week, so even the minor time savings from automating routine tasks can add up. The automation also ensures that I get all the required information in each email and that the format of each email is the same, making the approval process easier for my editor.

How to use RPA tools in Power Automate

For each topic, I need to email an approval request to my editor Mary, and upon approval, send a notification email to a Google list and create a template for the article, and ultimately send the article and record when it is invoiced.

We’ll automate sending the initial email and updating the spreadsheet for this example (Figure A).

Figure A

I used the Table function in Excel to set up my tracker, which essentially creates a database for Power Automate to use.

Microsoft provides a desktop version of Power Automate that’s a bit more sophisticated and complicated, so I recommend those new to RPA start with the online version, which can be accessed here. The online version also allows your flow, Microsoft’s term for an automation, to be run from your Excel workbook on any computer.

Upon logging into the Power Automate website, you’ll find a control panel and some common automations. These are a great starting point and allow you to learn by example as you modify the existing automations to suit your needs (Figure B).

Figure B

By selecting Create in the left-hand list, you’ll be presented with additional templates and the ability to search. When I search for “email excel row,” I’ll find a template that sounds like what I want to accomplish: Send An Email For A Selected Row (Figure C).

Figure C

If I select this option, Power Automate will prompt me for the connectors that it will use to execute the automation. Each step in the flow generally connects to an application or data and might require a login or authentication to complete (Figure D).

Figure D

The first step in the flow is usually a trigger, an event or action that occurs to start the flow. In this case, I manually start the flow from a selected row in a spreadsheet.

Use the first three drop-downs to locate the spreadsheet you created earlier. If you’re not finding any options in the Table selection, make sure you selected the rows in your spreadsheet and configured them as a table using the Table tab in Excel.

You’ll notice that two inputs are defined in the standard flow: One for the recipient’s email address and one for the email subject. Since we need those fields in the following action in the default configuration, we cannot delete them until they are removed from the Send An Email action.

I always send the approval to the same place, and we’ll define the subject from our spreadsheet, so I’ve deleted these options from the Send An Email action and can then remove them from the For A Selected Row action using the menu in the ellipsis.

If you did want to prompt the user for inputs manually, you could use the Add An Input button and create whatever inputs you needed.

Like many emerging tools, Power Automate has some odd nuances. For instance, I’ll have One Drive for Business listed multiple times in the Document Library drop-down. Selecting the first one generally seems to work.

When setup for my spreadsheet and with the defaults for Email and Subject removed, my flow looks like this (Figure E):

Figure E

I can now configure the approval email that I want to send. I’ll hard code the email address since I always send my email to the same place and use text and a field from my spreadsheet for the subject and body.

You’ll notice that if you tap on a field, you’ll get a pop-up with Dynamic Content, which are the various fields from your spreadsheet. You can enter static text and drop in dynamic content where necessary. I’ve added some static and dynamic content, so my Send An Email action ultimately looks like this (Figure F):

Figure F

For testing purposes, put your email in the To field to avoid any failed or improperly formatted emails going to a customer or colleague.

At this point, you can save and test your flow.

What’s somewhat confusing in this scenario is that if you click the Test button in the upper right corner of the screen, you’ll see that all the test options are grayed out. This is because this particular flow requires a row in a spreadsheet to activate (Figure G).

Figure G

To test these spreadsheet-based flows, open your spreadsheet in Excel. If you click the Data tab, you should notice a blue Flow button on the far right of the ribbon bar. Clicking this button lets you see your Power Automate flows for that spreadsheet once you’re logged into your Microsoft 365 account.

If you select a row in your table, either by selecting the entire row or just clicking a cell within your desired row, the Play button should activate and allow you to run the flow (Figure H).

Figure H

You may need to log in again depending on which actions were in your flow, but ultimately the flow should run. You may be slightly underwhelmed, as there are no flashing lights or information to show that your flow worked beyond a link to the run log.

However, after a few moments, you should receive an email from your flow and find a sent email in your inbox. We’ll next add a step to update our spreadsheet, which becomes a bit more exciting as cells in your spreadsheet will update after a few seconds.

In either case, checking the Run History for your flow from the Power Automate website will show its result (Figure I):

Figure I

Tapping a date entry provides a nice log of that flow run and will show the data retrieved and sent for each step in the flow.

How to add an update capability in Power Automate

While it’s nice to be able to send an email, it would also be helpful to update the status cells in the spreadsheet to track which articles have been sent for approval. We can accomplish this by adding a new step to our flow, using the New Step button, and locating the Update A Row action (Figure J):

Figure J

As before, you’ll select the location of your file and table and will notice two new options: Key Column and Key Value. The Key Column is the column of your spreadsheet that the action will search for the value you provide in Key Value.

In our case, we will search the Topic column for the Topic Title we emailed in the previous step. You’ll notice that selecting this field provides a similar Dynamic Content selection. Using the Topic element, we should be able to update the row in our table for the Topic that was just emailed. You’ll notice that you could use another spreadsheet or file so that you can perform multiple updates and actions from the initial trigger.

You’ll also notice that once you select your table, you’ll get fields for each column of your spreadsheet. This is where you provide the updated values that will appear in your spreadsheet.

In my case, I’m going to update the Mary column with a “Y,” indicating that I’ve sent my approval request to my editor, and I’ll change the Approved column to “P” for Pending, meaning I’m waiting for Mary’s approval.

Once complete, the action looks like this (Figure K):

Figure K

I can now save and test my flow and, when satisfied, update the To field to the email address of my editor.

Using these same techniques, I can build additional actions. For example, I’ve added the ability to create a new Word document for my article based on a template and send a different approval email with appropriate status updates to my tracker. Your only primary limitations are your imagination and whether you can find an action that does what you need or integrates with the correct application.

With about an hour of work for my article approval flows, I probably save 30 minutes each month that I used to spend manually creating emails and copying documents. While it’s not profound, an extra six free hours each year is a win I’ll gladly take, and I’m looking for other minor automations that can increase these time savings.