1

tl;dr: My custom-formula cells all go Loading... and they start filling in results, but they get stuck. A reload immediately provides the completed results. I don't want to reload every time I change a value.

In my sheet almost all cells are interconnected, so changing one cell triggers a recalc. Each row is a year, each column is an identical formula across all rows. Each cell can refer to earlier columns in its row or anything on the prior row, plus a few absolute locations containing the "inputs". Changing an input triggers a recalc of everything.

The column with my custom function will go Loading..., then one cell at a time it returns the value, almost one second per cell. OK, slow, but fine. But often it just stops completely partway down. Sometimes it starts up again, but often it never does.

But the cells WERE all recalculated. My custom function was called and returned values promptly, execution time of 0.125 secs or less usually. If I reload in the browser, I immediately get the fully recalculated sheet. It looks like some link is being severed between Sheets in my browser and Google's servers, so I stop seeing updates.

This is the first time I've ever used Apps Script -- or JavaScript for that matter -- but I have been programming in other ways for decades. If it matters, the custom function is purely mathematical, calling no services except Math, not even Spreadsheet, getting everything it needs in its arguments, using a few functions in the same file. It's a detailed taxes calculator.

I'm using a recent Chromebook.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Ethan Solomita
  • 11
  • 2
  • Welcome to [so]. How many times your custom function is used in your spreadsheet? Are the spreadsheet formulas dependent of the calculations of other formulas? How many cells have the data ranges of your spreadsheet? – Rubén Feb 02 '21 at 17:43
  • There are ~40 rows, ~35 columns, and the custom function is in one column. Its arguments are completely dependent upon prior columns/rows, so any change in the inputs will serially trigger 40 invocations of the function. I'm not sure what you mean by the last question in your comment. – Ethan Solomita Feb 02 '21 at 17:48
  • Can you provide a copy of the spreadsheet you are working on, free of sensitive information? – Iamblichus Feb 03 '21 at 08:53
  • @Iamblichus [link](https://docs.google.com/spreadsheets/d/1tlG8QEwmaNAjEITN-bCvDYXlhkmEzaXzJaMtMmBWCYg/edit?usp=sharing) Try modifying the value at AS8. This will trigger a row-by-row recalc. Columns AD/AE are the ones with my custom Apps Script function. As the rows recalculate, one row in column AD will say "Loading...". Most of the time, this process will hang, leaving one cell permanently "Loading...". Executions shows that my function was executed and all results returned within 15 seconds. If you Reload, the entire sheet will be complete, the "Loading..." will be gone. – Ethan Solomita Feb 03 '21 at 19:10
  • Have you tried triggering this via [onEdit](https://developers.google.com/apps-script/guides/triggers#onedite) trigger instead of via custom functions? In any case, I think the advice given by Rubén (reworking your custom functions to accept parameters for multiple rows and return multiple rows) could certainly improve your situation. – Iamblichus Feb 04 '21 at 09:50
  • I'll look into onEdit. I've been unable to understand how his advice applies here, since I'm not complaining (primarily) about performance, but instead that the sheet never fills in its updates. Also, there is no way to rework this to return multiple rows at once short of making the custom function return nearly every column, effectively replacing the spreadsheet with a custom function. – Ethan Solomita Feb 04 '21 at 15:35

1 Answers1

1

In order to avoid to have to reload your spreadsheet it's very likely that you will have to follow the guidelines for custom function optimization:

Summary: Instead of using one formula to calculate a single value use your formula to calculate multiple values and return them as an array.

The above will reduce the number of formulas and will improve your spreadsheet performance, but bear in mind that custom functions have a maximum execution time of 30 secs.

Also reducing the size of the data ranges in your spreadsheets and the number of blank rows and columns at the bottom and right of your data ranges will help in improve the performance of your spreadheet.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • My formulas cannot be further optimized in this way, and as I mentioned, the execution time is max 0.125 seconds, so that's not the problem. I already return two values from one invocation. Because each row of the sheet depends upon the prior row, I cannot return a vertical array of results, only horizontal, which I already do. Finally, the issue is NOT performance. The spreadsheet simply stops updating, Loading... forever. – Ethan Solomita Feb 02 '21 at 18:27
  • Since your formulas depends on other formulas you have to add to th 0.125 secs the time that it takes to write and read the values of each formula... and also consider that these calculations are chained... 0.125 + w/r read row 1 + 0.125 + w/r row 2... – Rubén Feb 02 '21 at 18:33
  • Regarding depending of the previous row, your function could store the values on each row in a variable then once al the calculations are done pass all the values at once. – Rubén Feb 02 '21 at 18:40
  • My custom formula does not directly do any reading or writing, if that matters. All data is passed in via arguments. It does not use "Spreadsheet". But like I keep saying the problem is not performance. The problem is that it hangs and I have to reload. – Ethan Solomita Feb 02 '21 at 18:40
  • the arguments are read in order to be used in the function even if SpreadsheetApp is not used as well the result is wrote in order to be displayed/stored in the spreadsheet. – Rubén Feb 02 '21 at 18:41