Using Microsoft Power Automate in SharePoint to Track Team Goals

If you’re a casual SharePoint user, you probably think of it as nothing more than a cloud-based document storage platform. But SharePoint has a lot more to offer than just a place for your team to share documents. With the right setup and plug-ins, it can be a powerful collaboration tool that syncs calendars, tracks tasks, manages workflows, and more. 

Microsoft Power Automate is one such automation tool. Power Automate can automate any number of day-to-day business tasks. Whether it’s scraping Twitter, calendar updates, automatic email notifications, accessing Google Drive, or calling a database, it’s got an option to access and update it.  

While not a perfect tool, Power Automate has many pros, making it a great automation alternative to creating custom code to connect your apps. Keep reading for an overview of how Microsoft Power Automate works and a breakdown of both its features and limitations. 

Why We Use Power Automate 

When the Data Team needed a way to track team members’ certifications to ensure everyone stayed up to date on trainings, we looked at several options. We could have built a database to store team members and certifications with different access levels for managers and teammates and added a graphical front-end to update statuses. But for the sake of timeliness and simplicity, we decided to look for another solution.  

We eventually discovered Microsoft Power Automate. After looking over its features and benefits, it was a natural choice for tracking our certifications. One of the main selling points was the fact that our team list was already in SharePoint. Beyond an easy implementation, Power Automate also sold us on the following benefits: 

  • It integrates with a wide variety of commonly used tools 
  • It’s easy to develop with, even if you don’t have a technical background 
  • It comes with several customizable templates 
  • There’s a strong community of users where you can find tutorials and troubleshoot with others  

How Power Automate Works 

Power Automate consists of a visual click-and-drag interface over an elegant JavaScript Object Notation (JSON) solution. At the visual level, you can drag a series of actions together and populate them with target values from a helpful “Dynamic Content” popup. While not an exhaustive list by any means, some of the main actions include: 

  • Accessing items from lists or directories 
  • Updating, moving, or deleting files 
  • Firing off emails 
  • Programming control structures such as conditionals and loops 

Each visual action has a corresponding JSON definition you can access with the “peek code” option—the backend interprets JSON commands to provide the promised effect. Supplementing the actions and dynamic content, you can apply a broad collection of functions to the dynamic values, including string, list, date, mathematical, and logical functions. 

Each action outputs the results of a RESTful request—a status code, header, and a JSON body. These outputs then auto-populate the Dynamic Content popup for actions downstream in the code, letting you pick and choose tags in an intuitive way.  

When a data source is accessed, it’s done as a RESTful call. The output is delivered in the same way as any other action. An interesting corollary to this setup is that you can trigger other Power Automate flows from an initial one, effectively functioning as individual methods and permitting code reuse. Unfortunately, this feature is only available at a more costly subscription tier, so you may not have access to its full functionality.   

The Limitations of Power Automate 

As useful as Power Automate is, we discovered several pain points as we configured it to track our team’s certifications. Here were some of the biggest limitations we noticed: 

Deleting files 

The SharePoint file deletion trigger doesn’t provide relevant information. It gives you the name and ID of the deleted file, the user who deleted it, and the time of deletion, but doesn’t provide a file path or anything to indicate a parent folder. Since the file is deleted by the time the flow triggers, having the name is next to useless for determining where the deletion happened. 

Debugging 

Dynamic content and expressions don’t lend themselves to easy reading or debugging. Power Automate wraps up dynamic expressions into nice little packages you can click into to expand, which means you’re either looking at your expression on a single line in the action tab or a single line in the popup. This makes it difficult to view the full string at once. 

Setting aside the issues with the length of dynamic expressions, Power Automate provides no error highlighting—and the dynamic content tab won’t close if the expression is syntactically incorrect. With longer expressions, this becomes extremely painful as the potential to misplace a parenthesis increases. Take this example of a line intended to extract the last name from a name in “Last, First” format below: 

substring(outputs(‘Name'), 0, max(0, indexOf(outputs(‘Name'), ','))) 

Copying code 

Copying code is difficult in Power Automate. While you can click and drag some pieces within an individual flow, selecting multiple actions at a time is impossible, as is copying between flows. The closest you can get is to “save as” a flow, then tailor it as you wish. This lets you copy a single large piece of code to a new flow but doesn’t solve the problem of integrating two existing pieces without rewriting everything from scratch.  
 

Overcoming Limitations to Track Certifications  

To overcome the certification tracking problem, we followed a few simple steps. First, we added a column “Certifications Current” to the existing SharePoint team list. Next, we created a template folder for the manager to populate with all required certifications, and a directory to contain individual team member folders. As team members complete certifications, they can upload the completed document to their folder.  

In Microsoft Power Automate, we then created a flow which triggers whenever a file is inserted or updated and compares the folder the file was inserted into against the template certifications. If the team member folder has all the file names from the template folder, the code then updates the SharePoint list item corresponding to their name (extracted from the team member folder) to indicate that they’re certified.  

If they lack some files, it updates the list to indicate that they are not certified. Similarly, if the template file gets updated, it applies the same check to all team members to ensure they’re still current—the same happens if a file is deleted. 

Additional Possibilities in Power Automate 

Although we’ve only just begun scratching the surface of what’s possible in Power Automate at U.Group, we’re exploring several further improvements to the certification tracking process. There’s value in tracking separate due dates for individual certifications, and in providing updates for team members and managers when the due date nears.  

Additionally, we could refactor the solution to make it more extensible to other projects. In order to support these areas, we would like to replace the template folder with a certification list containing the certification name (which the individual files should match), project name, and certification due date/frequency.  

We then plan to add separate flows for the manager and team member updates, set to fire off reminder emails as the due date approaches, or provide monthly status updates. Finally, we’ll copy the team member file directory under a “project” folder, and update the existing flows to only apply to certifications whose project name matches the appropriate folder, thus permitting the one certification check flow to cover multiple projects. 

Despite its limitations with interface and some of the commands, Microsoft Power Automate provides a powerful toolbox for automating and streamlining a wide variety of business problems. We look forward to continuing to explore its possibilities for tracking the Data Team’s workflows, goals, and more—and we hope you’ll give it a try for your team, too. 

Are you using Microsoft Power Automate in an innovative way? Share it with us on Twitter or Facebook 

Get alerted to new job postings, events, and insights by registering for our monthly newsletter.