Error in Google App Script custom function

Question

I have a spreadsheet with three columns. All the numbers are in the Fahrenheit column, and the other two are filled from the functions.

Fahrenheit  Celsius ComputedFahrenheit
-200
-180
-160
-140
-120
-100
-80
-60
-40
-20
0
32
40
60
80
100
120
140
160
180
200

Celsius and ComputedFahrenheit columns are populated using these functions:

function f2c(fahrenheit) {
  if (typeof(fahrenheit) !== "number") {
    Logger.log(typeof(fahrenheit));
    throw "Fahrenheit column must contain numbers";
  }

  var celsius = (fahrenheit - 32) * (5/9);
  return celsius;
}

function c2f (celsius) {
  if (typeof (celsius) !== "number") {
      throw "celsius must be a number";
  }

  var fahrenheit = (celsius*(9/5)) + 32;
  return fahrenheit;
}

All cells display data correct, except Row 14:

40     4.444444444      Error: Loading Data ..

Note: f2c uses the Farenheit column, c2f uses the Celsius column.

I cannot see the problem!

Project Key: MESQb2p_hPY7vMP_LZjizjHo2ajWN_uqm

SDC Key: df47e1281fe5caea

rickcnagy · Accepted Answer · 2014-04-02T01:08:12.520

Ok, so this seems to be a bug in the new Google Sheets related to custom functions that have infinitely repeating decimals in their arguments, likely related to this similar bug.

I created a spreadsheet using your functions and data. I found that the value would calculate sometimes after I refreshed, and other times stay stuck loading. However, if you round the output from c2f() and f2c(), the code is able to return all the time.

This code works and will end up with the same precision as if you don't round the output:

ROUND = Math.pow(10, 12);

function f2c(fahrenheit) {
  if (typeof(fahrenheit) !== "number") {
    Logger.log(typeof(fahrenheit));
    throw "Fahrenheit column must contain numbers";
  }

  var celsius = (fahrenheit - 32) * (5/9);
  return Math.round(celsius * ROUND) / ROUND;
}

function c2f (celsius) {
  if (typeof (celsius) !== "number") {
      throw "celsius must be a number";
  }

  var fahrenheit = (celsius*(9/5)) + 32;
  return Math.round(fahrenheit * ROUND) / ROUND;
}

I've implemented it in the sheet that I created. Let me know if that works!

When I reload, it shows 40, then goes back to the Error display. — user3236820, Apr 02 '14 at 00:35

score 0 · Answer 2 · answered Apr 02 '14 at 00:14

0

I've just recreated your spreadsheet and functions and on row 14 I get:

40 4.44444444444444 40

I suggest you try:

Creating a new spreadsheet
Creating a new google script file
Re-test

answered Apr 02 '14 at 00:14

Stuy1974

120
3
7

I created a new spreadsheet, new script file and it displays correctly. See my response below to br1ckb0t – user3236820 Apr 02 '14 at 00:41

Error in Google App Script custom function

2 Answers2