Leveling Up - Understanding Multi-contact and Multi-picklist Columns in the Smartsheet API

Smartsheet likes to have it both ways: It’s important to keep your implementations from breaking (aka backwards compatibility), but there are always new and more interesting features, such as multi-contact and multi-picklist columns (sometimes referred to as multi-dropdown columns). So how does the Smartsheet API meet both of those criteria?

In gaming language, new features are available when you reach a certain level: level 14 gives you access to a new avatar or at level 31 you can use pineapples as ammunition for your rocket launcher. Gamers say they’re “leveling up.”

In the same sense, new Smartsheet UI features like multi-contact or multi-picklist use a level query parameter as a differentiator between the old way of doing things and the new features. When you don’t use the level query parameter, the values for these types of columns return as a string (as the column type is converted to TEXT_NUMBER).

Example: multi-picklist for backwards compatibility

In the following set of JSON response snippets, the sheet has a multi-picklist column and the cell has two values: May and August.

With a GET /sheets/{id} request and no query parameters, the relevant section of the Columns object looks like the following:

{
    "title": "Column5",
    "type": "TEXT_NUMBER",
    "validation": false,
},

And the relevant section of the Cells object combines both cell values into one string:

{
    "columnId": 8447963757340548,
    "value": "May, August",
    "displayValue": "May, August"
},

You may also have noticed that there is both a “value” and a “displayValue” parameter. (Hint: you’re really hoping for an “objectValue” parameter but it isn’t here yet. The objectValue parameter is more robust and has the capability to display multiple values in an array. I recommend using an include=objectValue parameter if you want the most complete information.)

Example: multi-picklist with the new functionality

By using the level query parameter to tell the Smartsheet API servers that you’ve leveled up, you can get more complex data back with a column type of MULTI_PICKLIST. Don’t forget to include objectValue!

Here’s an example in Node.js:

// Specify query parameters
var params = {
 include: "objectValue",
 level: "2"
};
 
// Set options
var options = {
 id: 5913695999955124,
 queryParameters: params
};
 
// Get sheet with multi-picklist info
smartsheet_client.sheets.getSheet(options)
 .then(function(sheetInfo) {
   console.log(sheetInfo);
 })
 .catch(function(error) {
   console.log(error);
 }); 

In this case, the relevant section of the JSON response for the Columns object will now look like the following:

{
	title: 'example multi picklist column',
	type: 'MULTI_PICKLIST',
	options: [Array],
	validation: true,
},

And the relevant section of the JSON response for the Cells object will look like the following:

{
	"columnId": 8447963757340548,
	"objectValue": {
		"objectType": "MULTI_PICKLIST",
		"values": ["May", "August"]
	},
	"displayValue": "May, August"
},

What About Multi-contact?

Multi-contact works in much the same way. Smartsheet developed multi-contact first, so the levels are lower. But you still want to use both query parameters of level and include=objectValue.

What Level Goes with Which Request?

Let’s go back to the video game analogy. Multi-contact came first, so the level query has a lower number. It’s slightly different for dashboards vs reports vs sheets, but the table below will help you keep track of which level goes with which thing.

Recently, Smartsheet announced multi-picklist columns, so they’ll be the next level. You’ll need to level up again.

If you want multi-contact and multi-picklist information for a sheet, use “level=2”, for a report, use “level=3”, and so on.

Use the Higher Level

Just as in gaming, once you level up, you don’t have to go back a level to use the things from the previous level--they all travel with you. So to be sure you always get both multi-contact and multi-picklist data in your responses, use the highest level as a query parameter in your GET requests.

I don’t know what video game you had in mind when I first introduced the level-up metaphor, but I’m going to go shoot pineapples at aliens using my new rocket launcher! Or, you know, see actual email addresses in the response to my GET query.