It’s well known that reading all the values at once from a Google Spreadsheet is much more efficient than doing it one by one. You can work round this if you are starting from scratch, but what if you are migrating from Excel/VBA and want to minimize the structural changes? Of course in Excel, it’s also more efficient to read all the values at once, but not the order of magnitude you find with Google Apps Script.
Caching Values
It occurred to me that it would be pretty straightforward to abstract getting spreadsheet values using a caching mechanism, so I put one together for a complex project migration from Excel to Google Apps Script. You can get the code for the getValues() cache here.
How much faster
I took a small sheet, a few hundred cells, and found that using this caching mechanism, it was a factor of 70 times faster.
Heres the test results
1 2 3 4 5 |
time to read inputdata sheet values|10376 time to read cached inputdata sheet values using foreach|148 time to read cached inputdata sheet values using loop|146 time to read cached inputdata sheet values using foreach 2nd time|44 time to read inputdata sheet values- 2nd time|10357 |
Heres the test code
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455 var nIters = 2;var x;var start = new Date();var wr = wholeSheet("inputdata");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();}}DebugPrint('time to read inputdata sheet values', new Date() - start);var start = new Date();var cache = sheetCache(wr);for (var n = 0; n < nIters ;n++)cache.forEach( function(v) {x = v;});DebugPrint('time to read cached inputdata sheet values using foreach', new Date() - start);// do the same thing again, this time using a loopvar start = new Date();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);}}DebugPrint('time to read cached inputdata sheet values using loop', new Date() - start);// second time foreachvar start = new Date();var cache = sheetCache(wr);for (var n = 0; n < nIters ;n++)cache.forEach( function(v) {x = v;});DebugPrint('time to read cached inputdata sheet values using foreach 2nd time', new Date() - start);var start = new Date();var wr = wholeSheet("inputdata");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();}}DebugPrint('time to read inputdata sheet values- 2nd time', new Date() - start);
This has now been extended to cache all apps sheet methods, such as getFontColors, getComments etc. See http://excelramblings.blogspot.co.uk/2012/03/google-apps-script-access-to.html or https://ramblings.mcpher.com/Home/excelquirks/gooscript/optimize for details.