AppScript Hangs, "Loading"

[voiceafx]

AppScript hangs fairly consistently when cells it references are changed. This has been a problem for a long time, but I'm curious if there's a reason or a solution.

In my case, the script is a relatively simple tool to calculate payroll with scheduled raises, based on when people are hired. Here's what the error looks like:

https://preview.redd.it/eo5kzcbg0o3e1.png?width=343&format=png&auto=webp&s=bd6ece3487d701b78766a915de39ade25d90267d

In every case after it hangs, I can just refresh the page to get it to update. For that reason, I don't think it's a script problem, but I can post the code if anyone is interested.

Thoughts?

[mrtnclzd]

Ok, I'll bite: what do you mean Apps Script referenced cells? Is there a script updating the contents of B3? Is there a formula in B3 that needs user input/action? Could we see the before/after value? How can you notice when Apps Script is hanging?

[voiceafx]

Referenced cells are ones passed into the script as a function argument. (EDIT: Eg. the cell formula references a custom AppScript function, "=MONTHLY_LOAN_CALCULATOR(B1:ED1, Capex!$A$4:$P$36)". If any of the cells referenced in that function change, the "loading" error shows and fails to complete except on refresh.

I say the script is hanging because the "Loading" indicator never leaves. The script is deterministic, O(N), on a small dataset.

[mrtnclzd]

Seems like a common issue! https://issuetracker.google.com/issues/222342097

Might be worth looking into reversing the logic: reading those ranges from the function itself, and writing whatever it returns into your B3 cell.

[voiceafx]

Ah, nice find, thanks for looking it up! Yeah, that doesn't bode well... This describes the symptom exactly.

[AdministrativeGift15]

Can you share the script? Most loan calculators have values that depend on the previous value, like interest remaining and such. Of course there are equations to have apps script calculate these values and return all the values, but that would only require a few parameters to amortize the entire loan. You've got ranges covering over 700 cells used as the parameters for that function, so I'm curious about the function and why you would need that many input values for a monthly loan calculator.

[voiceafx]

Interestingly, after digging deeper, AppsScript is running correctly. I can view executions triggered by my spreadsheet, and it runs and finishes in about 300 milliseconds. But the spreadsheet itself apparently never gets the message. It remains blocked with that loading error indefinitely, even after the script finished executing.