PowerApps are powerful! One of the great features is to be able to connect to an Excel spreadsheet and literally turn it into an app that you can use on your phone. Here we will go through just how to do that.
First, let’s create an Excel workbook. I have one that has a list of Customers (fake numbers, of course!):
Select the range of your data, and then Home->Format as Table:
Note do not use spaces in the header labels or it will be replaced by x0020 in PowerApps.
Check My table has headers:
From the Design tab, set the name of the table to Customers:
Note if you do not do this, you will receive an error message “There are no tables in this file” when trying to connect from PowerApps.
Upload this into OneDrive:
Now go to https://powerapps.microsoft.com, and select to create an app:
You will see something like below. Select OneDrive for Business Phone Layout:
Click Create:
You will see your account. Select it to view your files. Then select the Excel file you uploaded:
Choose the Customers table:
And in the lower right, Connect:
You will see the App created. Click Play to see what the app looks like:
This will take you to the app preview. You can see the app automatically has created a page that lists all the customers:
Select a customer. We can see individual details of the customer:
Click the top right to Edit. We will change the annual revenue to 5000000:
Click the check to submit the change.
Let’s look at our Excel workbook in OneDrive. We can see the change has been reflected:
You can also create a new record by clicking the + sign:
And delete records by selecting them and clicking the Delete icon:
Now let’s save the app. Go to File:
Let’s call it Customer Excel:
And Save:
That’s it! Now let’s use the app on our mobile device.
Sign into the PowerApps mobile app and select the Customer Excel app we created:
You will see the same screens:
Select an item, e.g. Pepsi. Make an update, set the Annual Revenue to 5000001 and click to submit:
In your OneDrive Excel file, you will see the update has been made:
Note you should not have the Excel workbook open in OneDrive when you are trying to update it, or you will receive the error “The requested resource is locked. Server response: Cannot write file to filesource. File is being locked. clientRequestId. There was a problem saving your changes. The data source may be invalid”. To resolve this, close the file and try saving again:
That’s it! You now have built a mobile app that can create, read, update and delete records in an Excel spreadsheet. And we did this with no code.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
