Google表格脚本显然没有返回数字 [英] Google Sheet Script apparently not returning a number

问题描述

我正在Google脚本中编写我的第一个自定义函数。所有的调试工作都很好，我的代码完全运行（显然）。

有一个已经写好的函数（ CONVERT_RACETIME_TO_SECONDS ），它接受一个特别格式化的字符串，然后返回秒数。

此函数应该取两个范围，并将第二个范围的最高值除以第一个范围的最低值。

  / ** 
 *一个函数，它取两个值的范围并返回一个值。 
 *返回第一范围最小值范围内第二范围最大值的百分比。 
 *这等于max（secondrange）/ min（firstrange）
 * 
 * @param {Array} fast最快时间范围
 * @param {Array} slow最慢时间的范围
 * @return计算出的比率。 
 * @customfunction 
 * / 
函数SLOWEST_RATIO（fast，slow）{
 if（！（fast instanceof Array）||！（slow instanceof Array））{
抛出'无效：需要输入范围'; 
} 
 
 var最快= 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 /最快; 
 
 var ratiotype = typeof ratio; $！
 $ b if（！（ratiotype =='number'））{
 throw'无效：ratiotype是'+ ratiotype; 
} 
 
收益率; 
 
 
 function test_slowest_ratio（）{
 var faster = ['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（最快，最慢）; 
 
 Logger.log（'比例应该是非零数字：'+ ratio +'（是'+（typeof ratio）+'）'）; 
} 
  

它使用第二个函数完美地测试。

[15-08-26 17：42：04：396 BST]开始执行[15-08-26 17:42:04 ：403
BST] Logger.log（[比例应该是非零数字：1.5711447904712912
（是数字），[]]）[0秒] [15-08-26 17:42： 04：404 BST]执行
成功[0.002秒总运行时间]

但是，当我通过电子表格相同的值，但在单元格中，表格会给我一个错误：错误：结果不是数字 >

Javascript和Google应用程序脚本没有提供一种方法来将自定义函数的返回类型强制为数字。

这个答案接近，但返回类型是一个数组，而不是数字。

答案对于此问题是正确的，但没有示例说明如何强制cor rect类型的返回值。

链接有将JavaScript字符串转换为数字并列出一些陷阱的示例。

强制'返回一个数字，为​​函数添加 return Number（ratio）; ：

 函数SLOWEST_RATIO（快，慢）{
 if（！（fast instanceof Array）|| ！（slow instanceof Array））{
 throw'无效：需要范围输入'; 
} 
 
 var最快= 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 /最快; 
 
 var ratiotype = typeof ratio; $！
 $ b if（！（ratiotype =='number'））{
 throw'无效：ratiotype是'+ ratiotype; 
} 
 
 return Number（ratio）; 
} 
  

查看我的问题&回答类似的问题。

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"

解决方案

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表格脚本显然没有返回数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！