It’s more popular than ever for organizations to move their file collaboration workload to SharePoint Online and OneDrive.
Many businesses are creating multiple SharePoint sites for Company divisions, departments, and projects. A huge influence on this current trend is the adoption of Microsoft Teams, which has increased the number of SharePoint sites dramatically in Office 365 tenants.
With this huge growth of SharePoint sites usage, IT Admins need tools that can help them manage SharePoint sites efficiently. Usually, PowerShell is the most effective tool for helping IT admins solve most of the automation and bulk operation tasks, but it requires experience, knowledge, and training.
In this article, I’ll demonstrate how to build a simple application that solves some of the SharePoint management tasks utilizing Power Automate and Power Apps. This approach will provide your IT support team a Reach user experience without writing a single line of PowerShell and still achieves the desired results.
Use Case for SharePoint Document Library Bulk Provisioning System
Contoso has over a dozen SharePoint sites, all of which have identical document libraries. Every document library must have identical columns to store additional file metadata.
In this article, we’re building an application with a simple user interface allowing you to create identical document libraries and columns in more than one site from a single interface.
Before we get started, here’s an example of how the final application will look.
Above, I’ve selected three sites and configured the document library settings, which include the library name and columns. Also, all three sites have one-click libraries calibrated with identical configurations.
This simple application is a good starting point and easily expandable according to your business needs.
What do you need to build this application?
- An Office 365 license that includes Sharepoint, Power Automate, and Power Apps for Office 365
- A user account that has rights to create Libraries and Lists in SharePoint sites
- And most importantly, a passion for automating manual processes
PowerApps and Power Automate
The application we’re creating consists of one Power App and two Power Automate flows.
- PowerApps: this is the user interface we’re utilizing to configure our document library deployment
- Power Automate:
- One to Validate the SharePoint site URL – it checks if the details entered are correct
- Another to create a Library, this loops through selected sites and creates a library according to the configuration in the Power App
Building the application
We’ll get started by building the user interface of the application.
- Launch https://powerapps.microsoft.com/ and sign in with your Office 365 credentials.
- From Power Apps home page, select the Canvas app from blank, type the Name of the application, and choose Tablet format.
Our app will have the following sections:
- An area to validate SharePoint site URLs
- A sites list: the list of the sites where the library will be created
- Column configuration: column’s name and column’s type
- Columns list: shows all the columns that will be created in the library
First, let’s build some visuals into our application. For this, we’re going to use Labels, Text inputs, and Buttons.
To create the labels with custom text and background fill color you need to:
- Insert a label and change two properties such as Text and Fill.
- Create your rectangles with a transparent background and blue border.
- Amend your buttons so they have custom text (Text: “Button Text”) and a dark blue background (Fill: RGBA(56, 96, 178, 1)).
At this point, these visuals don’t do anything; they’re just placeholders for us to use in upcoming steps. Please note, if you want to use formulas from this article without modifying them, I would recommend changing the name of the visuals. In this article, I’ll provide names in each step to make this clear. Below is the screenshot of the names of the objects used in the canvas.
At this step, make sure you look for a text input field called TextInputSPURL because this field will be used to validate SharePoint Site URLs.
The next step is to add two Gallery controls. One which will display all the sites we’re adding new libraries to, and the second to display all the columns we’re adding to the library.
Navigate to the top menu, select Gallery, and choose Blank vertical. Power Apps will ask you to select the data source, you can skip this step for now. I will name the first gallery as GalSiteList and the second GalColumnsList.
Place GalSiteList into the B1 section and GalColumnsList into the B2 section.
Set GalSiteList gallery to Title layout and GalColumnsList to “Title, subtitle and body”. The below screenshot demonstrates how I set up the GalColumnsList gallery.
Next, we need to add four text input controls and one dropdown to the Library and Columns Configuration section of the canvas.
- Library name (text input control) – TextInputNewLibrary
- Column type (drop-down control) – DropDownColumnType
- Internal column type (text input control: will be used in Power Automate) – TextInputTypeID
- Column name (text input control)-TextInputColumnName
- Description (text input control)-TextInputColumnDescription
In the above list, I’ve highlighted in bold the names for the controls I’ve chosen, which I can then later use in formulas. For a better user experience, add labels in front of each text input control.
Now we need to configure the text input controls according to your business requirements. First, select TextInputNewLibrary and in the HintText property type Enter Library Name. This will be useful for your users to identify the purpose of the text control.
Next, select DropdownColumnType from the drop-down list and in the Items property of the control, paste this value: [“-“,”Text Box”, “Date”,”Currency”].
Here, we’re configuring a drop-down with a list of options we’re allowing our users to create in the document library. I’m only adding three options here for simplicity.
Third, select TextInputTypeID field and in the Default property of the text input paste this formula:
If(DropdownColumnType.Selected.Value = “Text Box”,”2″, DropdownColumnType.Selected.Value = “Date”,”4″, DropdownColumnType.Selected.Value = “Currency”,”10″ )
What we’re doing here is setting an ID for each type of SharePoint column. This is how Microsoft identifies each type of column internally, and we’ll be using these IDs in Power Automate later in this article.
You can visit Microsoft’s documentation for a complete list of column types. If you look closer at the formula, we’re connecting two fields (DropdownColumnType and TextInputTypeID), basically, when users select Text Box in the drop-down, TextInputTypeID gets a value of “2” automatically.
What I do to prevent users from modifying the field is set the DisplayMode property of the field to Disabled. You can also hide the control by setting the Visibility property to False.
Next, we need to configure the buttons. First, select the button called Add to Sites List. The purpose of this button is to take whatever is in the TextInputSPURL and store the text value in the application’s internal collection for future use. Paste this formula into the OnSelect property of the button:
Collect(SitesList,{SiteURL:TextInputSPURL.Text}); Reset(TextInputSPURL);
Formula explanation:
We are using the Collect function to create a collection named SitesList with one column. A collection is a complex variable to store data in table format.
In our case, the SitesList table only has one column called SiteURL. The next function in the formula is Reset to clear the text input field after we added the URL to the collection.
To display the content of the collection, we will be using a gallery (GalSiteList) that we added earlier to the canvas. Select GalSiteList gallery and type SitesList into the Items property.
SitesList is the name of the collection that we just created. To clear the collection, add another button and paste this formula into OnSelect property:
Clear(SitesList);
If you set up everything correctly, the image below demonstrates how it should work.
Next, add two more buttons to the canvas and position them right under the description field in the Library and Columns Configuration section:
- Add to Queue (ButtonAddToQueue)
- Clear Queue (ButtonClearQueue)
These two buttons will play the role of adding columns to the queue and clearing the queue if necessary. To do this, paste the below formula into On Select property of the ButtonAddToQueue:
Collect(ColumnsList, { ColumnType:DropdownColumnType.Selected.Value, ColumnName:TextInputColumnName.Text, ColumnTypeID:TextInputTypeID.Text, ColumnDescription:TextInputColumnDesc.Text } ); Reset(DropdownColumnType); Reset(TextInputColumnDesc);Reset(TextInputTypeID);Reset(TextInputColumnName);
Formula explanation:
If we look at the formula closely, we can see that formula has two sections, one section is to create a collection with four columns (Column type, Column name, Column type ID, and Description), and the second section is to clear all these text input fields after column is added to the queue.
Paste formula below into the On Select property of the ButtonClearQueue button:
Clear(ColumnsList)
This formula clears the entire collection, where we store column names and column types.
To display items stored in the collection, we will use GalColumnsList that we created and placed on the canvas earlier in this article. Select GalColumnsList and paste the name of the collection (ColumnsList) into the Items property of the gallery. If you set everything correctly, this is how it should work:
Now we’ve built our user interface to configure the SharePoint library creation, next we’ll look at configuring the workflows.
In Part Two, we’ll create an automated workflow to provision document libraries in multiple sites using Power Automate. You’ll also learn how to convert Power Apps collections into a JSON object which is useful knowledge for other use cases you may have in your environment.
