SharePoint Integration with Dynamics 356 using Power Automate.

In this blog you will get to know how to copy excel file from one directory to another and how to get modify it with Dynamics CRM fields value at the SharePoint server.

In one of my projects we got the requirement to fetch values from Dynamics CRM and insert these values in an excel file that is located at share point. We have created one Template file and we are copying that file to another directory and we are inserting this Dynamics values in the copied file.

As we have various ways to accomplish this requirement, you can use plugin, Azure function or logic Apps, but according to me, Power Automate (MS Flow) is one of the convenient way to achieve this type of complex requirement.

Power Automate is a quick service that helps you to easily build business apps that run on browsers and in phone or tablet without any coding experience needed. It allows anyone in an organization to unlock new business agility.

Before jump into power automate, first create your excel template file and import it into your SharePoint site.

For demo purpose I have created an excel template file as below, make sure you have to use a table format.

Let’s start and navigate to the power app portal https://make.powerapps.com/ and create blank Flow. 

Select the trigger and provide below properties, Environment, Entity Name, and Scope.

After that select an action and choose SharePoint’s “Copy file”.

The properties you are required to fill are as follows.

1. Current Site Address: SharePoint site address (Source)

2. File to Copy: Select the file you want to copy.

3. Destination site: SharePoint site where you want to copy the file.

4. If another file is already there: provide what action needs to perform by flow if the file already exists, you can provide dynamic value as well.

To insert values in excel select new step and choose ‘Update a row’ action of excel online.

Once you select excel action, the properties you are required to fill are as follows:

1. Location: Provide SharePoint path.

2. Document Library: Provide a directory where your file is located 

3. File: Provide file ID from copy file action (Dynamic value).

4. Table: Provide excel table name where you want to insert your values.

5. Key Column: provide the column name of the table.

6. Key-Value: Row name of the table

7. Item: a value that wants to insert (dynamic value from  CRM).

Note: One trick to find table GUID, first select hardcode file path in ‘File’ property and it will display a list of table names in ‘Table’ property. Select your table and then remove the hardcoded file path and replace it with the ID of the copied file, table GUID will get auto-populated.

Now just create a record in CRM and Flow will get a trigger, just validate your flow once.

On the success of Flow, navigate to share point site and open newly created (copied) file, here you can see excel file is updated with CRM values.

Similarly, you can add Update action in the loop to insert values in multiple rows.

Happy No coding/Low coding

Original Post https://vikrantsdynamicsblogs.wordpress.com/2020/04/23/sharepoint-integration-with-dynamics-356-using-power-automate/