Google sheets custom function error “Result was not a number”

Question

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;
}

Answer 1

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.

Here is JSFiddle with script code.