By 
In this “CRUD Operation in PowerApps Using Excel” tutorial, we will learn how to perform CRUD operations in PowerApps using the excel stored in OneDrive, using any other traditional language 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 (CRUD Operation in PowerApps Using Excel)?
- 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 the prerequisites, we need to have a sample excel data file stored in the OneDrive location.
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.
On the next screen, click on the “+ New connections” link.
From the list of connections list, select the “OneDrive for Business” connection.
Click on the OneDrive for Business Create button.
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 uploaded at the beginning of this demo as part of the prerequisites.
Now choose a table from your excel file – you may have multiple tables, select the one which you need.
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 (PowerApps using excel as data source)?
Format as table excel data is very simple, open your excel datasheet, from the Home tab, and select “Format as Table” where we could see multiple formats options, we can select any of them, and that’s it.
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.
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.
How to preview the PowerApp (PowerApps from excel)?
To preview the PowerApp, click on the preview play button or simply press the F5 key.
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:
View Item Details button:
Delete and Edit an item button:
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:
Few takeaways: PowerApps from Excel
- 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: CRUD operation in PowerApps using excel (PowerApps using excel as data source)
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: PowerApps Tutorial
You may also like the below PowerApps articles:
- How to send email from PowerApps button click?
- How to open outlook from PowerApps step by step?
- Show or hide columns conditionally in SharePoint list form
- Create your first chatbot in PowerApps step by step
- Cascading dropdown in PowerApps using SharePoint data
- CRUD operations in PowerApps using SharePoint online list
- CRUD Operation in PowerApps Using Excel OneDrive
- Phone number and email validation in PowerApps
- Connect to a SharePoint list in PowerApps step by step
- Collection variable in PowerApps
- Understand the difference between Set and UpdateContext function
- String concatenation function in PowerApps
- 3 ways to create Power Apps – Types of Power Apps
- Overview view of PowerApps development environment in Power Platform
- Customize SharePoint List Forms Using PowerApps step by step – Office 365
- Create free development environment using Power Apps Community Plan
- Create a canvas app with data from an Excel file
Buy SharePoint Online eBook
Buy the premium version of SharePoint Online & Office 365 administration eBook from here:
Disclaimer: The sample customer excels data in the above demo was downloaded from google.
About Post Author
1 comments on “Best way CRUD Operation in PowerApps Using Excel OneDrive – Microsoft 365”