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.
 

About DataTable

APPLIES TO

  • Smartsheet Advance Package

RELATED CAPABILITIES

Who can use this capability?

You must be an Admin or Owner of the sheet to use DataTable.

DataTable can store millions of rows of data, integrate with external systems using Data Shuttle, and sync subsets of that data into sheets.

DataTable allows 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.

Access DataTable

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. 

 

Browse interface showing DataTables icon highlighted

 

Prerequisites

To use DataTable, you must:

  • Be a Licensed User
  • Have Owner or Admin permissions on any DataTable connected sheets

Get started with DataTable

You can build a DataTable from scratch, but the easiest way to create one is to upload the data via Data Shuttle. 

  1. Create a DataTable.
  2. Connect a Data Shuttle workflow to DataTable to import additional data into the DataTable
  3. Set up connections to bring records from DataTable into a sheet
  4. Manage your DataTable. (edit field settings, delete records, track and edit connections)

DataTable tips and best practices

Keep these tips in mind as you work with DataTable:

  • Your imported dataset can contain no more than 2 million rows and no more than 200 fields/columns.
  • If you perform lookups and updates to your data or merge in new rows, your data must contain a column with a unique identifier for each data point. The unique identifier keeps your data consistent. When creating multiple workflows into a DataTable, use the same field in each workflow for your unique identifier to prevent empty fields or duplicate rows.
  • You can connect up to 100 sheets to your DataTable.
  • When connecting a DataTable with a sheet via Data Shuttle, Smartsheet limitations still apply.
  • Data should flow from your original source into DataTable, and down into subsets of sheets. DataTable cannot be used to sync data back from your sheet. Use Data Shuttle to offload sheet changes back to your original source, if required. 
  • The connection ID is a helpful piece of information if you need support. To find the ID, go to the Connection Summary screen and select the arrow icon between the DataTable and sheet names.
  • Data Shuttle workflows into a DataTable have different options to those directly into a sheet. Data Shuttle workflows:
    • Cannot schedule workflows to run On Attachment to a sheet
    • Cannot add advanced date format options
    • Cannot replace all data with a new import file
    • Cannot delete rows that do not match filter criteria

Follow the step-by-step instructions to create a DataTable.

Create and populate a DataTable

APPLIES TO

  • Enterprise
  • Smartsheet Advance Package

RELATED CAPABILITIES

Who can use this capability?

You must be an admin or owner of the sheet to access 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 allow cross-team collaborations using a single data source.

Prerequisites

To use DataTable, you must:

  • Be a Licensed User
  • Have Owner or Admin permissions on any DataTable connected sheets
  • Have DataTable permissions enabled in User Management by a Smartsheet System Admin

You can add, edit, or remove fields after you've created the DataTable. DataTable supports text, number, date, and checkbox fields. The field type is important for searching and filtering – for example, if you want to search or filter based on dates, your field will need to be the associated date type.

Create a DataTable

There are two ways to create a DataTable: Create an empty data table and populate the data later, or use a Data Shuttle workflow. 

Option 1: Create an empty DataTable

Using this method, you will create an empty DataTable and then use a Data Shuttle workflow to populate the data. Creating the DataTable first gives you more data formatting options at setup. 

  1. On the left navigation bar, select Browse > DataTables.
  2. On the top right corner of the DataTables screen, select Create, then select From scratch
  3. Follow the instructions on your screen.

You can edit the DataTable schema on the Setting page at any time. Be aware changes can impact your sheet connections and Data Shuttle workflows.

Option 2: Create a new DataTable and Data Shuttle workflow

The import wizard auto-detects the source file column heading and maps the headers to your DataTable schema.

This method does not support advanced date format options. The Data Shuttle workflow option will only detect the standard ISO format YYYY-MM-DD HH:MM:SSZ. If your source file contains a different format, use option one, above. 

  1. On the left navigation bar, select Browse > DataTables.
  2. On the top right corner of the DataTables screen, select Create, then select From OneDrive, Google Drive, Box, or Smartsheet attachment
  3. Follow the instructions on your screen. 

Import data into a DataTable

Once you have a created DataTable, you can create DataShuttle workflows to populate the DataTable with additional data.

You can create multiple workflows for a single DataTable, but use the same unique identifier to prevent the addition of duplicates and empty fields on subsequent workflow runs.

Learn more about DataShuttle. 

  1. Log into datashuttle.smartsheet.com
  2. Select the plus icon in the left navigation bar.
  3. Select Upload Data and follow the instructions on your screen. For Target (step two), select DataTable and proceed with the setup.

Follow the step-by-step instructions to upload data to a DataTable.
 

Follow the step-by-step instructions to connect a sheet to a DataTable.
 

Connect a sheet to a DataTable

APPLIES TO

  • Smartsheet Advance Package

RELATED 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 a DataTable are kept in sync with your source of truth.

Prerequisites

To use DataTable, you must:

  • Be a Licensed User
  • Have Owner or Admin permissions on any DataTable connected sheets
  • Have DataTable permissions enabled in User Management by a Smartsheet System Admin

Connect a sheet to a DataTable

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 a 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, 
    similar to a VLOOKUP 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

Step 1: Select the table


  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 Add and update mode.
  3. Go to the sheet you want to connect with a DataTable, select the Connections menu, and then click Connect to a DataTable.

Step 2: Filter your data

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

You must have at least one filter criterion; 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

Step 3: Map data from the 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.

Step 4: 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. 

Step 5: 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

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 a DataTable and then select Next. The list shows only the DataTables you have created.

Step 2: Select a unique identifier

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

  • After you map your unique identifiers, select Next.

Step 3: Match fields

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

To map data from the 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.

Step 4: Review connection

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.