Debugging a Google Sheets Custom Function

Question

I have the defined the following custom function in Google Sheets:

/**
 * Finds the maximum value in one column
 * by comparing cells matched in another column,
 * using a comma-delimited lookup in a cell.
 *
 * @param {string} references Comma-delimited string referencing values.
 * @param {array} keys Array of strings to match.
 * @param {array} values Array of values to compare.
 * @return The maximum value of all referenced values.
 * @customfunction
 */
function MAX_LOOKUP(references,keys,values){
  if (!references || !keys || !values) return "";
  var refs = {};
  references.split(/, ?/).forEach(function(key){ refs[key]=1 });
  var val,max = -Infinity;
  for (var i=keys.length;i--;){
    if (refs[keys[i]] && (val=values[i])>max) max=val;
  }
  return max>-Infinity ? max : "";
}

In the spreadsheet—used like =MAX_LOOKUP(G9,A:A,I:I)—this always returns an empty string. When I test it in the Script Editor, however, it works correctly:

function test_MAX_LOOKUP(){
  var result = MAX_LOOKUP(
    "foo, bar, baz",
    ["no", "bar", "no", "baz", "foo", "no"],
    [ 99,   42,    99,   17,    1,     99 ]
  );
  Logger.log( result ); // Is correctly 42
}

Obviously there's some difference between the values I'm getting from the spreadsheet and my test, but how do I figure out what is the problem? If I set a breakpoint in the function, it only happens when debugging from the Script Editor. If I put Logger.log calls in the function, they only are logged when I run from the Script Editor.

How can I see what values are being passed to my function function?

Answer 1

To see what's being passed, use "print" style debugging by returning strings from your function. For example:

function MAX_LOOKUP(references,keys,values){
  return JSON.stringify(references);
  //return JSON.stringify(keys);
  //return JSON.stringify(values);
}

It's cumbersome, but you can step back and forth between editing the function to change what's returned, saving the script, and then view the spreadsheet to see your debug answer. (Tip: open the spreadsheet and script editor in separate windows, not separate tabs.)

---

Using the above technique we can see that the problem with the above script is that a column range like A:A returns single-value arrays for each row, not the value of the cells. The script needs to be changed to:

if (refs[keys[i][0]] && (val=values[i][0])>max) max=val;