Created: 3/8/22 @ 1 PM EST

Edited: 3/9/22 @ 10 AM EST

Special thanks to Anthony Sanfilippo, David Carli-Arnold, and Michael Bickers for Advance Review

I wanted to write this post because for the technical crowd, the Column Type help article is frequently insufficient for implementation. In this way, this post seeks to offer a more detailed reference to compliment the information in the help article. Regarding API documentation, I will reserve the right to specific and general observations where appropriate, but the documentation serves as primary, and the perspectives presented on this post serve as a foil to base Smartsheet implementation. In this way, references can be found more specifically on the API Documentation. Although deprecated, I reference sections of the old documentation as well.

A list of data types in Smartsheet include the following:

• Text
• Number
• Date
• Datetime
• Predecessor and Duration
• Time
• Contact
• Multi-Contact
• List / Array / Range

Note that this list covers standard grid data types but is not exhaustive; there is nuance around checkboxes, for example, that flag 1 as equivalent to true (system: true) in certain situations. I offer a post on checkboxes in greater detail and may write about different other types in the future.

Text

Text can be determined by using =ISTEXT(). Text is the backbone of Smartsheet data. It should be noted that most cell-linked data comes over to the destination sheet as Text. You cannot use math functions on text. You can tell text from number by observing the placement of the text. Without any formatting, text aligns left, whereas numbers align right (in the US; international variance exists here):

With text, you can perform any manipulation function, and adding quotes to any of the other below data types. This abets the testing process, as frequently, a #INVALID COLUMN TYPE error blocks your testing process, and this is a quick way to see it in plain text.

Number

Numbers behave more or less like numbers behave in other systems such as Excel and Google. A crucial difference in Smartsheet, being an international platform, is that it does not make sense to use string validation functions on numbers. For example, different cultures use commas and periods (, .) to designate decimal places. For our South African clients, we use ; in formulas in place of the comma.

At a certain point, as with all systems, Smartsheet encounters an overflow error:

Date

A date comprises of a Day, Month, and Year, but did you know that depending on account preferences, this ordering can change for different individuals? For example, a company that has offices in EMEA and California may see different things for different individuals. We have ran into this for our clients, and some best practices to follow are as follows:

• Do NOT use static formulas when referring to Dates. For example, the usage of =LEFT(Date@row,2) may be convenient, but if the date were to shift, this function would cease to make sense. Instead: try using the date support functions, such as Month(), Year(), and Day()
• When constructing dates, use the =DATE() function rather than =DD+MM+YYYY. As mentioned above, because of different date formats for different folks, save yourself the time by using something secure.
• You can use up to +/- 99 as a key to enter in a date in a Date column. While this indirectly deals with dates as a data type, this is a useful shortcut when managing project plans and deserves a mention. You can also use t when referring to today to get today’s date. Google and Microsoft, take note!

As well, Dates conform to a sheet owner’s timezone, so a date in one timezone may show up as a different date for another user in Date and Datetime columns. Plan appropriately when using automations with dates.

In the back-end, dates can be thought of as numbers. You can use the function =Date@row + 5, and it will give you 5 days later. This is useful property for us, as formulas such as MAX() and MIN()apply to dates as well.

There are other ways to increment dates, especially when it comes to handling variances. Observe the following 3 functions, taken from the Smartsheet Functions List:

API: The Smartsheet API returns all dates and times in the UTC time zone in ISO-8601 format, that is, YYYY-MM-DDTHH:MM:SSZ. If you are specifying a date and time, you should also send that information in ISO-8601 format. If a date/time needs to be displayed to an end-user in their local time zone, you must do the conversion using the user’s time zone, which you can obtain by getting the current user. Here, value and objectValue are not the same, so be careful with your calls.

Datetime

A datetime inherits many properties of a date, but has a time attached. Datetime is used in a set of protected columns only, so you cannot construct a datetime. To get a date from datetime, use =DATEONLY().

Datetime is used in the following columns:

• Start Date (Gantt) with dependencies
• End Date (Gantt) with dependencies
• Baseline Start Date with dependencies
• Baseline End Date with dependencies
• Created By (System)
• Modified By (System)

For example, below, I do not have predecessors enabled, but “Start” and “End” drive my gantt.

When I convert this to a predecessors enabled sheet, observe what happens:

It’s worthy to note that many of the functions that work on dates work on datetimes as well. Observe the below:

However, this is tricky. By incrementing my duration to 15.5 days, the NETDAYS() function displays 22.0, suggesting that it rounds up half-days.

API: The type is ABSTRACT_DATETIME, which the new documentation does not yet tell you. The Smartsheet API returns all dates and times in the UTC time zone in ISO-8601 format, that is, YYYY-MM-DDTHH:MM:SSZ. If you are specifying a date and time, you should also send that information in ISO-8601 format. If a date/time needs to be displayed to an end-user in their local time zone, you must do the conversion using the user’s time zone, which you can obtain by getting the current user. Here, value and objectValue are not the same, so be careful with your calls.

Predecessor and Duration

Closely related to datetime are Predecessors and Durations.

I won’t go deeply into these types, as the help article is quite robust, and more importantly, you cannot do much with these.

Mostly, when working to build any flags or notifications based on this, a formula writer should observe the case where there are multiple dependencies. This gets tricky, and any formula available at this time is unable to solve for the n-case. Thus, you must settle for some upper bound of dependencies. Discuss within your project team what the right number is when implementing, and be careful in testing this.

Time

Times are a column that is not accessible in Smartsheet to modify. It is worthy to note that they are beholden to the working day parameter:

This is why your date time starts with 8:00 AM and ends with 4:59 PM, typically.

Contact

Contacts are potentially the most complex data type in Smartsheet. Contacts are beholden to multiple forces, including the directory of your organization merged with your personal contact list.

If you do a =Contact@row + "", you get the name:

Otherwise, if no name is available (for my individual personalized training account, I did not enter in contact information, we observe just the email):

It’s worthy to note that you cannot enter in a contact on a form, so any contact fields used on forms should use the validation for email if you want to be able to use contacts in tandem with automations. Although not recommended, a contact can, in this way as well as manually, enter in a sheet with no email. These will fail when trying to send an automation.

API: The above discussion is congruent in the API, where have, for a Contact object:

• id
• email
• name

Similarly, if a get value call is made, you will get the name if available. Otherwise you will get the email.

Multi-Contact

A multi-contact acts as an array of contacts. We will dive deeper into an array in later sections. As one would expect, when taking a Multi-Contact into a string, you get the above, with commas (in a US-based account; international exceptions exist) separating a list.

API: A multi-contact object is very complex. Please refer to the documentation for post/push operations. This was challenging the first time I developed a solution using the API.

List / Array / Range

A list, array, or range, as they’re sometimes called in various states, refers to a matrix of NxM size in Smartsheet, where typically, N or M>1. For the remainder of this post, I will refer to this object type as a range, although this may be semantically inaccurate in certain situations.

In standard usage, functions will call on a range of 1xM size. For example, a simple =SUM() function looks at the highlighted range. This range can either be a column or a select number of cells. Smartsheet will respect standard range formatting, such as [Primary Column]:[Primary Column]. Calling this will get the entire primary column, no matter how long it is. This range can also be designated via cross-sheet reference.

A cross-sheet reference is a repeatable range that looks at another sheet. For example, you can reference a select group of cells or a column. This way, you can create metrics against the entirety of the column. The syntax is defined in a builder:

You cannot store a range in a cell, and many errors from formulas arise when the output of the formula is a range. Even with column types that empirically would respect ranges, such as Multi-select Dropdowns, respect special syntax. See below:

The above doesn’t work, as the Multi-Select Dropdown respects a particular input; in fact, it is a disguised string, with a join on a carriage return (the standard of use is CHAR(10)).

Building a Range

You can build a range a few ways, as the above cross-sheet reference, or using =primary:primary, but the most powerful tool that sees daily usage the COLLECT() function. This function builds a range based on any selection criteria. In essence, this function allows you to do a “Anything-If”. A SUM(COLLECT()) can be equivalent to a SUMIFS(). The same thing is true about any other mathematical-numeric functions, such as MAX(), AVG(), and RANK().

Parsing Through Ranges

Generally, when using a range, there is a purpose or set of data we are looking for. In general, think about the following:

• Element of an ordered range: if you know your element is ordered, you can use INDEX(range, n) to obtain the nth element. For example, if you want to know the first unread ticket in a certain list you’ve built via COLLECT(ticket dates, ticket status, unread), you can pull back that date.
• Math on a range: performing math where SUMIFS() or AVERAGEIF() doesn’t suffice.
• Ordering an unordered list: in the first column, I created an unordered list of numbers. In the second, I use a For-Loop-Style function set to create a ordered list:

Each cell below row 1 has the function =MAX(COLLECT([Unordered List]:[Unordered List], [Unordered List]:[Unordered List], <[Ordered List]n)), where n is the row number of the cell above it.