Technology

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.

Smartsheet: Checkboxes

Author’s note: everything I say about checkboxes applies to flags and stars

An interesting thing I’ve noticed is the data type ‘null’ in Smartsheet. If we look at a given checkbox column, all of the checkboxes appear to be unchecked:

Let’s put example data in here. Suppose you are the head of a shipping and logistics branch, and you wanted to make your sheet track whether or not a shipment was sent.

Let’s suppose that in this way, you’re using Delivery Date to represent the date in which you clicked your checkbox. However, at some point, you want to send the checkbox back to no because the driver miscommunicated and accidentally checked off Test 7 when they should have checked off Test 8:

There, we fixed it right?

Not entirely. Look at the column Out for Delivery below, as I change it into Dropdown (Single Select):

I’ve now introduced the value false. Note that Smartsheet is actually doing a favor to you by updating these in plaintext. In fact, true false are protected values in a checkbox column. Whenever you are writing a formula to count the number of ‘true’s and ‘false’s, Smartsheet is doing some data type reconciliation.

In essence, a checkbox truly holds 3 types of data: true, false, and null. Only checkboxes that have never been used hold null. They will generate a checkbox for you visually when you insert any value on any other row-level cell. It is difficult for the user, then, to know whether something was false or null; we had a technical escalation once, as once upon a time, Pivot App was distinguishing between false and null and was erroneously (at least to the end user) producing an incorrect result.

In this way, if you are doing API work, be aware of this difference. It likely does not matter most other times.

When you are checking a checkbox column, note that your formula will return ‘true’ (in logic terms, not Text/Number) if you do [Out For Delivery]7 = 0 within our original screenshot. This is because Smartsheet is also doing a nice thing by allowing you to count 1’s and 0’s within checkboxes.

Thus, null and false map to 0 and true maps to 1. In formulas and sheet-level comparisons, it is equivalent to say [Out For Delivery]7 = true and [Out For Delivery]7 = 1.

Another implication of this data reconciliation aspect of the column is that it reads logical statements. If you wanted to make the checkbox automatically turn “true” when Delivery Date is not blank, you can write a formula that says =[Delivery Date]@row <> "". No =IF() needed!

Note that this formula only really works in the checkbox column, as it produces a logical value. Otherwise, you will get:

After changing the column back into a checkbox and viewing cell history, I see:

In this way, this does NOT present false when you are switching the column back, as the data type is still system true or false. Strangely, if you do our standard technique of checking system values, by doing

=[Out for Delivery]@row + “” in order to convert the data type into text, it says false (as shown in the screenshot below). However, this is true about our null cells as well.

How you CAN tell is when you change the column value back:

Smartsheet: How to Count an Element in a Multi-Select Dropdown

My coworker Anthony and I were working on sorting out the prioritization of enhancements. In order to properly count the number of elements within a Multi-Select, CONTAINS() won’t do it right. Below, I will walk through the thought process.

In a data set, where you are trying to count enhancement data and you have 2 sheets, you would start with building a metric.

Column Name: Enhancement

  1. Sheets
  2. Sheets, Dashboards
  3. Reports
  4. Sheets – Scale
  5. Sheets
  6. Sheets

….

You may be tempted to start off with a simple count.

=COUNTIF({Enhancement}, "Sheets")

You quickly recognize, however, as your product gets more and more robust, “Sheets” is no longer specific enough. Also, the column is Multi-Select Dropdown, so if we write our formula like above, we will fail. This is because Smartsheet will attempt to do a string match.

In our above example, the formula will return 3. We want it to return 4.

We need to add:

  1. A way for the string match to not be an exact match
  2. A way for the data to be accurate for the “Sheets” label

You may be tempted to implement:

=COUNTIF({Enhancement}, CONTAINS("Sheets", @cell"))

However, this would produce 5. Why? Because our data contains one element: “Sheets – Scale” would falsely set off our flag because this specifically, this element contains “Sheets”. Thus, our formula would add it.

The correct implementation would be:

=COUNTIF({Enhancement}, HAS(@cell, "Sheets"))

This would correctly produce 4. The reason is that the HAS() formula looks at either a value or a range. You need to call the cell itself as a range into the HAS() formula. If you call an array (i.e. Multi-Select Dropdown cell within Smartsheet with multiple elements), Smartsheet will intelligently consider this a range. Within this range, it will search for “Sheets” and correctly flag the answer. Note that HAS() also produces a boolean, so it would return true for this count comparison. This is what “@cell = 5” or other comparators in COUNTIF() is doing any time you are attempting to evaluate a range.

Smartsheet Advanced Tips: SCC Update All Reporting

This post represents a public cross-listing of a post I made to the Consulting Organization at Smartsheet in Feb 2019. The purpose is to help out potential end-users reading this. All thoughts are my own.

My team and I have discovered that you could use Global Updates to bulk update reporting. I have recently had to use this in order to update 100+ projects quickly.  The clients had provisioned many projects without us expanding the inbound cell-link limit for the summary sheet, so many of the projects were not able to be cell-linked in correctly.  Associated with this methodology are two major advantages:

  • Speed & Effort: this is the primary advantage.  By executing this by the push of a global update, we can ensure that this process is done the same every time
  • Robustness: this is the secondary advantage.  By allowing the engine to track successes and failures, we can make sure that the correct info is filled in for each project.  The Global Updates error handler will allow you to detect errors faster and with greater accuracy than a manual sweep would, especially if your solution has multiple summary sheets and / or metadata sheets.

In order to implement the methodology, we have to use add profile data.  As tested, adding a new column or locking a column will not work. These do not appear to call the profile data reporting piece of SCC.  To our best knowledge, you must use the “Add Profile Data” method, although further tests were not completed on the remaining methods.

In order to complete this in the task in the least destructive manner, we want to update profile data that already is blank in the main text / value column, one column to the right of our primary column.  A good example of this is updating profile data that has nothing in the primary column. To achieve this, I updated “Project Manager” with a blank value within the text/number value column. To note, it was already blank because we stored the data for “Project Manager” in the associated contact column in the Metadata sheet.

Then, running the global update is standard.  Roughly 6/140 projects failed on the first pass unexpectedly.  I simply re-ran the global update to complete the process. It is unclear why the update failed for some, but after running the process again, there were no obvious impacts. 

Smartsheet Advanced Tips: Calculate Relative Date

This post represents a public cross-listing of a post I made to the Consulting Organization at Smartsheet in Feb 2019. The purpose is to help out potential end-users reading this. All thoughts are my own.

I’ve come up with a useful formula that can be used to dynamically calculate the date of the Sunday, given a week:

=Date@row - (WEEKDAY(Date@row) - 1)

Breaking this down, the Date@row is the date given to calculate.  This can be any date given this week.  For example, today is Tuesday, the 19th.  

The subtraction subtracts the quantity of the weekday of today (in this example it’s 3) from the date today.  To get Sunday (which is the first day of the week), we need to subtract two days from this. Thus, there is an additional – 1 for Sunday.  If you need to calculate other days, we will need to shift the – X.  For example, for Monday of the week, we will need to -2 from WEEKDAY(Date@row). 

This is useful in a number of ways, but primarily, I’ve used it in two cases.

Weekly / Monthly Status Log

If a client needs to do status tracking and have a visible historical, we can create a log that automatically inputs a list of days they need to fill in the statuses.  This plays nicely with alerts and actions, as we can drive an update via the dates.  This also mitigates the use of the today() function, which has been traditionally problematic, while allowing us to have a dynamic status update requests and mitigating manual work.  In this example screenshot, we have a current month (manual, allows for selection of display status), a month of the project, the date related to that month (i.e. first Sunday of the month), and the status update.  We surface this to both the Project Dashboard and the master roll up as metadata, but this works with an update request to the Project Manager. 

Cycle Time (Weeks)

If you want to compute cycle time by weeks, this is one way to do it (number of Sundays elapsed).  This is a workaround for our not having a modulo and round function, since round can go up or down.  If you approximate the weeks via Sundays, you will always get a number that is a multiple of 7.  This cycle time measure is useful for project types that are longer, as I’ve heard clients say that 285 or 390 is such an abstract number, whereas 40 weeks or 56 weeks is a nicer way of doing so.  This also helps create consistency with Gantt charting that doesn’t go by days.  

Smartsheet Advanced Tips: TODAY() Refresh

This post represents a public cross-listing of a post I made to the Consulting Organization at Smartsheet in Jul 2020. The purpose is to help out potential end-users reading this. All thoughts are my own.

As a set of guiding principals and assumptions, let’s start off with the basics:

  • A cell-link or static reference (`DATE1`) is cheaper than a new calc (`TODAY()`)
  • A refresh on one sheet is more expensive than a refresh on many sheets
  • The maximum cell-link connections you should use is 5 (this is a bit arbitrary, but Cell-Links slow down as number of Cell-Links previous increase

Based on this set of guidelines, let’s define the critical steps to set up the methodology for a single sheet:

Single-Sheet Setup

Refreshing the TODAY() function on a single sheet can be done with the following steps:

  1. Implement `=TODAY()` as needed in a cell (i.e. in `DATE1`) 
  2. Create a new automation workflow that unlocks a row, where a dummy column has a unique value (i.e. Primary == <<DO NOT DELETE>>)
  3. Create a new automation workflow that locks a row, where a dummy column has the same unique value (i.e. Primary == <<DO NOT DELETE>>)

Now, turning them both on, only one should fire each day.  However, this should be sufficient in setting up the workflow. Cell link/formula around your sheet(s) as needed.

When you are done, you should have:

Control Center Setup

Refreshing the `TODAY()` function on the intake sheet can be done with the following steps:

  1. Implement `=TODAY()` as needed in a cell (i.e. in `TODAY1`) 
  2. Implement `=$DATE$1` in all subsequent date cells
  3. Set Primary == <<DO NOT DELETE>>
  4. Create a new automation workflow that unlocks a row, where a dummy column has a unique value (i.e. Primary == <<DO NOT DELETE>>)
  5. Create a new automation workflow that locks a row, where a dummy column has the same unique value (i.e. Primary == <<DO NOT DELETE>>)
  6. Log into Control Center
  7. Select Today as a profile data, linking from `TODAY` column to the `DATE` column on your metadata.
  8. Call this `TODAY` anywhere else in your solution package.

Multi-tier considerations or chained intake sheets should potentially not have the `TODAY()` on the Summary Sheet, as archiving may break these `TODAY()` functions.