PowerApps Custom Connector to enumerate SharePoint Online sites

Component Architecture

Introduction

With the introduction Power Platform, business users (a.k.a. power users) can now develop apps and automation solution with some basic programming concepts using a no-code/low-code platform. Power Platform is known to have built-in connectors for various Microsoft and non-Microsoft service workloads, both on Cloud and On-prem, that widens up the reach for the Power Users to try their hands in building applications using a wide variety of technology stacks.

But there are still some grey areas where built-in connectors are not available and we have to count on RESTful APIs that are exposed to be consumed, considering certain factors like, authentication mode, request header and body etc. Here lies the essence of custom connectors, which can be leveraged across all components (like PowerApps, Power Automate, Power BI, Power Virtual Agents) in a Power Platform environment, for achieving those integrations, data ingestions, CRUD operations etc. where built-in connectors fall short.

Objective

Enumerating SharePoint Online sites in Microsoft 365 tenant is a frequent requirement in various applications being developed encompassing Microsoft collaboration on cloud solution. Conventionally, developers have used PowerShell, C# and client-side scripting in achieving this. But what about Power Platform? Do we have suitable actions in SharePoint connector? These are the questions started making rounds in my head, when one of my recent client requirements needed this and I eventually got bewildered to learn that we do not have a built-in connector for this, along with several other frequent and complex operations we do on SharePoint Online.

I ended up building a custom connector that calls Microsoft Graph APIs, exposed by SharePoint Online. This article is a step-by-step walkthrough that will help you achieve what I did in no time. You can then use the custom connector in all the components in Power Platform.

Prerequisites

1. You need a minimum Power Platform Environment Role “Environment Maker” (see https://docs.microsoft.com/en-us/power-platform/admin/database-security to learn more on Power Platform Environment user security)
2. You need an Azure AD App Registration (see https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal to learn how to create Azure AD Application and service principals). For creating this, you can make use of your free version of Azure AD that comes with your Microsoft 365 plan, as this is not a premium feature.
3. Knowledge in Power Platform (see https://docs.microsoft.com/en-us/learn/paths/power-plat-fundamentals/ to learn more)
4. Knowledge in RESTful APIs
5. Knowledge in Microsoft Graph APIs (see https://docs.microsoft.com/en-us/graph/overview to learn more)

Steps

The steps below are grouped under relevant sections (two broad categories "Azure AD App Permission" and "PowerApps Studio"), highlighted and number marked, that signifies the sequence of actions to be taken to achieve the objective (sated above).

Azure AD App Permission

Your Azure AD App registration details, as follows, need to be captured in this step for later use.

Web Redirect URL 

Go to Azure portal (https://portal.azure.com/) > Azure Active Directory > App registrations. Then click open your app registration. Finally 

1. Click on “Authentication” (see Fig. 1).

2. Then under “Web” > “Redirect URIs”, you have the URL to copy (Fig. 1). Save it somewhere, as you will need it later steps.

Fig. 1

Client ID

In your App registration click on “Overview” (# 1 in Fig. 2) to get the Application (client) ID as shown below. Click on the “Copy to clipboard” button (# 2 in Fig. 2) to get it copied. Save it somewhere, as you will need it later steps.

Fig. 2

Client Secret  

Click on “Certificates & secrets” (# 1 in Fig. 3) and then “Value” under “Client Secrets” (# 2 in Fig. 3). Copy and save it somewhere so that you can use it later steps.

Fig. 3

API Permissions

Finally under “API permissions” (# 1 in Fig. 4), select the permission as shown in point # 2 in Fig. 4. And then you must “Grant admin consent for <your organization name>”, as shown in point # 3 in Fig. 4.

Fig. 4

PowerApps Studio

Go to PowerApps Studio > “Custom Connectors” (# 1 in Fig. 5) under “Data”. Click on “New custom connector” (# 2 in Fig. 5) and then “Create from blank” (# 3 in Fig. 5).

Fig. 5

Give a name to your connector as shown in Fig. 6 and click on “Continue”.

Fig. 6

Then, in the following screens configure each tab (i.e., “General”, “Security”, Definition”, “Code” and “Test”)

General Tab

1.       Upload connector icon – Select a suitable image from your local file directories.

Fig. 7

2.       Configure “Host” and “Scheme” values as shown below in Fig. 8.

Fig. 8

Security Tab

Authentication Type - Select value as “OAuth 2.0” as shown in Fig. 9. 

Fig. 9

Configure “OAuth 2.0” as shown in Fig. 10.

Get Client ID and Secret from Azure AD App Registration step (see above). The rest of the configurations can be found from the below screenshots (Fig. 10 & 11). Keep all the values exactly same as the screenshots.

Fig. 10

Fig. 11

Definition Tab

Firstly, click on “New action” as shown below to create your connector’s action. Note: You can add multiple actions in your connector by repeating the below steps for every action you add.

Fig. 12

Then, set name, description and Operation ID as follows.

Fig. 13

The configure “Request” details as follows -

• First click on “+ Import from sample” (# 1 in Fig. 14).
• Then, select “Get” as “Verb”. You could select other verbs for CRUD operations. Here, our goals is to get all sites in your tenant. So it is read operation that can be achieved by “Get” verb.

 

Fig. 14

• URL: https://graph.microsoft.com/v1.0/sites?search=*&$select=displayName,webUrl,name (# 3 in the above screenshot) for this use case is given below. This RESTful MS Graph API Url will return all sites in the SharePoint Online tenant with metadata as Site URL, Site Display Name and Site Internal Name.
• Finally click on “Import” button.

Then, configure “Response” details as shown below. Click on “default” and then on “Import from sample” as shown in below screenshots (Fig. 16 and 17).

Fig. 15

Fig. 16

Then, fill up the “Headers” and “Body” as shown in the below screenshot (Fig. 17) with the values retrieved from Graph Explorer (see Fig. 18 and Fig. 19)

Fig. 17

The “Body” in the above screenshot can be retrieved from Graph Explorer > Response preview as shown in the below screenshot (Fig. 18):

Fig. 18

The “Headers” in Fig. 17 can be retrieved from Graph Explorer > Response headers as shown in the below screenshot (Fig. 19): 

Fig. 19

Code Tab

Fig. 20

As shown in the above screenshot, keep “Code Disabled”.

Test Tab

Fig. 21

Do not forget to click on “Create Connector” before being able to test the connector, that you just created. 

Calling connector from PowerApps canvas app

Finally, to make use of the custom connector you created in the above step, follow the below steps:

• Open PowerApps Studio from https://make.powerapps.com/
• Click “Apps” in the left navigation bar.
• Click on “New app” and select “Canvas”.
• Provide a name of your app in the “App name” textbox and select one of the formats from “Tablet” or “Phone”.
• Follow the numbered steps as shown in the below screenshot (Fig. 22)

Fig. 22       

• Add a “Drop down” control from “Input” group of controls in your left toolbox.
•  Set the “Items” with the formula as shown in below screenshot (Fig. 23) and save your app.

Fig. 23

• Finally preview your app to see the site display names being enlisted when you clik on the dropdown control as shown below in Fig. 24.

Fig. 24

Conclusion

If you have reached here, that means you have successfully created a custom connector that enlists all SharePoint Online sites in a Microsoft 365 tenant and added a formula to bind them in a PowerApps app.

See you in my next article. 😊