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 the subset of data, map the DataTable data into the sheet columns, and control how changes to the DataTable apply 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 the DataTable.
   
• 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. This 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. Your sheet always matches what’s in the DataTable. Because of this, 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.

Add & update mode

Step 1: Select the table

1. Go to the sheet 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. The list includes DataTables you created and those you’re shared to as an Admin or Viewer. 

Step 2: Filter your data

Filter the data you sync from the DataTable into your sheet. Your sheet is limited to 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 ten 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 to be met 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 you’d like that information 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 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 is displayed. To revise your connection settings, select Back. 

Select Create when you’re done. Any data that matches the filter criteria is automatically added 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 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. The list includes DataTables you created and those you’re shared to as an admin or viewer.

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, employee email address, or database record ID. If the 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.
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 that information to port to. You must select at least one field. The names in your DataTable and sheet do not need to match.
2. Select Next.

Step 4: Review the connection

A summary of the connection you’ve built is displayed. 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.