Debugging a custom function in Google Apps Script

Question

I am trying to create my first custom function for a Google Spreadsheet in Apps Script and I am having a hard time using the debugger.

I am working on the custom function demo code from the Google documentation and I have set a breakpoint in the custom function drivingDistance(origin, destination) that is used in a cell of my spreadsheet. The problem I have is, that that the debugger shows the parameters that are passed into the function as being undefined. The content of any other variables that are created during execution is displayed correctly though (as long as they do not depend on the input parameters).

Funny thing is that although the input parameters are displayed as undefined, the function's calculations succeed, so this seems to be a debugger issue. Unfortunately this problem prevents me from successfully learning to create and debug own code (as I will have to work with complex input parameters).

I have a feeling that the problem is connected to the server-side execution of Apps Script, so I tried to log the input parameters using the Logger class and I also tried to copy these variables into new local variables. But all I came up with was undefined.

Another strange hint is, that typeof of the parameters returns String. But getting the length of them throws an error and trying to concatenate them with another string returns the string "undefined" (see my screen dump).

I am looking for insights about what is going on here.

Answer 1

The debugger is probably not lying to you - if you launch that function in the debugger, it will have no parameters passed to it. No worries, though, you just need to make sure that you get values to use for debugging. Take a look at How can I test a trigger function in GAS?, which demonstrates techniques that can be applied for custom functions.

Instead of defining an event to pass to the function, you'll want to provide (or retrieve from your spreadsheet) values for the parameters.

function test_drivingDistance() {
  // Define a set of test values
  var testSet = [[ 'Washington, DC', 'Seattle, WA' ],
                 [ 'Ottawa, ON', 'Orlando, FL'],
                 [ 'Paris, France', 'Dakar, Senegal']];

  // Run multiple tests
  for (var test in testSet) {
    Logger.log('Test ' + test + ' = ' + drivingDistance(testSet[test][0],testSet[test][1]));
  }

  // Get parameters from sheet
  var TestFromSheet = drivingDistance(ss.getRange('A1').getValue(),ss.getRange('A2').getValue());
}

You get the idea. You can still set breakpoints inside your function, or use debugger to pause execution.

---

Edit - examining arguments

What arguments is the custom function receiving when called from a spreadsheet?

You're limited in what you can do to debug this, since the debugger can't be used to examine your custom function when invoked from Sheets, and security limitations on custom functions block Logging. It might be enough to get an understanding of argument passing in general. While javascript functions may have named parameters, all arguments are passed as an Array-like object, called arguments. This custom function will return an array that reports the arguments received. When called from a spreadsheet, each argument will appear in its own cell, starting at the cell you enter the function into:

function testArguments(  ) {
  var argArray = [];
  for (var arg in arguments) {
    argArray.push("arguments[" + arg + "] = " + JSON.stringify(arguments[arg]))
  }

  return argArray;
}

In javascript, there aren't really types like int or float - just Number. Those parameters will show up without quotes on them, and look like numbers. Dates arrive as Date objects, but when printed this way show up as Date-y strings. Strings have quotes.

A custom function never receives a range as an argument; when you provide a range parameter in the spreadsheet, its contents are collected into a one or two-dimensional array, and the array is the argument.

Answer 2

You can use this hack to see the structure of the arguments being sent into the custom function:

function TEST(input) {
  return (JSON.stringify(input));
}

The results will show up in your sheet like this: