Google Sheets Apps Script自定义函数"Result was not a number"报错求助

I’ve run into this exact issue before! The problem almost always boils down to your custom function returning a string that looks like a number instead of an actual JavaScript number type—even if it displays as a number in the cell, Google Sheets won’t treat it as a calculable value for functions like SUM().

Here’s how to fix it step by step:

• Explicitly convert your result to a number type
  If your calculation ends up with a string (common if you’re parsing text, concatenating values, or reading from text-formatted cells), wrap the final value in Number(), parseFloat(), or parseInt() to force it to a numeric type.
  Example of incorrect code:

  function myBadFunction() {
    // Returns a string, not a number
    return "42";
  }
  

  Corrected version:

  function myFixedFunction() {
    // Returns an actual number
    return Number("42");
    // Or use parseFloat() for decimals: return parseFloat(calculatedString);
  }
  

• Check for accidental type conversion in your logic
  If you’re pulling values from cells, double-check that you’re not working with string representations of numbers. For example, if a cell is formatted as text, getValue() might return a string instead of a number—convert it first before doing calculations:

  function sumTwoCells() {
    var sheet = SpreadsheetApp.getActiveSheet();
    // Convert cell values to numbers immediately
    var val1 = Number(sheet.getRange("A1").getValue());
    var val2 = Number(sheet.getRange("B1").getValue());
    return val1 + val2;
  }
  

• Verify the return type with logs
  To confirm what your function is actually returning, add a log statement to check the type:

  function myFunction() {
    var result = someCalculation();
    Logger.log(typeof result); // Should log "number", not "string"
    return Number(result);
  }
  

  You can view the logs by going to View > Logs in the Apps Script editor.

• Handle array results correctly
  If your custom function returns an array (for multi-cell output), make sure every element in the array is a number, not a string:

  function arrayFunction() {
    var rawResults = [["10", "20"], ["30", "40"]];
    // Convert each element to a number
    return rawResults.map(row => row.map(cell => Number(cell)));
  }
  

Once your function returns a proper JavaScript number type, Google Sheets will recognize it as a calculable value, and SUM() and other built-in functions will work as expected.

内容的提问来源于stack exchange，提问作者Dave Hite