Create a Canvas app from scratch using Excel from OneDrive for business

Create a Canvas app from scratch using Excel from OneDrive for business

This is PART 1 of the blog Series Create a Canvas App from Scratch. In this blog tutorial we will learn step by step process to create our first canvas app using Excel as our data source from OneDrive for business

This image has an empty alt attribute; its file name is image-1.png
  • It will open another window of Power Platform admin center. On the right, from the menu click on the Environment. Click NEW, a form will appear. Fill in the details, In the Type input box from the dropdown select Trial and click save.
This image has an empty alt attribute; its file name is image-2.png
  • Create an Excel file with Employee records like first name, last name, email ID, Date of birth, Date of joining, Salary etc. Format that data as a table, named EmployeeDetails.
This image has an empty alt attribute; its file name is image-4.png
  • Save the file with the name of Employee records.xlsx, close it and upload it to cloud storage account like in this case OneDrive.
  • Now sign in to Power Apps.
  • On the left-hand menu click on create, that will open 3 ways to create an app screen i.e Canvas app from blank, Model-driven from blank, Portal from blank.
  • Select Canvas app from blank to create an app
This image has an empty alt attribute; its file name is image-6.png
  • Mention the name for your app, select Phone layout and then select Create
This image has an empty alt attribute; its file name is image-8.png
  • If a dialog box showing Welcome to Power Apps Studio appears, select Skip
  • On the left side menu click on Data, and select New connection, select the cloud-storage in this case OneDrive for your data and click Create, provide your credentials, if asked.
This image has an empty alt attribute; its file name is image-10.png
  • From the top navigation menu on the Home tab click New screen dropdown and it will display all screen types, choose List. A new screen is added with several default controls like Gallery control, search box, label. The Gallery covers the entire screen under the search box.
  • In the new screen on the top left corner, select the Label control, and then replace [Title] with Employee records.
This image has an empty alt attribute; its file name is image-15.png
  • Under Tree view, in the left navigation bar select BrowseGallery1. A selection box with handles surrounds the gallery.
This image has an empty alt attribute; its file name is image-13.png
  • On the right-hand pane, under Properties tab, select the dropdown arrow for the Layout, it will display different layout. Select Title, subtitle and body
This image has an empty alt attribute; its file name is image-16.png
  • In the BrowseGallery1, on the top dropdown input box, select Items and in the formula bar, replace CustomGallerySample with EmployeeDetails and replace sampleText with First Name, last name, Employee ID and mail in first instance and with Email ID in second instance.
This image has an empty alt attribute; its file name is image-17.png
  • On the right-hand side pane, under Properties tab, select Edit next to the Fields label.
  • In the Body1 box, select Employee ID, in the Subtitle2 box, select Last Name, and in the Title2 box, select First Name.
This image has an empty alt attribute; its file name is image-18.png
  • In the upper-right corner of the Data pane, select the close icon (X) to close it.

Note: SortByColumns and Search functions in the formula bar are used to sort and filter the items in the gallery.

  • Next step is to create the change screen by selecting the drop-down arrow next to New screen under the Home tab, and then select Form
This image has an empty alt attribute; its file name is image-20.png
  • Select EditForm1 from the left navigation bar
  • On the right-hand pane, under the Property tab, select the drop-down arrow next to Data source, and then select EmployeeDetails from the list that appears.
This image has an empty alt attribute; its file name is image-21.png
  • Under the data source, in the Fields label, select Edit Fields
  • In the Fields pane, select Add field, select the check box for each field, and then select Add.
  • Change the position of the field by collapsing the arrow given next to the name of each field, and then drag the fields in the order required.
This image has an empty alt attribute; its file name is image-22.png
  • In the upper-right corner of the Fields pane, close it by selecting the close icon (X).
  • In the formula bar of EditForm1, set Item property to BrowseGallery1.Selected.
  • At the top of the screen, select the Label control, and then replace [Title] with Edit details.
  • To delete and rename the screen, select the ellipsis (…) from the left navigation.
  • For screen1, select the ellipsis (…), and then select Delete.
This image has an empty alt attribute; its file name is image-23.png
  • For Screen2, select the ellipsis (…) and select Rename, and then type orpaste ViewScreen.
This image has an empty alt attribute; its file name is image-25.png
  • Change the name of Screen3 to ChangeScreen also in the similar way as the Screen2.
This image has an empty alt attribute; its file name is image-27.png

Configure icon on the ViewScreen

  • Near the top of the ViewScreen, select the circular-arrow icon.
  • Set the OnSelect property for that icon in the formula bar to Refresh(EmployeeDetails)
This image has an empty alt attribute; its file name is image-29.png
  • When the user selects this icon, the data from EmployeeDetails is refreshed from the Excel file.
  • Select the plus icon (+) from the upper right corner of the ViewScreen and set the OnSelect property to NewForm(EditForm1);Navigate(ChangeScreen,ScreenTransition.None)
This image has an empty alt attribute; its file name is image-31.png
  • When the user selects this icon, ChangeScreen appears with empty fields, so that the user can easily create a record.
  • Select the right-pointing arrow for the first record in the gallery. Set the OnSelect property for the arrow to this formula: EditForm(EditForm1); Navigate(ChangeScreen, ScreenTransition.None).
  • When the user selects this icon, ChangeScreen appears with each field showing the data for the selected record, so that the user can edit or delete the record more easily.
This image has an empty alt attribute; its file name is image-33.png

Configure icons on the ChangeScreen:

  • On ChangeScreen, in the upper-left corner select the “X” icon.
  • Set the OnSelect property for that icon in the formula bar to ResetForm(EditForm1);Navigate(ViewScreen, ScreenTransition.None)
  • When the user selects this icon, any changes that the user made in this screen are discarded, and the view screen opens.
  • In the upper-right corner, select the checkmark icon.
  • Set the OnSelect property for that icon in the formula bar to ResetForm(EditForm1);Navigate(ViewScreen, ScreenTransition.None)
  • When the user selects this icon, any changes that the user made is this screen are saved, and the view screen opens.
  • On the Insert tab, select Icons, and then select the Trash icon. Set the Color property to White and position it next to the checkmark icon.
This image has an empty alt attribute; its file name is image-36.png
  • Set the Visible property for the trash icon to EditForm1.Mode = FormMode.Edit, so that the icon appears only in the Edit mode and not in New mode
This image has an empty alt attribute; its file name is image-38.png
  • Set the OnSelect property for the trash icon to this formula: Remove(EmployeeDetails,BrowseGallery1.Selected);Navigate(ViewScreen,ScreenTransition.None)
This image has an empty alt attribute; its file name is image-40.png
  • When the user selects this icon, the selected record is deleted from the data source, and the view screen opens.
  • Finally, you can test the app. Select the ViewScreen and then open Preview by selection the Preview icon near the upper right corner or by pressing F5.
  • Type or paste one or more letters in the search box to filter the list based on the First name, Last name, email ID.
  • Select the sort icon one or more time to show data in the ascending or descending order.
  • Create, update or delete the record.
  • Close Preview mode by pressing Esc or by clicking on close icon
  • Lastly press Ctrl-S to save the app in the cloud to run it from other devices.

Following the above steps and you can easily create a Canvas app from Excel data source.

Please follow and like us:

Rashmi Kaushik

“Nothing can withstand the power of the human will if it is willing to stake its very existence to the extent of its purpose.” - Benjamin Disraeli. I am Rashmi Kaushik, working as a blogger, Software Consultant and Subject Matter Expert in Cynoteck Technology Solutions Ltd.. I believe hard work, dedication, willingness, desire to succeed, and determination are the collective keys that can open the lock of success. I am MB-200: Microsoft Power Platform + Dynamics 365 Core and AZ-900 Azure fundamentals certified.

Leave a Reply

Your email address will not be published. Required fields are marked *