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.