Connect your first sheet to DataTable

Prerequisites

To use DataTable:

• Check that you have Owner or Admin permissions on any sheets where you want to build a DataTable connection.
• DataTable must be a premium application available to you.
• You must have DataTable Premium Application permissions enabled in Admin Center by your System Admin.

Log in to DataTable to check your access level. Otherwise, visit the DataTable Marketplace page to contact your Sales representative for pricing-related inquiries.

Connection methods

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

• Add & update mode: Pull subsets of the data into a sheet from a DataTable. You can use filter criteria to narrow down data, map into the sheet columns, and control how to apply the DataTable changes to the sheet. Use the add, update, and remove options to match your sheet to your connection's filter criteria.
• Lookup mode: Merge data from a DataTable into a sheet based on a unique identifier, similar to a VLOOKUP. You select a unique identifier to look up values in the DataTable and choose sheet columns to map back to the DataTable. Lookup mode allows you to use a DataTable as a reference for processes running on a sheet.

In Lookup mode, your connection overwrites sheet changes with DataTable data. In lookup mode, the connection locks the mapped columns, so people can't edit the data.

Changes made in the sheet don't sync back to the DataTable.

You can connect up to 1,000 sheets to your DataTable.

Add & update mode

Step 1: Select the table

1. Go to the sheet that you want to connect with a DataTable.
2. Switch to Grid view by selecting Grid in view switcher in the top toolbar.
3. Select the Connections menu, and then select Connect to a DataTable.
4. Select Add & update mode.
5. Select the DataTable you want to use.

Step 2: Filter your data

Filter the data you sync from the DataTable into your sheet. Your sheet limits are 500,000 cells of data and 20,000 rows, while the DataTable can have more data.

You must have at least one filter criteria; you can add up to 10 criteria to your connection. The criteria depends on the data field type you filter on.

1. Select all in Sync records that meet all the conditions to toggle between requiring all filters and at least one condition. Select +Add New Condition if you want to add more conditions. 
2. Set your filters, and then select Next. 

Step 3: Map data from the DataTable into a sheet

1. Toggle on the fields you'd like to map and then select the sheet column to port to. The names in your DataTable and sheet don't need to match. 

   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 a value in this column, the corresponding row stops receiving updates.

2. Select Next.

Step 4: Set connection behaviors

1. Toggle any combination of behaviors: 
   • Add rows as they are added to the DataTable
   • Update rows as they change in the DataTable
   • Remove rows that no longer match the filter conditions
2. When you're done toggling on the behaviors, select Next.

Step 5: Review the connection

A summary of the connection you've built appears.

1. To revise any connection settings, select Back to navigate to them, and then revise them.
2. When you're done, select Create on the last page.

Smartsheet automatically adds DataTable data that matches the filter criteria to your sheet. New data may take several minutes to appear, and you may need to refresh your sheet to see the new data.

Lookup mode

Step 1: Set up the connection

1. Go to the sheet that you want to connect with a DataTable and select Grid view.
2. Select the Connections menu, and then select Connect to a DataTable.
3. Select Lookup mode.
4. Select the DataTable you want to use. 

Step 2: Select a unique identifier

1. Select a unique identifier to match sheet rows to DataTable records.
   • Make sure to select a DataTable field that contains unique values. Each value is typically a string of numbers and letters from the dataset, such as a store code or database record ID.
   • If the DataTable has records with matching unique identifier values, the connection uses the data from the first record it finds. Usually, this is the record with the lowest DataTable record ID.
2. After you map your unique identifier, select Next.

Step 3: Match fields

1. Toggle on each field you want to map and then select the sheet columns you'd like to port the field data to.

   Select at least one field. The names in your DataTable and sheet don’t need to match.

2. Select Next.

Step 4: Review the connection

A summary of the connection you've built appears.

1. To revise any connection settings, select Back to navigate to them, and then revise them.
2. When you're done, select Create on the last page.

The connection automatically runs and adds lookup values for unique identifiers that match DataTable record IDs. New data may take several minutes to appear, and you may need to refresh your sheet.