Data Types in Smartsheet

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:

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:

This appears to happen somewhere between 9,000,000,000,000 and 9,999,999,999,990.

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:

Observe that taking “Start” and turning it into a string yields an 8 AM time as well.

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:

Select the above to build the reference
Here, you can select either a cell, cells, a column, or set of columns.

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)).

We can insert values into this column type by creating a =JOIN([Primary Column]1:[Primary Column]2, 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.

APEX IR All Columns Table

I haven’t seen this particularly well documented, so I’m going to write about it.  TL;DR – there’s a table that handles the mapping between database columns to APEX reports.

I recently had the problem where I needed to find the equivalent to all_tab_columns in the database.  all_tab_columns is a table that stores information on tables that has stats about a lot of random stuff:

Screen Shot 2017-08-14 at 10.00.51 AM

describe all_tab_columns

Frequently, this table is used as a source of information and not one that is directly queried upon a lot.  This table describes, among other things, useful information about dependencies and data model (easier to find instances where columns are used on views without directly using SQL Developer), column and table names (to cross reference), etc as well as a number of somewhat useless stats (looking at you, average column length).

Screen Shot 2017-08-14 at 10.07.24 AM.png

Let’s sort! (?)

There’s also a user_tab_columns that allows for pull on schema:

(Writer’s Note: while testing for these things, I ran into ‘SP2-0749: Cannot resolve circular path of synonym “user_tab_columns”‘, which apparently is also an extremely poorly documented error as well.  This is non-malicious, and resolved by rerunning the describe.  I assume it’s problematic only because my connection cut while running the query twice. A Google search on this yields precisely three results.  Maybe we can make the 4th!)

Screen Shot 2017-08-14 at 10.13.04 AM.png

user_tab_columns

As one would expect, this is the same table as above but without the SYS tables/views. In fact this pretty much just has the owner’s tables/views because the database is smart enough to know what schema you’re in.   Either way, these are tremendously useful.

Meanwhile, in Frontendland, you have APEX Report Pages.  These interactive reports (IR for short) are built upon a query on your database.  Your report query has a lot of niches that a traditional SQL or PL/SQL cannot do, but it is nice being to quickly raise a page that end users can view.

Let’s assume we have a table called employees with five columns:

  1. ID
  2. DEPT_NO
  3. NAME
  4. SALARY
  5. YEARS_WORKED

Using this, we can do a number of things.  For example:

  • APEX can create an interactive report titled Senior Employees and sort on number of years with some conditional logic; employees here can be sorted into buckets of <5, 5-10, 10-20, 20-30 with appropriate titles: entry, senior, executive, and senior executive based on years worked.  On Apex, this interactive report might look like:
    • Table: Seniority
    • Columns: Name, Department Number, Seniority (Virtual Col. based on above logic)
  • APEX can create a registry for departments.  We can generate three reports based on departments that are authorized for each department’s users:
    • E.g.: Accounting Registry, Sales Registry, Product Development Registry
    • HR would be given access to each of these authorizations
  • APEX can create a tiered salary auditing system for parity’s sake – when handing out raises, managers can query on Salary and Years worked.

Let’s assume that Luke’s Board Game company has all of the above.  Each of these reports happen on different pages in APEX.  Thus, we have our database columns hacked and mashed into different tables that show up for the end users.  However, these aren’t displayed as what they are in the database.  However, APEX doesn’t really store anything, really.  It’s all done in the database, and APEX is just a front end handler.  How does APEX hack this together?

Queue a magic table: APEX_APPLICATION_PAGE_IR_COL.

Screen Shot 2017-08-14 at 10.29.58 AM.png

APEX cousin of all_tab_columns

As you can see, this table is broken hierarchically.  This table saves workspace, application ID, and application name, and loads all of these things when displaying them on the graphical interface for building on APEX.   All of the above changes would be stored in this table.

In the above scenarios

  • APEX can create an interactive report titled Senior Employees and sort on number of years with some conditional logic; employees here can be sorted into buckets of <5, 5-10, 10-20, 20-30 with appropriate titles: entry, senior, executive, and senior executive based on years worked.  On Apex, this interactive report might look like:
    • Table: Seniority | Really not a ‘table’ to us technical folk.  This would be stored under Region Name, and the page would be stored here as well.  Each region (the region displaying this) has a unique ID and the columns themselves have a unique ID in this table.  
    • Columns: Name, Department Number, Seniority (Virtual Col. based on above logic) | This table is the table that stores the mapping between Department Number and dept_no is stored.  Note that this stores column aliases, so if you have virtual columns, they’re stored as the name you give them
  • APEX can create a registry for departments.  We can generate three reports based on departments that are authorized for each department’s users: 
    • E.g.: Accounting Registry, Sales Registry, Product Development Registry | stores “accounting_registry” if you give it that alias.  This does NOT store the base column name.
    • HR would be given access to each of these authorizations | Slightly divorced from authorizations, but this table also stores the permissions for things like sorting, filtering, group bys, charting, and other interactive reporting features. 
  • APEX can create a tiered salary auditing system for parity’s sake – when handing out raises, managers can query on Salary and Years worked. 

Additionally, the Component Signature column stores a lot of the deep-dive page-level information.

I will probably do a follow-up post on this, but this is a very powerful table to get to know.

 

-L

 

 

This was written using APEX 5.1 and Oracle SQLDeveloper 4.2 on Aug 14 2017.