Google Sheet Script apparently not returning a number

Question

I'm writing my first custom function in Google Script. All works well in debug, my code runs (apparently) perfectly.

There's a function already written (CONVERT_RACETIME_TO_SECONDS) which accepts a particularly formatted string and then returns a number of seconds.

This function should then take two ranges and divide the highest of the second range by the lowest of first range.

  /**
   * A function that takes two ranges of values and returns a single value.
   * Returns the percentage of the maximum of the second range over the minimum of the first range.
   * this is equal to max(secondrange)/min(firstrange)
   *
   * @param {Array} fast The Range of fastest times  
   * @param {Array} slow The Range of slowest times
   * @return The calculated ratio.
   * @customfunction
   */
  function SLOWEST_RATIO(fast, slow) {
      if (!(fast instanceof Array) || !(slow instanceof Array) ) {
      throw 'Invalid: range input required';
    }

    var fastest = Math.min.apply(null, fast.map(CONVERT_RACETIME_TO_SECONDS));
    var slowest = Math.max.apply(null, slow.map(CONVERT_RACETIME_TO_SECONDS));
    var ratio = slowest/fastest;

    var ratiotype = typeof ratio; 

    if (!(ratiotype == 'number')) {
      throw 'Invalid: ratiotype is ' + ratiotype;
    }

    return ratio; 
  }

function test_slowest_ratio() {
  var fastest = ['21:03.55','21:43.83','22:41.31','23:32.44'];
  var slowest = ['31:11.29','31:19.18','33:05.22','28:17.76'];

  var ratio = SLOWEST_RATIO(fastest, slowest);

  Logger.log('Ratio should be a non-zero number: ' + ratio + ' (is ' + (typeof ratio) + ')');
}

It tests, using the second function, perfectly.

[15-08-26 17:42:04:396 BST] Starting execution [15-08-26 17:42:04:403 BST] Logger.log([Ratio should be a non-zero number: 1.5711447904712912 (is number), []]) [0 seconds] [15-08-26 17:42:04:404 BST] Execution succeeded [0.002 seconds total runtime]

But when I call from a spreadsheet with the exact same values, but in cells, Sheets gives me an error: "Error: Result was not a number"

Dave Hite Dave Hite · Accepted Answer · 2018-02-06T21:37:47

Javascript and Google app-script does not provide a way to 'force' a return type to number for a custom function.

This answer gets close but return type is an array, not a number.

The answer for this question is correct but no examples for how to coerce the correct type for the return value.

This link has examples of converting Javascript strings into numbers and lists some of the pitfalls.

In order to 'force' the return to a number, add return Number(ratio); to the function:

  function SLOWEST_RATIO(fast, slow) {
      if (!(fast instanceof Array) || !(slow instanceof Array) ) {
      throw 'Invalid: range input required';
    }

    var fastest = Math.min.apply(null, fast.map(CONVERT_RACETIME_TO_SECONDS));
    var slowest = Math.max.apply(null, slow.map(CONVERT_RACETIME_TO_SECONDS));
    var ratio = slowest/fastest;

    var ratiotype = typeof ratio; 

    if (!(ratiotype == 'number')) {
      throw 'Invalid: ratiotype is ' + ratiotype;
    }

    return Number(ratio); 
  }

See my question & answer for a similar issue.

Google Sheet Script apparently not returning a number

1 Answers