Power Automate to the rescue

As you can see, when looking at my previous articles regarding PowerApps and Power Automate (formerly known as Flow), I spend a substantial amount of time on building workflows with Power Automate and custom forms with PowerApps.

For your reference, here is a list of related articles I published so far.

1.  Pre-Populate a PowerApps form
2. Printing a custom form
3. Attaching files to a Common-Data-Service record

This article will be all about my personal lessons-learned, my workarounds and my recommendations. I thought I share what I had learned so far because when I started with PowerApps and Power Automate, I learned a lot from the community, and with this article, I want to give knowledge back to the community. If you have a great workaround that you think would be a perfect addition to this article, please let me know.

Initializing string variables

I started my SharePoint career as a SharePoint developer many years ago at a time when developers used the powerful server-side code API provided by SharePoint on-premises. Maybe that is the reason why I love to use variables in my workflows – although not always needed. When using variables of type string, I struggled to initialize them to be empty properly. I tried single quotes, double quotes, and other very creative approaches. Nothing worked – means: initializing a new variable of type string to be empty! Once I realized that my creativity wouldn’t lead to a solution, I reached out to the community and my valued fellow MVP Haniel Croitoru had an easy solution:

To create an empty variable of type string, provide a name for the variable and select string as the type (see above screenshot). Now click on the text field next to Value and after that, click on Add dynamic content. A pop-up window with a blue heading will show up (see above screenshot). If it doesn’t, simply click on Add dynamic content again. In the pop-up window, click on Expression and enter two (2) single quotes on the right next to fx. That kind of expression does the trick.

Creating a document library

My demo workflow (see previous articles) takes some information provided by a user, performs a quick validation, and creates a subsite and a document library within that subsite. I was surprised when I realized that there is no workflow activity yet to build a document library within an existing site. Again, I did some research, and this is the solution I added to my workflow.

As there is no pre-configured activity, we need to let SharePoint know what we want to achieve. This can be done by using the Send an HTTP request to SharePoint, which technically establishes a REST API call and makes SharePoint do the heavy lifting.

In the metadata (body) of this REST API call, I provide the properties and their values needed by SharePoint to create the library. 

In my example, the properties look like this:

With this basic set of metadata, SharePoint can create a document library based on the given base template with the given name and description. Here is the tricky part: in my original example, I used a variable rather than a hard-coded library name. Even when using a variable, you need to enclose the variable in single quotes. Sounds weird, but as the Power Automate runtime engine will replace the variable with its value before sending the actual HTTP request to SharePoint, single quotes are needed (see above screenshot).

As this method technically uses common SharePoint REST API calls, which are sent via an HTTP Request to SharePoint, there is also a convenient way to deal with the response received from SharePoint, which I'll explain in the following section of this article. Here is a list of available base templates for lists and libraries in SharePoint and here is detailed documentation on the SharePoint REST API.

Dealing with REST API call responses

As explained in the previous section, sometimes we need to use the Send an HTTP request to SharePoint workaround. These requests are usually followed by a response sent back by SharePoint, and there is a convenient way in Power Automate to deal with the parameters included in the response. This is when the Parse JSON activity comes in handy:

Basically, this activity takes the data from an HTTP response received from SharePoint and extracts the parameters and their values to make them available in Power Automate. Let’s see how this activity can be used. First, this activity needs to be added to the workflow. As a configuration, it just requires the body information from a previous HTTP Request activity (see the Schema field). 

But there is more needed to take full advantage of this activity. Additional information needs to be provided as Schema is a required field. How do we get that Schema information? This is how I do it: Before adding a Parse JSON activity, I temporarily add a Send email notification activity and add the Body variable of the previous HTTP Request to the email body (see screenshot):

Once I receive the email, I copy the entire JSON data from the email’s body and paste it in the Schema field of the Parse JSON activity. Now the temporary Send Email notification activity isn’t needed anymore and can be removed from the workflow. All the JSON properties read from the response will now show up in the list of dynamic content and can be used as variables. Here is an example of how that looks like:

As you can see, the JSON properties read from the HTTP Response show up as variables and can be used in subsequent workflow activities.

Creating sites in SharePoint

My workflow also needs to create a site in SharePoint, and again I used the Send an HTTP Request to SharePoint activity. Here is a screenshot of the activity I used:

Again, the magic happens within the Body section of this activity. In my example, I provided a URL, a title, a description, language information and a WebTemplate (classic team site). Here is a list of all available web templates in SharePoint. Although I mentioned this before, please ensure that all variables you use in the Body field are enclosed in single quotation marks. If you forget the quotation marks, you will receive an error message from SharePoint, and depending on where the quotation marks are missing, it can be tricky to understand the SharePoint error message and locate the missing quotes. If you try this approach, keep in mind that it can take some time until the new site is created. In my test environment, this workflow activity sometimes took up to 90 seconds (or more), until the site got created, but in most cases, the new site was created within 15 seconds.

Updating Hyperlink fields in a SharePoint list or library

The workflow that I was working on needs to update a SharePoint list item with a link to a document. The associated generic SharePoint list was configured with a common Hyperlink field. My first attempt was to use the Update Item workflow activity, which is available in Power Apps. Unfortunately, the workflow activity couldn’t update the Hyperlink field properly. A Hyperlink field in SharePoint consists of two internal properties: the display name and the URL. When I tried to update the field in SharePoint by using the OOTB Update Item activity, both internal Hyperlink field properties were filled with the just URL. I didn’t find a way to update a Hyperlink field in SharePoint with the OOTB Update Item activity properly, so I switched back to the workaround I discussed in the previous section: the Send an HTTP Request to SharePoint. Basically, I again used a REST API call to make SharePoint update the field in the SharePoint list. Here is a screenshot of how I configured the activity:

Here comes the tricky part: the metadata ‘type’ needs to follow a very specific naming convention. In my example, it looks like this: SP.Data.ProjectsListItem. The name of the list I used in my example is Projects. The term ListItem needs to be added without any additional period. The name of the Hyperlink field I use in my example is ProjectSite. By using this approach, you can set both Description and URL accordingly (see screenshot above). Please also note, that although I use variables, those variables need to be enclosed in single quotes as they will be replaced by their values before the request is sent to SharePoint.

Captions of Power Automate activities

As you can see in the screenshots of this blog post, each activity has a caption or title which can be changed by using the context menu and selecting Rename. I recommend changing the caption of each activity to make the entire workflow more readable. However, changing the caption of an activity can cause errors in your workflow, which sometimes are hard to find. Means: if you change the caption of an activity after the workflow has been created, it can happen, that you receive an error message when trying to save or run the workflow. Why? Well, let’s have a closer look.

In the previous section of this blog post, I explained how to use the Parse JSON activity. Let’s assume my workflow sends a notification once a new project site has been created.

The problem becomes apparent when we look at the code of this activity. This can be done by clicking on Peek Code in the context menu of this activity. The following screenshot provides a view on the underlying code of the Send eMail Notification flow activity:

As you can see here, Flow is using the caption of the Parse JSON activity to get access to the description (see notificationBody in line 11 – current caption is ‘Parse_JSON’). If you change the caption of the previous Parse JSON activity, the code used here in the Send Email notification activity is not getting updated, which will result in an error message if you try to save or run the workflow. The error message will tell you that Power Automate can’t access the Description variable. These errors can be tricky to find, so I thought I share my lessons learned regarding the renaming of Flow activities. In a nutshell: some Power Automate activities reference other activities by their title. If you change the title of an activity, references might not get updated automatically.

Iterating SharePoint lists and libraries

One of the everyday tasks in many workflows is to iterate lists of data. Depending on the Power Automate activity used, there can be multiple options to iterate items in a SharePoint list. In this article, I want to show the fundamental method, but there might be different (or even better) solutions. Let’s see how you can iterate items in a generic SharePoint list. It all starts with a proper activity – in this case, I use the SharePoint Get Items activity:

To be able to use this activity, you only need to provide the site address and the list name. The advanced options of this activity provide additional settings to tweak performance or to reduce the number of returned items by applying a filter, but they are not required.

Once this activity has been added to a workflow, how do we deal with the results? Technically, this activity provides a list of items, and we can use the Apply to Each activity to get access to this internal list. Here is how this looks like:

This activity needs a list of items as input, and in my example, I use the value property exposed by the previous Get Items activity. So far, so good – but how can we get access to a single item stored in the value property? In my example, I created a conditional branch as I want to look for an item in my list of projects which is using a specific project number. I created the following activity:

In this condition, I check if a Project Number (stored in a variable – left side in purple) matches the Project Number of an item retrieved by the previous Get Items activity (right side in light blue). A closer look at the light blue input field in the above screenshot reveals the magic:

To get access to a specific item in the list of items (retrieved by the Get Items activity), we can use the item() operator. In my example, the item() operator gets its input from the previous Apply to each activity (the item currently processed). Basically, I am accessing the list of items passed as a parameter to the Apply to each item in list of projects activity, and I am referencing the field within the item by its name (Project Number). These are the necessary steps you can use to iterate items in a generic list in SharePoint. This procedure also works if you want to iterate a list of files stored in a document library or the data records (data sets) stored in a Common Data Service entity. Don’t forget to enter this function call as an expression! I sometimes experienced difficulties with Power Automate not accepting the expression. In that rare case, I just entered item()?[‘Project_x0020_Number’] manually and hit enter. Power Automate appears to be smart enough to update the expression with the proper action name on its own.

Passing parameters from and to PowerApps forms

One of the great features of PowerApps and Power Automate is the ability to pass parameters from PowerApps to Power Automate and back.

Passing parameters from Power Apps to Power Automate is a widespread procedure as often workflows (created in Power Automate) need parameters (or values) from a form. A classic example is a custom form that is using an approval workflow. The user who is supposed to approve is passed from the form to the workflow as a parameter. Technically, this is easy to accomplish as there is the Ask in PowerApps feature - see next screenshot:

Whenever a workflow is connected with a PowerApps form, this feature is available. If you want to see how this is used in a real-world example, I encourage you to have a look at the previous articles of this series.

Power Automate can pass parameters back to a PowerApps form as well. Means: a workflow can pass a value back to a PowerApps form as well. A common example is this: let's assume there is a Power Automate workflow that creates a new item in a generic list in SharePoint (like some project-related data). Once the workflow created the item in the SharePoint list, it can pass the URL (or the ID) of the data item back to the PowerApps form. A Power Automate workflow could also pass a flag back to the form, indicating if the workflow has been successful. This is how this is done:

There is a specific PowerApps action available in Power Automate, that can be used to return values. Once you added this action, you can select, what type of data should be returned to PowerApps:

I have seen many workflows created in Power Automate, which receive parameters from a Power Apps form when being called, but to me, it seems that the return path -means passing parameters from a workflow back to a form- isn't used that often.

Conclusion

Since Microsoft first introduced Power Automate (if I’m not mistaken, the general availability was announced in Q4 2016), Power Automate has evolved significantly. Power Automate became an excellent replacement for the OOTB workflow templates in SharePoint and SharePoint Designer workflows. Power Automate plays out its advantages when it comes to including external systems (like Office 365 applications, 3rd party systems, or the Common Data Service available in Office 365). Still, there are some disadvantages. For me, the most prominent drawbacks are the cumbersome editor and missing SharePoint functionality, which requires unpleasant workarounds like the Send HTTP request to SharePoint action. Microsoft sometimes announces Power Automate as a no-code solution used to create professional business application workflows. Well, that’s true, but I still think users need to have a basic understanding of the fundamental principles of common programming languages to take full advantage of Power Automate. Don’t get me wrong – you don’t need to be a developer to create great workflows. But to be able to use workarounds successfully, you need to have some knowledge around REST calls and JSON responses - at least, if you use Power Automate with SharePoint Online.

DevFacto blog: Why use Microsoft Flow?

Shane Young: Upload to SharePoint Document library (Great solution! Must read!)