Apps Script Custom Function- Internal Error

I've just started playing around with Apps Script. I opened a new spreadsheet, accessed the script editor, and wrote the following simple function: function DOUBLES(num) { retur

Solution 1:

As per Google's documentation:

A custom function call must return within 30 seconds. If it does not, the cell will display an error: Internal error executing the custom function.

This was indeed my case and I after a wee bit of code optimization I reduced the occurrence of the error to 10% of the cases.

Solution 2:

There appears to be a recently-introduced bug in the handling of Google Apps Script custom functions.

This is apparently the subject of Issue 5222. (I say "apparently" because that issue report isn't really clear. But good enough.) Star it to get more attention on it, and to receive updates.

Solution 3:

In case it saves someone else a bit of time tracking down the cause of this error (fairly rare in 2021):

Many (but not all) of the custom functions on a sheet I copied from another spreadsheet were returning the Internal error executing the custom function error response.

Nothing I tried** changed that behavior, and the functions are all very small/quick, so it wasn't the 30-second timeout.

What finally worked was to make a copy of the entire spreadsheet; the functions in the copy worked normally, so I just moved the original to Trash and renamed the original.

** I tried: renaming the functions & calls to them; adding & testing new functions; making a copy of the script file and removing the original file; pasting a copy of the sheet over the first copy; making a new copy of the source sheet in the target spreadsheet -- all ended up returned the same Internal error executing the custom function errors. Perhaps the copied code was still linked to the bound script in some way, but wasn't accessible?

Solution 4:

I resolved the error by switching to the code editor and running the custom function from there once. The #ERROR Internal error executing the custom function. disappeared.

Please note that I did not have to grant any extra permissions when I run the function from the code editor, because the function does not need any.