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

TL;DR - Try duplicating the sheet tab and delete the old one

I struggled with this issue today, and tried some of the approaches mentioned. For various reasons, renaming the function wasn't possible for me.

It was very clear that if I called a my function like this in cell X25:

=myFunction("a", 1, "b", 2, "c", 3)

The cell would be stuck "Loading...", while just changing a parameter slightly (e.g. converting a number to a string) the cell would evaluate fine.

=myFunction("a", "" & 1, "b", 2, "c", 3)

Just copying the code into another cell (e.g. X24) and executing it there seemed to bypass the problem. As soon as I moved it back to the original parameters or cell, it got stuck "Loading..." again. So I would assume it's some kind of caching of "Cell ID", function and parameters that go bonkers on Google's side.

My good-enough solution was to simply duplicate the Sheet tab, delete the old one, and finally rename the new one back to the original name. This solved the problem for me.

I also had the "loading data..." error but none of the fixes described here worked for me. It didn't seem to be caused by the issues described here. In my case, I narrowed it down to a specific floating point operation issue (it looks like a real bug in Google Sheets to me), and documented one possible work around at

Google Sheets / Apps "Loading data" error: is there a better workaround?

To summarize (at the request of commenter Steve), if a cell with

   = myfunction(B10)

generated a "loading data" error, then for me it could be fixed by wrapping the argument in a "value()" function:

   = myfunction(value(B10))

which converts the number in cell B10 (which seemed like a normal number but generated problems somehow) into a normal number that works fine.

Update 2022

It looks like this bug is still happening. I tried ALL the solutions mentioned here but none worked.

What worked was to start with a blank slate. I recreated the file, copy-pasted my data, reapplied my preferred style and format, and lo-and-behold the sheet finally managed to pull the data using my custom functions.

This is a definitely a bug on Google's end - and it's all the more annoying because they removed the "Report a problem" button from the "Help" section.

Nevermind

The newer sheet has stopped working too. This is so annoying ..

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)

My app script pulling data from my MSSQL database displayed just fine on GoogleSheets my laptop browser but then did not display on the Android GS app.

Per this thread it looks like there's a number of issues that could cause this, but @DestinyArchitect's answer above re: Permissions seemed like the simplest fix.

While testing my app script, Sharing was off for this GoogleSheet file. Once I moved it to my team's folder where we have default Sharing switched on with a few team members, the MSSQL data showed right up on the GoogleSheet in my Android GS app.

Easy fix, this time...

In my case, the cell was stuck with a Loading... message due to "probably" a race condition between functions and formulas resolutions.

This is my custom function:

function ifBlank(value1, value2) {
  return !!value1 ? value1 : value2;
}

This is the formula calling it: =IFBLANK(VLOOKUP($A2,Overrides!$A$2:$E,5,FALSE),VLOOKUP($A2,'_resourceReq'!$A$2:$C,3)) Those VLOOKUP values could be pretty complex and could also take some time to resolve.

Solution: (in my case)

Wrapping the VLOOKUP() into TO_TEXT() or VALUE() for example.

So, changing the formula to =IFBLANK(TO_TEXT(VLOOKUP($A2,Overrides!$A$2:$E,5,FALSE)),TO_TEXT(VLOOKUP($A2,'_resourceReq'!$A$2:$C,3))) worked well.

If that doesn't work maybe try resolving the value from a function into a cell before using it as the argument of your custom function.

In my case, multiple cells using functions experienced this issue, but the simple answer was... wait.

In my case, I was scraping data via importXML functions across multiple rows and columns. I was thrilled with the results, feeling on top of the world, then "Loading..." started showing its ugly face. For way too long. That's how I wound up here in troubleshooting mode, impatient and upset that Google was doing me wrong.

I tried many of the solutions here, only to find my "Loading..." antagonist acting unpredictably, popping up randomly like whacamoles, nothing to do with the code itself.

So. In my case, it was a matter of waiting it out (towards an hour for some rows, but I had so many cells fetching url data).

My layman's guess is that fetching data like this gets put in their bandwidth pipeline, lesser priority than typing a url into a search bar or other user requests.