javascript - Why my Google App Scripts slow down over time? - Stack Overflow

admin管理员组

文章数量:1404596

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 pletely 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

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 pletely 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

• javascript
• performance
• google-apps-script
• google-sheets

Share Improve this question Follow edited Nov 4, 2022 at 10:43 TheMaster 51.3k77 gold badges7373 silver badges100100 bronze badges asked Nov 4, 2022 at 10:32 Da NiDa Ni 19622 silver badges99 bronze badges 1

• Without seeing your code its hard to tell, but simply putting setValues() in a loop can slow it down. Let say you loop through each row and write the row to the spreadsheet and then progress to the next row, for example. – TheWizEd Commented Nov 4, 2022 at 10:48

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 5

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_CONSTRAIN as 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 + 1 and someway doing a "filldown" along many rows, so the spreadsheet ends with =A2 + 1, =A3 + 1,... =A10000 + 1. To calculate =A10000 + 1 first 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 puting factors like IDE - Server munication, 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 puter.

Your puter 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:

• code-smell
• technical-debt

References

• CodeSmell, by Martin Fowler

The sheet itself will affect performance. If it bees 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.

本文标签： javascriptWhy my Google App Scripts slow down over timeStack Overflow