Use PowerApps for an automation Front-End

/

The year is almost over and we celebrate the Festive Tech Calendar hosted by the Cloud Family. Before going into the topic of this blog I want to thank everyone participating in the festive tech calendar for their hard work! And I also hope it will reach it’s goal for donation to the charity Missing People.

As tech people we are all often very busy with fixing all kind of issues. So we don’t want to be bogged down by simple things which can be automated. But what do we do after we automated these things? Often I see these scripts or systems being dropped at some first line support and have them deal with it. In this blog post I want to show how you can easily create a front-end system which can be used by end-users to trigger your automation. By making a front-end like this you can “shift-left” of even “shift-left-left” this automation all the way to the end-users.

The automation

Let’s start with the automation. This can be done in many different ways, so to make sure we create a robust system we want to make it in a way where it doesn’t really matter how our automation is handled. For this example I’ve chosen for a automation runbook in an azure automation account.

This is a simple PowerShell script with two parameters and some output.

Param
(
  [Parameter (Mandatory= $true)]
  [String] $Name,

  [Parameter (Mandatory= $true)]
  [Boolean] $IsNice
)
if($IsNice){
    Write-Output "Create present for $Name"
}
else {
    Write-Output "$Name wasn't nice this year so doesn't get a present"
}

This script will take the values given and output some text based on the value of IsNice.

The orchestration

To start the automation runbook we need some intermediate automation. This will take a trigger based on our front-end and start the runbook. To keep this layer very simple and not very resource intensive I’ve chosen to build this in an Azure Logic App with a Consumption Plan. To make sure the logic app has rights on the automation account a managed identity is used.

With these roles:

Authentication against office 365 services like SharePoint are done by a user account, most office 365 connectors require some kind of user account to connect. Best way would be to create a special user account for this kind of automation which is not used for any other purposes so the activity of the account can be monitored for any weird behavior.


The logic app is made pretty rudimentary for this example. If you want to make this more generic you would need to add more to it. You could also chose to make a different logic app for every automation you are using.

What’s happening in this logic app are the following steps:
1. It checks regularly if an items is added to a sharepoint list.
2. If a item is added to the SharePoint list it will trigger the rest of the logic app.
3. It will set the status field in the list to a new value
4. It will start the automation runbook but won’t wait for completion
5. It will set the status field in the list to reflect the automation has started
6. It will loop and during the loop check the status of the automation job
7. If the automation job is completed it will update the status in the list and store the output of the automation.

The data

As you saw in the logic app a SharePoint list is used. Many different data systems could be used to do this, but when working with PowerApps the integration with SharePoint is very easy. So for this blogpost I’ve chosen to work with a SharePoint list.

This SharePoint list has a few columns. First it has the Name and IsNice value we are going to need for the runbooks. These values are passed from this list to the runbook in the logic app. Besides that there is a State and Output column which will be used to give information back to the end user. The last column in the ID columns. This is a default column in sharepoint which can be made visible by clocking the add column option and going to the show/hide columns.

The front-end app

The front-end app is made in PowerApps. First of all a warning when using PowerApps. This platform is mostly made for end-users so apps are stored in a user context. So beware that if you are logged in as your own user when creating a logic app it will be stored to your account. There are ways of exporting and moving logic apps but this can often break data connections. So if you want to deploy this in a production environment it’s best to first decide which account will be used to “store” this app. Just like with the SharePoint authentication you could opt to use a dedicated user account for this.

Our front-end app is going to require a few components:
- A form to enter information for the automation
- A button to start the automation
- A view to see the progress and eventually output of the automation
- A way to reset the form so the next automation can be started

In this blogpost I won’t cover all basic functionality of PowerApps. There are some very good resources for this already. I will show some of the techniques used which combined with basic knowledge should give your the tools to create this app too.
First off the form. this is a basic edit form generated from the data source. You can remove all fields not needed, so only the Name, IsNice and State will remain. For the state a default value can be given and then the field can be set hidden. This way once the automation is started a state will be instantly entered which will then be altered by the logic app once this triggers.
If you also want to alter the names of the appearances of the fields they will eventually show up as custom fields in the properties of the form.

We need to make sure the mode of the form is set to “New” so people can add new entries in the form.
Once the submit button is clicked we want to hide the form and show the output and state. In PowerApps the easiest way to control the visibility once actions are done is by setting the visibility to a variable. The variables have to be declared before the PowerApp is fully loaded so this is done in the Screen “onVisible” attribute.

NewForm(Form1);
Set(FormVisible,true);
Set(StateVisible,false);
Set(SubmitVisible, true);
Set(ButtonVisible, false);
Set(TimerGo, false);

This will set the Form to the New mode and it will Define 4 variables.
After this a button can be created to submit the form. This button can have the following “OnSelect” code.

SubmitForm(Form1);
Set(FormVisible, false);
Set(StateVisible, true);
Set(SubmitVisible, false);
Set(ButtonVisible, true);
Set(TimerGo, true);

This will submit the form data, so whatever is entered will be send to SharePoint and several of the variables are set to different values. Do note that in a production environment you probably want to have some data validation here too and always try to have as many fields with dropdown boxes or other ways to pick the right value instead of relying on manual entering of data.

After the form is submitted we made the Form invisible and the submit button is also made invisible. The State is set to visible. This state will consist of some textboxes. Two of them are simple labels for state and output. The other two have an expression like this:

First(SortByColumns(Presents, "ID", Descending)).Output

This expression will look for the newest item in the list and give the output back. (in case of the state the last value needs to be altered to state).
Do note that this solution will only work if no jobs are submitted after this. If you want to be sure you will keep seeing the right state and output you need to add your own kind of ID in the PowerApp form and send this too SharePoint too. You can store this ID and then search for it in the data source to get the right entry.

The next step is making sure the state and output are actually updated. PowerApps by default wont update values. So we need to create a system which will refresh the data source so it retrieves the new values. This can be done by adding a timer.
The timer can be set to an interval like 1 second and have the following code at the OnTimerEnd.

Refresh(Presents)

This will refresh the datasource. In the code for the button a variable was set already for TimerGo. This variable can be used to determine if the timer should run or not by entering it in the start attribute.

The last step of the logic app is to create a button which will be shown to reset the form. It can have this code:

NewForm(Form1);
Set(FormVisible,true);
Set(StateVisible,false);
Set(SubmitVisible, true);
Set(ButtonVisible, false);
Set(TimerGo, false);

This will reset the form again so a new automation can be started.
If everything is done this way a logic app like this can be made:

If you want you could even integrate the PowerApp in Microsoft Teams. There are build in functionalities to have PowerApps run in Teams.

Conclusion

Creating a front-end solution like this is not that complicated. There are many different tools which have nice connectors. You could also consider using a different platform like a self service portal in a ITSM tool to create the front-end, the possibilities are endless. Do keep in mind that when creating a solution like this you want to have a intermediate data source where all requests are logged, and you probably want your automation to pull information from there. This way there won’t be a direct push of information from the front-end solution to your back-end solutions. It also gives you time to do some more data validation. It’s even possible to add an approval step. For example you could work with two SharePoint lists, the first one is filled with the requests from your front-end. Once it’s in there a approval request is send to an approver and after it’s approved it will be moved to the second list where it’s processed. This will slow down the process but creates more control over it. It’s even possible to have it both where some types of requests are auto approved while others do need manual approval.
Do keep in mind that a solution like this is only as strong as the quality of the front-end. Especially because end-users will be using this. So make sure you do proper user testing in your development project before deploying it to the end-users.
One last thing to consider are the licensing plans for PowerApps. If you want to open up the automation to users outside your own organization these licenses can become complicated or very expensive. If you are only supplying users which already have Office 365 subscriptions to use services like SharePoint and other office services this shouldn’t add any costs.
I hope this blogpost has given your some inspiration on how to easily shift-left some of the automation by using tools like PowerApps.