Speeding up Google Apps Script

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 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);

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

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);