Smartsheet DataMesh: Eliminate Typos, Avoid Duplicative Data Entry, and More

Smartsheet DataMesh provides lookup functionality between sheets and facilitates data consistency. With Smartsheet Data Mesh you can:

  • Automatically find and remove duplicates
  • Create links between sheets based on lookup values that 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 that contains phone numbers for a list of people, you could use data mesh to update a different sheet with those same phone numbers)

This is a premium add-on that is available for purchase. For more information about how to obtain Smartsheet Premium Apps, please contact Smartsheet Sales.

Before You Begin

Note that Smartsheet also supports cross-sheet formulas. Before using DataMesh, you may want see if there’s a simpler solution that could be achieved with those. For more information, see the article Formulas: Reference Data from Other Sheets.

In order to use DataMesh you must be a licensed Owner or Admin on both the source and target sheets, and on a plan that includes DataMesh.

DataMesh also requires that you have two data sheets set up:

  • The source sheet
  • The target (destination) sheet 

The two sheets must have at least one column of data in common. (It is not required that the columns have the same name, but you’ll have an easier time mapping the columns with similar data are similarly named.)

For information on browser compatibility, see System Requirements and Guidelines for Using Smartsheet.

Access DataMesh

Click the Launcher to access the App.

Launcher

 

NOTE: If you have trouble accessing the app from the Launcher, please see Access Premium Apps and Templates With the Launcher.

Combine or Refine Data Sets with DataMesh

You’ll create new DataMesh configurations in the Create New Config wizard. There are five steps in the creation process. To start the wizard, click New Config in the upper-right corner of the DataMesh app and then follow the steps in the wizard.

Data Mesh New Config

Step 1: Select the Source Item

  1. Search or browse to find the sheet or report that contains the data that you want to display in the meshed sheet.
  2. Once you locate the sheet, select it 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.

  1. In the Lookup Values box under Source Sheet, select the value that’s common between both sheets (the value that you want to look up in the target sheet).

    NOTE: The values in the Lookup Value aren’t copied between sheets.
    Data Mesh Sample Column Map
     
  2. Under the Source Sheet and Target Sheet(s) sections, select the fields to be mapped. That is, 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 over to the target sheet.

    NOTE: The column names being mapped don’t need to match; however, you’ll have an easier time if you stick to a standard naming convention for similar data across sheets.
  3. When you've selected the columns that you want to map, click the Next button.

Step 4: Define View Options

  1. 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 Data Mesh values.

      NOTE: The Update Immediately execution frequency isn't supported when using a report as your source.
       
  2. 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.Data Mesh Define View Options
  3. 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.

Data Mesh Confirm Update

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.

Data Mesh Run Image 1

Step 1: Details

Confirm that the options are correct, then click the Run button.

Data Mesh Run Details

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.

Data Mesh Run Image 2

Step 3: Confirm

Here you'll see the resulting summary for each target sheet. 

Data Mesh Run Image 3

After you click the Close button and navigate back to Smartsheet, you'll find the meshed data in the sheet that you specified as the target. (You may need to refresh the sheet to see the changes.)

Transfer Your DataMesh Configuration to Another Person

Prior to transferring your DataMesh configuration ensure that the person you're transferring your configuration to meets the following requirements:

  • They've signed in to the Data Mesh application
  • They have Admin-level sharing permission or higher on both the source and target sheets

To Transfer the configuration:

  1. Click the Transfer icon on the right of the DataMesh window.

    DataMesh Transfer icon

    The Transfer Configuration window appears.
     
  2. Type the email address associated with the Smartsheet account of the person that you want to transfer your configuration to.
  3. 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.