Power Apps in SharePoint

In this Power Apps Tutorial, we will discuss what is Power Apps in SharePoint. Also, we will cover the below topics that are related to the Power Apps SharePoint:

• Power Apps SharePoint connector
• Power Apps SharePoint list integration
• Power Apps SharePoint image column
• Power Apps SharePoint yes/no field
• Power Apps SharePoint remove
• Power Apps SharePoint remove If
• Power Apps SharePoint required field
• Power Apps SharePoint title field required
• Power Apps SharePoint refresh
• Power Apps SharePoint thisitem
• Power Apps SharePoint web part
• Power Apps SharePoint user profile
• Power Apps SharePoint integration of multiple forms
• Power Apps SharePoint web part size
• Power Apps SharePoint auto-increment id
• Power Apps SharePoint list 5000 items or row limit

Power Apps SharePoint Connector

SharePoint allows businesses to share and communicate with their employees, partners, and customers. You can use the On-Premises Data Gateway to manage documents and list items to connect to SharePoint Online or an on-premises SharePoint 2013 or 2016 farm.

There are various types of connectors available in Power Apps. Of them, one is the SharePoint connector that allows you to connect a canvas app with SharePoint Online sites, or on-premises SharePoint farms using the on-premises data gateway.

Data type mappings in Power Apps SharePoint

The below table represents about the data type mappings in Power Apps SharePoint.

SharePoint	Power Apps
Yes/No	Boolean
Single line of text, Multiple lines of text, Calculated column	Text
Date and Time	DateTime
Image	Image
Choice column, Lookup, Person or Group, Metadata, Task Outcome, External Data,	Complex
Number, ID, Currency	Number

Find the SharePoint Connector on Power Apps

We can find the SharePoint connectors on the Power Apps, the following steps are:

• Sign in to the Power Apps.
• On Power Apps studio, go to Data (on the left panel).
• Click on +Add Data.
• Expand the Connectors under the Data sources.
• Scroll down or search ‘SharePoint’ on the search bar to find the SharePoint connector.

This is how to find the SharePoint connector on Power Apps.

Also, read: Power Apps Gallery Filter [With Real Examples]

Power Apps SharePoint list integration

Here we will see how to connect a SharePoint list to Power Apps. For example, we have a SharePoint list having some columns such as:

Columns	Type
Title	Single line of text
Types	Choice
Price	Currency
OrderDate	Date and Time

Now we will connect this SharePoint list to a canvas app on Power Apps.

• After finding the connector option, select the data sources.
• Choose your Sharepoint site.
• It will show all the lists that the site contains.
• Choose the SharePoint list i.e. Product.
• Connect.

It will connect to the app. We can see the SharePoint list in the Data section.

Now the SharePoint list is connected to Power Apps. We can create an app using the SharePoint list. To do this, we have another post where we have discussed building an app using the SharePoint list on Power Apps.

This is how to do SharePoint list integration on Power Apps.

Power Apps SharePoint image column

Here we will see how to show an image on Power Apps that comes from the SharePoint image column.

For example, we are going to the previous SharePoint list (i.e. Product) including an image column (type- image) and having some random data.

Now we will show this item in our canvas app on Power Apps with an image.

• Open the Power Apps studio.
• Create a blank Canvas App.
• On Power App studio, go to Insert tab > Gallery > Vertical.
• Click on the Vertical gallery to add it to the default screen (i.e. Screen1).
• Set the properties (on the right panel), such as:
  • Data Source – Product

We can see it will show the items in the gallery below:

But initially, the item’s image will not appear properly. To display the image, click on the image and set the image properties as below:

Image = ThisItem.Image

This is how to show an image column from the SharePoint list in the Power Apps gallery or form.

Also, read: Power Apps Image Control – How to use

Power Apps SharePoint Yes/No field or Boolean

Here we will see how to work with SharePoint Yes/No field in Power Apps. Also, we will see how to filter this field to show the data. For example, have a simple SharePoint list called ‘Product’:

Here the ‘Delivered‘ is the yes/No column. Now we will connect this SharePoint list to the Power Apps. The following steps are:

• Sign in to the Power Apps.
• Fetch the SharePoint list to the Power Apps. (Follow the previous example to do this)
• On the default screen (Screen1), add labels (4 labels) where 1 is for the header, and another 3 is to hold the header of the Product’s name, order date and delivered column.

• Then add a blank gallery to Screen1. Go to Insert tab > Gallery > Blank flexible height.

• Select the gallery, then go to the properties. Set the data source as Product.
• Then add some 3 labels to the gallery. For title, Order Date, and Delivered. The chart will look like the below:

• On Power Power Apps, the SharePoint Yes/No column represents a boolean (i.e. True for Yes and False for No).
• Now we will add the below function to the gallery. That will filter the result as per the value insert. Select the gallery and update the ‘Items’ function as

Items - Filter(Product, Delivered = false)

Similarly, if we set the function as ‘True’ then only the ‘true’ value i.e. the delivered items will display.

This is how to work with the SharePoint yes/no column or Boolean in Power Apps.

Power Apps SharePoint Remove

Here we will see, how to remove SharePoint items from Power Apps. We are going to use the above Sharepoint list with added some extra items to it.

Let’s move to the Power Apps where we will operate the ‘Remove’ function. In Microsoft Power Apps, use the ‘Remove’ function to remove and delete a specific record or records from a data source. To work with this function, the following steps are:

• On Power Apps studio, add a new screen (Blank) or you can use the default screen.
• Go to the Insert tab.
• Select the Data table.
• Set the Data sources i.e. Product on the properties (left panel).
• Click on the Edit field to add the fields to the data table.

• Add a button to this screen and place it under the table. Click on the Insert tab > Button.
• On the button’s properties, give a text to the button as ‘Remove‘.
• On the button’s ‘OnSelect‘ property set the function as below:

OnSelect = Remove(Product,Filter(Product,Title = "Laptop"));

Where, Product – Data Source Name, Title – SharePoint list column name, Laptop – The product which we want to filter.

As per the above expression, it will filter the data source or table and remove all the items whose title is equal to Laptop.

Now save the app and publish it. Then play this app and click on the Remove button. We can see the items whose title is equal to laptop have been removed from the data table.

Also, we can see the item got removed from the SharePoint list as well.

This is how to remove the item from SharePoint via the Power Apps button click.

Power Apps SharePoint RemoveIf

Similarly, there is another function ‘RemoveIf‘ is used to remove and delete a record or records depending on a condition in Power Apps.

Here we are going to use the previous SharePoint list i.e. ‘Product‘.

As per our requirement, we want to remove the item whose price is less than $50 using Power Apps. To implement this, the steps are:

• Go to Power Apps, and follow the above step to add these SharePoint items to the data table on Power Apps.
• Add a button to the screen and Named it ‘Remove‘.
• Click on the button and set the below function ‘OnSuccess‘ properties as below:

RemoveIf(Product,Price<50);

Now save the app and publish it. Play this app and click on the ‘Remove‘ button. We can see the data got removed whose price is less than 50 from both Power Apps and SharePoint lists.

This is how to remove the SharePoint items based on condition using the Power Apps button.

Power Apps SharePoint required field

Here we will see how to work with the SharePoint required field on Power Apps. We have a SharePoint list named ‘Voters list‘.

Let’s make a field as required or a mandatory field i.e. Address on the SharePoint list. We can convert any field as required in both platforms i.e. SharePoint and Power Apps.

First, we will see how to mark the Address field as required in the SharePoint list.

• Once the column created, go to list setting > columns > Address > Require that this column contains information > Yes > OK.

Or,

• On SharePoint list, click on the Address column > Column Setting > Edit > More Options > Require that this column contains information > Yes > Save.

Also, we can work with this required field on Power Apps and the following steps are:

• On Power Apps studio, add an ‘edit form‘ on Screen1 or the default screen.
• Connect it to the Data source.
• Add the fields from ‘Edit fields‘.

Note –We can see there is an asterisk (‘*‘) symbol, on the Address field as we made this field mandatory.

Note– Make sure to set the Form’s default mode as New.

Also, we can make any data field ‘Required‘ in Power Apps. For example, let us make the Age field mandatory. To do this, the following steps are:

• On that ‘edit from‘, select the Age data card.
• Go to Advanced (left panel).
• Click on the lock icon to unlock the Advanced (Next to the properties).

• Set the Required field as ‘true’.

• Now, we can see an asterisk symbol on the Age field.
• Click on the button and add the below function that will submit the form as well as display a new form.

SubmitForm(Form1); NewForm(Form1); ResetForm(Form1)

Now save and publish the app. Click on Play and fill in the data. Here we will not add any data to the Age or Address field. Click on the Button. We can see an error message will appear on the header of the form (like below) notifying us to fill the required field.

This is how to Make SharePoint Field Required with PowerApps.

Power Apps SharePoint title field required

Similarly, here we will see how to work with the SharePoint title field on Power Automate. By default, the title field is set as ‘Required‘ on each list.

But we can customize it as per our requirement and work with Power Apps. For example, we have a simple SharePoint list named ‘Cars’.

By default, the title is a required field. It allows the user to customize this field and remove the required option from this.

To do this, go to list setting > Column section > Click on the Title field > Require that this column contains information > Yes > OK.

Click on No to remove the requirement from this field. When we use this list as a data source on the Power Apps, we can see a mini asterisk symbol on the title field.

This is how to use the SharePoint title as a mandatory field on Power Apps.

Power Apps SharePoint Refresh

Why do we need to refresh the data? The main reason is that we want to receive the most recent updates from our data source. We may need to modify our data sources on time.

There is an option to refresh the data source on the Power Apps. Here we are going to use the previous SharePoint list ‘BikeSells‘. For example, we need to rename the column name from ‘Quantity’ to ‘Qty’ on the SharePoint list for some business requirements.

To update the column name on the Power Apps, the following steps are:

• On Power Apps studio, go to Data.
• Click on the More actions (…).
• Hit the Refresh.

Now, we can see the field name has been changed on the Power Apps edit form.

Apart from this, there is a function named ‘Refresh‘ that will refresh and reload the data sources with the current changes. To implement this, the following steps are:

• On Power Apps, add another blank screen.
• On that screen, add a data table (under the Insert tab).
• Choose the data source i.e. BikeSells on the left panel.
• Remove or add the data fields on the Edit field.

• Add a Reload icon to the data table. Go to the Insert tab > Icons > Reload Icon.
• Click on the icon and set the below function on the icon’s OnSelect property:

OnSelect = Refresh(BikeSells)

Now save the app and publish it. Play this app. Let us update an item in the SharePoint list. For example, we will change the Enfield 350‘s qty 15 to 22.

Back to Power Apps, click on the refresh icon. We can see the data got refreshed according to the list.

This is how to refresh a Data source’s structure in PowerApps using the refresh function.

Power Apps SharePoint thisitem

In Power Apps, the ‘thisitem’ function is used to show the latest record’s current item in a Gallery or form control.

For example, we have prepared a SharePoint list named ‘Employees‘.

Now we will fetch this data to the Power Apps gallery. Where we will see how the ‘thisitem‘ function works.

• On Power Apps studio, add a new screen or you can use the default screen.
• Go to the Insert tab.
• Click on gallery > Select Vertical.
• Set the data sources as ‘Employees‘.

By default, it will show the details of the items on the gallery-like below:

On the gallery control, the item property is set as ‘Employee’ which is a data source. The gallery’s primary item is a template, which is duplicated for each employee. The formula in the template refers to the current item as ‘ThisItem‘.

We can see the formula, by clicking on the image:

Similarly, when we click on the title label, we can see it will redirect to the title field of the SharePoint list for each item.

Let us do some modifications to the subtitle of the gallery using the ‘thisItem’ function. The above gallery, it is showing only the first name of the employee; but we want to show the full name of the employee. To do this, follow the below steps:

• Select the ‘Subtitle‘, under the Gallery control.
• Modify the ‘Text‘ properties as per the below function:

Text = ThisItem.'First Name' & " " & ThisItem.'Last Name'

This is how to use the Power Apps ‘ThisItem’ function on the SharePoint list.

Power Apps SharePoint web part

Here we will see how to embed a Power Apps form on the SharePoint modern page using a web part. There is a web part ‘Microsoft Power Apps’ that is used to embed the form on the SharePoint modern page.

Let us have a look at some features of using this web part.

• Users can immediately be signed into PowerApps when you use the Web portion.
• The Web part automatically adjusts the application to the best possible size in the canvas.
• The resizer control allows the user to resize the app.
• Also compatible with the SharePoint mobile app.

Below we will describe a step-by-step guide to embedding a form on SharePoint:

• In our previous post, we build a Power Apps form from which users can input the data and store them in the SharePoint list. First, follow this blog and create an app on Power Apps (i.e. Guest Registration Form).
• After creation, save the app and publish it.
• On Power Apps, go to Apps > select the App > More commands > Details.

• Copy the App ID which is used to embed the form on the SharePoint modern page.

Move to the SharePoint site. On the Home page, click on +New > Page > Blank > Create Page.

Give a name to the page (ex- Guest Registration Form) and click on the + symbol to add the web part to the page. Search ‘Microsoft Power Apps‘ on the search bar.

Select the web part. Once the web part is added, on the right panel paste the App ID that we have copied from the Power Apps.

Also, it allows us to embed the Power Apps via the Web link that we got from the Power Apps. Click on Publish to publish the page. Now, the user can operate the form or app directly from the SharePoint site.

This is how to embed Power Apps on the SharePoint web part.

Power Apps SharePoint user profile

Here, we will see how to show the user profile from SharePoint to Power Apps. For example, we have a SharePoint list named ‘Task list’.

Now we will see how to show the user’s profile of the respective user on Power Apps. To do this, the following steps are:

• Create a blank canvas app.
• On the default screen, add a gallery. Go to the Insert tab > Gallery > Vertical.
• Add the data source to it i.e. Tasklist.
• Add a label to give a look to the gallery.

To retrieve the user profile, we will add another connector to the gallery i.e. ‘Office365Users‘.

Click on Data (on the left pannel) > +Add data > Office365Users.

To show the respective user profile, we will add the below expression on the ‘items’ property of the gallery.

Items= AddColumns(
      'Task list',
      "UserPhoto",
      If(
        !IsBlank('Assigned To'.Email)
        And Office365Users.UserPhotoMetadata('Assigned To'.Email).HasPhoto,
        Office365Users.UserPhotoV2('Assigned To'.Email)
      )
    )

Where,

• Task list – Data Source
• UserPhoto– A new column to add the photos
• Assigned To – The people column name from SharePoint

Note – Some users in the company will not have a photo, and attempting to download a missing photo will result in a Power Apps runtime error. To avoid this, we can use the Office365Users connector’s UserPhotoMetadata action to only retrieve photos for users who have them

Select the image control on the gallery, and insert the below expression.

ThisItem.UserPhoto

This is how to display a user photo from the SharePoint list on Power Apps.

Power Apps SharePoint integration of multiple forms

Here we will see how to integrate multiple forms on Power Apps. We are going to use multiple forms on one screen on Power Apps.

Previously, we have seen how to submit a form on the SharePoint list. Now we will see how to integrate and submit multiple forms on SharePoint at a time.

We are going to use 2 SharePoint lists i.e. Employees (we have already used this list in the last example) and Projects.

1. Employees

2. Projects

Let’s move to Power Apps to integrate these lists on a single screen. The following steps are:

• On Power App studio, add a new blank screen or use the default screen.
• Add a label and give a text to it.
• On that screen add an ‘Edit form‘ from the Gallery. Go to the Insert tab > Gallery > Edit.
• Set data source as ‘Employees‘ and add the field from ‘Edit fields‘.

Similarly,

• Add another label and give a text as ‘Projects‘.
• Add another ‘Edit Form‘ to that screen.
• Here set the data source as ‘Projects‘.
• Click on ‘Edit fields‘ to add the fields on the form.

Note- Differentiate both forms with different background colors.

• Add a button control to this screen. That will submit the data from the Power Apps form to SharePoint lists.
• Go to the Insert tab > Button. Rename it as ‘Submit‘.
• On that button, set the ‘OnSelect‘ properties as below:

SubmitForm(Form4); ResetForm(Form4); NewForm(Form4);
SubmitForm(Form5); ResetForm(Form5); NewForm(Form5);

Where,

• Form4 – The Employees Form
• Form5 – The Projects Form

Now save the form and publish it. Play this app and insert data on the Form. Click on the Submit button.

We can see the data got submitted in both lists with a single click.

Similarly, the data got updated on another list:

This is how to integrate SharePoint multiple forms on Power Apps.

Power Apps SharePoint web part size

Here we will see how to customize the size of the Microsoft Power Apps web part on the SharePoint modern page.

In our above example, we have seen how to embed a Power Apps form on the SharePoint modern page’s web part.

Let’s take another example where we have prepared a Power Apps to form using the Employee data source (as similar to the last example).

Now save the form and publish it. Copy the App ID. Next, go to SharePoint Modern page, create a blank page, add a Microsoft PowerApps web part and paste the App ID. We can see the form will appear on the SharePoint like below:

But as per our business need, we want to customize the web part size. To do this, we need to customize the form size and it will automatically appear on the modern page.

• Once the form is completed, go to file > Settings > Displays.
• On Size, select ‘Custom‘.
• Set the width as 2049.
• Keep the toggle ‘Off‘ on ‘Scale to fit.

• Now Save the app and publish it.
• Again add this form on the Web parts. Now, we can see it will display the form with a customized web part size.

This is how to customize the SharePoint web part size on Power Apps.

Power Apps SharePoint auto-increment id

Here, we will see how to do SharePoint auto-increment ID in Power Apps. For example, we have a SharePoint list named ‘Product’ having some random data:

By default, there is an ID column in the SharePoint list. But we want to show this ID in the Power Apps form. Suppose when we enter an item, it will show the ID automatically in the Form that is incremented by 1 as compared to the last ID in the SharePoint list.

Here, the last ID is 37. When we add a new item the ID should be displayed as 38 in the Power Apps form. Next, the ID will be 39, and so on. To do this, the following steps are:

• On the canvas app, add an Edit form on the screen.
• Connect the form with the data source i.e. Product. We can see what the form will look like below:

• Next, remove the label from the ID and add a text input to it. Go to the Insert tab > Input > Text Input.

• Set the display mode as the view on the text Input’s properties.

• Select the text input, and set the Default property with the below expression:

If(Form2.Mode= FormMode.New,Last(Product).ID+1,Parent.Default)

• Add a button and named it ‘New‘. Set the ‘OnSelect’ property as below:

OnSelect = Refresh(Product);
           NewForm(Form3)

It will refresh the data source as well as create a new form. Where,

• Product– Data source
• Form3 – The name of the edit form

• Add another button on that screen and named as ‘Save‘. On the ‘OnSelect‘ property set the below expression:

OnSelect = SubmitForm(Form3)

Now save the app and publish it. Play this and add data to the form. Click on Save.

Next, when we click on the New, we can see the ID is auto-incremented as compared to the last ID i.e. 39.

This is how to do SharePoint auto-increment id on Power Apps.

Power Apps SharePoint list 5000 items or row limit

Here we will discuss what are the SharePoint list limits and how to overcome this issue.

Every data source (SharePoint, Data verse, OneDrive) in PowerApps is limited to 500 items. This means that you cannot receive more than 500 items from a data source.

In any case, there will be multiple times when we will need more than 500 items. Assume that a simple task list for a 1000-person industry can easily generate around 50000 rows in total. In such a case, how to handle the limitation on Power Apps? In this following session, we will discuss some options, by which we can overcome form these issues.

Increase the total limit items that you want to fetch

This is the easiest way to set up the issue. In the Power Apps setting, there is an option ‘data row limit’ that allows the user to set how many rows are retrieved from the data source (i.e. server-based connectors) where the delegation is not supported.

By default, the row limit is set as 500 on Power Apps, but we can set the item limits to a maximum of 2000.

On Power Apps studio, go to file > Settings > General > Data row limit > 2000.

This method has a restriction i.e. a maximum value of 2000 that you can set, which means we can’t get even more than 2000 items using the same rules as for 500 items. Even the PowerApps will be unaware of the 2001 item.

Consider the use of static data

In certain cases, static data is the best solution to get rid of the limitation issues. Before using the static data, we need to remember some points:

• As the name suggests, we can not modify the static data in the Power Apps.
• Because static data is attached to your app, it increases the total size of the app, which might directly affect app loading time.

We can add the static data to the app by following the below steps:

• On Power App Studio, go to Data.
• Add Data source.
• Select Import from Excel.
• Select your excel file with a data table.

Use Delegation

Delegation is a mechanism for quickly and efficiently accessing all data from a data source in a friendly manner.

Let’s take a simple example to understand this statement and see how to make the data source delegable.

Suppose, we have a SharePoint list having 8000 rows and 150 of them have the text column ‘City’ set to ‘Mexico,’. As per our need, we want to filter the data and retrieve those 150 data. But how is it possible to retrieve the data from a large data set?

In such a case, we need to create a collection to collect the data and then add a filter function using the delegable ‘StartWith‘ function to retrieve data from the whole data.

ClearCollect(MyCollection, Filter(SP_List, StartsWith(City,"Mexico") ) ) 

As we are using the delegation criteria on SharePoint, it will filter 150 data from the 8000 rows. These are the scenarios to handle the SharePoint row limitations via Power Apps.

Also, you may like the below Power Apps Tutorials:

• Power Apps SharePoint Button
• Power Apps Radio Button
• Power Apps Data Table
• Microsoft Power Apps tutorial for beginners – Build an app in Power Apps
• Power Apps Navigate Function + 16 Examples
• Power Apps WeekNum and ISOWeekNum Function with Examples
• PowerApps form benefits for SharePoint
• PowerApps tips and tricks
• PowerApps nested gallery control example

Conclusion

From this Power Apps Tutorial, we learned how to work with Power Apps using SharePoint. Also, we have discussed the below examples:

• Power Apps SharePoint connector
• Power Apps SharePoint list
• Power Apps SharePoint image column
• Power Apps SharePoint yes/no field or boolean
• Power Apps SharePoint required field
• Power Apps SharePoint title field required
• Power Apps SharePoint refresh
• Power Apps SharePoint remove
• Power Apps SharePoint remove If
• Power Apps SharePoint thisitem
• Power Apps SharePoint web part
• Power Apps SharePoint user profile
• Power Apps SharePoint integration of multiple forms
• Power Apps SharePoint web part size
• Power Apps SharePoint auto-increment id
• Power Apps SharePoint list 5000 items or row limit