Search code examples
google-sheetsgoogle-apps-scriptgoogle-sheets-formula

Spreadsheet formulas calculation control

I want to translate a vba excell script to google apps sheet

In order to optimize the algorithm, I need to stop, refresh and restart the auto calculation.

In VBA I used:

Application.Calculation = xlCalculationManual

Application.CalculateFullRebuild

Application.Calculation = xlCalculationAutomatic

Is there an equivalent way to get the same functionality in Google Apps Script?

Solution

  • No, not directly anyway.

    In Google Apps Script though, the values are only calculated when needed. That is, you can write multiple values to a sheet, and it'll only calculate when in the end, after you've finished.

    Unless your script tries to read values after it has written some (not a very good practice). Or if you explicit call SpreadsheetApp.flush().

    I doubt you're having performance problems due to automatic calculation, the whole sheet would have to get slow. Script performance problems are almost never related to the sheet performance, but just a bad code issue or a huge task (for scripts).