Google Scripts Returning Not a Number

Question

I've been working on Google Scripts for a bit, but I can't seem to find the solution to my problem. What I am trying to do is multiply the contents of two cells on a spreadsheet together with my function calculateRates(). Here is my code:

/** 
@customFunction
*/
function calculateRates(hourDecimal, personRate) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //Access spreadsheet
  var sheet = ss.getSheets()[0];  //Access sheet

  var range = sheet.getDataRange(); //Access all cells

  var values = range.getValues(); //Call to get value of certain cell

  var totalEarned = hourDecimal * personRate;
  Logger.log(totalEarned);

  return totalEarned;
}

The problem with it is that after I designate two cells to multiply together as the parameters of the function on the spreadsheet, there is an error that reads "Result was not a number." I know I have the function correct on my spreadsheet, so that can't be the problem..... Any ideas? Thanks for your help!

Are you calling the function from the Spreadsheet UI? example in a cell you type '=calculateRates(B1,C1)'? Also, you're only multiplying the cells you set as arguments, you really don't use the first 4 lines of your function or there is more code? And finally the these values are entered manually or come as a result of some function? — ocordova
Yes, I am calling the function from the spreadsheet. There's more code but in a separate function that is working, but this function I'm having trouble with. Sorry, I am a beginner, so I don't know if I have to recall all the spreadsheet objects I think. As for the values, one value is entered manually while the other comes as the result of a function. — jgnibo

ocordova ocordova · Accepted Answer · 2016-08-02T04:40:58

This error occurs when you try to perform calculations with values that are not all numbers, example a Number and a String.

Google Sheets stores data depending on the value of the cell, the values may be of type Number, Boolean, Date, or String (Empty cells will return an empty String). When these values are used by Custom Functions, Apps Script treats them as the appropriate data type in JavaScript.

You need to make sure the values of the cells are Numbers, take into account that if the cell contains some space or other non-numeric characters like "2 2" or "3/2" it will be treated as String and you will need to trim or extract those special characters before any calculation.

Google Scripts Returning Not a Number

1 Answers