Fixing a vague returned error in a GSheet custom function

19 Views Asked by At

I wrote a custom function in Google App Script in Gsheet. I pass it some text and a number. The function executes a SWITCH() statement and does some very simple math based on the SWITCH() argument.

It has been working fine for months now. However, this morning, all calls to the function return "#ERROR: Internal error executing the custom function." ERROR.TYPE returns 8.

I've made no changes to the code for at least 2 weeks.

I cleared browser cache, rebooted my machine. Deleted all calls to the function. Typed the function call back into a cell and the error persists.

What am I missing?

Here is the code:

function toThisAccount(location,bookCode,txAmount,charterRate,totalCollected,account) {
// location - unused for now.
// bookCode must be string (nb#, fp#, pif#)
// tx is the amount paid by client in this transaction

location=bookCode; // overwrite location for now (nov 2023). will include location in future revision.
location+=account; // eg. NB1IH

// need to make this persistent, somehow.
//        [IH  , FH,   FX,   PR]
var nb1 = [0.5,  0.3,  0.2,  0];
var nb2 = [0.5,  0.2,  0.2,  0.1];
var nb3 = [0,    0.5,  0.3,  0.2];
var nb4 = [0,    0.5,  0.2,  0.3];
var fp1 = [0,    0.3,  0.6,  0.1];
var fp2 = [0,    0.3,  0.6,  0.1];
var fp3 = [0,    0.3,  0.5,  0.2];
var fp4 = [0,    0,    0.5,  0.5];
var pif1 = [0.5*(nb1[0]+fp1[0]), 0.5*(nb1[1]+fp1[1]), 0.5*(nb1[2]+fp1[2]), 0.5*(nb1[3]+fp1[3])];
var pif2 = [0.5*(nb2[0]+fp2[0]), 0.5*(nb2[1]+fp2[1]), 0.5*(nb2[2]+fp2[2]), 0.5*(nb2[3]+fp2[3])];
var pif3 = [0.5*(nb3[0]+fp3[0]), 0.5*(nb3[1]+fp3[1]), 0.5*(nb3[2]+fp3[2]), 0.5*(nb3[3]+fp3[3])];
var pif4 = [0.5*(nb4[0]+fp4[0]), 0.5*(nb4[1]+fp4[1]), 0.5*(nb4[2]+fp4[2]), 0.5*(nb4[3]+fp4[3])];

var accountDistribution = [nb1,nb2,nb3,nb4,fp1,fp2,fp3,fp4,pif1,pif2,pif3,pif4];

switch(location){
  case "NB1IH":
    return accountDistribution[0][0]*txAmount;
    break;
  case "NB2IH":
    return accountDistribution[1][0]*txAmount;
    break;
  case "NB3IH":
    return accountDistribution[2][0]*txAmount;
    break;

...lots of cases here...

  default:
   return 0;
}
}

Thanks so much everyone. Really appreciate it.

I tried deleting all calls to the function.

I tried clearing browser cache.

I tried rebooting my machine.

No matter what I seem to do, the call always returns #ERROR.

0

There are 0 best solutions below