Applies to
Sync Smartsheet with Microsoft Dynamics 365
Use the Microsoft Dynamics 365 Connector to have Leads, Opportunities, Accounts, Contacts and other Dynamics 365 entities sync between Smartsheet and Dynamics 365 automatically.
The Dynamics 365 Connector is available as a premium offering. For information about pricing, please contact our sales team.
Once the Smartsheet for Dynamics 365 Connector has been purchased, a user that has Smartsheet System Admin and Dynamics 365 Admin permissions must install it using the steps in our Smartsheet for Microsoft Dynamics 365 Connector: Admin Setup article.
Before You Begin
To create workflows in the Dynamics 365 Connector, you must be a Licensed Smartsheet User. Your Smartsheet System Admin can grant you a license by following the steps in the Managing Users article.
Sign in to the Connectors
Once the Microsoft Dynamics 365 Connector has been configured, you’ll use the following URL to create and sync workflows with either integration:
https://connectors.smartsheet.com/c/plugin/dynamics
Create a Workflow
After logging in to the Connector, you can create workflows that handle the automatic sync. You’ll be guided through this process with the workflow wizard.
To get started creating a new workflow:
- Click the menu in the upper-left corner of the page and select Dashboard.
- Click Add Workflow to start the workflow wizard.
Workflow Wizard
Use the workflow wizard to select your sheet and Microsoft Dynamics entity, and map their respective columns and fields for synchronization.
- On the Settings page, you’ll establish the basics for your workflow:
Next to What do you want this workflow to do? choose the sync direction of the workflow (to Dynamics 365, to Smartsheet, or bidirectionally between the two applications).
Next to Sheet Name, select an existing sheet or select Create a new sheet to immediately create a new sheet in Smartsheet.
Select a Dynamics 365 entity from the next dropdown.
NOTE: Once you save the workflow, the sheet, Dynamics 365 entity, and their sync directions cannot be modified; however, you can create a new workflow to select different settings. - Click Continue to confirm your settings.
- Use the Fields section to map the fields between Dynamics 365 and Smartsheet.
If needed, you can select new column in the dropdown picker to insert a new column in the sheet. To include more Dynamics 365 entity fields in the workflow, select add field.
- You can alter the sync direction using the flow dropdowns
between entity fields and columns.
NOTE: You may not be able to modify the sync directions of certain mappings based on your permissions in both Dynamics 365 and Smartsheet, as well as the field’s use in the integration (such as the Dynamics ID). - Click Continue to confirm your field mapping choices.
- On the Dynamics Filters and Sheet Filters pages, you can create filters to better control the type and amount of data that’s shared between the two applications. (Check out the Filters section below for more information.)
- On the Save page, you’ll name and save your workflow and review a summary of updates that will be made to your Dynamics 365 entity and sheet.
If you find that the Update Summary displays an unexpected amount of changes will be made, you have the opportunity to step back through the wizard and make additional changes to the workflow, such as adding filters. - Click Save Workflow to start the workflow sync, adding any Dynamics 365 records and rows specified in the Update Summary.
Once you save the workflow, you’ll complete the wizard and be taken to the Dashboard. Your workflow will run and will automatically update records in Dynamics 365 and rows in your sheet in Smartsheet according to the rules you set up in the workflow.
To modify an existing workflow, follow the steps in the Managing Workflows section below.
Use Filters to Fine-Tune Data Synchronization
Dynamics Filters
You have the option to add filters to better regulate the Dynamics 365 records that flow into the sheet. These filters can help ensure that you sync only fields that pertain to a specific project, client, or employee, for example.
To create Dynamics filters:
- Click add filter to populate the list of available entity fields, and then select one of them to create the filter.
- Use the dropdown to select the condition of the filter. For example: equals, contains, is blank.
NOTE: A text field will appear on the right if you need to enter additional filter criteria. - Click Continue to confirm and apply the filters.
You’ll see the number of fields that will be updated in your Dynamics 365 entity on the Save page of the wizard next to Update Summary. You can use this to gauge the amount of data flowing into the entity from your sheet.
If the Update Summary count appears unexpectedly high or low, you can go back to add or remove filters as needed.
Smartsheet Filters
You have the option to add filters to control the rows that sync to objects in Dynamics 365. You can use these filters to better regulate the records that sync back to your object. For example, use a filter to sync only specific rows that have a Status Reason value of In Progress, back to the Opportunity entity in Dynamics 365.
To create Sheet filters:
- Click add filter to populate the list of available sheet columns, and then select a column to populate the filter.
- Use the dropdown to select the condition of the filter. (Examples: equals, contains, is blank.)
NOTE: A text field will appear on the right if you need to enter additional filter criteria.
- Click Continue to confirm and apply the filters.
You’ll see the number of rows that will be added and updated in your sheet on the Save page of the wizard next to Update Summary to get an understanding of the changes that will be made after the workflow is saved.
If the Update Summary count appears unexpectedly high or low, you can go back to add or remove filters as needed.
Keep Related Records Together with Smartsheet Hierarchy
Define row groups in your workflow to keep your Dynamics 365 records together as parent and child tasks in Smartsheet. Create row groups to have your workflow organize your Dynamics 365 records by the options of a specific field, like Opportunity Stages (such as Qualify, Proposal, Contract), without having to manually move them around in your sheet after they sync.
Keep the following in mind when defining row groups:
- In Group by, you can select an existing Dynamics 365 field or type a new value to group records as they sync to your sheet. The Group by will determine the Primary column value of the parent rows in your sheet. For example, when you Group by the Stage of an Opportunity, parent rows are created in your sheet for each stage (such as Proposal, Qualify, Contract)
- New parent rows will be created at the bottom of your sheet unless your Group by completely matches an existing Primary column value of the sheet.
- You can define a sub group for an additional layer of organization. Have records sync, for example, underneath an International parent row that is also a child to a Sales row in Smartsheet.
- Dynamics 365 records must have the correct hyperlinks in the Primary ID column on your sheet. (If a Primary ID column doesn’t exist yet, it will be added to your sheet automatically when the workflow runs for the first time.) Manually copying and pasting Dynamics IDs from Dynamics 365 into your sheet may cause the workflow to not sync as expected.
Manage Workflows
You’ll manage workflows from the Dashboard. Click the three-line menu in the upper-left corner of the page and select Dashboard to see a list of the workflows you’ve created.
Settings
Click Settings to expand the following options:
- Edit Workflow—takes you back through the workflow wizard to make changes to the workflow.
- Rename Workflow—change the title of the workflow.
- Disable/Enable Workflow—stops or starts automation of the workflow.
- Delete Workflow—removes the workflow from the Dashboard.
NOTE: The action of deleting a workflow cannot be undone, and deleted workflows cannot be recovered.
Find and Fix Sync Errors
Several resources are available to help you with detecting and resolving workflow errors, including:
- The Run History page: This provides a count of the number of sync errors for a workflow and displays an error message with details on the issue. See the Run History section below for more troubleshooting information.
- Error Report Emails: As issues occur, email message are sent to the workflow owner and to the person who edited the field or row in the workflow that caused the issue.
- The Sync Error column in Smartsheet populates a message on rows with a sync issue. (This column should exist on any sheet associated with a Dynamics 365 entity.)
Use the Run History Page to Troubleshoot Sync Errors
After creating a workflow, you’ll be able to see its sync history, including any errors that occurred, on the workflow’s Run History page.
To get to the Run History page:
- Click Dashboard from the menu to view the list of workflows.
- Click the link under Last Run to see a workflow’s Run History page.
- You can then click the timestamp (under Last Run) of a specific run instance to see any error messages.
Connectors Workflow Best Practices
Save and Refresh Often
Since you'll be working across two applications, the opportunities for synchronization errors are increased as more people collaborate. To help minimize the risk of sync issues, save changes and refresh your sheets as you make updates. Saving and refreshing frequently will ensure that others see your changes and that they're synced to Dynamics 365 and Smartsheet according to the workflows you've set up.
For more information about saving and refreshing sheets in Smartsheet, see the Help Center article Saving and Refreshing Sheets.
Give Viewer Permissions on Sheets
To prevent accidental changes to the sheet that could potentially cause sync issues between Smartsheet and Dynamics 365, you can give Viewer permissions to collaborators on your sheet.
With Viewer permissions, collaborators are able to see the sheet data but won’t be able to edit data.
View Changes
In Smartsheet, you can use the View History feature to see a record of changes that were made to a cell. For sheets that include data shared between Smartsheet and Dynamics 365, the name of the user associated with the change will be the person who created the workflow.
This information can help you isolate down to a specific user’s workflow if you’re finding unexpected changes are being made to your sheet, possibly from incorrectly mapped columns and you’re not receiving errors in the Sync Error column.
For more details on tracking changes in Smartsheet, see Viewing Cell History in the Help Center.
Push Data from Smartsheet to Dynamics 365 for Specific Rows Only
To give you greater control over which rows explicitly push data back to Dynamics 365, create an Update in Dynamics 365 column in the sheet in Smartsheet and a filter in the Connector workflow:
- In Smartsheet, in the sheet that you plan to connect to the Dynamics 365 entity, create a new column (steps on creating new columns in a sheet are available in the Help Center), select the Checkbox type, and name the column Sync to Dynamics.
- Log in to the Smartsheet for Dynamics 365 page, and follow the steps to create a workflow.
- In the Workflow wizard, specify a bi-directional workflow and create filters to sync only rows from the sheet that have the Update in Dynamics 365 column checked.
The next time the sheet and the Dynamics entity is synced, data will be pushed back to Dynamics only for the rows for which the Sync to Dynamics checkbox is checked.
Check Sheet Permissions on Unexpectedly Blank Columns
Data may not update to a sheet depending on your sharing permissions, but you may not receive an error. If a column appears blank on a sheet that you’ve mapped in your workflow, the column may be locked on the sheet. Have the sheet Owner (or another Admin) grant you Admin permissions on the sheet with the steps in the Sharing Sheets Help Center article.