Are the new Google Sheets faster than the old?

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
In old Spreadsheets, it has always been best to get all the data in one go, play around with it, and write it all back in one go. Let’s see if there is still such a dramatic difference with the New Sheets For comparison, I’m also approximating the same thing in VBA in offline mode. I’ll also repeat the test a number of times throughout the day to get a good sample of numbers.

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
For more stuff like this, see Excel Liberation. I haven’t reproduced the timer code here, but if you are interested you can find it here.

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *