Find Columns Quicker with a Column Map

Let's be honest. Finding the right column when you're using the Smartsheet API can be tedious. The API gives you an array of column objects that you loop through to find the object with the matching columnName that you're looking for. Once you find the desired column, you grab the columnId--because to perform any actions on a column through the API you need to have the columnId.

With the Smartsheet JavaScript SDK you can get columns either by calling getSheet() or getColumns(). In this case, let's go for getSheet.

To get things started, initialize a Smartsheet client object with a valid access token, and creating variables for sheetId, dateColumnName, and dateColumnId.

const client = require('Smartsheet');
const smartsheet = client.createClient({ accessToken: smartsheet_access_token });
const sheetId = 3378835908519812; 
const dateColumnName = "Birthday";    // title of the column 
let dateColumnId = 0;   // variable to hold the id of the column

Using the smartsheet object and sheetId, grab the sheet, loop over the columns to find the Birthday column, and set the value for dateColumnId.

smartsheet.sheets.getSheet({ id: sheetId}).then(function(sheet) {
    // for each column title listed in the dateColumnNames array, 
    // get the columnId for that column and set it to dateColumnId
    for (let i = 0; i < sheet.columns.length; i++) {
        // when the column title matches the dateColumnName
        if (sheet.columns[i].title === dateColumnName) {
            // set the dateColumnId
            dateColumnId = sheet.columns[i].id;
            break;
        }
    }
})

Now, use the value for dateColumnId to get to cells in the Birthday column. But, what happens when you want to find the id of another column? You could initialize a couple more values to store the other column's id and title. And then, add another conditional statement to the column loop to look for the matching column title. After all that, the code is going to get really messy. Let's look at a cleaner way to find columnId values based on columnTitle. This can be done by creating a mapping or key-value pair that uses the columnTitle as the key and the columnId as the value.

In JavaScript, the quickest way to create this mapping is to use the reduce function, a higher-order function built into the Array.prototype. As stated on MDN web docs the reduce method, "executes a reducer function...on each member of the array resulting in a single output value." In this case, the function is going to reduce the columns array down to a single object of column titles and ids.

Let's go back to the response of the getSheet function used earlier. Instead of looping over sheet.columns call sheet.columns.reduce().

const columnMap = sheet.columns.reduce(reducerFunction, {});

In this reduce function there are two arguments that are passed in: the reducerFunction which will be executed on each element in the columns array, and the {} to inialize an accumulator value. This accumulator value will be added upon each time the reducerFunction executes--which will be on each element in the columns array. The reducerFunction will also take two arguments, and look like this:

const reducerFunction = (tempMap, col) => { 
    tempMap[col.title] = col.id; 
    return tempMap; 
}

tempMap is the accumulator value that was initialized with {}, and col represents the column object array element that is being executed upon. The function uses the column title to set the key, and the value is the column id. When the reduce() completes the result of the map looks like this.

{ Name: 4615776901588868, Birthday: 3489876994746244 }

Now, to get the id of a column, rather than looping over the columns array, just call the columnMap using the title of the desired column.

columnMap["Birthday"];

This is just one of several ways to make your application code more elegant. Let us know in the comments below if there are other pain areas that you experience with the Smartsheet API, or if you have come up any elegant solutions that you'd like to share with the Smartsheet Developer community.