After multiple sessions about extending PowerApps with custom code I had during the past six months, a lot of people have asked me if I could write it all down. Since PowerApps are declared by Microsoft as a successor of InfoPath (without 1:1 parity, though), this blog post series will be targeted mainly to SharePoint developers, but also to everyone else, who wants to simulate things like custom functions, custom data sources, and code behind – things which were well known in InfoPath, but are nonexistent in PowerApps.
So, there it is, a short series of 4-5 tutorial like blog posts on extending PowerApps and Flow. I’ll cover adding custom data sources via Azure API Apps, adding custom functionality via Azure Functions, exchanging data between PowerApps, Flow and other systems via Azure Service Bus, and extending Flow with custom actions. Part 1 is about using Azure API Apps for adding wrappers around custom data sources, and using it in PowerApps.
The case
It is very often a case, that you need to integrate data from legacy applications and/or data sources, with modern application platforms, such as Azure and Office 365. It might not even be a legacy data, a lonely database on some forgotten server, is sometimes sufficient to give you a headache. PowerApps offer a really cool possibility to access such data sources through Web APIs, deployed as Azure API Apps. In our demo use case here, we have a SQL Server database with a datatable of beers, which we would like to manage through PowerApps.
The same API App can also be used for extending PowerApps expression language, but we will cover that in the next blog post.
So how do we proceed? First, we are going to build a restful Web API, as a wrapper around that database table. Then, we are going to deploy it to Azure, as an API App, with swagger interface enabled. After that, we are going to import that swagger definition into PowerApps, and to build an app around it.
So, let’s start
The first thing we are going to do, is to start the Visual Studio, and to create a new Api APP. In order to do that, create a new Web Project, and select Azure API App.
Since the main purpose of our API App will be to make a restful service wrapper around a database, let’s add some data to it. I will choose here ADO.NET Entity Data model and Code First from Database, but it can really be anything. I am going to call it BeerDataModel.
So let’s use my beers database. 
I am hosting it in Azure, but it can be really anywhere.
I will need to create a connection string in Visual Studio…
…and to choose table(s) which I want to publish through this restful API. In this case, we will keep it simple, and take only one table – “allbeers”.
Once we have added the data model to our service, we need to add some controllers to wrap that data. In my case, Scaffolded Item will do fine:
I will say that it is Web API 2 Controller with actions around Entity Framework data:
Visual Studio will be nice enough, to immediately offer me model and data context classes.
Once when it is all done, my project looks like this: Model Class, Data Context class, my new controller, and default controller (“ValuesController”) which was created initially with the project. We can delete that one, since we will not need it.
Now, we need to enable swagger UI. Swagger is service definition format, used for restful services, much as WSDL was used with SOAP. Visual Studio offers us instant enabling of swagger UI in the SwaggerConfig.cs file in App_Start. In short, you can just uncomment the .EnableSwaggerUi part. But, it is worthwhile to take look at that file, much of the how swagger works with your service can be understood from there.
Once it has been done, you can actually just press F5 and look the magic happen. Since we didn’t define any startup document, IIS Express will just return 403 error. No problem, since we have the swagger interface enabled, let’s just append “/swagger” to your service url, and voila… There is your service! With service description, and options to test all the methods.
For example, click on GET allbeers operation, and then click on “Try it out!” – you’ll get all your beers as bunch of JSON.
The next we need to do, is to deploy this Web API it as an API App. This is a fairly simple process form Visual Studio, you will need to select or create new Azure web application, and to publish it as an App Service.
Now let’s go to our Azure management portal, and check if the API App has been properly created:
Also, test it through the swagger UI of the deployed API:
Once you are sure it all works, open the swagger API definition from swagger UI, and save it is a JSON file:
Once you have done that, go to the PowerApps portal, in the connections section, and click on add new connection, New Custom API.
In the dialog that you will get after it, you will have to name your custom API, to upload the swagger API definition and icon.
We will talk about authentication a bit later, but for the moment, in the authentication dialog, choose the only possible option – No authentication.
And there is your new custom API! Now, let’s create a PowerApp from it.
In the early version of PowerApps, when the Enterprise plan was still available, it was possible to use a wizard generate an app from the custom API. That feature hit the dust at some point of time, and it seems that it is not coming back. So, you will have to wire the controls to your custom data source manually. Which is not difficult, but not something you would let your power users (intended audience here) do. A practical workaround is to create a PowerApp from any data source, and then to change wirings manually. In my case, I created a dummy PowerApp from an existing SharePoint list, and through the Data sources dialog in PowerApps app for Windows 10, added my custom API as a new connection:
In the screens that PowerApps wizard created for me, I just changed the existing item sources with my RESTful API methods (get all, get one, put, post, delete), and I was good to go:
That’s it – this is your PowerApp, which is using custom API as a data source! Pretty cool, huh? Well…
Security
…well, only partly. As you have probably noticed, there is zero security around this API, and in this App. When we already speak about the features which have bit the dust, passing authentication tokens to API Apps was one of them. That “Authentication” dialog was not always empty – “Azure Active Directory” was there earlier. When that option was selected, and when Azure API App’s authentication was set to AAD (screenshot below), PowerApp (actually, an AAD App created in background) was passing tokens to API App, and user was properly impersonated.
Now, if you try to turn the App Service Authentication On, and to configure AAD authentication, PowerApps will fail with bad response message (Service returns 401 – unauthorized). This would be my plea to PowerApps team to bring back the AAD authentication (this is a must), and to implement a key authentication (this would also be really cool), otherwise all of this is only partly usable – there are no that many data sources that can remain unprotected.
So much for now, in the next part, we will see how to reuse newly created API App to extend PowerApps’ expression language, by adding some custom controllers.
