DataTable

APPLIES TO

  • Smartsheet Advance Package

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.
 

Get started with DataTable

APPLIES TO

  • Smartsheet Advance Package

RELATED CAPABILITIES

Who can use this capability?

  • Owner
  • Admin

DataTable by Smartsheet 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.

DataTable is available as a premium offering. For pricing information, contact our sales team.

Log in to DataTable

Access DataTable

To use DataTable, your Smartsheet plan admin 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:

  • Have Owner or Admin permissions on any DataTable connected sheets
  • Have DataTable permissions enabled in User Management by a Smartsheet System Admin
  • DataTable must be a premium application available on the plan.

To check your access level, log in to DataTable.

Visit the DataTable Marketplace page to learn how to contact your Sales representative for pricing-related inquiries.

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 more data into the DataTable.

  3. Set up connections to bring records from DataTable into a sheet

  4. Manage your DataTable

DataTable tips and best practices

Keep these tips in mind as you work with DataTable:

  • Your dataset can contain no more than 2 million rows and 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 1,000 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.You can’t use DataTable 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 than those directly into a sheet. Data Shuttle workflows:
    • Can’t schedule workflows to run on attachment to a sheet
    • Can’t add advanced date format options
    • Can’t replace all data with a new import file
    • Can’t delete rows that don’t match filter criteria

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

Create and populate a DataTable

APPLIES TO

  • Smartsheet Advance Package

RELATED CAPABILITIES

Who can use this capability?

  • Owner
  • Admin

Overview

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 must be the associated date type.

To use DataTable, the user must meet all of the following criteria:

  • You must have Owner or Admin permissions on any sheets where a DataTable Connection will be built.
  • DataTable is a premium application available on the plan.
  • You must have DataTable Premium Application permissions enabled in User Management by your Smartsheet System Admin.

To check your access level, log in to DataTable.

Visit the DataTable Marketplace page to learn how to contact your Sales representative for pricing-related inquiries.


Create a DataTable

There are two ways to create a DataTable:

  • Create an empty data table and populate the data later

  • Use a Data Shuttle workflow

 

Option 1: Create an empty DataTable

Using this method, you 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 and then select Create a blank DataTable.

  3. Follow the instructions on your screen.

You can edit the DataTable schema on the Settings page. Be aware changes can affect your sheet connections and Data Shuttle workflows.

 

Option 2: Create a new DataTable and Data Shuttle workflow

Using this method, you create the DataTable and populate it with an initial data set at the same time.

  1. On the left navigation bar, select Browse > DataTables.

  2. On the top-right corner of the DataTables screen, select Create and then choose from the following options:

    • OneDrive

    • Google Drive

    • Box

    • Smartsheet attachment

  3. Select Continue to Data Shuttle and log in to Data Shuttle if prompted.

  4. Follow the instructions on your screen to build the workflow. Learn more about creating a Data Shuttle workflow.

  5. After building your workflow, select Publish & Run to generate a Data Shuttle workflow to upload your data and create your DataTable.

You can continue using this Data Shuttle workflow to load new data sets to your DataTable.

 

Keep this in mind
  • The import wizard auto-detects the source file column heading and maps the headers to your DataTable schema.
  • This method doesn’t support advanced date format options. The Data Shuttle workflow option only detects the standard ISO format YYYY-MM-DD HH:MM:SS. If your source file contains a different format, create a blank DataTable using Option 1.
  • The date format tells the DataTable how to parse the data in your CSV or Excel file when Data Shuttle loads data into your DataTable. For example, if you select 12/31/1999, DataTable expects your dates to load in with the format MM-DD-YYYY. If you load data into a DataTable without specifying the Date format, the expected date format is YYYY-MM-DDTHH:MM:SSZ; for example, 2020-09-17T00:00:00Z

Import data into a DataTable

Once you’ve created a 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.

The Data Shuttle to DataTable features vary slightly, and we don’t yet allow data to be deleted on import.

 

  1. Log in to Data Shuttle.
  2. Select the plus icon in the left navigation bar.
  3. Select Upload Data and follow the instructions on your screen. 
  4. For Target (Step 2), select DataTable and proceed with the setup.

Make sure you’ve selected field type Number for any numeric data. DataTable doesn’t interpret special characters (currency symbols, commas, percentage signs, etc.) as numeric values. Don’t include percent, commas, or currency symbols; instead, format percentages as a decimal value (for example, 0.5 instead of 50%).

 

Smartsheet University subscribers can learn this process in the Create and connect a DataTable course.

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?

  • System Admin
  • Owner
  • Admin

Start using DataTable

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 for you.
  • You must have DataTable Premium Application permissions enabled in User Management by your System Admin.

To check your access level, log in to DataTable. 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 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.

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.