DataTable FAQs

Applies to

Smartsheet Advance Package

Capabilities

Who can use this capability

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

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

If I change the data in my sheet, will the sync process overwrite my change?

It depends on how you set up your connection.

If you use Add & Update mode, your connection will sync data into your sheet when a record is added or changed in the DataTable. If you make a change in your sheet and want it to match the DataTable, you must manually refresh the connection from the Table Connection Summary screen.

If you use Lookup mode, the connection will overwrite changes made in the sheet with data from the DataTable. Your sheet will always match what’s in the DataTable. Because of this, the connection will lock 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 does not 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 does not 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 additional data to your sheet.

If your sheet reaches the capacity limits of 500,000 cells, 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.

 

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 will have the corresponding values mapped in based on how the connection is configured.

 

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

The connection will use the record it finds first. Typically, that will be 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 will overwrite existing data in the sheet cells that are mapped 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 are parsed when importing data. For example, if you select 12/31/1999, the DataTable will expect your dates to be loaded in the format mm/dd/yyyy. 

If you later change the date format, the DataTable will need a few moments to process the change before you can load data in the new format. In addition, you will need to update any records in your DataTable that have dates in the old format before they can be filtered 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 select the date with the date picker. Dates in a filter need to be in the format defined by the date picker (mm/dd/yyyy).

For example, if you wanted to filter to records after July 9, 2021, you would type 07/09/2021. You can use the is less than, is less than or equal, is greater than, is greater than or equal, is between, is equal to, and is not equal to operators for date comparisons.

The field's date format does not change how you need to structure the dates in your filter criteria.

 

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

DataTable doesn’t interpret special characters (currency symbols, commas, percentage signs, etc) as defining numeric values; it 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. Do not 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 .99  or 78.23% to .7823 
    • Strip out currency symbols, For example,  change $123.45 to 123.45