Status Update
Comments
jp...@google.com <jp...@google.com>
oh...@gmail.com <oh...@gmail.com> #2
jp...@phageguard.com <jp...@phageguard.com> #3
ri...@basichomeloan.com <ri...@basichomeloan.com> #4
mm...@dealmakerwealthsociety.com <mm...@dealmakerwealthsociety.com> #5
fl...@gmail.com <fl...@gmail.com> #6
The problem
I also experience this issue in my Google Sheet document.
Is it too many API calls?
What partly works
It only works, when I go into a cell and modify the function, press enter, undo my changes and then press enter again.
So what does not work for me sometimes is the initial loading after some time.
Once I have it running, it works for me for some hours, even with initial loading of the document.
But after a day or so, I experience the initial loading problem again.
Suggestion
Why don't you just execute the app script in my browser (I have enough resources, no need for API calls)
And only when calculated values change, do you send a cell change to your server.
Or you never send changes and always run the calculations in the browser.
And a word on attitude
I think this is the same as this one:
Why is this issue marked as fixed? You probably do not look at it anymore because for you "it is fixed", but it comes up in Google search result and that is why people actively comment on it.
I know you are not making lots of money from your "Office Suite", but unless you sweat through the small stuff and create a product that works really well, then no big company will switch form MS Office to yours. If this is just a drive-by-product for you then fine, but then I will not switch from Excel.
mu...@gmail.com <mu...@gmail.com> #7
For me the custom function worked for a day, and now not even the temporarily fixes work.
I will be looking for another solution to use in my project, because I don't believe Google is even trying to fix this...
Description
For several months now, we have been receiving complaints from our customers about the operation of our custom function in Google Apps Script. The most common complaints are: "I'm getting the #NAME error, what should I do?" or Endless "Loading..." appears on my screen".
The problem is further complicated by the fact that many customers use our function in conjunction with COUNT and COUNTA functions, which obscures any error that might be occurring.
The function in question is called VALUESBYCOLOR and it is part of the "Power Tools" and "Function by Color" projects. This function uses "expensive" calls to SpreadsheetApp, like "getBackgrounds".
Here are my observations from some tests that I have conducted:
1) When there is a large number of functions (400 across the entire document) operating on small volumes of non-overlapping and unlinked data (about 60,000 cells, mostly numbers), an "Internal error executing the custom function" error is frequently encountered. What's strange is that when this function is the only one in the document or is the only one being calculated at a particular moment, it runs fine within 30 seconds.
The issue here also lies in the fact that when target ranges are changed, the customer may inadvertently trigger the recalculation of several functions, which then need to be manually triggered one by one for recalculation.
2) In a dataset with about 11,000 rows and 10 columns, where each row has a function that works with the data (mostly text) in that row, I found that it leads to an endless "Loading..." message and the document becomes completely inaccessible when attempting to use it through SpreadsheetApp or Sheets API. This behavior is quite unexpected.
In the attached video example, I have populated almost every cell with strings of 512 characters in length. Firstly, I haven't tried to find the minimum data volume threshold where the issue starts to manifest. Secondly, I am certain that this is not the weirdest case where users employ this function. Thirdly, the issue is consistently reproducible. Fourthly, when I experimented with simpler data, occasionally, instead of "Loading...", I observed the #NAME error on a part of functions.
3) Using the same data set as the previous case, but rather than applying a unique formula for each row, I dragged the function =SUM(VALUESBYCOLOR("yellow", "black", A1:J1820)) over 350+ rows.
After a long waiting period, I start to consistently observe the #NAME error. In the attached video example, I progressively added functions, but this did not help. Eventually, after a very long wait and dozens of document reloads, the error disappears. However, as soon as you change a value in the range specified as a function argument, everything starts all over again.
4) The scenario of using large ranges as function arguments often leads to memory overflow (in Google Chrome) and browser tab crashes.
Video: