Apps Script timer collection

One thing I've found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good and what is bad. 

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. 

How the timer(s) work.

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

  // cache read: steps through all cells in a sheet
   useTimer('a').start('read cached inputdata sheet values using foreach');
   cache.forEach( function(v,r,c) {
       x = v;
     }
   );
   useTimer('a').stop();


You can of course nest timers
 useTimer('x').start('the whole thing');

     useTimer('a').start('time to read inputdata sheet values');
        // do something
     useTimer('a').stop();

useTimer('x').stop()
DebugPrint(useTimer().report());

And that's all there is to it. Note that userTimer().report() without a timer ID, will summarize all known timers. 


function test() {
 // read a lot of cells

 var nIters = 2;
 var x;
 var wr = wholeSheet("inputdata");
 useTimer('x').start('the whole thing');

 useTimer('a').start('time to read inputdata sheet values');
 for (var n = 0; n< nIters ;n++) {
   for (var i = 0; i < wr.getNumRows(); i++)
   for (var j = 0; j < wr.getNumColumns(); j++) {
     x = wr.offset(i,j).getValue();
   }
 }
 useTimer('a').stop();

 useTimer('b').start('time to read cached inputdata sheet values using foreach');
 var cache = sheetCache(wr);
 for (var n = 0; n < nIters ;n++)
   cache.forEach( function(v) {
       x = v;
     } 
   );
 useTimer('b').stop();
 
 // do the same thing again, this time using a loop

 useTimer('c').start('time to read cached inputdata sheet values using loop');
 var cache = sheetCache(wr);
 for (var n = 0; n< nIters ;n++) {
   for (var i = 0; i < cache.getRowCount() ; i++)
   for (var j = 0; j < cache.getColumnCount(); j++) {
     x = cache.getValue(i+1,j+1);
   }
 }
 useTimer('c').stop();
 
// second time foreach
 useTimer('d').start('time to read cached inputdata sheet values using foreach 2nd time');
 var cache = sheetCache(wr);
 for (var n = 0; n < nIters ;n++)
   cache.forEach( function(v) {
       x = v;
     } 
   );
 useTimer('d').stop();
 
 
 useTimer('e').start('time to read inputdata sheet values- 2nd time');
 for (var n = 0; n< nIters ;n++) {
   for (var i = 0; i < wr.getNumRows(); i++)
   for (var j = 0; j < wr.getNumColumns(); j++) {
     x = wr.offset(i,j).getValue();
   }
 }
 useTimer('e').stop();
 useTimer('x').stop();
 DebugPrint(useTimer().report());
}

And here is the result, that shows us that caching is getting on for 100 times as fast as getting values one by one.
x : 23123 : the whole thing : a : 11018 : time to read inputdata sheet values : b : 118 : time to read cached inputdata sheet values using foreach : c : 138 : time to read cached inputdata sheet values using loop : d : 102 : time to read cached inputdata sheet values using foreach 2nd time : e : 11742 : time to read inputdata sheet values- 2nd time :

The timerCollection class code

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.



Code


Take a look at  From VBA to Google Apps Script for more like this.. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.  





Comments