Applies to

Smartsheet Advance Package

Capabilities

Who can use this capability

  • Owner
  • Admin

DataTable FAQs

PLANS

  • Smartsheet Advance Package

Permissions

  • Owner
  • Admin

To use DataTable:

  • You must have Owner or Admin permissions on any sheets where a DataTable Connection will be built.
  • DataTable must be 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

How often is data synced from a DataTable to a sheet?

DataTable checks for any new updates and pushes them to the sheet approximately every 5 minutes.

If I change the data in my sheet, will DataTable overwrite my manual changes?

It depends on how you set up your connection.

If you use Add & Update mode, your connection syncs data into your sheet when you add or change a record in the DataTable. The connector settings that affect syncs are Add rows as they are added to the DataTable and Update rows as they change in the DataTable.

If you use 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 matching, the connection locks the mapped columns so that people can’t edit the data.

Learn more about connections

 

What data does DataTable sync to my sheet?

Currently, DataTable only syncs data into cells. It doesn’t sync formatting, attachments, proofs, comments, or cell history.

Does the data sync in both directions?

DataTable syncs data one way, from your DataTable into a sheet. It doesn’t sync changes you make in the sheet back to the DataTable.

What happens if my sheet fills up?

Your sheet can fill up when the filter criteria for your DataTable connection match too many records or add more data to your sheet.

If your sheet reaches the capacity limits of 500,000 cells, the connection stops syncing data.

 

If syncing stops, update the filter criteria for your connection to be more specific and remove rows from your sheet.

 

Once you have created room in your sheet, you can activate the connection again.

 

For a lookup mode connection, what happens if there are multiple rows in my sheet with the same unique identifier?

Each row in the sheet has the corresponding values mapped in based on how you configure the connection.

 

For a lookup mode connection, what happens if there are multiple records in my DataTable with the same unique identifier?

The connection uses the record it finds first. Typically, that is the record with the lowest record ID.

For a lookup mode connection, what happens if I change the unique identifier in my sheet?

The DataTable overwrites existing data in the sheet cells that map to the DataTable.

How do I create a date field in my DataTable?

When you create a new DataTable, add a field and select Date as the type, and specify the date format.

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. The date format only affects how the dates parse when importing data. For example, if you select 12/31/1999, the DataTable will expect your dates to load in the format mm/dd/yyyy.

 

If you load in data to a DataTable without specifying the Date format, the expected date format is yyyy-mm-ddThh:MM:ssZ, e.g. 2020-09-17T00:00:00Z. This is the case if you create your DataTable initially from a DataShuttle workflow. 

 

If you later change the date format, the DataTable needs time to process the change before you can load data in the new format. In addition, you’ll need to update any records in your DataTable that have dates in the old format before they can filter correctly in a connection.

 

 

How do I filter based on a date field in my DataTable?

When you create a new connection, select a field of type Date and then choose the date with the date picker, using the format mm/dd/yy. For example, to filter records after July 9, 2021, you would enter 07/09/21. For date comparisons, you can use operators like is less than, is greater than, is between, is equal to, and is not equal to.

An escalation identified that the ‘In the Next/Last N days’ filter option was excluding the current day, causing inaccuracies in customer data. To address this, a new filter option was created, stating ‘from today/tomorrow/yesterday’. This allows customers to include today's date in their relative date filter criteria if desired.

 

Sometimes my numbers import as strings, not numeric values. Why is that?

DataTable doesn’t interpret special characters like currency symbols or commas as defining numeric values. DataTable only sees those characters as part of a text string. Use decimal places to indicate currency amounts or percentages instead.

  • To format percentages in your data set, use a decimal without a % symbol. For example 0.5 instead of 50%
  • To format monetary values, use a decimal only. Don’t use a comma or any currency symbols. For example:  15000.00 instead of $15,000.00 or 15,000.00

To have your data, appear on sheets as numbers:

  1. Set the DataTable field type to Number.
  2. Send data into the DataTable with the following restrictions:
    • Strip out commas for numeric values, for example, change 1,000 to 1000 
    • Convert percents to decimal format. For example, change  99% to 0.99  or 78.23% to .7823 
    • Strip out currency symbols, for example, change $123.45 to 123.45 

Is there a limit on the number of sheets that can be connected to a DataTable?

Yes. You can connect up to 1,000 sheets to your DataTable.

Is there an additional cost for DataTable?

Yes, DataTable is a paid premium offering. Learn more about the features and benefits here. For information about pricing, please contact our sales team.

Why would I want to use DataTable? Why not just store my data in a sheet?

DataTable is meant to store larger datasets in Smartsheet so you can reference across multiple sheets. This is helpful for ultra-large datasets like client lists or part numbers that may be coming from other systems. 

If you have a dataset less than 20,000 rows, you can achieve similar results by porting your information into a sheet and creating DataMesh workflows to other sheets. 

If your dataset is larger than 20,000 rows, you would need to load the dataset into DataTable to be able to leverage it within Smartsheet. 

Can DataTable connect directly with other external systems?

DataTable cannot connect directly with external systems. The way to bring in data from external systems into DataTable is via file exports from your external systems and file imports into Smartsheet via Data Shuttle. Data Shuttle can perform automatic uploads of CSV or XLSX exports from other systems.

If you need to create a direct connection to another system, we have several integrations and connectors to specific systems (including bi-directional syncs with JiraSalesforce, and Microsoft Dynamics 365). Bridge by Smartsheet can also be used to create custom integrations with other systems. Contact Smartsheet Sales to speak to a representative and learn more about what product fit is best for your situation.

What data sources can I use as ingestion points for DataTable?

Since the upload mechanism of DataTable is through Data Shuttle, the options for data upload sources are the same as those for Data Shuttle — CSV, XSLX, or Google Sheet files that are either attachments to a sheet in Smartsheet, or stored in Google Drive, Microsoft (OneDrive/SharePoint), or Box. 

When I have DataTable purchased on my account, who can use it?

If DataTable is available on your account, your System Administrator can grant access to any user on your account (individually or in bulk) to DataTable at no additional cost. Access can be granted or restricted through the User Admin Center.  

How do I see what DataTables have been created?

You can always see any DataTables you have created. If you’re a Smartsheet System Admin, you can see all DataTables created by users in your organization. For more information on managing DataTables, see our Manage existing DataTables article.

How does DataTable deal with sheet limits?

While DataTable has a limit of 2 million rows, sheets still abide by the Smartsheet limits of 20,000 rows. Because of this, when creating a connection to a DataTable, you will need to filter down to a dataset smaller than the sheet limits. 

If your sheet reaches its capacity limits, the connection will stop syncing data. If this happens, update the filter criteria for your connection to be more specific and remove rows from your sheet. Once you have created room in your sheet, you can enable the connection again.

What are known limitations of DataTable?

DataTables can contain 2 million records (rows), 200 fields (columns), and 4,000 characters per field. 

Each DataTable can have 100 connections to sheets and each sheet can have 1 connection to a DataTable (i.e. you cannot connect a sheet to multiple DataTables).

 

Was this article helpful?
YesNo