New Google Sheets custom functions sometimes displ

问题:

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.

回答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.

回答2:

FWIW, I just ran into this and the culprit ended up being a getRange() call that pulled several thousand rows into an array. Periodically it would get hung on the "Loading..." message.

I worked around it by putting that range into the document cache. It's a little kludgy because the cache only stores strings, not arrays, but you can force it back into an array using .split(',') when you need to access the array.

(In my case it's a single array. There's probably a way to do it using a double array, either by sending each row or column into its own cache, or reading the cache value back N items at a time, each N becoming its own array.)

Here's the relevant bit from my code:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mySheet"); //search the "mySheet" sheet
// is the big list already in the cache?
var cache = CacheService.getDocumentCache();
var cached = cache.get("columnValues");
if (cached != null) {
   var columnValues = cached.split(','); // take the cached string and make it an array
} else { // it's not in the cache, so put it there
  var column = 1; // the column with your index
  var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); // first row is header
  cache.put("columnValues",columnValues,60000); // this forces the array into a string as if you used .join() on it
}

This is definitely a bug in Apps Script -- getRange() shouldn't hang without a timeout or error message. But at least there's a workaround. Here's the bug I opened against it, where I've also put the full code.gs from my sheet.

回答3:

I also had the problem that you explained. It seems that it can be caused in more than one way.

I ended up finding that my custom function was displaying that error because it relied on data from an =IMPORTRANGE() call, and that call was failing.

I eventually found that the =IMPORTRANGE() call was failing because I had forgotten to update the URL that it was importing from when I had uploaded a new version of that imported-from sheet. It seems that trying to IMPORTRANGE from a trashed file can cause the infinite "Loading..." error.

回答4:

One cause: Permissions needing authorizing.

As far as {this problem, better phrased the cell result(s) of a custom function displaying the disgustingly-vague message ‘Loading... Error: loading data...’}, indeed in the case where all instances of the same/similar custom function call displaying this error, is that Google Sheets needs permissions to run the script (often additionally: meaning in the past it didn't need these), so instead of {acting appropriately: then prompting the user for these permissions else returning that error}, Sheets instead hangs with this disgustingly vague error.

Additional permissions can be needed from 1 or more:

1. Google App Scripts has since rewriting their permission structure --how this problem now just happened to me, per my internal note O80U3Z.
2. Your code or some library it uses made changes to require more access ...but in this case you have a much better chance of guessing the cause of this disgustingly-vague error, so hopefully won't be reading here.

To fix, I explicitly ran my GAS spreadsheet code by both: clicking one of my custom menu functions and, in the ‘script editor’, running one of my custom JS functions notably the ‘onOpen()’ since that is most comprehensive. The first promoted me for indeed new permissions, via popup ‘Authorization Required
The application "MM6ZBT(MM6Z83 script)" needs authorization to run.’, though onOpen() also did this in cases of GAS revising its permissions since we used that sheet. Then, as I was still getting this ‘Loading...’ error, I reloaded the web page (so the sheet), and, at least for these cases of this disgustingly vague error, it was gone and the computations worked fine :-)

回答5:

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!

回答6:

Add-ons

I had two add-ons, and no function was loading.

I removed them, and all is well!

回答7:

I can't add a comment, but I wanted to chime in that I also had this problem today, after 2 years of the sheet running successfully, and I found this thread based on searching for answers. I used the solution provided above to rename the function, and replace the name wherever appropriate within the sheet. This was easy for me, since I only had once instance of the function being called in the sheet. But, as soon as I renamed/replaced, the sheet started functioning again.

回答8:

For me, renaming the custom function solved the problem. For now at least.

回答9:

Just to add to Azmo 's answer...

I in fact removed all trailing semi-colons from the code:

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

And discovered, that when doing this over a large range you can also max out the acceptable number of calls to the API.

To get around it I added an IF THEN check around my custom script call.

So instead of:

=checkedOff(H10,H11)

Use something like this to check for a populated field before execution:

=if(H17<>"-",checkedOff(H10,H11),0)