In December, Google released new Sheets, saying they are faster and better than the old – including working offline. I’ve made a couple of tests to compare the old and the new – a simple operation – writing data.
In each case I’m doing 2 tests
- Writing data cell by cell to a sheet
- Writing data in a single operation to a sheet
The results
This shows the average run time across each of the 3 variations
So we see that that writing values all at once, as opposed to cell by cell is still about 5 times as fast, and that new sheets is indeed 2-3 times as fast as old sheets for this kind of test. Of course, VBA, since it is entirely local is much faster than either to perform equivalent operations
The code – Google Apps Script
function test () { var data = createTestData(); // sheets var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); // clear the sheets sheet1.getDataRange().clearContent(); sheet2.getDataRange().clearContent(); // resize them for the test data resizeSheet(sheet1,data); resizeSheet(sheet2,data); // do the various tests var REPEAT = 10; for (var i=0;i<REPEAT;i++) { useTimer("A","one cell at a time old sheets").start(); writeOneCellAtATime (sheet1,data); SpreadsheetApp.flush(); useTimer("A").stop(); useTimer("B","all at once").start(); writeAllAtOnce (sheet2,data); SpreadsheetApp.flush(); useTimer("B").stop(); } Logger.log(useTimer().report()); } function writeOneCellAtATime (ss,data) { // write the data 1 cell at a time data.forEach(function(row,r) { row.forEach ( function (cell,c) { ss.getRange ( r+1,c+1, 1,1).setValue (cell); }); }); return ss; } function writeAllAtOnce (ss,data) { // write the data in one shot if (data.length)ss.getRange (1,1,data.length,data[0].length).setValues(data); return ss; } function resizeSheet ( ss, data) { // need to extend the sheets if necessary var howMany = ss.getMaxRows() - data.length ; if (howMany < 0 ) ss.insertRowsAfter(ss.getMaxRows(), -howMany); var howMany = ss.getMaxColumns() - data[0].length ; if (howMany < 0 ) ss.insertColumnsAfter(ss.getMaxColumns(), -howMany); return ss; } function createTestData () { var data = [],TESTSIZE = {rows:100,columns:20}; for (var i = 0; i < TESTSIZE.rows ; i++ ) { // add some random data of random size var c=[]; for (var j=0; j < TESTSIZE.columns ; j++ ) { c.push (arbritraryString( randBetween(10,200) )); } data.push(c); } return data; } function arbritraryString (length) { var s = ''; for (var i = 0; i < length; i++) { s += String.fromCharCode(randBetween ( 33,125)); } return s; } function randBetween(min, max) { return Math.floor(Math.random() * (max - min + 1)) + min; }
The code – VBA
Private Function testgsheets() Dim data As Variant, c As cProgressTimer Dim d As Double, d1 As Double, d2 As Double Set c = New cProgressTimer data = createTestData wholeSheet("Sheet1").ClearContents wholeSheet("Sheet2").ClearContents Dim REPEAT As Long, i As Long REPEAT = 10 Application.Calculation = xlCalculationManual Application.ScreenUpdating = False For i = 1 To REPEAT d = c.cMicroTimer writeOneCellAtATime Sheets("Sheet1"), data d1 = d1 + c.cMicroTimer - d d = c.cMicroTimer writeAllAtOnce Sheets("Sheet2"), data d2 = d2 + c.cMicroTimer - d Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print d1, d2 End Function Private Function writeOneCellAtATime(ss As Worksheet, data As Variant) As Worksheet Dim r As Long, c As Long For r = LBound(data, 1) To UBound(data, 1) For c = LBound(data, 2) To UBound(data, 2) ss.Cells(r + 1 - LBound(data, 1), c + 1 - LBound(data, 2)).value = data(r, c) Next c Next r Set writeOneCellAtATime = ss End Function Private Function writeAllAtOnce(ss As Worksheet, data As Variant) As Worksheet ss.Cells.Resize(UBound(data, 1) - LBound(data, 1) + 1, UBound(data, 2) - LBound(data, 2) + 1).value = data Set writeAllAtOnce = ss End Function Private Function createTestData() As Variant Dim data As Variant, i As Long, j As Long ReDim data(100, 20) For i = LBound(data, 1) To UBound(data, 1) For j = LBound(data, 2) To UBound(data, 2) data(i, j) = "p" + arbritraryString(randBetween(10, 200)) Next j Next i createTestData = data End Function Private Function arbritraryString(length As Long) As String Dim s As String, i As Long s = "" For i = 1 To length s = s & Chr(randBetween(33, 125)) Next i arbritraryString = s End Function Private Function randBetween(min As Long, max As Long) randBetween = Application.WorksheetFunction.randBetween(min, max) End Function