The Smartsheet DataMesh app is a premium app that eliminates typos, duplicative data entry, and unnecessary work by mapping your Smartsheet data between large sheets and reports. It provides a way to automatically copy and paste data to ensure consistency among one-to-many relationships.
Use case examples include:
- Project Planning: Pull cost information from a rate table into your projects.
- Property Management: Ensure all address and location specific data is consistent.
- Human Resources: Keep employee information current across multiple sheets.
About DataMesh
DataMesh can help with the following:
- Project Planning: Pull cost information from a rate table into your projects.
- Property Management: Ensure all address and location-specific data is consistent.
- Human Resources: Keep employee information current across multiple sheets.
With DataMesh, you can:
- Automatically find and remove duplicates
- Create links between sheets based on lookup values you specify
- Automatically populate empty cells in one sheet based on a known lookup value in one or more sheets (for example, if you have a source sheet containing phone numbers for a list of people, you could use DataMesh to update a different sheet with those same phone numbers)
Learn more about DataMesh pricing on the DataMesh Marketplace page.
Prerequisites
To use DataMesh, you must
- Be a Licensed User
- Have Viewer permissions or higher on any source sheet and Editor permissions or higher on any target sheet
- Have DataMesh Premium Application permissions enabled in User Management by a Smartsheet System Admin
DataMesh is a premium application available on the plan.To check your access level, login at datamesh.smartsheet.com
Create a new DataMesh configuration
You'll create new DataMesh configurations in the New Config wizard. There are five steps in the creation process.
To start the wizard, log in to DataMesh and select New Config in the upper-right corner.
Step 1: Select the Source Item
- Search or browse to find the sheet or report containing the data you want to display in the meshed sheet.
- Select the item and click the Next button.
Step 2: Select the target sheets
1. Search or browse to find the sheets to which you want to copy the data.
2. Once you locate the sheet, select it and click the Next button.
Step 3: Map sheet columns
In this step, you'll choose which fields to map and determine what information gets moved where.
- In the Lookup Values box under Source Sheet, select the common value between both sheets (the value you want to look up in the target sheet).
NOTE: The values in the Lookup Value aren't copied between sheets.
- Under the Source Sheet and Target Sheet(s) sections, select the fields to be mapped. If there's a match between the source and the target sheets for the Lookup Value, the data you specify in the Data Field boxes (or column names) will be copied to the target sheet.
NOTE: The column names being mapped don't need to match, but mapping is easier if you use standard naming conventions for similar data across sheets. - Select the columns that you want to map, and then select Next.
Step 4: Define view options
- To specify how (and how often) you want the data to be meshed from the various dropdown menus, use the view options:
- Overwrite Existing Data in Target Sheets: If set to Yes, the config with overwrite any data in the data field column in the target sheet.
- Duplicates in Source Sheet: This option controls how to handle duplicates if found in the source sheet.
Pick 1st Match will use the first matching lookup value from the source sheet.
Ignore Entries will skip duplicate lookup values entirely. - Data Mapping Format: This option controls whether you want the data to be actively synced with the Cell Link functionality (see the Cell Links help article for details on how the feature works) from the source sheet or copied over from the source sheet.
NOTE: The Copy and Add Data option will copy over lookup values that match between the source and target sheets but will also add new lookup values from the source sheet that don't have a matching lookup value in the target sheet. - Execution Frequency: Scheduled execution frequency to update DataMesh values.
NOTE: The Update Immediately execution frequency isn't supported when using a report as your source.
- Type a title resembling the purpose for the mesh (for example, the type of data you're meshing) in the Name field under Config Name.
- Click the Next button.
Step 5: Confirm and update
Here you'll confirm that you mapped the data fields correctly, that you're happy with the specified options, and that the Config Name makes sense. If everything is in order, click the Update button.
Run the DataMesh config
Once the DataMesh config has been created, it is ready to run! If you've set an Execution Frequency (see "Step 4" in the section above), the DataMesh will execute at the next scheduled run time.
If you'd like the config to run right away, click on the name of the config from the main page.
Step 1: Details
Confirm that the options are correct, then click the Run button.
Step 2: Preview
Preview what will be populated on the Target sheet. You can also see details about the following:
- Total Rows Affected
- Total Rows Unaffected
- Total Duplicates in Source
- Total Rows Not Matched
If everything is in order, click the Submit button.
Step 3: Confirm
Here you'll see the resulting summary for each target sheet.
Select Close and go back to Smartsheet. The meshed data will appear in the sheet you specified as the target. You may need to refresh the sheet to see the changes.
Transfer your DataMesh configuration to another person
Before transferring your DataMesh configuration, ensure that the new owner meets the following requirements:
- They're a member of your Smartsheet account with a license and have signed into DataMesh
- They have Admin-level sharing permission or higher on both the source and target sheets
To Transfer the configuration:
- Click the Transfer icon on the right of the DataMesh window.
The Transfer Configuration window appears.
- Enter the email address associated with the Smartsheet account to which you want to transfer your configuration.
- Whomever you're transferring your configuration to will receive an email notification asking them to sign in to DataMesh and accept the transfer.
Once they sign in to DataMesh and accept the transfer, that's it! They become the new owner of the configuration.