Connect a sheet to a DataTable

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 changes to the DataTable apply to the sheet. Use add, update, and remove options to ensure that your sheet matches the filter criteria defined in the connection.

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 which 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, the connection overwrites changes made in the sheet with data from the DataTable. Because of how lookup mode works, 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.

Remember 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, select the Connections menu.
2. Select Connect to a DataTable.
3. Select Add & update mode.
4. 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. You can filter your data using various criteria depending on the field type.

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 select Next. 

Step 3: Map data from the DataTable into a sheet

1. Toggle on the fields you’d like to map, 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 values in this column, the corresponding row will stop 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’ve toggled on your desired set of behaviors, select Next.

Step 5: Review the connection

A summary of the connection you’ve built displays. To revise your connection settings, select Back.

Select Create when done. It automatically added any data that matches the filter criteria to your sheet. It may take several minutes, and you may need to refresh your sheet.

Lookup mode

Step 1: Set up the connection

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

Step 2: Select a unique identifier

1. Select a unique identifier to match rows in your sheet to records in the DataTable. Be sure to select a DataTable field that contains unique values. This 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 lowest DataTable record ID.
2. After you map your unique identifier, select Next.

Step 3: Match Fields

1. Toggle on the fields you want to map, then select the sheet column you’d like information to port 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 displays. To revise your connection settings, select Back.

Select Create to finalize your connection. The connection automatically runs and adds lookup values for any unique identifiers with a match in the DataTable. It may take several minutes and you may need to refresh your sheet.