Given this scenario, we need to consider few things: The user is seeking a dynamic and automated way to enter this data into the Power Apps instance. Re-entering this data manually is out of the question and creating import files every time to import the data is too inefficient. The rules for entering this data are widely varied depending on different scenarios and too complex to implement as logic on the model-driven or canvas app. In our sample scenario, the user has an Excel document with multiple worksheets with tables containing data that needs to be stored in the Power Apps instance. Set the Scene! Our Sample Microsoft Dataverse Scenario For example, Excel JavaScript API gives access to strongly typed objects for worksheets, ranges and tables, etc.
To tackle this particular challenge, we are going to explore implementing Excel Office Add-ins along with Office and Excel JavaScript APIs. We have tools in Power Platform that we can use to interact with Excel, notably Power BI for data analytics and Canvas Apps and Power Automate flows through connectors, but the experience is limited as dynamic CRUD operations are not supported through these tools whilst the user is working on the Excel document. Whatever the cause might be, we need to find a way to enable users to use Excel efficiently with our business systems which in our case would be Power Apps and Dynamics 365. The cause of this might be manifold that it is inevitably easier to work in Excel or the business systems that are built-for-purpose do not cater for all scenarios. The opportunity to streamline the business processes and the ability to access data from a single centralised location are often missed when users work in their silos in Excel. The issue is when there is a lack of discipline and governance around the use of Excel, especially around data integrity, disparity and duplication. In certain scenarios rightfully so, as there is no competing tool that is as flexible and powerful enough to handle the complexity and variance of rules like Excel.