CRUD Operation in PowerApps Using Excel OneDrive

CRUD Operation in PowerApps Using Excel OneDrive

No comments

 1,574 total views,  14 views today

In this tutorial, we will learn how to perform CRUD operations in PowerApps using the excel stored in OneDrive, using any other traditional languages will at least take a couple of hours to implement, however, using the PowerApps tool we can implement in just 5 minutes. So, let’s get started with how to do it.

What we are going to learn from this article?

  • How to perform the CRUD Operation in PowerApps Using Excel OneDrive step by step?
  • How to create a PowerApps model-driven app from Excel OneDrive?
  • How to preview the PowerApp?
  • How to format the excel data as a table?
  • What is OneDrive for Business?
  • How to create a connection for OneDrive for Business in PowerApps?

As part of pre-requisites, we need to have a sample excel data file stored in the OneDrive location.

Sample customer data excel file in OneDrive
Sample customer data excel file in OneDrive

Now login to the PowerApps portal – https://make.powerapps.com, then select your environment, here is my environment URL – https://make.powerapps.com/environments/Default-8bde4d0e-9bbb-42a2-86ca-46884a08df50/home, for you it will be a different URL.

Once we log in to this make PowerApps portal environment, we will get the below app-building page.

Click on the “Other data sources” as we don’t see OneDrive data source over here.

Build your business app faster using PowerApps with OneDrive excel
Build your business app faster using PowerApps with OneDrive excel

On the next screen, click on the “+ New connections” link.

Add new connection in PowerApps portal
Add new connection in PowerApps portal

From the list of connections list, select the “OneDrive for Business” connection.

OneDrive for business connection in PowerApps
OneDrive for Business connection in PowerApps

Click on the OneDrive for Business Create button.

Create OneDrive for business connection in PowerApps
Create OneDrive for business connection in PowerApps

What is OneDrive for Business?

OneDrive for Business is a cloud storage, file hosting service that allows users to sync files and later access them from a web browser or mobile device. Connect to OneDrive for Business to manage your files. You can perform various actions such as upload, update, get, and delete files.

Once the connection got established successfully, we can see the OneDrive for Business connection is added to the connections list.

Now, choose your excel file (you may have multiple files), here I am selecting my customer file as I have only one file which I have uploaded at the beginning of this demo as part of the pre-requisites.

Choose an excel file from OneDrive connection in PowerApps
Choose an excel file from the OneDrive connection in PowerApps

Now choose a table from your excel file – you may have multiple tables, select the one which you need.

Choose table in OneDrive for Business excel for PowerApps
Choose a table in OneDrive for Business excel for PowerApps

Note:

  • In order to develop a PowerApp out of an excel file, the excel data must be in the tabular format otherwise it won’t work. See the below section on how to format the excel data as a table.

How to format the excel data as a table?

Format as table excel data is very simple, open your excel datasheet, from the Home tab, select “Format as Table” where we could see multiple formats options, we can select any of them, that’s it.

Excel data format as table
Excel data format as table

Now moving back to our actual demo part.

Once we select the table from the OneDrive for Business and click on the “Connect” button from the bottom of the page, it will validate the authentication process and load the excel data in PowerApps Studio – this process might take a couple of seconds to minutes – it depends on the volume of data you have in your source excel file.

Welcome to PowerApps Studio Skip button
Welcome to PowerApps Studio Skip button

Once you see the “Welcome to Power Apps Studio” pop-up message, click on the “Skip” button.

Wow, now we can see customer data apps created automatically in PowerApps without writing a single line of code.

PowerApp model driven app is created from excel OneDrive
PowerApp model-driven app is created from excel OneDrive

How to preview the PowerApp?

To preview the PowerApp, click on the preview play button or simply press on the F5 key.

Preview button in PowerApps to run the app locally
Preview button in PowerApps to run the app locally

PowerApps model-driven app in preview mode:

PowerApps model driven app in preview mode
PowerApps model-driven app in preview mode

Notes:

We can do the following using this model-driven PowerApp:

  • We can perform CRUD operations (Create, Read, Update, and Delete).
  • We can search the data.
  • We can sort the data in ascending or descending order.

Create a new customer screen:

Create new customer screen in PowerApps model driven app
Create new customer screen in PowerApps model-driven app

View Item Details button:

View Item Details button in PowerApps model driven app
View Item Details button in PowerApps model-driven app

Delete and Edit an item button:

Delete and edit item in PowerApps model driven app
Delete and edit an item in PowerApps model-driven app

Edit an item screen in PowerApps:

Once we click on the pencil icon (Edit), we will get to see the edit item screen like below:

Edit item screen in PowerApps model-driven app
Edit item screen in PowerApps model-driven app

Few takeaways:

  • Whether we add a new item or edit an item from this app – it will be added/modified in the origin Datasource (customer excel file stored in the OneDrive).
  • When we delete an item from this app – the data not only be deleted from the app screen but also from the origin Datasource (customer excel file stored in the OneDrive).

Summary: What we had here?

Thus, in this article, we have learned the below with respect to performing the CRUD operations in PowerApps using the excel data stored in the OneDrive for Business:

  • How to perform the CRUD Operation in PowerApps Using Excel OneDrive?
  • How to perform CRUD operation in PowerApps?
  • How to create a PowerApps model-driven app from Excel OneDrive?
  • How to preview the PowerApp?
  • How to format the excel data as a table?
  • What is OneDrive for Business?
  • How to create a connection for OneDrive for Business in PowerApps?

See Also

You may also like the below PowerApps articles:

Buy the premium version of SharePoint Online & Office 365 administration eBook from here:


Buy SharePoint Online & Office 365 Administration eBook

 

Disclaimer: The sample customer excels data in the above demo was downloaded from google.