Google表格自定义功能错误“结果不是数字” [英] Google sheets custom function error "Result was not a number"

问题描述

我有一个Google Sheets Apps脚本自定义函数会产生错误：结果不是数字。从自定义函数返回的值，尽管数字不被视为数字，而与应用于单元格的格式无关。所计算的值不能用于其他内置函数，如SUM（）。

有类似的问题已经被提出，请参阅这里， here ，以及此处（可能还有其他...），但不解释如何更改返回类型。

Google应用程序脚本 DataTypes的文档并不清楚如何将您的自定义函数强制到特定的返回值类型。

链接至示例

以下是我遇到麻烦的功能（注意getCell（）不包括在下面） ：

  / ** 
将所有与交易类别和日期范围相匹配的交易汇总。 
 
 @param category_name要在
上过滤的类别@param date_min要过滤的最小日期范围
 @param date_max要过滤的最大日期范围
 @param [time ]可选更新时间/日期
 @return匹配输入条件的交易总和
 @customfunction 
 * / 
函数sumCategory（category_name，date_min，date_max，time）{
 var date_min = new Date（date_min）; 
 var date_max = new Date（date_max）; 
 var date_month = date_min.getMonth（）; 
 var date_year = date_min.getYear（）; 
 
 var sheets = ['Account1'，'Account2']; 
 
 var column_name = {
日期：日期，
类别：类别，
金额：金额
}; 
 
 var msg =; 
 var sum = 0.0; 
 var spreadsheet = SpreadsheetApp.getActive（）; 
 
 //缓存过期前的秒数。 
 var cache_time = 120; 
 
 var cache = CacheService.getScriptCache（）; 
 $ b $ // //根据请求创建缓存键category_year_month 
 var cache_key = category_name +'_'+ date_year.toString（）+'_'+ date_month.toString（）; 
 
 //如果我们有这个类别的缓存，然后返回它... 
 var cached = cache.get（cache_key）; 
 if（cached！= null）{
 return cached; 
} 
 
 //缓存的类别总和
 var cat_sum = {}; 
 
 //遍历所有工作表，以及构建类别和缓存的所有事务
 for（var x = 0; x  var s =张[x]; 
 //Browser.msgBox(\" Working on sheet：+ s）; 
 var cur_sheet = spreadsheet.getSheetByName（s）; 
 
 //获取整幅图的数据范围
 var data = cur_sheet.getDataRange（）; 
 var num_rows = data.getNumRows（）; 
 var num_cols = data.getNumColumns（）; 
 
 //遍历此表中的所有行
 for（var i = 2; i< num_rows; i ++）{
 var xdate = new Date（getCell（column_name。日期，我，数据））; 
 var cat = getCell（column_name.Category，i，data）; 
 var amount = getCell（column_name.Amount，i，data）; 
 
 //如果交易类别为空，则设置为'未分类'
 if（！cat）{
 cat =Uncategorized; 
 
 var xkey = cat +'_'+ xdate.getYear（）。toString（）+'_'+ xdate.getMonth（）。toString（）; 
 
 //检查我们是否已经存储了这个类别
 if（！cat_sum.hasOwnProperty（xkey））{
 //初始化
 cat_sum [xkey] = 0.0; 
} 
 cat_sum [xkey] = cat_sum [xkey] + parseFloat（amount）; 
 
} //结束循环行
} //结束循环表
 
 //构建缓存
（var key in cat_sum）{
 //console.log（key，dict [key]）; 
 cache.put（key，cat_sum [key]，cache_time）; 
} 
 
 //如果未找到类别...设置为0. 
 if（！cat_sum [cache_key]）{
 cat_sum [cache_key] = 0.0; 
 cache.put（cache_key，0.0，cache_time）; 
} 
 
 var return_value = cat_sum [cache_key]; 
 
 //返回请求的类别
 return return_value; 
} 
  

解决方案

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

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

这个问题是正确的，但没有关于如何强制返回值的正确类型的示例。

此链接有将JavaScript字符串转换为数字的例子，并列出了一些缺陷。

为了强制返回一个数字，
add 返回Number（cached）; 和
返回Number（return_value）; 给函数。

举例表格脚本已更新为返回一个数字。

I have a Google Sheets Apps Script custom function that produces the error: "Result was not a number". The value returned from the custom function although a number is not being treated as a number regardless of the 'format' applied to the cell. The calculated values cannot then be used in other built-in functions such as SUM().

Similar questions have been asked, see here, here, and here (and probably others...) but don't explain how to change the return type.

The google apps-script documentation for DataTypes isn't clear how to "force" your custom function to a particular return type.

Link to example sheet with script.

Here is the function i'm having trouble with (note getCell() not included below):

/**
 Sum all transactions across sheets that match category and date range.

 @param category_name The category to filter on
 @param date_min Minimum date in range to filter
 @param date_max Maximum Date in range to filter
 @param [time] Optional update time/date
 @return The sum of transactions that match input criteria
 @customfunction
 */
function sumCategory(category_name, date_min, date_max, time) {      
  var date_min = new Date(date_min);
  var date_max = new Date(date_max);
  var date_month = date_min.getMonth();
  var date_year = date_min.getYear();

  var sheets = ['Account1', 'Account2'];

  var column_name = { 
    Date:   "Date",
    Category: "Category",
    Amount: "Amount"
  };

  var msg = "";
  var sum = 0.0;
  var spreadsheet = SpreadsheetApp.getActive();

  // Number of seconds before the cache expires.
  var cache_time = 120;

  var cache = CacheService.getScriptCache();

  // Build the cache key with requested category_year_month
  var cache_key = category_name + '_' + date_year.toString() + '_' + date_month.toString();

  // If we have this category cached, then return it...
  var cached = cache.get(cache_key);
  if (cached != null) {
    return cached;
  }

  // Category sum for cache
  var cat_sum = { }; 

  // Loop through all sheets, and all transactions building the category sum cache
  for(var x = 0; x < sheets.length; x++){  
    var s = sheets[x];
    //Browser.msgBox("Working on sheet: " + s);
    var cur_sheet = spreadsheet.getSheetByName(s);

    // Get data range for whole sheet 
    var data =  cur_sheet.getDataRange();
    var num_rows = data.getNumRows();
    var num_cols = data.getNumColumns();

    // Loop through all rows in this sheet
    for(var i = 2; i < num_rows; i++){
      var xdate = new Date(getCell(column_name.Date, i, data));
      var cat = getCell(column_name.Category, i, data);
      var amount = getCell(column_name.Amount, i, data);

      // If the transaction category is empty, set to 'Uncategorized'
      if(!cat){
        cat = "Uncategorized";
      }
      var xkey = cat + '_' + xdate.getYear().toString() + '_' + xdate.getMonth().toString();

      // Check if we have not already stored this category
      if(!cat_sum.hasOwnProperty(xkey)){
        // initialize
        cat_sum[xkey] = 0.0;
      }
      cat_sum[xkey] = cat_sum[xkey] + parseFloat(amount);

    }// end loop rows
  }// end loop sheets

  // Build cache
  for (var key in cat_sum){
    //console.log( key, dict[key] );
    cache.put(key, cat_sum[key], cache_time);
  }

  // If category not found... set to 0.
  if(!cat_sum[cache_key]){
    cat_sum[cache_key] = 0.0;
    cache.put(cache_key, 0.0, cache_time);
  }

  var return_value = cat_sum[cache_key];

  // Return the requested category sum
  return return_value;
}

解决方案

Javascript and Google apps-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(cached); and return Number(return_value); to the function.

Example sheet with script has been updated to return a number.

这篇关于Google表格自定义功能错误“结果不是数字”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！