In Part One, we built a core user interface using Power Apps for SharePoint document library provisioning. For the second half of this series, we’re going to build an automated workflow using Power Automate to provision a document library according to the user’s input in multiple SharePoint sites. You’ll also learn some useful techniques for converting Power App collections into a JSON object, which may help you to solve other tasks in your environment.
We’re creating two primary Flows in Power Automate:
- Validate SharePoint site URL
- Create a SharePoint library in all selected sites
Validate SharePoint URL
To make sure that we’re calling the actual SharePoint site, we’ll be using an HTTP call to the SharePoint site collections, we’ll then trigger Power Automate right from Power Apps.
To do this, select the Validate button (internal: ButtonValidate) that we placed on the canvas at the beginning of part one, select the Action tab and click Create a new flow.
You’ll be taken to flow.microsoft.com and there will be a list of templates, select the PowerApps button from the list of templates.
The Validate URL flow will consist of 4 simple steps:
- Run HTTP query using the URL typed by the user in the PowerApps
- Initialize an object variable and store HTTP response from Step 1
- Retrieve URL property from the object (Step 2)
- Return the URL to Power Apps for a comparison
Let’s break it down action-by-action.
1. In the first action, we’ll configure the HTTP query using the URL typed by the user and passed by Power Apps to Power Automate. Add a new Send HTTP request to SharePoint action to the Power Automate. For the Site Address, use dynamic content and choose Ask in PowerApps.
For the Method, select GET because we are querying data from SharePoint. For the Uri use _api/web/. There are different types of SharePoint REST service endpoint URIs, but in our case, we need site details, and we’ll be using _api/web. For more details read Microsoft’s documentation of REST service endpoints here. When using the SharePoint HTTP query, headers are usually the same for all scenarios. You can use the below:
Accept: application/JSON;oData=verbose Content-Type: application/JSON;oData=verbose
Below is the screenshot of how your step should look like. You can rename the step to GetURLObject.
2. The second action is to get the response from GetURLObject step and store in the object type variable. Click on Add new step and find the Initialize variable. Name the variable URLObject (you can name your variable the way you want), select Type Object and for the value use dynamic content picker and select the Body from the GetURLObject step.
3. The content stored in the variable is a complex Object. In the next step, we’ll have to retrieve the URL property from the Object. To add a new step to the Power Automate, search for Compose (Data Operations), and then add. For the Inputs use expressions and paste the below line of Expression. When copying this, make sure to name your variable URLObject in the previous step.
variables(‘URLObject’)?[‘d’]?[‘URL’]
4. The last step is to return the URL value to the Power Apps. Add a new step and search for Respond to Power Apps or Flow action. Add the action and the output. The output will be plain text (we will call it URL), and the output will be the content from Compose URL step.
That was the last step in our Validate Site URL Flow. Click Save and return to Power Apps.
In Power Apps, select the Validate button again, and from the Action tab, associate new Validate Site URL Flow with that button. Enter the below formula to OnSelect property of the button:
UpdateContext({URLValue: ValidateSiteURL.Run(TextInputSPURL.Text)})
Formula Explanation:
UpdateContext({LocalVariableName: }) – this formula creates a local Power Apps variable to store the outcome of the Validate Site URL Flow. Where the local variable name is URLValue.
ValidateSiteURL.Run(input value) – this one we’re calling the ValidateSiteURL Flow, it passes text value from the TextInputSPURL text box from the PowerApps. This is the value used in Flow when we configured as Ask in PowerApps.
Now we want to show our user if the URL entered in the TexyInputSPURL matches what was returned from the flow. Here, you need to insert a circle icon to the canvas and paste this formula to the Fill property of that circle:
If(!IsBlank(TextInputSPURL) And TextInputSPURL.Text=URLValue.url, Green, Gray)
Below, we’re checking if the text input field is not blank, and the text in the text field is equal to the value stored in the URL value variable (which stores the outcome of the Flow), then Fill=Green, otherwise Fill=Gray.
Then, I’ll enter the Non-SharePoint URL first and validate it. When I’m happy with the validation results, I’ll enter the SharePoint site URL and validate that too. If the URL entered is equal to the SharePoint site URL, we will get a green circle icon.
Create a SharePoint library in all selected sites
Earlier in the article, we created a process to collect the list of SharePoint site URLs and a collection of columns for the library. The workflow will take the first site URL and loops through the columns collection to create them; then it will take the second site URL and loops through columns again. This process will continue until all site URLs are covered. The diagram below is a graphical representation of the process.
Using the Power Apps user interface we created earlier in this article, we need to add multiple site URLs to the collection. Here, you mustn’t forget to validate the URLs and to make sure that all URLs are SharePoint sites, then you can create a collection of the columns.
For the columns, create one for text, date, and currency field. Now, as both collections are ready, select the Create Library button (internal name: ButtonCreateLibrary), navigate to the Action tab, and select Flows again. Click on Create new flow button in the button associated flows blade. From the templates list, select the PowerApps button Flow template. We’ll be asking flow in PowerApps for three key pieces:
- Site address (collection)
- Library name (text field)
- Columns (collection)
We’ll store each of the above in the variables. Here you need to add a new step to your Flow and select Initialize variable. Name the variable SiteName, select String for the Type, and choose Ask PowerApps for the Value.
You’re probably thinking, why are we selecting string for the variable type when we are passing a collection (object)? This is because at the time of writing there is no native way to pass a PowerApp collection to the Flow. What we will do is to convert Collection to JSON object in the PowerApp and pass it to the string variable in the Flow.
Add another step, again select Initialize Variable. Name the variable LibraryName, select String Type, and choose Ask PowerApps for the value. Repeat the same steps for the Column variable. You should have something similar.
The next two steps in the Flow will be parsing these two JSON objects and getting actual values (Site URLs, Column Names, and Types).
Parse JSON action requires two values, Content, which is a JSON object (we will get it from Power Apps), and Schema. Schema is a JSON code that will recognize the structure of the JSON object. How do we get the schema? Before adding Parse JSON steps, let’s save our Flow and return to Power Apps.
Select Create Library button in PowerApps, and from the Action tab choose Flows. From the list of Flows, select the one you’ve just saved. In my case, the Flow name is CreateLibraryFlow. In the Flow, we’re asking for three pieces of Information from PowerApps, SiteName, LibraryName, and Columns in that specific order.
This is how we’re going to pass values from PowerApps. When it’s added, connect the Flow to the button, Power Apps will ask to pass those values in OnSelect property of the button.
Now we need to pass the values we’ve discussed. To convert Collection into the JSON object, we’re going to use the Power App’s JSON function. Paste this formula into the OnSelect property of the button.
CreateLibraryFlow.Run(JSON(SitesList),TextInputNewLibrary.Text, JSON(ColumnsList));
Formula explanation
CreateLibraryFlow.Run – We are calling our Flow CreateLibraryFlow
FlowName.RUN(Value2, Value2, Value 3) – Passing three values that we configured in the Flow to ask in PowerApps
JSON (SiteList) and JSON(ColumnsList) – Using JSON function to pass PowerApps collections. SiteList is a collection of all sites and ColumnList is the collection of columns.
TextInputNewLibrary.Text – The value of the text input, which is the Library name.
Now, to get the Schema for the JSON actions for our next steps in the Flow, we’ll have to click the Create Library button to run our partially configured Flow.
Note: Make sure that your SitesList and ColumnsList collections are not empty.
After clicking Create Library button, navigate to Power Automate and open the last run of the Flow.
Expand the SiteName and Columns steps and copy Value data from each step to a text file for the reference.
Next, edit the Flow and add the Parse JSON step. The Parse JSON step requires two parameters, the JSON Object (Content), and Schema. The JSON object (Content) will be the variable SiteName that stores JSON object that we passed from Power Apps.
To create JSON Schema, click on Use Simple payload to generate schema link and past value of the returned value inside of Initialize variable step that we copied earlier.
After clicking Done, the schema will be generated.
Repeat the above steps for Column’s JSON object. Add parse JSON step, use Columns variable as content, and generate the schema using data from the test run.
We’ll be looping through all the sites, creating the library and then looping through all columns and creating them in the existing library. This will be representative of a loop inside the loop. In this context, let’s call them a parent and child loop.
Add a new step and search for Apply to each. For the parent’s loop, Select the output from the previous step, which is a Body of Parse JSON- Site Names object. Imagine that we passed the list of all site URLs, now we have to take each URL and create a library. To do that Add another step, search for Compose and get the Item from the JSON object by adding this line of code.
item()[‘SiteURL’]
If you’re wondering how did I get the ‘SiteURL’ parameter, look at your JSON schema, and you’ll find all the parameters there.
The next step is to create a library. Add new Send an HTTP request to the SharePoint step, which requires the following information:
Site Address: we will pass data from previous Compose action
Method: POST
URI: _api/lists
Headers –
Accept: application/JSON;oData=verbose
Content-Type: application/JSON;oData=verbose
Body:
{‘__metadata’: {‘type’: ‘SP.List’},
‘AllowContentTypes’: true,
‘BaseTemplate’: 101,
‘ContentTypesEnabled’: true,
‘Description’: ‘My List Description’,
‘Title’: ‘@{variables(‘LibraryName’)}’}
The Body section has a few important parts I want to highlight. Firstly, with BaseTemplate:101, there are different SPListTemplateType that exist, and 101 is the document library. For the full list, you can visit Microsoft’s SPListTemplateRType Enum page. The next is Title, we’re using Variable Library name that we collected from Power Apps.
Now the Library creation step is done; the next step is to get through columns and create them in the library. Inside of the parent loop (apply to each), create another Apply to each step, and Select an output from the previous step should be the JSON object that we created earlier.
Inside this loop, add another Send an HTTP request to SharePoint step and configure it following the below information:
Site address: use the same Compose step with the site URL that we created earlier.
Method: POST
Uri: _api/web/lists/GetByTitle(‘@{variables(‘LibraryName’)}’)/Fields
Headers –
Accept: application/JSON;oData=verbose
Content-Type: application/JSON;oData=verbose
Body:
{‘__metadata’: {‘type’: ‘SP.Field’},
‘Title’: ‘EXPRESSION HERE’,
‘Description’: ‘EXPRESSION HERE’,
‘FieldTypeKind’:’EXPRESSION HERE’,
‘Required’: true,
‘Hidden’: false}
Note: here, we will have to add custom expression to the area highlighted in green. You will have to place the cursor between single quotation marks and configure each property.
Title : item()[‘ColumnName’]
Description: item()[‘ColumnDescription’]
FieldTypeKind: item()[‘ColumnTypeID’]
And that was the final step! You have now created an application for building your own custom SharePoint Document library bulk provisioning system. Our Flow is ready to accept requests from the Power Apps and manage bulk library creation. You can now utilize the solution for daily business tasks for seamless bulk SharePoint document library deployment. To conclude, I would like to say that the Microsoft PowerPlatform empowers IT Pros to do more and stress less.
