PowerApps Canvas - Edit CDS data via Excel Add-in

[shawnaby]

Hello All,

I saw that PowerApps portal mode can create a feature where you edit CDS data via an edit in Excel view (You download the excel file, enable the add-in and it allows you to make changes to records and then hit publish within Excel)

How can I call that within Canvas? Example: I want to click a button on the screen that will let me download the Excel file of CDS entity and allow me to use the add-in to publish the new changes. I do not want to move everything into PowerApps Portal as I lo

Best Regards,

[Drrickryp]

Hi @shawnaby 

I don't think that you can do it from within the app.  To activate this feature (editing tabular data from CDS in an Excel file), you do it from the Entities area in an environment.  Select the entity and look at the ribbon at the top.  Select the Edit data in Excel and it will download a linked Excel file with your data.  Once you make the changes, you can publish it and the changes will be saved.  I don't think that you can add columns though.  There are hidden columns that contain metadata and these should not be modified. 

Not being able to do this from within PowerApps is probably by design.  I shudder to think what might happen if one of my users got that kind of access to modify the data in an entire table. 

[shawnaby]

Thanks again @Drrickryp ,

I'm aware of that feature since I use that a lot to test enter/change some dummy data. I've looked into the gallery option where you can edit field values using the patch function but I want to edit multiple records at the same time, across the applicable fields (not the system generated ones that should not be changed).

Have you figured an alternative way in your canvas apps to do this?

Best Regards, 

[HSheild]

HI @shawnaby 

You can also export to Excel for updating via a Model-driven Power App using the Open in Excel Online feature.  This gives quite a nice user experience as the user can open Excel Online within the Power App, update data and then publish back.  No need to download Excel onto your machine.

See https://docs.microsoft.com/en-us/powerapps/user/export-to-excel-online

[Drrickryp]

There are a number of posts in the community and blog regarding how to use ForAll() and Patch() functions together to accomplish your goal,  Run a search for those two. 

[HSheild]

@shawnaby 

If it has to be done in a Canvas app, have you also looked at creating an editable table in your Canvas app.

For example

https://www.youtube.com/watch?v=O2WqhRTUhyw

[shawnaby]

Hi @HSheild 

Thanks for your response. I've also seen this feature and link. Unfortunately I want to really keep the look and feel of things within my canvas app. That's why I was wondering if we could have this feature within Canvas. I can technically move everything from canvas to model driven app, but I lose the look and feel of these special screens I've made.

Would you just recommend just moving everything to model-driven?

Best Regards,

[Drrickryp]

You may also want to check the videos produced by @Shanescows  on how to create an InfoPath type gallery for data entry to get a few more ideas for a canvas app. 

[shawnaby · Accepted solution]

@HSheild 

Thanks for your quick response again. I've done this table actually, but the issue with this is still editing one field value one by one, you can't just copy and paste rows of data like Excel can to make mass edits.

Best Regards,