Optimizing access to sheet values) so you need to dig into exactly what is good and what is bad.One thing I've found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see
To help with that, here is a simple timer collection that can be used to report on whats going on. It's not as fully featured as the VBA Optimization capability, but it's good enough for now.
This is now part of a Google Apps Script shared library you can incorporate in your own project.
The idea is that you can have a collection of timers, each operating independently, which can be used to report on what happened between starting and stopping.
The concept is simple. You can have as many timers as you want, and call them whatever you want, optionally adding a longer text description of what it is doing. You start a timer, do something, stop the timer, report on how long it took
You can of course nest timers
// do something
And that's all there is to it. Note that userTimer().report() without a timer ID, will summarize all known timers.
Here's the example from timing the effectiveness of caching getValues().
And here is the result, that shows us that caching is getting on for 100 times as fast as getting values one by one.
There are a few more methods and properties than used in the examples above. You can figure them out from the code below. Note that there is no need to initialize anything. All that is taken care of automatically when you access the useTimer() function.
The code is in the mcpher library. See here for how to incorporate it into your project.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > From VBA to Google Apps Script >