Follow the step-by-step instructions to create a DataTable.
DataTable allows you to visualize and collaborate on large data sets in Smartsheet by connecting and combining siloed data from ERPs, CRMs, and databases. With all of your data in Smartsheet, you can manage sharing and access, trigger automated actions, display metrics and summaries in dashboards, and make data-driven decisions that improve the way you work -- all without specialized technical skills.
DataTable is a new type of object that can store millions of rows of data, integrate with external systems using Data Shuttle, and sync subsets of that data into sheets.
DataTable makes it possible for you to use Smartsheet features to work with large data stores or unique custom views, applied formulas, workflows, and more on that data. And your data is always consistent and up to date.
To use DataTable, your Smartsheet account administrator must grant you the DataTable role in Admin Center. To create a connection between a DataTable and a sheet, you must also be an admin or owner of the sheet.
- On the left navigation, select Browse and then select DataTables.
Get started with DataTable
- Create a DataTable.
- Upload data to a DataTable with Data Shuttle.
- Connect your sheet(s) to a DataTable. Select your connection type, Add & Upload or Lookup.
You’re done! You can now use data from your DataTable in sheets to analyze, perform calculations, create workflows, and more. If you want to make changes, you can manage your DataTable.
Create a DataTable
DataTable is a way Smartsheet can store millions of rows of data and sync subsets of that data into sheets. Once your DataTable is created and populated with data, you can connect the DataTable to a sheet (or multiple sheets) and use Smartsheet capabilities to work with your data.
- On the left panel, click Browse> DataTables and then click Create.
- Enter a name for your DataTable and then select Next.
You can add fields to your DataTable. DataTable supports text, number, date, and checkbox fields. Fields in DataTable work very much like columns in a sheet. You can enter whatever type of data you want but the type will determine how you can search across the data. For example, if you want to search based on dates, your field will need to be of type date.
- Fill out the Create fields for your table form and then click Next. Enter a name for the field on the left and select the field type on the right.
- Select Next and review the fields. Select Back to make changes or Create to build the DataTable.
Follow the step-by-step instructions to upload data to a DataTable.
Use Data Shuttle to upload data to a DataTable
Data Shuttle is the best way to upload data to your new DataTable. If you don’t have data in your DataTable, you’ll be prompted to set up a DataShuttle workflow.
Using DataShuttle, you will:
- Create an upload workflow for your data
- Set the DataTable as your target
- Configure how you want the data uploaded
- Schedule your workflow and more
Learn more about using DataTable to upload data here.
Follow the step-by-step instructions to connect a sheet to a DataTable.
Connect a sheet to a 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
- Navigate to the sheet you want to connect with a DataTable and then select Connections.
- Select Connect to a DataTable.
- 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
- 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.
- 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.
Set up the connection
- Go to the sheet you want to connect with a DataTable, select the Connections menu, and then click Connect to a DataTable.
- Select Lookup mode.
- 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.
On the left are DataTable fields, and on the right, sheet columns.
To map data from your DataTable into a Sheet:
- Slide the toggle next to the field you want to map.
- Select the sheet column from the dropdown.
- Select Next to continue or Back to edit your unique identifiers.
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.