Object Passing In Google Sheets

Categories: [coding], [spreadsheets]
Tags: [apps-script], [code], [spreadsheets]

Suppose you're building a spreadsheet in Google Sheets, and you have each row corresponding to- essentially- an entity. Now you're clever, and set up Apps Scripts in order to manage more complex aspects of your worksheet. But how do you pass objects from the spreadsheet context to the script context?

I'll be using this public spreadsheet for this demo.

Suppose I have the following table in my spreadsheet, and I want to add an adoption probability calculator.

Starting off with the basic spreadsheet.

Normally, I could do that in the sheet directly (especially for a simple example like this) but try to imagine something much more complicated. How could I pass structured-ish data, especially if I might add columns in the future?

The answer is to pass key and value ranges. Passing ranges to a custom function takes them in as Ranges. These can be changed into arrays and used to populate an object. We start off by writing a function into the cell we want, like this.

=getAdoptionProbability($A$1:$D$1,A2:D2)

Notice that I've made my "keys" reference absolute, that way when I clone this cell for the rest of the column, my keys stay the same throughout.

Then, I'll open the editor in order to write the that this function executes.

We write the function we want into the cell, then open the editor.

Below is the code we'd write into the editor in order to get the calculator hooked up to the function we just tried to call.

/**
 * Calculates the probability of adoption for prioritization.
 *
 * @param {array} keys the keys for the given cat object.
 * @param {array} values the values for this cat object.
 * @return {number} the probability of adoption.
 * @customfunction
 */
function getAdoptionProbability(keys, values) {
  // The data we want is in the 1st element of these 2 dimensional arrays.
  var newCat = _buildCatObject(keys[0], values[0]);

  return 1; // Because all cats deserve to be adopted!
}

function _buildCatObject(keys, values) {
  // Always double check.
  if (keys.length != values.length) {
    throw 'Key length and array length do not match.';
  }

  var newObj = {};

  // Assign the values for each key.
  for (var i = 0; i < keys.length; i++) {
    if (!!!values[i]) continue;
    newObj[keys[i]] = values[i];
  }

  return newObj;
}

And that's all there is to it! Now the function is hooked up to the spreadsheet and it converts our key/value arrays into a manageable JavaScript object.

The finished function with JSDoc documentation in our spreadsheet.

It even looks sharp in Spreadsheets.