New Google Sheets custom functions sometimes display "Loading..." indefinitely

Question

SPECIFIC FOR: "NEW" google sheets only.

This is a known issue as highlighted by google in the new sheets.

Issues: If you write complex^* custom functions in google-apps-script for google sheets, you will occasionally run into cells which display a red error box around the cell with the text "Loading..."

Google has suggested:

If this occurs, try reloading the page or renaming the function and changing all references to the new name.

However for other developers experiencing this issue (and who are unable to escape the "loading..." error), I've written my findings in the answer below on how to get past this (with limitations) consistently.

---

^*We're treating this question as the canonical answer for Google Sheet's indefinite "Error... Loading data" problem. It's not limited to complex or slow functions.

Answer 1

I also had the infinite loading issue with the following function.

// check if an item can be checked off function checkedOff( need, have ) {   var retStr = "nope";   if( have >= need ){     retStr = "yep";   }   return retStr; }; 

Turns out you shouldn't have a trailing ";". Removing the semicolon solved the problem.

// check if an item can be checked off function checkedOff( need, have ) {   var retStr = "nope";   if( have >= need ){     retStr = "yep";   }   return retStr; } 

This runs as one would expect.

Answer 2

Important Tip: Create multiple copies of your entire spreadsheet as you experiment. I have had 3 google spreadsheets corrupted and rendered completely in-accessible (stuck in a refresh loop). This has happened when I was experimenting with custom functions so YOU HAVE BEEN WARNED!

You will want to try one or many of the following ways to fix this issue:

1. As suggested by google, try re-loading the spreadsheet or re-naming the function or changing the parameters in the cell to see if this fixes the issue.

2. Surround ALL your custom functions in a try-catch block. This will help detect code issues you may not have tested properly. Eg:

   try{ //methods }catch(ex){ return "Exception:"+ex; }

3. Revert to the old sheets and test your functions and check for any other type of error such as an infinite loop or invalid data format. If the function does not work in the old sheets, it will not work in the new sheets and it will be more difficult to debug.

4. Ensure NONE of your parameters refer to, can expect to or will ever contain a number larger than 1 million (1000000). No idea why but using a number larger than a million as any parameter will cause your function to fail to execute. If you have to, ask the input to be reduced in size (maybe divide by 1000 or ask for M instead of mm).

5. Check for numeric or floating point issues where numbers may exceed a normal set of significant figures. The new sheets seems to be a little glitchy with numbers so if you are expecting very large or very complex numbers, your functions may not work.

Finally, if none of the above work, switch to the old google sheets and continue working. If you find any other limitations or causes for functions to fail to execute, please write them below for me and other users who are heavy g-sheet users!