Connect a sheet to a DataTable

Applies to

Smartsheet Advance Package (Requires Enterprise Plan)

Capabilities

Who can use this capability


You must be an admin or owner of the sheet to use DataTable.

When you connect a sheet to a DataTable, the data is automatically synced from the DataTable into the sheet.  This means that any sheets that are connected to your DataTable are kept in sync with your source of truth.


There are two ways to connect a sheet to a DataTable:

  • Add and update mode:  This connection type pulls subsets of the data into a sheet from a DataTable. You can use filter criteria to narrow down the subset of data, map the DataTable data into the sheet columns, and control how changes to the DataTable are applied to the sheet. Use add, update, and remove options to ensure your sheet matches the filter criteria defined in the connection. You can count on your sheet being up-to-date with the latest data from your DataTable. 

    This method does not sync changes made in the sheet back to the DataTable. 

  • Lookup mode: This connection type merges data from a DataTable into a sheet based on a unique identifier. You will select a unique identifier to look up values in the DataTable, and choose which sheet columns to map back to the DataTable. This method allows you to use a DataTable as a reference for processes running in a sheet.

    The connection will overwrite changes made in the sheet for any mapped columns. Lock your mapped columns to prevent this from happening.  

    This method does not sync changes in the sheet back to the DataTable

Add and update mode

Select the table

  1. Navigate to the sheet you want to connect with a DataTable and then select Connections
  2. Select Connect to a DataTable.
  3. Select the DataTable you want to use. The list includes only the DataTables you created. 

Filter your data

Filter the data you will sync from your DataTable into your sheet. Your sheet is limited to 500,000 cells of data, while your DataTable will likely have significantly more data.

You must have at least one filter criteria; you can add up to ten filter criteria to your connection. You can filter your data using various filter criteria depending on the field type.

  • Select Sync records that match at least one condition to toggle between requiring one or more conditions to match or, leave it off to match all conditions. After you’ve set your filters, click Next

Map data from your DataTable into a sheet

  1. Click the toggle next to the field you want to map, and then select the matching sheet column from the dropdown.
    NOTE: The connection adds a new column to your sheet called DataTable Record ID. This ID tracks rows added from the DataTable. This column is locked and hidden by default. If you change values in this column, the corresponding row will stop receiving updates.
  2. Click Next.

Set connection behaviors

You can set the connection to:

  • Add rows to the sheet as they are added to the DataTable
  • Update rows to the sheet when they are changed in the DataTable
  • Remove rows from the sheet when they no longer match the filter conditions
    NOTE: If a previously synced row no longer matches your filter criteria the row will be deleted. 

Leave these settings in their default state (add and update rows) unless you only want updates or new rows added.

  • Select Next when you’re done or Back to edit your filters. 

Review the connection

To revise your connection settings, click Back (or the step on the left menu).

Click Done if you’re satisfied with the way the connection is configured.  

Lookup mode

Set up the connection

  1. Go to the sheet you want to connect with a DataTable, select the Connections menu, and then click Connect to a DataTable.
  2. Select Lookup mode.
  3. Select a DataTable and then select Next. The list shows only the DataTables you have created.

Select a unique identifier

Select a unique identifier to match rows in your sheet to records in your DataTable. 

Be sure to select a DataTable field that contains unique values. This is typically a string of numbers and letters that comes with the dataset, such as a store code, employee email address, or database record ID.

If your DataTable has records with multiple matching unique identifier values, the connection will use the data from the first record it finds. In most cases, this will be the lowest DataTable record ID.

  • After you map your unique identifiers, select Next.

Match Fields

On the left are DataTable fields, and on the right, sheet columns. 

To map data from your DataTable into a Sheet:

  1. Slide the toggle next to the field you want to map.
  2. Select the sheet column from the dropdown.
  3. Select Next to continue or Back to edit your unique identifiers.

Review connection

Review your settings.

To revise your settings, select the item you want to revise from the left menu, or click Back to make more changes.

Select Done to complete the connection.