Dynamics 365 integration with SmartSheet using Flows–What’s there and what’s not.

A dry spell of a month and here I am back to my favourite way of connecting with community which is writing one more blog post.

So here I got a new requirement from my customer. Basically they are using Smartsheet and Dynamics 365 and the simple requirement is whenever any changes are made in the smartsheet (row added or updated), some updates need to happen back in Dynamics 365.

Although I haven’t used Smartsheet connector in Flow, I was just aware of it and my initial hunch was like this would a walk in the park for flows. But like so many other times, this time also it didn’t quite turnout like the way we expected.

The first scenario was “When a row is added to a smartsheet”, I should be able to sync those changes to Dynamics 365 instance. So I started creating and flow and searched for Smartsheet connector. And the trigger is just there.

“When a new row is created”

I select that trigger and connect to the smartsheet.

As you can see from the above screenshot, I provided the smartsheet I want connect to and also mentioned the columns that I want to sync to. “Requestor” and “Business Group” are columns in the smartsheet as seen from the below screenshot.

The next step is to identify the values of the fields entered by the user. Now this one can be tricky if you are new here. For this demo I will use the Initialize variable action and take the value of the Requestor and Business Group in those variables. But before we do that, we need to understand how does it return the data.

So I go ahead and add a new row to the Smartsheet. As expected within a minute the flow fired. Let’s inspect the body of the flow here.

As seen from the above screenshot, it returns cells collection. The value highlighted here is for the" “Business Group” field.

To take this value and assign it a variable you can use the below expression.

triggerBody()[‘cells’][0].displayValue

In this way you can use variables for other values and then use CDS connector to update CRM.

Now comes the update part. Surprisingly there is no trigger for when a row is updated in Smartsheet.

We have something – ‘When a sheet is update’ but unfortunately that does not return the updated rows. Infact it returns the whole sheet data. Kind of stuck and hence had to resort to custom development of consuming SmartSheet API’s.

Would be delighted if I have some inputs here!

Hope this helps next time you get a requirement on this.

Debajit Dutta

(Dynamics MVP)

For consultation/ corporate training visit www.xrmforyou.com or reach out to us at info@xrmforyou.com

Our product offerings:

Role based views for Dynamics 365 (http://www.xrmforyou.com/role-based-views.html)

CRM-Sharepoint Attachment uploader and metadata manager (http://www.xrmforyou.com/sharepoint-integrator.html)

Record Cloner for Dynamics 365 (http://www.xrmforyou.com/record-cloner.html)

Multiselect picklist for Dynamics 365 (http://www.xrmforyou.com/multi-select-picklist.html)