Excel was there, is there and will be there forever. One of the most loved tools from Microsoft if not the most and something which everyone is comfortable with.
Using excel files stored in OneDrive as datasource with Power Apps Canvas apps is a very common scenario. We see so many businesses transitioning to Power Apps over time but still they love their excels and they want to continue with it.
But when you try to connect to an excel file stored in One Drive from Power Apps, you have couple of options
- OneDrive for Business connector.
- Excel Online (Business) connector.
I have the below excel file stored in onedrive for business. A very simple table named – OfficeSupplies. Observe the Item Id column which I will use as the Primary key column of the table in excel.
Let’s start with the traditional OneDrive for Business connector. I connected to the table using OneDrive for Business connector.
Nothing fancy here. I then use a form to submit a record to Excel. The record is inserted into the excel but there is an additional column __PowerAppsId__.
The __PowerAppsId__ column is created automatically with a unique ID for each row of your Excel table. This column helps Power Apps know which rows to change.
But we have the ItemId column which I have kept to make every item unique. Unfortunately the onedrive for business connector will always create the column.
Also not to forget, if you have multiple users working with excel at a time, you will receive the error as shown in the following screenshot.
“The requested resource is locked”.
Let’s try the Excel Online (Business) connector now. When you choose your excel now from One Drive, you have the option to select an Identifier. You can use the auto generated option which will create the column __PowerAppsId__ or you can use a column for your excel for the identifier.
All other things remain same. Now when the record is submitted, the record is inserted successfully, without creating the column __PowerAppsId__
Not to forget the error – “Requested resource is locked” also does not appear anymore.
Hope this helped! You will also like the below posts.
Debajit Dutta
Business Solutions MVP