Why so slow?
Note – this article was written when Apps Script first came out. Since that time, the speed of Sheets access has increased significantly, but I leave this article here for interest.
The Data Manipulation Classes that I am migrating in this project – see Data manipulation Classes in Google Apps Script, are highly efficient in Excel and are much faster than working directly in the Excel object model. However, you do have to get the data in there in the first place.
It is well known that, in Excel, reading a whole bunch of cell values in one go is much faster than accessing them individually. In Google apps script, this is even more so the case, since the overhead associated with a client/server request for data and the internet speed (as opposed to local computer speed) will introduce an unacceptable delay in most ports from Excel to Google Apps. I wondered if there was a way to introduce local caching, so if any sheet was ever accessed, all its values would be transferred locally and any further accesses would be to the cached copy. I got results that showed this was 70 times faster. You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download See also this blogpost on optimizing google apps scripts getValues() for details. You can also find the code for a timer, and the test results for this in Apps Script timer collection.
This is now part of a Google Apps Script shared library you can incorporate in your own project.
Caching of Google Docs Sheet Values
Here are the functions to allow caching, but first, some examples of how to use them. The complete code (work in progress) can be found here – Google Apps Script Roadmapper code
This reads in all the values in a couple of sheets, and shows the number of rows and columns. You don’t need to care whether this was an actual read from the server or whether this was a cache access.
|
1 2 3 |
// read a couple of worksheets DebugPrint (sheetCache("isbn").getRowCount()); DebugPrint (sheetCache("inputdata").getColumnCount()); |
|
1 2 3 4 5 |
// print them out sheetCache("isbn").forEach( function(v,r,c) { DebugPrint (v,r,c); } ); |
This will return a single value at the row, column specified. If it is outside the range of the sheet, and Empty() value is returned
|
1 |
DebugPrint ('outside range of sheet', sheetCache("isbn").getValue(50,50)); |
|
1 2 3 4 |
DebugPrint ('values overlap edge of range', sheetCache("isbn").getValues (Sheets("isbn").getRange("e3:m8"))[0].length); } |
What about other cacheable values
So why not use this same technique for every cacheable value ( getFontSizes(), getHorizontalAlignments() etc..) . See Google Apps Script Range object for the full list.
Turns out this is a trivial matter, since javaScript treats everything as an object, including functions, you can just pass the particular function you want called, and keep a separate cache going for each one. For example, this will execute getBackGroundColors() instead of getValues() to populate the cache, and return the background color at row rn, column cn of worksheet wn. Any cacheable method can be used, they all work the same way as getValues(), and you will see the same kind of performance gain that we have already seen.
|
1 |
sheetCache(wn,'getBackgroundColors').getValue(rn,cn) |
Cache identification
The function sheetCache(object) takes a range, a worksheet, or a worksheet name. It will always return the cache associated with that worksheet or implied worksheet. Here is the code. Note that the default method is getValues, but this could be any cacheable method that returns values for the whole sheet, such as getComments, getFontColors etc.
|
1 2 3 |
function sheetCache(ob,method) { return createSheetCache().getCache(ob,fixOptional (method,'getValues')); } |
How the method is executed
As mentioned, the 2nd (optional .. default is getValues) argument specifies the method that would retrieve the values or other objects in bulk from the sheet to fill up the cache. A separate cache is maintained for each method that is invoked. Here is the getValues() that is executed within the caching class. Note how the google apps script native method fills up the cache this.xValues = r[this.xMethod](); if it is the first time accessed it if it needs to be refreshed. It is that simple.
|
1 2 3 4 5 6 7 8 9 |
cCacheItem.prototype.getValues = function(r) { if (this.dirty() || !this.xValues) { var r = wholeSheet(this.xName); DebugAssert(r[this.xMethod], this.xMethod + ' method does not exist for range' + sad(r)); this.xValues = r[this.xMethod](); this.setDirty(false); } return isUndefined (r) ? this.xValues : this.getValuesOfRange(r); } |
Values by range
By default, cache.getValues() will return all the values of the selected method in the used worksheet. However, you can force an array of values to match the shape of a range, by passing a range argument to getValues(). For example this will return a block of values for e3:m8, and will pad them out if this range goes beyond the used range of the sheet.
|
1 2 |
sheetCache("isbn").getValues (Sheets("isbn").getRange("e3:m8"))) |
Writing to cache
You can also write to cache and commit it when finished. Any method that has both a set and get (for getValue and setValue) will use the same cache. This means that any updates you make with set, but have not yet committed, will be returned on subsequent gets. Note that if you have formulas, you would need to commit, and mark the cache as dirty so that subsequent getValues would return the recalculated value.
Here is an example from the Google Script Color Ramp project, where the background colors are written to cache.
|
1 2 3 4 5 6 7 |
var cacheColor = sheetCache(wr,"getBackgroundColors"); for (var i = 0; i < rampList.length ;i++ ){ for (var m = 0; m < npoints ; m++) { cacheColor.setValue(rampLibraryHex (rampList[i], 0, npoints -1, m),i+1,m+1); } } cacheColor.close(); |
How this works
the .setValue() method makes changes to the cache, extending it if necessary, and marking it as needing to be flushed at some point
|
1 2 3 |
cCacheItem.prototype.setValue = function (value,rn,cn) { return (this.extend (rn,cn).setTouched().xValues[rn-1][cn-1] = value); }; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cCacheItem.prototype.commit = function (optOut) { var oPlace = this.xParent.getWn(fixOptional(optOut, this.xName)); if ( this.touched() || oPlace != this.xName ) { // maybe writing to a different place var r = vResize(wholeSheet(oPlace),this.getRowCount(),this.getColumnCount()); var m = 'set' + Mid(this.xMethod,4) ; DebugAssert(r[m], m + ' method does not exist for range' + sad(r)); r[m](this.xValues); // if this is the same sheet as cache then its now clean if (oPlace == this.xName)this.setTouched(false); } return this; }; |
the .close() method commits any changes and voids the cache
|
1 2 3 4 5 6 |
cCacheItem.prototype.close = function () { //commit changes and kill the cache this.commit(); this.quit(); return null; }; |
the .quit() method voids the cache
|
1 2 3 4 5 6 |
cCacheItem.prototype.quit = function () { // abandon changes and kill the cache this.xValues =null; this.xParent.caches().remove(this.xKey); return null; }; |
Scalability
I gave some thought to scalability (would this be practical with 000’s of cells in a sheet?). In the end, I figured that google Docs sheets are anyway pretty small by nature, and if it was not scalable for cache approach, it probably wouldn’t be a candidate for scripting anyway. I’ll look into this a little more later.
Code
The code is in the mcpher library. See here for how to incorporate it into your project.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 |
/** @description * client caching for GAS worksheet access * will cache all calls to get..(), set...() methods so they can be done in a single call * a separate cache is created for each type of method (getValues,getBackGroundColors) etc. * See https://ramblings.mcpher.com/reusing-code-from-this-site/ for more details */ /** * cCache * @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="https://ramblings.mcpher.com"> ramblings.mcpher.com</a> */ /** * @static */ var worksheetCache; /** * Creates the cache container if not already known * @return {cCache} a new cache container */ function createSheetCache(){ return worksheetCache ? worksheetCache : (worksheetCache = new cCache()); } /** * finds (or creates) a cache for the identified worksheet * @param {string|Sheet|Range} ob Identifies the worksheet target * @param {string=} method the required method (default get/setValues()) * @return {cCache} a new cache container */ function sheetCache(ob,method) { return createSheetCache().getCache(ob,fixOptional (method,'getValues')); } /** * a cCache * @class * @implements {cCache} * @return {cCache} a new cache container */ function cCache() { var pCaches = new collection(); this.caches = function () { return pCaches; } return this; }; /** * generate a unique Key to use to identify the cache - used internally * @param {string} wn the worksheet name * @param {string} method the requested method * @return {string} the key */ cCache.prototype.getKey = function (wn,method) { // will use the same cache for both set and get methods return wn + '@' + (Left(method,3) == 'set' ? 'get' + Right(method,Len(method) - 3) : method ) ; }; /** * resolve the type of object being used to identify the cache required and return its worksheet name * @param {string|Sheet|Range} ob Identifies the worksheet target * @return {string} the worksheet name */ cCache.prototype.getWn = function (ob) { return this.getSheetObj(ob).getName().toString(); }; /** * resolve the type of object being used to identify the cache required and return its Sheet * @param {string|Sheet|Range} ob Identifies the worksheet target * @return {Sheet} the worksheet */ cCache.prototype.getSheetObj = function (ob) { // this one detects the object type & gets the sheet being referred to return DebugAssert (isTypeString(ob) ? Sheets(ob) : //its a sheet name ob.getSheet ? ob.getSheet() : //its a range ob.getName ? ob : //its a sheet null,'unknown object making getSheetObj cache request' ) ; // dont know what it is - give up }; /** * return the cache given an item and its method * @param {string|Sheet|Range} ob Identifies the worksheet target * @param {string} method the required method * @return {cCacheItem} a single cache */ cCache.prototype.getCache = function (ob,method) { // find the existing cache or create it. var wn = this.getWn(ob); var key = this.getKey(wn,method); var cache = this.caches().item(key,false); if (!cache) { cache = new cCacheItem(this,method); var cache = this.caches().add (cache,key); cache.xName = wn ; cache.xKey = key; cache.xWsRange = wholeWs(this.getSheetObj(ob)); } DebugAssert(cache.xName == wn, "somehow using the wrong cache"); return cache; }; /** * create a new cCacheItem * @class * @param {string|Sheet|Range} p the parent cCache container * @param {string} method the required method * @return {cCacheItem} a new cacheitem */ function cCacheItem(p,method) { this.xValues = null; // will hold cached values this.xParent = p; this.xMethod = method; this.xActive = false; this.xDirty = true; // force a refresh }; /** * return the values for an entire row * @param {number} rn the row number (Base 1) * @return {<Array>.*} array of values for the given row */ cCacheItem.prototype.getEntireRow = function(rn) { return this.getValues()[rn-1]; }; /** * return the values for the range given * @param {Range=} r the target range (default the whole sheet) * @return {<Array>.*} array of values for the given range */ cCacheItem.prototype.getValues = function(r) { // get from sheet if we dont have it already if (this.dirty() || !this.xActive) { DebugAssert(this.xWsRange[this.xMethod], this.xMethod + ' method does not exist for range' + sad(this.xWsRange)); this.xValues = this.xWsRange[this.xMethod](); this.xActive = true; this.setDirty(false); } return isUndefined (r) ? this.xValues : this.getValuesOfRange(r); }; /** * return the first value of the range given * @param {Range=} r the target range (default the whole sheet) * @return {*} value of first cell in the given range */ cCacheItem.prototype.getFirstValueOfRange = function (r) { // get from cache or if outside range return empty - only returns 1 value return this.getValue (r.getRow(),r.getColumn()); }; /** * return a single value * @param {number} rn the row number (Base 1) * @param {number} cn the column number (Base 1) * @return {*} value at rn,cn */ cCacheItem.prototype.getValue = function (rn,cn) { // get from cache or if outside range return empty return (rn > this.getRowCount() || cn > this.getColumnCount() ) ? Empty() : this.getValues()[rn-1][cn-1]; }; /** * set a single value * @param {*} value the value to set * @param {number} rn the row number (Base 1) * @param {number} cn the column number (Base 1) * @return {*} the value that was set */ cCacheItem.prototype.setValue = function (value,rn,cn) { return (this.extend (rn,cn).setTouched().xValues[rn-1][cn-1] = value); }; /** * set multiple cells to the same value * @param {*} value the value to set * @param {range} r the range to set it in * @return {cCacheItem} self */ cCacheItem.prototype.setRepeatValue = function (value,r) { var nc = r.getNumColumns(); var nr = r.getNumRows(); var sr = r.getRow(); var sc = r.getColumn(); var self = this; for (var i = 0; i < nr ; i++ ) for (var j = 0; j < nc ; j++ ) self.setValue(value,i+sr,j+sc); return self; }; /** * set multiple cells to the same rowheight * @param {*} value the height to set it to * @param {range} optr the range to set it in * @return {cCacheItem} self */ cCacheItem.prototype.setRowHeight = function (value,optr) { var r = fixOptional (optr, this.xWsRange); var nr = r.getNumRows(); var sr = r.getRow(); var self = this; var sheet = r.getSheet(); for (var i = 0; i < nr ; i++ ) sheet.setRowHeight(i+sr,value); return self; }; /** * set multiple cells to the same columnWidth * @param {*} value the height to set it to * @param {range} optr the range to set it in * @return {cCacheItem} self */ cCacheItem.prototype.setColumnWidth = function (value,optr) { var r = fixOptional (optr, this.xWsRange); var nc = r.getNumColumns(); var sc = r.getColumn(); var self = this; var sheet = r.getSheet(); for (var i = 0; i < nc ; i++ ) sheet.setColumnWidth(i+sc,value); return self; }; /** * commit the contents of the cCacheItem back to the sheet * @param {string|Sheet|Range=} if specified will clone the cache to a different sheet * @return {cCacheItem} the cCacheItem */ cCacheItem.prototype.commit = function (optOut) { if ( this.touched() || !IsMissing(optOut) ) { var oRange = DebugAssert( IsMissing(optOut) ? this.xWsRange : this.xParent.getSheetObj(optOut) ? wholeWs(this.xParent.getSheetObj(optOut)) : null, 'invalid cache clone attempt'); var r = vResize(oRange,this.getRowCount(),this.getColumnCount()); var m = 'set' + Mid(this.xMethod,4) ; DebugAssert(r[m], m + ' method does not exist for range' + sad(r)); r[m](this.xValues); // if this is the same sheet as cache then its now clean if (IsMissing(optOut))this.setTouched(false); } return this; }; /** * clear the cCacheItem and delete it without committing the contents */ cCacheItem.prototype.quit = function () { // abandon changes and kill the cache this.clearCache(); this.xParent.caches().remove(this.xKey); return null; }; /** * clear the cCacheItem without committing the contents */ cCacheItem.prototype.clearCache = function () { this.xData = null; return this; }; /** * clear the cCacheItem contents */ cCacheItem.prototype.clear = function (optR) { if (IsMissing(optR)) { var rn = this.getRowCount(); var cn = this.getColumnCount(); var rs = 1; var cs = 1; } else { var rn = optR.getNumRows(); var cn = optR.getNumColumns(); var rs = optR.getRow(); var cs = optR.getColumn(); } DebugAssert(this.xMethod=="getValues", 'Can only clear cache values for now-you asked for', this.xMethod); for ( var i= rs ; i < rn+rs ; i++ ) for ( var j= cs ; j < cn+cs ; j++ ) this.setValue (Empty(), i, j); return this; }; /** * Commit the cCacheItem contents to the sheet, and delete the cache */ cCacheItem.prototype.close = function () { //commit changes and kill the cache this.commit(); this.quit(); return null; }; /** * Extend the cache if rn,cn outside current range of sheet - Internal Use:called automatically if needed * @param {number} rn the row number being accessed * @param {number} cn the column number being accessed * @return {cCacheItem} the cCacheItem */ cCacheItem.prototype.extend = function(rn,cn) { // maybe we need to extend the number of rows var cr = this.getRowCount(); var cc = this.getColumnCount(); if (!this.xValues) this.xValues =[]; // need to add any rows? if (rn > cr ) { for (var i = cr ; i < rn ; i++) { this.xValues[i]= [] ; for ( var j=0; j < cc ; j++ ) this.xValues[i][j]= Empty(); } } // maybe the number of columns ? if ( cn > cc){ for (var i = 0 ; i < this.getRowCount() ;i++) { for (var j= cc ; j < cn ; j++){ this.xValues[i][j]=Empty(); } } } return this; }; /** * set or clear whether the cache has been written to - Internal Use:called automatically if needed * @param {boolean=} touched whether cache is written to - default true * @return {cCacheItem} the cCacheItem */ cCacheItem.prototype.setTouched = function(touched) { this.xTouched = fixOptional(touched,true); return this; }; /** * check whether the cache has been written to * @return {boolean} has cCacheItem been written to? */ cCacheItem.prototype.touched = function() { return this.xTouched; }; /** * set or clear whether the cache is valid and force a refresh * @param {boolean=} dirty whether cache is valid - default true * @return {cCacheItem} the cCacheItem */ cCacheItem.prototype.setDirty = function(dirty) { if (dirty) { DebugAssert(!this.touched(), 'cache dirty request with outstanding write cache requests'); } this.xDirty = fixOptional(dirty,true); if (this.xDirty) { // force a refresh now this.getValue(1,1); } return this; }; /** * check whether the cache is valid- when maintained automatically should always be false * @return {boolean} whether cCacheItem is valid */ cCacheItem.prototype.dirty = function () { return this.xDirty ; }; /** * get the number of rows in the cCacheItem * @return {number} whether cCacheItem is valid */ cCacheItem.prototype.getRowCount = function () { return this.getValues() ? this.getValues().length : 0; }; /** * get the max value in cache * @return {*} max */ cCacheItem.prototype.max = function () { var m; for (var i=0; i < this.getRowCount() ; i++ ) for (var j=0; j < this.getColumnCount() ; j++ ) if (this.xValues[i][j] > m || isUndefined(m)) m = this.xValues[i][j] ; return m; }; /** * get the min value in cache * @return {*} min */ cCacheItem.prototype.min = function () { var m; for (var i=0; i < this.getRowCount() ; i++ ) for (var j=0; j < this.getColumnCount() ; j++ ) if (this.xValues[i][j] < m || isUndefined(m)) m = this.xValues[i][j] ; return m; }; /** * get the number of columns in the cCacheItem * @return {number} whether cCacheItem is valid */ cCacheItem.prototype.getColumnCount = function () { return this.getValues() ? this.getValues()[0].length : 0; }; /** * enumerate a the collection in the cCache container * @this {cCache} * @param {function(*,number,number)} a function that will be called for each item */ cCacheItem.prototype.forEach = function (yourFunction) { var nr = this.getRowCount() ; var nc = this.getColumnCount() ; // get the values for this cache var v = this.getValues(); // this will call your function for every value for (var rowIndex = 0 ; rowIndex < nr ; rowIndex ++ ) for (var colIndex = 0 ; colIndex < nc ; colIndex ++ ) { if ( yourFunction (v[rowIndex][colIndex],rowIndex+1,colIndex+1) ) return true; } }; /** * return the values for the range given * @param {Range} r the target range * @return {<Array>.*} values from the given range */ cCacheItem.prototype.getValuesOfRange = function (r) { // get from cache or if outside range return empty var needRows = r.getNumRows(); var needCols = r.getNumColumns(); var nr = r.getRow(); var nc = r.getColumn(); var cacheRows = this.getRowCount(); var cacheCols = this.getColumnCount(); // the whole thing ? if (needRows == cacheRows && needCols == cacheCols && nr == 1 && nc == 1) { return this.getValues(); } // need to make a new shaped array var v= new Array(needRows); for (var i=0 ; i < needRows; i++ ){ v[i]= new Array(needCols); for (var j=0 ; j < needCols; j++ ){ v[i][j]= this.getValue(i+nr ,j+nc); } } return v; }; |
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.