Smartsheet logo

Related Items

Cell Reference

Cell Value Representation

Cell objects retrieved through the Smartsheet APIs have two main attributes representing cell values: Cell.value, and Cell.displayValue. A third attribute, Cell.objectValue is currently used only for adding and updating predecessors, or for multi-contact or multi-picklist details, such as email addresses or values in a multi-picklist. An empty cell returns no value.

Cell.displayValue is always a string and is only returned for certain column types (see below). It represents the formatted value as it should be displayed to an end-user. For example, if a TEXT_NUMBER column is formatted as a US Dollar currency, its value may be a number like 1234.5678, but its displayValue is "$1,234.57".

Cell.value represents a cell's raw value and can be one of the following primitive JSON types: string, number, or Boolean, depending on the column type. An empty cell returns no value. Complex types are represented as strings, formatted as described below:

Help with Project Columns

Column Type Possible Types for Cell.value Returns Cell.displayValue?
ABSTRACT_DATETIME string: a project date and time in ISO-8601 format, or a free-form text value.
number: see Dates and Times for how to request dates to be returned as numbers.		 No.
CHECKBOX Boolean: true if the checkbox is checked, false if unchecked, no value if the cell hasn't been touched yet.
string: a free-form text value.		 No.
CONTACT_LIST string: an email address representing a contact, or a free-form text value. Yes: same as value for free-form strings; for contacts, the contact's name if any, else their email address.
DATE string: a date in ISO-8601 format, or a free-form text value.
number: see Dates and Times for how to request dates to be returned as numbers.		 No.
DURATION string: a duration value such as "4d 6h 30m" in the user's locale, or a free-form text value.
See the [Help Center](<%= PROJECT_COLUMN_HELP_URL %>) for more information on durations.		 Yes: same as value
MULTI_CONTACT_LIST string: only visible when using a query parameter of level and the value appropriate to the dashboard, report, or sheet that you are querying, otherwise the column type is TEXT_NUMBER. Yes: same as value; to see actual email addresses, see below.
MULTI_PICKLIST string: only visible when using a query parameter of level and the value appropriate to the dashboard, report, or sheet that you are querying, otherwise the column type is TEXT_NUMBER. Yes: same as value; to see objectValue, see below.
PICKLIST string: one of the picklist's column options, or a free-form text value.
number: numeric values		 Yes: same as value for strings; for number values, the number with formatting applied.
PREDECESSOR string: a comma-delimited predecessor list such as "12FS +3d 4h, 14SS", or a free-form text value.
See the [Help Center](<%= PROJECT_COLUMN_HELP_URL %>) for more information on predecessors.		 Yes: same as value
TEXT_NUMBER string: free-form text values
number: numeric values		 Yes: same as value for strings; for number values, the number with formatting applied.

Cell.objectValue is an object representation of a cell's value and is currently used for adding or updating predecessor cell values, or for multi-contact details, such as email addresses.

  • For predecessors, it provides a more "programmer friendly" format for assembling predecessors. To update a cell's predecessors, set objectValue to a PredecessorList object containing Predecessor objects.
  • For multi-contact or multi-picklist details, use both a level query parameter and an include=objectValue query to see email addresses rather than display names or to see multi-picklist values.

Cell Value Parsing

The flexibility in cell value data types is a powerful feature in the Smartsheet application; however, it poses a challenge for an API in terms of parsing. Being too flexible might result in unexpected behavior. For instance, if you write code to post a Date value to a Smartsheet and the API operation succeeds, you might assume that the date value you sent was interpreted as date. What happens if you posted your date in the wrong format? Do you really want Smartsheet to keep the malformed date as a string value? Probably not.

To address this problem, the Smartsheet API employs a simple scheme to indicate whether you want a more predictable and strict interface or a more flexible one. By default, a cell value is expected to conform to "strict" rules for the type of the cell's column. If an input value doesn't conform, the API returns error code 1042.

If, however, you want the same flexibility as the Smartsheet Web app, you can disable the strict rules, and we’ll do our best to make sense of it. To enable lenient parsing simply include "strict": false in the Cell object in your request body.

NOTE: How does strict cell value parsing compare to cell validation settings? Strict cell value parsing determines how string values are parsed. It is set on a per-call basis. In contrast, cell validation is part of the column definition. The overrideValidation property is restricted to sheet admins and does not override strict parsing.

The parsing rules for the various column types are as follows:

ABSTRACT_DATETIME

Value Description
lenient Smartsheet attempts to convert the string value to date using ISO 8601 date format, as well as several locale-specific date formats. If the value is a parsable date format, Smartsheet recognizes the date and stores it as such. All other values are simply text values.
strict The value must be a string value and a valid ISO 8601 date (YYYY-MM-DD). Alternatively, if Unix time (also known as epoch time) is used, you can use the query parameter of numericDates set to true to have Smartsheet convert epoch time to human readable dates. See Dates and Times for more information.

CHECKBOX

Value Description
lenient Boolean values and string values of true and false are handled the same as strict. All other values are saved as text values.
strict Only Boolean values (true or false) are valid.

CONTACT_LIST

Value Description
lenient If the value is a valid email address, Smartsheet handles it the same as strict. If not, Smartsheet saves the value as a text value.
strict The value must be a valid email address. If displayValue is set, Smartsheet uses that as the name; otherwise, if Smartsheet finds a match among the the access token owner's contacts, Smartsheet associates this cell with that existing contact.

NOTE: See Contact List Columns for more information.

DATE

Value Description
lenient Smartsheet attempts to convert the string value to date using ISO 8601 date format, as well as several locale-specific date formats. If the value is a parsable date format, Smartsheet recognizes the date and stores it as such. All other values are simply text values.
strict The value must be a string value and a valid ISO 8601 date (YYYY-MM-DD). Alternatively, if Unix time (also known as epoch time) is used, you can use the query parameter of numericDates set to true to have Smartsheet convert epoch time to human readable dates. See Dates and Times for more information.

DURATION

Value Description
lenient Numeric values are treated as duration values in days. String values which are valid duration strings in the user's locale are treated as durations, and any other values are treated as free-form text values.
strict Only valid duration strings in the user's locale are valid. Information on duration strings can be found in [Help Center](<%= PROJECT_COLUMN_HELP_URL %>).

NOTE: You may use the query string parameter projectParseLocale with a supported locale string to force parsing in the specified locale (for example, using en_US lets you send in English values regardless of the user's locale).

MULTI_CONTACT_LIST

Value Description
N/A Set using the objectValue attribute for the Cell object, which is inherently strict. See Cell Reference.

MULTI_PICKLIST

Value Description
N/A Set using the objectValue attribute for the Cell object, which is inherently strict. See Cell Reference.

PICKLIST

Value Description
lenient All numeric and text values are valid. Formatted numbers are parsed like TEXT_NUMBER formatted numbers.
strict The value must be a string and must be one of the options for the picklist.

PREDECESSOR

Value Description
N/A Set using the objectValue attribute for the Cell object, which is inherently strict. See Cell Reference.

TEXT_NUMBER

Value Description
lenient All numeric and text values are valid. Formatted numbers passed as text values, such as currencies ("$5,000"), percentages ("50%"), or decimals ("100.5") are parsed to their numeric equivalents, based on the locale of the access token owner, with the proper formatting enabled for the cell.
strict All numeric and text values are valid and are interpreted literally.

NOTE: The Smartsheet application only supports numeric values in the range -9007199254740992 to 9007199254740992. If using strict parsing, any numeric value outside that range results in error code 1148. If using lenient parsing, the value is silently converted to text.

Contact List Columns

With columns of type CONTACT_LIST, the cell attributes value and displayValue are treated independently. The contact’s email address is represented by value, while the contact’s name (and the value displayed in the cell in the Smartsheet app) is represented by displayValue.

When creating or updating cells for a contact list column, the displayValue attribute works as follows:

  • If displayValue is non-null and non-empty, the Smartsheet cell displays the value provided.
  • If displayValue is an empty string, the Smartsheet cell displays the email address.
  • If displayValue is null or absent, Smartsheet makes a best guess effort at filling it in with a contact’s name based on the email address.

Images in Cells

For details about working with images in cells, see Cell Images.

➔ Next to Cell Images Basics