Spreadsheet recalculation slows everything down

[Andrew Apell]

My add-on is resource intensive. Each operation performs multiple calculations before returning results.

As such, some of the heavy users have reported that, because Google Sheets keeps recalculating cells, the whole process is greatly worsened.

Is there any trick I can use to ensure that spreadsheet recalculation is temporarily set aside until the operation is done?

VBA has this code:

Application.Calculation = xlManual

Is there a Google Sheets alternative?

[Andrew Roberts]

This is a bit around the houses, but could you use the fact that the GSheet doesn't refresh custom function return values unless the parameters change.

You could replace the sheet functions with similar custom functions, that you could trigger by changing a dummy parameter???

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b636cebe-3ed5-4117-b798-47269f561fd1%40googlegroups.com.

[Andrew Apell]

🤔🤔🤔

EDIT:

I guess this means read once, write once... seems like the only way at this point.

On Thursday, 12 March 2020 13:46:13 UTC+3, andrew wrote:

This is a bit around the houses, but could you use the fact that the GSheet doesn't refresh custom function return values unless the parameters change.

You could replace the sheet functions with similar custom functions, that you could trigger by changing a dummy parameter???

On Thu, 12 Mar 2020 at 09:57, Andrew Apell <chis...@gmail.com> wrote:

My add-on is resource intensive. Each operation performs multiple calculations before returning results.

As such, some of the heavy users have reported that, because Google Sheets keeps recalculating cells, the whole process is greatly worsened.

Is there any trick I can use to ensure that spreadsheet recalculation is temporarily set aside until the operation is done?

VBA has this code:

Application.Calculation = xlManual

Is there a Google Sheets alternative?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Bruce Mcpherson]

Custom functions spend most of their time in starting and finishing, and very little time in processing. I did an analysis of this a couple of years back. 

https://ramblings.mcpher.com/google-apps-scripts-snippets-2/using-array-formulas-to-improve-performance/

Perhaps there's a way to convert your functions to array formulas so they run only once rather than for each cell.

On Thu, 12 Mar 2020 at 09:57, Andrew Apell <chis...@gmail.com> wrote:

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.