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
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
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 loop var 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 foreach 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 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.