Dynamics <> Excel Integration via PowerApps?

[MSPatrick]

Dear all,

I didn´t find any solution yet on my current requirement. I´m doing some calculations on quotes, opportunities etc. for several customers and products in Dynamics 365. There are some calc logics which are quite complex and I don´t want to do too much coding in Dynamics itself also because I want to keep the system flexible. So, therefore, I just want to add the articles on the opportunities and then export into a predefined Excel sheet with some functions, macros etc.

The output on the excel sheet for each line (each line means one line per article) should then automatically go back into CRM to put in the right price, discount etc. Can this be done via PowerApps or is there any other possibility to achieve this?

Thanks in advance

Patrick

[Yahya]

I dont think this is possible. I tried simple calculation in excel to be 'synced' with CDS, I didnt feal safe. eventtually, CDS has its own calculations and logic that with the time, it will improve (it is not close to workable i know, for instance there is no LEFT/RIGHT functions), but I dont think CDS is meant to offer what excel offers from the sense of complicated calculations.

[v-xida-msft]

Hi @MSPatrick,

Do you want to create a auto-sync process between Dynamics 365 entity and Excel sheet using PowerApps?

If you want to create a auto-sync process between Dynamics 365 entity and Excel sheet using PowerApps, I afraid that there is no way to achieve your needs in PowerApps currently.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

As an alternative solution, if you want to create a auto-sync process between Dynamics 365 entity and Excel table, I think Microsoft Flow could achieve your needs.

You could consider add a trigger within a flow to detect if a record is added within your Dynamics 365 Entity, if yes, create corresponding record within the pre-defined Excel table (<-- Sync records from D365 Entity to Excel table).

If you want to sync records from Excel table to your Dynamics 365 entity automatically, you could add a Recurrence trigger within a flow, then synx Excel table data into your D365 entity in a specific interval automatically (<-- Sync Excel table data to D365 entity).

More details about Microsoft Flow, please check the following article:

https://docs.microsoft.com/en-us/flow/getting-started

Best regards,

Kris

[Kevin23]

I was looking to do something a little similar a few weeks ago and came up with the following high level pattern.

Using the Microsoft Graph, Workbooks and Charts functions you can query against an Excel file that is stored in OneDrive/SharePoint. This workbook can have a table or series of functions that you can use to calculate your totals, based on data that you pass in from your powerapp. Once the excel file has done the calculation, you can then read the value back into your powerapp. I found the MSGraph a little hard to work with but from a pattern perspective, it will work. My particular use case meant I used this pattern with Flow, rather than directly from a PowerApp, but you should be able to do the same thing with a PowerApp.

Here is the documentation on the MSGraph, Excel functions. https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/resources/excel. I thought I had seen a blog post on the powerapps site covering this pattern but am unable to find it now.

Post back once you have something working, I think it would make an excellent use of the different tools available to us.