Formerly known as Microsoft Flow, Power Automate is a web-based service that helps to create automated workflows between your favorite apps and services to synchronize files, get notifications, collect data and more. Power Automate is a part of the Office 365 suite and is available in most Office 365 subscriptions. While its new name does not roll off the tongue quite like the old one did, it certainly flows (pun intended) with the Power Platform.
For those that have a deep SharePoint Server background, you might think that Power Automate is a replacement for the SharePoint Designer workflow. For us SharePoint aficionados, that’s certainly a selling point. But while Power Automate does provide you with a rich UI tool to handle automated processes in SharePoint, it is really a universal automation tool that connects all kinds of applications by utilizing custom and standard connectors.
So, what exactly can you build in SharePoint by using Power Automate? In this blog post, I will demonstrate 5 practical Power Automate examples that maximize SharePoint functionality.
For these use cases, we will create a couple of solutions for DevFactonaut, a fictitious company, and discuss various automation scenarios that provide value to its specific departments.
If you wanted to reproduce a site structure in multiple environments, Power Automate is great for deploying lists and libraries. For example, you might need to provision several lists and several libraries to promote a Site Lifecyle Management process through environments used for Development, User Acceptance Testing, and Production. Another use case scenario could be that as a consultant, you might want to build out a structure on your development tenant and deploy the structure to a client’s tenant using Power Automate (assuming the option is available for you).
You can also choose to complement the Power App you built that has a data connection to a SharePoint site containing lists and libraries. Power Automate would deploy the dependencies associated to the Power App. Typically, you would execute a PowerShell script but this is another option to consider when automating these site artifacts rather than performing the task of creating the lists and libraries manually.
POST https://{site_url}/_api/web/lists
POST https://{site_url}/_api/web/listsdocumented here.
The Base Template of 100 is the Template ID and it would be using the Custom List template. For your reference, below is a table outlining the corresponding Template ID to a few List Templates.
Name | Template ID | Description |
Announcements | 104 | A list of news items, statuses and other short bits of information. |
Calendar | 106 | A calendar of upcoming meetings, deadlines or other events. Calendar information can be synchronized with Microsoft Outlook or other compatible programs. |
Contacts | 105 | A list of people your team works with, like customers or partners. Contacts lists can synchronize with Microsoft Outlook or other compatible programs. |
Custom List | 100 | Using a list gives you the power to share information the way you want with your team members. Create your own list from scratch, add any other columns you need, and add items individually, or bulk edit data with Quick Edit. |
Document Library | 101 | Use a document library to store, organize, sync, and share documents with people. You can use co-authoring, versioning, and check out to work on documents together. With your documents in one place, everybody can get the latest versions whenever they need them. You can also sync your documents to your local computer for offline access. |
Form Library | 115 | A place to manage business forms like status reports or purchase orders. Form libraries require a compatible XML editor, such as Microsoft InfoPath |
Issue Tracking | 1100 | A list of issues or problems associated with a project or item. You can assign, prioritize and track issue status. |
Links | 103 | A list of web pages or other resources. |
Picture Library | 109 | A place to upload and share pictures. |
Survey | 102 | A list of questions which you would like to have people answer. Surveys allow you to quickly create questions and view graphical summaries of the responses. |
Tasks | 171 | A place for team or personal tasks. |
Wiki Page Library | 119 | An interconnected set of easily editable web pages, which can contain text, images and web parts. |
Refer to the FieldType Reference link listed at the end of this blog post to identify the FieldType Number to the Field . Now, let’s create a Status field with a datatype of Choice that contains choices of In Progress, Approved, and Rejected.
If successful, then the SharePoint site would appear to show the Vacation Request list and a Status custom field. I created additional columns that will be used in the next use case.
If your organization is planning a migration to Office 365 this can come in handy. You might already have a defined file plan for files that will be migrated to a specific Document Library or folder, but what about those simple excel tracking sheets? A Simple Excel file contains a large table without macros, conditional formatting, VB code, or referencing to another sheet. Using the SharePoint UI to perform a bulk import to a SharePoint list can be tedious and inconvenient especially if you do not already have the privileges to execute a PowerShell script. One option to resolve this is converting the Excel table into a SharePoint list.
In the previous example, we created a simple Vacation Request list. Now, we will add on to it a list to demonstrate the migration from an Excel table to a SharePoint list.
When running an instance of the flow, you might encounter this error when dealing with Date columns in an Excel Table.
{ "status": 400, "message": "String was not recognized as a valid DateTime" }
{ "status": 400, "message": "String was not recognized as a valid DateTime" }
This is because the flow is reading the value of the ‘Start Date’ column and ‘End Date’ column as a serial number of the date. For example, the serial number 43946 represents April 25, 2020. To resolve this error, we will need to alter the column format from Date to Text or copy and paste it in a new column of Text format. Rerun the flow once the changes have been done to the file. Once your flow is completed successfully, navigate to your SharePoint list to view all the newly created list items.
Another example of a common SharePoint and Power Automate automation is provisioning new users in Azure Active Directory. This is a great way to automate the onboarding process that is helpful to both HR and IT departments. For this use case, we will provision a new user once a department manager submits a New User request.
The request or service ticket to setup the Active Directory Account can be stored in a SharePoint List or any 3rd Party application and will trigger the flow once the Status is set to Approved. To keep the scope of this use case simple, I’ll refer to a “Click a button to create a new Azure AD user account” template that is available in the Templates section.
Keep in mind that the account used to create this automation will need the necessary permission to use the Azure AD connector used in this template.
The trigger in this template has inputs that prompt the user running a new instance of this Power Automate flow. For simplicity’s sake, I’ll call this user the flow initiator. The flow initiator is prompted for the following input properties and their responses will be used as the properties in the Azure Active Directory User.
So, what does this workflow look like in practice? In this scenario, the flow initiator is likely an HR professional or a Hiring Manager. This person receives the email sent in the last action, Send an email (Outlook) and pass it on to the new hire. After the new hire goes through an orientation session and gets settled at their workstation, they get a print out with their credentials from the flow initiator.
The days of printing out a Vacation Request form, filling it out, and leaving it on your manager’s desk for are hopefully a thing of the past. With Power Automate, organizations can modernize legacy ways or mundane processes and introduce technology effectively.
In this example, we will use Power Automate to start the approval process for a type of document, notify the approver that there is an action task pending their approval, and allow the approver the option to respond on their workstation or on a mobile device using the Outlook app or the Power Automate app. Both apps can be downloaded from the iOS App store or Android Play store.
To demonstrate the approval request and approval response using Power Automate, let’s modify the Vacation Request list that we used previously.
For the sake of simplicity, we will keep the scope of our flow to only one manager. In our example, the manager receives an email notification regarding the request. Then, they respond directly in the email by clicking either the Approve or Reject button shown in the body of the email, optionally, they add a reason and then click the Submit button.
Depending on the response of the manager, the flow will move on to the outcome chosen. At which point, we change the value of the Status column to either Approve or Reject and send an email notification to the Requester (Created By – Email Address).
UI Flows in Power Automate allow you to create and record Robotic Process Automation (RPA) capabilities to automate repetitive tasks in Windows and Web applications. With RPA, organizations can streamline business operations, reduce cost, minimize errors, improve compliance and productivity. Think for a moment of a task you need to complete on a reoccurring basis. Perhaps, you generate monthly financial reports for the financial department, or an inventory report for the procurement department, or you just want to get more done in less time. RPA is the way to do it. In this example, we’ll demonstrate the automated process of routing content from a Microsoft Form to an on-premise data source, Access.
For our next scenario, our fictitious DevFactonaut company wants to automate its sales process. DevFactonaut has a Contact Us form on their public facing website. The form, built using Microsoft Forms, is configured for anonymous use. Potential customers visit the company website, fill out the form, and connect with sales about the services offered.
To handle that, we will set up a form that upon submission triggers a flow that saves the entry in an Access database file stored on the company’s server. We will be using Power Automate, UI Flows (Desktop), Microsoft Forms, and Access.
This automated flow type uses the When a new response is submitted (Microsoft Forms) trigger. We will then add the Get response details (Microsoft Forms) action to retrieve the fields from the Contact Us form. The last action will be the Run a UI flow for desktop (UI flow) action, and we’ll map the Microsoft form fields to the UI flow’s input fields. This action is a premium action, and you’ll need to validate if you have the sufficient licensing associated to your account.
With minimal effort, we were able to get a working UI flow to be recorded, have it invoked from an automated flow, and the automated flow to handle the data intake submitted from the Microsoft Forms. You may have reasons for and against using Access database. Here, I used Access, to show you that you can collect data externally and store it in an internal database.
So, what did you think? Does this interest you? Do you see possible scenarios on how to integrate these demos in your organizations? Let’s connect and continue the conversation.
That’s a wrap! We went through 5 use cases done with Power Automate in a very grounded and simplistic scope. However, I hope this gives you insights and showcases the powerful automation of Power Automate. Pun intended.