I built a script that elaborate google sheets and extract them to a Drive folder. To create it script I followed the Best Practices. What I'm noticing is that over time the execution slowed down and I don't understand why sometimes is faster than others.
I tried with smaller scripts --> just 1 getValues(), elaborate the array of arrays completely and only inside the code, and at the end 1 setValues() to plot the data into sheet --> and these type of scripts don't slow down over time. What I'm trying to understand is if other aspects rather than the quality of the code affect the speed of the GAS execution
CodePudding user response：
Besides what was mentioned by TheMaster, other things that might slowdown your spreadsheet over time are increasing the number of
- formulas using volatile functions like
now(),today(),rand(),randbetween(). - formulas using functions that connect the spreadsheet to other things like import functions,
GOOGLEFINANCE, etc. - "slow" functions like
SUMPRODUCT,MMULT. - formulas with open references without using
ARRAY_CONSTRAINas they will be returning blank rows and columns that might make that new rows and columns be added each calculation.
Also
- long formulas chains that grow over time, like
=A1 1and someway doing a "filldown" along many rows, so the spreadsheet ends with=A2 1,=A3 1,...=A10000 1. To calculate=A10000 1first should calculate the first formula, then the second formula and so on. - spreadsheet features linked to open ranges or that use formulas like the mentioned above, like data validation, conditional formatting, charts, pivot tables, etc.
Regarding the differences from one execution to another, besides the regular cloud computing factors like IDE - Server communication, Google Apps Script might be using some sort of "warm up" and/or catching, meaning that the "first" execution might take longer than the next one, by the other hand, scripts rely on Google Cloud projects. Default Google Cloud projects are deleted on scripts that have not used and new project is added when the script is used again. I'm don't know how big could be the impact on the script performance but that is one operation that is not done all the time.
You might want to learn specifically about code smells, technical debt and more broadly about software engineering.
The performance of Google Apps Script code that uses Class SpreadsheetApp is affected by data, data validation, conditional formatting, formulas, and other features. Also it might be affected by the cloud circumstances and local circumstances.
By cloud circumstances, I'm referring to Google data centers, the Internet and your ISP. By local circumstances by your local network and your computer.
Your computer might affect script performance if it uses methods that depend on the user interface as a recalculation forces a UI refresh and some methods might cause a spreadsheet reload like change the spreadsheet time zone or locale.
The sister site, Software Engineering has tags for code smell and technical debt:
References
CodePudding user response：
The sheet itself will affect performance. If it becomes bulky
with unoptimized long formulas, written without regard to performance, and/or
has increased number of rows/columns(including empty ones)
they will affect calls to spreadsheet.