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.
This is now part of a Google Apps Script shared library you can incorporate in your own project.
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.
Here’s the example from timing the effectiveness of caching getValues().
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
The code is in the mcpher library. See here for how to incorporate it into your project.
/** @description
* simple timer for GAS profiling
* See https://ramblings.mcpher.com/reusing-code-from-this-site/ for more details
* @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="https://ramblings.mcpher.com"> ramblings.mcpher.com</a>
*/
/**
* @static
*/
var timerList;
/**
* Creates the timerCollection if not already known and returns a new timer item
* @param {string} s Identifies a timer item
* @param {string=} textOptional some additional description to add to reports about the timer item
* @return {timerItem} a new timer item
*/
function useTimer(s,textOptional) {
if (!timerList) {
timerList = new timerCollection ();
timerList.xTimerList = timerList;
}
return isUndefined(s) ? timerList : timerList.timerThing(s,textOptional);
}
/**
* the timerCollection
* @class
* @return {timerCollection} a new timer collection
*/
function timerCollection () {
this.xCollection = new collection(0);
this.xMaster = new timerItem(EGAHACKS.EGAHACKSTimer,"Master Timer");
this.xMaster.start();
return this;
};
/**
* finds the timer item with the given key or creates it and adds to the collection
* @param {string} s Identifies a timer item
* @param {string=} textOptional some additional description to add to reports about the timer item
* @return {timerItem} the timer item assoacited with the given key
*/
timerCollection.prototype.timerThing = function (s,textOptional) {
var id = this.exists(s,false);
if (!id) {
id = this.xTimerList.xCollection.add (new timerItem(s,textOptional),s);
}
return id;
};
/**
* finds the timer item with the given key
* @param {string} s Identifies a timer item
* @param {boolean=} complain whether to deliver a message if timerItem not found
* @return {timerItem} the timer item assoacited with the given key
*/
timerCollection.prototype.exists = function(s,complain) {
return this.xTimerList.xCollection.item(s,complain);
};
/**
* enumerate a the collection in the timerCollection
* @this {timerCollection}
* @param {function(timerItem)} a function that will be called for each item
* @return {timerCollection} the timer collection
*/
timerCollection.prototype.forEach = function (yourfunction) {
for (var i =0 ; i < this.xCollection.count() ;i++) {
yourfunction(this.xCollection.item(i));
}
return this;
};
/**
* reports the statistics for each item in the timer collection
* @param {boolean =} optShowMaster whether to report on the overall time from the first call to the timerCollection(default yes)
* @return {string} the report on all timer items
*/
timerCollection.prototype.report = function (optShowMaster) {
this.xMaster.stop();
var s = fixOptional (optShowMaster, true) ? this.xMaster.report() : '';
this.forEach( function (item) {
if (s) s+= "\n";
s += item.report() ;
}
);
return s;
};
/**
* the timerItem
* @class
* @return {timerItem} a new timer item
*/
function timerItem(s,t) {
this.xText = fixOptional(t,s);
this.xKey = s;
this.clear();
return this;
};
/**
* reports the statistics for a single timer item
* @return {string} the report on this timer item
*/
timerItem.prototype.report = function() {
return this.xKey + ' : elapsed ' + this.elapsed() + ' : iterations ' +
this.iterations() + ' : ' + this.xText + ' : '
};
/**
* Starts a single timer item
* @param {string} t any text to report against this timer item
* @return {timerItem} the timer Item
*/
timerItem.prototype.start = function (t) {
if (!isUndefined(t)) this.xText = t;
this.xFinish = this.xStart = new Date();
return this;
};
/**
* stops a single timer item and increments the number of iterations
* @return {timerItem} the timer Item
*/
timerItem.prototype.stop = function () {
this.pause();
this.xIterations ++;
return this;
};
/**
* pauses a single timer item but does not increment the number of iterations
* @return {timerItem} the timer Item
*/
timerItem.prototype.pause = function () {
this.xFinish = new Date();
this.xElapsed += this.duration();
return this;
};
/**
* how long a timerItem lasted
* @return {number} the duration
*/
timerItem.prototype.duration = function() {
return this.xFinish - this.xStart ;
};
/**
* clear resets a timerItems statistics
* @return {timerItem} the timer Item
*/
timerItem.prototype.clear = function() {
this.xIterations = this.xElapsed = 0 ;
return this;
};
/**
* how long since the timer started till now
* @return {number} how long since the timer started
*/
timerItem.prototype.soFar = function() {
return new Date() - this.xStart ;
};
/**
* the total elapsed time for all iterations of this timer
* @return {number} the total elaspsed time this timer was active
*/
timerItem.prototype.elapsed = function() {
return this.xElapsed;
};
/**
* the total number of iterations of this timer
* @return {number} the total number of iterations of this timer
*/
timerItem.prototype.iterations = function() {
return this.xIterations;
};
Continue reading about VBA to Apps Script here