Google add-ons vs Microsoft Office Apps: Read/Write tests

We’ve been spending a lot of time with the Office/Productivity offerings from Google (Google Drive/Docs/Sheets) and Microsoft (Office 365, SharePoint, Word, Excel) and both of these two platforms have some drawbacks, but also some redeeming factors.

• Specifications and limits
• Google Sheets write test
• Google Sheets read test
• Excel Online write test
• Excel Online read test
• Observations
• Conclusion

Google’s offering is purely cloud-based, your add-ons and scripts are hosted by Google and run on both the server and on the client. Microsoft’s offering is unique in the fact that apps for Office can run inside the browser based version of Office on the desktop version. A major drawback we’ve stumbled upon was the fact that Office task pane apps only support the desktop version of Microsoft Word and not the web-based version of MS Word! Because of this, today’s article will only compare Google Sheets and Microsoft Excel Online. It won’t be fair to compare a browser based version of Google Docs with a desktop version of Microsoft Word.

Specifications and limits

Google Sheets should support 2 million cells of data, which translates to a million rows by a million columns. I would not, however, recommend creating such large sheets as performance will be drastically affected. Google does not clearly state what the files size limit is for sheets created through Google Sheets, but it does state that uploaded spreadsheets that are converted to Google spreadsheets cannot be larger than 20 MB.

Microsoft Excel Online supports a file size limit of 250 MB. The sheet sizes are somewhat tricky to calculate as the number of columns supported are 2,147,483,647 bytes or 2 Gigabytes. The amount of rows supported in a table is 1,999,999,997.

Pretty impressive stuff from both Google and Microsoft, let’s see how they measure up.

Google Sheets write test

We’ll create a script that will write random data to our Google spreadsheet. The data will be a combination of 20% strings, 20% dates and, 60% numbers. We’ll start by generating a relatively small table of 500 rows and 20 columns.

The user interface will consist of a simple task pane with two buttons. One to write data to the sheet, the other to read the data. The user interface will be created using the Google UI service and the function responsible for creating the UI will be called createUI. The code for the function follows below:

function createUI() {
    var app = UiApp.createApplication();
    app.setTitle('Google Sheets Write/Read Test');
    var buttonWrite = app.createButton("Write Data");
    var buttonRead = app.createButton("Read Data");
    var panel = app.createVerticalPanel();
 
    panel.add(buttonWrite);
    panel.add(buttonRead);
    app.add(panel);
 
    var handlerWriteButton = app.createServerHandler('writeData');
    buttonWrite.addClickHandler(handlerWriteButton);
    var handlerReadButton = app.createServerHandler('readData');
    buttonRead.addClickHandler(handlerReadButton);
 
    return app;
}

To make generating the data easier, we’ll simply load a JSON array from a text file and load it into the Google Spreadsheet using the setValues function of the Range object. This will happen as soon as the user clicks on the “Write Data” button, which in turn calls the writeData function:

function writeData() {
    var numberOfRows = 500;
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getRange("A1:T" + numberOfRows);
    var response = UrlFetchApp.fetch("https://coalitionsoft.net/Data500x20.txt");
    var arr = JSON.parse(response.getContentText());
    var start = new Date();
    range.setValues(arr);
    var end = new Date();
    var seconds = (end.getTime() - start.getTime()) / 1000;
    range.getCell(1, 1).setValue("Write took " + seconds + " seconds.");
};

In the function above, we’ll get a reference to a range object, which is the size of the number of rows we’ll add with 20 columns. This range in set using the getRange function of the Sheet object. Next, we’ll load a JSON array from a file hosted on an external server. This is accomplished using the UrlFetchApp object’s fetch function.

Next, after receiving a response from the fetch function, we’ll load the content of the response into an array. We’ll then get a reference to the current date/time and write the array values to the sheet using the Range object’s setValues function. After the values have been set, we’ll get the date/time and write the amount of seconds it took to the first row/column of the sheet.

The results of our write tests for Google Spreadsheets were:

*Results are based on an average of 3 best times

Google Sheets read test

To test the read speed, we’ll get a reference to the range we’ve inserted using the writeData function and loop through each row in the range’s columns. As we iterate over the rows, we’ll write the cell value to a string variable and record the number of seconds it took to read each cell’s value. The function responsible for this is readData, and its code looks like the following:

function readData() {
    var numberOfRows = 500;
    var values = "";
    var sheet = SpreadsheetApp.getActiveSheet();
    var start = new Date();
    var range = sheet.getRange("A1:T" + numberOfRows);
    var rows = range.getNumRows();
    var cols = range.getNumColumns();    
    for (var i = 1; i <= rows; i++) {
        for (var j = 1; j <= cols; j++) {
            var cellValue = range.getCell(i, j).getValue();
            values = values + cellValue;
        }
    }
    var end = new Date();
    var seconds = (end.getTime() - start.getTime()) / 1000;
    range.getCell(1, 1).setValue("Read took " + seconds + " seconds.");
};

The readData function retrieves the sheet data and then iterates through the data, but let’s also test the speed at what the getValues function of the Range object fetches the data. To do this, we’ll use similar code, but we will not iterate over the returned array:

function getValues() {
    var numberOfRows = 500;
    var ui = SpreadsheetApp.getUi();
    var start = new Date();
 
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getRange("A1:T" + numberOfRows);
    var result = range.getValues();	
    var end = new Date();
    var seconds = (end.getTime() - start.getTime()) / 1000;
    range.getCell(1, 1).setValue("Read took " + seconds + " seconds.");
};

The results of our read tests for Google Spreadsheets were:

*Results are based on an average of 3 best times

Excel Online write test

To test Excel Online, we’ll create another Task pane app, and add two buttons to the user interface, one to read the data and another to write. This is done by editing the mark-up in the Home.html file:

<div id="content-header">
    <div class="padding">
        <h1>Excel Online Read Test</h1>
    </div>
</div>
<div id="content-main">
    <div class="padding">
        <button id="writeData">Write Data</button>
        <button id="readData">Read Data</button>
    </div>
</div>

Next, we’ll add event handlers for both buttons in the Home.js file:

(function () {
    Office.initialize = function (reason) {
        $(document).ready(function () {
            app.initialize();
            $('#writeData').click(writeData);
            $('#writeData').click(readData);
        });
    }});

The writeData function will be called to insert data in the spreadsheet and to read it we’ll call the readData function. The code for the writeData function follows below:

function writeData() {
    var rows = [["Brown", "McKnight", "Joel", "Von",...]];
    var start = new Date();
    Office.context.document.setSelectedDataAsync(rows, { coercionType: "matrix" },
               function (result) {
                   if (result.status == "succeeded") {
                       var end = new Date();
                       var seconds = (end.getTime() - start.getTime()) / 1000;
                       app.showNotification("It took " + seconds + " seconds.");
                   }
                   else {
                       app.showNotification("Could not insert.");
                   }
               });
}

In the above code, we declared the data array and set it to an array of values (Truncated in the code), we then save the current date/time and the values in the rows array to the spreadsheet using the setSelectedDataAsync function. Note that we set the coercionType to matrix.

If we successfully inserted the data, a notification is displayed to indicate the amount of time it took to write the data. Let’s see how Excel Online measures up:

*Results are based on an average of 3 best times

Excel Online read test

Reading the Excel Online data will be accomplished using the readData function. This function will get the selected data in the worksheet using the getSelectedDataSync function. It then iterates over all the data and assigns the value to a variable. We then record the number of seconds it took to loop through the entire selection. Below the code for the method:

function readData() {
    app.showNotification("Reading data, please wait...");
    var start = new Date();
    Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix,
    function (result) {
        if (result.status == Office.AsyncResultStatus.Succeeded) {
            for (var i = 0; i < result.value.length; i++) { // iterate on the array
                var row = result.value[i];
                for (var cell in row) {
                    var value = row[cell];
                }
            }
            var end = new Date();
            var seconds = (end.getTime() - start.getTime()) / 1000;
            app.showNotification("Read took " + seconds + " seconds.");
        }
        else {
            app.showNotification("Error:" + result.error);
        }
    });
}

As with the Google Sheets experiment, let’s also just test the time it takes the getSelectedDataAsync function, by using the following code:

function getDataAsync() {
    app.showNotification("Reading data, please wait...");
    var start = new Date();
    Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix,
    function (result) {
        if (result.status == Office.AsyncResultStatus.Succeeded) {
            var end = new Date();
            var seconds = (end.getTime() - start.getTime()) / 1000;
            app.showNotification("Read took " + seconds + " seconds.");
        }
        else {
            app.showNotification("Error:" + result.error);
        }
    });
}

The read results for Excel Online are:

*Results are based on an average of 3 best times

Observations

My thoughts and opinions about the test results and overall impression of how Google Sheets and Excel Online handle large amounts of data follow below.

Google Spreadsheets performance on large amounts of data

This was a very interesting test as the two platforms have very remarkable ways of handling large amounts of data. Google adds the first 20 or so columns very quickly and then starts to slow down, by adding columns two by two and approximately 400 rows at a time. Google does, however, show a small infinite progress indicator to indicate it is busy, but this does not always get shown, leaving the user to wonder what is going on.

I’ve also later discovered that the write and read durations specified earlier on in this article might not be 100% accurate as it appears that Google does write the data asynchronously. And as mentioned this does appear to happen in batches of 400.

Excel Online performance on large amounts of data

Excel Online had an intuitive method of working with large amounts of data, as it showed a “Working on it” overlay on the screen, and swapped between a “Still working on it” and “Working on it” message – which is a nice touch.

Conclusion

As I’ve mentioned both environments have their pros and cons. I do feel that if ever there needs to be a love child between Google and Microsoft, this is it. The smoothness and responsiveness of Excel Online combined with the vastly superior object model of Google Sheets would be a fantastic environment to work with and build apps on and I hope we see this in future.

Another major plus point for Office Online is the fact that we can use Visual Studio to build Apps for Office. Whereas Google’s online code editor is impressive, Visual Studio is still one of the best IDEs out there.

Thank you for reading. Until next time, keep coding!

Available downloads:

Test Read/Write project

You might also be interested in:

• How to create Excel Apps for Office 365
• Extending Google Spreadsheets with Google Apps Script