Excel Online & Google Sheets for developers – what’s the difference?

In the last few weeks we’ve covered a lot of aspects of both Google Sheets and Excel Online and we’ve seen that both platforms offer developers various options to customize and integrate with. We also realized that each platform has as a number of caveats as well as a number of redeeming qualities.

In this article we’ll evaluate what we’ve learned so far about Google Sheets and Excel Online and compare the two platforms and what it means for developers.

• UI Extensibility
• Excel Online
• Solution architectures
• Object models
• Limitations
• Google Sheets Store vs. the Office Store

UI Extensibility

Both Google Sheets and Excel Online provide developers with a way to add their own UI in the form of a side bar or task pane. These are analogous to the task panes that are available in Microsoft Office and can be used to create a variety of user interfaces.

Google Sheets

Google allows developers to create user interfaces using either their UI or Html service. Using the UI service inside their scripts developers can use a wide variety of UI elements such as radio buttons, check boxes, and buttons and even dialog boxes. All controls that are rendered using the UI service conform to a standard style and if you wish to have a bit more control over your user interface, you can use the Html service, although a rather large number of restrictions apply here.

Google sheets also offers the following extensibility points:

• Custom Functions
• Custom Menus
• Custom Dialogs

Custom Functions are comparable to User Defined Functions available in Microsoft Excel and is used in a similar fashion inside Google Sheets. The following image illustrates how to use a custom function from within Google Sheets:

To create a custom function for Google Sheets, select Script Editor from the Tools menu. The Script editor will open and you can create the custom function as you would any normal JavaScript function.

Custom menus in turn, allow a developer to add their own menu items to the standard Google Sheets menu. To add your own menu items, create a new function using the Script Editor and the addMenu method of the SpreadSheet object to create a new menu as demonstrated with the following code:

function createMenu() {
    var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [{
        name: "Create Sample Data",
        functionName: "writeData"
    }];
    activeSheet.addMenu("My Custom Menu", entries);
};

After the script ran, you should see a “My Custom Menu” item inside Google Sheets:

Google Sheet’s custom dialogs provide a way to display dialog windows to the user which either shows the user a notification or it can accept user input. You can also create a customized dialog that contains your own UI elements.

The following code shows an alert and sets the active cell’s value to the choice the user made:

function showAlert() {
    var myUI = SpreadsheetApp.getUi();
    var result = myUI.alert('Confirmation', 'Are you sure you want to generate new data?', myUI.ButtonSet.YES_NO);
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getActiveCell();
    range.setValue(result)
}

You can also accept inputs using dialogs, the following code accepts a text input and saves the value to the active cell:

function acceptInput() {
    var myUI = SpreadsheetApp.getUi();
    var result = myUI.prompt("Enter destination city");
 
    var destinationCity = result.getResponseText();
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getActiveCell();
    range.setValue("You are going to " + destinationCity);
}

Excel Online

Excel Online allows developers to essentially do anything they are able to do inside a standard Html page. It is also evident that Microsoft promotes the use of jQuery by automatically including a reference to the library inside the main page when creating a new Office App. Microsoft does not dictate the look and feel of Office Apps and developers are free to design their UI’s as they see fit.

Developers have two choices to display their user interface inside Excel Online:

• Task pane
• Content apps

Task panes as similar to the Google Sheets sidebars and as I’ve mentioned earlier, developers are pretty much free to put any Html element inside the task pane. The task pane itself is not very flexible as developers are not able to specify the width of the task pane.

Content apps is unique to Excel Online and enables developers to show web content in line with the Excel document. It is comparable to inserting a chart inside MS Excel and can be moved around inside the document in a similar fashion as you would a chart object.

The Bing Maps App is a good example of an Excel Content App. As illustrated in the image below, the app highlights data on a Bing map based upon data inside an Excel spreadsheet.

Solution architectures

Now let’s have a closer look at the solution architecture of Google spreadsheets and Excel apps.

Hosting

Google App scripts are hosted on the Google servers. This is not necessarily the case with Gmail gadgets though, which can be hosted on your own private server. To run an app script is as easy as opening the script editor and running the script, Google will take care of the deployment and hosting. Of course all of this requires a Google Drive account.

Excel Online on the other hand requires you to provision a Developer site on SharePoint in order to create and host your apps. It takes a few more steps to create the app from within the developer site and deploying and running the app is not as straight forwards as with Google Sheets.

Development environment

Both Google Sheets and Excel Online offer fairly decent online IDEs which provide developers with intelli-sense. A mayor benefit of the Google IDE, which the Microsoft IDE does not offer, is that it provides compile-time error checking and will display a notification if something has gone wrong, i.e.:

Of course, the Microsoft IDE trumps the Google IDE in the fact that the entire app project can be downloaded and opened inside the desktop version of Visual Studio.

Code protection

A concern for many developers is the fact that when running JavaScript inside a browser their code can be rather easily viewed by using a tool such as Fiddler. Fiddler is a web debugging proxy that developers can use to view Http traffic. This means that when running an Excel Online App, I can easily see which scripts are run and using Fiddler, view the source code inside the script.

For example, if you refer to the following image, you’ll see that the Fiddler is able to get the source of the CSS, HTML and JavaScript of my TaskPaneApp1 App.

On the other hand, I was not able to see the source for my Google App script. Not sure if I simply missed it or if Google has some super intuitive way of hiding the source.

Object Models

An object model is arguably the key point for any developer when it comes to creating our own customizations.

Data manipulation

When it comes down to it, I have to say that the Google API does feel more mature than the Excel Online/Office.js API. Microsoft went the route of providing a unified Object model, for example Excel, Word and PowerPoint that has a Document
object which shares a number of properties and functions whereas Outlook has the Mailbox object. Both objects can be accessed via the Context object.

Google provides object models that are a lot more aligned to the existing MS Office COM based object models. For example, the main point of entry for developing a Google Sheet script is the SpreadsheetApp object which is analogues to the Excel.Application object in the Office object model. Google also has more functions to manipulate and get access to ranges, whereas Excel Online provides only two fairly generic methods called getSelecteDataAsync and setSelectedDataAsync in order to get or set data in the spreadsheet. The same two methods can also be used to get or set data inside a Word document, and I think this is where the limitation crept in.

With Google’s array-based API for ranges it is also a bit clearer to understand what is returned and what to expect, although the Microsoft functions do accept a CoercienType parameter with which you can get the data in a variety of formats.

Documentation

Microsoft’s Office.js documentation does not fall short, however, I did find the Google Apps documentation far easier to follow and much more comprehensive. This could perhaps entirely be contributed to the fact that the API is more mature than Microsoft’s.

Limitations

As I’ve already mentioned both platforms have their strengths and weaknesses. Let’s point out the most significant limitations of each.

Same-origin policy

A big thorn in my side, with the Office.js object model, was that retrieving data from external websites was a mayor challenge due to their same-origin policy. Of course a standard work around for this is to use JSONP, but this depends whether the service you are using, does indeed support it.

Google provides an UrlFetchApp object that enables you to fetch data from an external server eliminating the need to use a JSON work around. This is a nice touch and can save you a lot of time and effort.

Size limitations

The Napa development tools IDE only allow you to upload files smaller than 1 MB. I’ve also experienced a number of timeouts when saving large code files using the IDE. Fortunately, the time-out problem is relatively easy to work around by opening the project in Visual Studio.

Google does not allow file uploads inside the App Script project, so the same limitations could not be tested.

Google Sheets Store vs. the Office Store

Both Google and Microsoft offers App stores with which users can add new apps to Excel or Sheets. To add an app to Google Sheets, select “Get add-ons” from the Add-ons menu. A modal dialog window will open which will list all the add-ons available for Google Sheets. You are also able to search for a specific add-on.

Clicking on the add-on will take you to a page that describes the add-on. To add it to your Sheets, simply click on the install button in the top right-hand corner.

Excel Online supports Apps but you cannot insert apps into the sheet via Excel Online. Instead, you’ll need to first add the app to the desktop version of Excel and then upload the document to Excel Online.

In order to add an app to Excel, click on the Store or My Apps button inside the Apps ribbon group on the Insert ribbon tab. This will display a similar window to the one Google Sheets displays where you can see a list of available apps and also search for a specific app.

Clicking on an app will show you a short description about the app as well as the name of the publisher. To add it to your sheet, click on the Trust It button.

Conclusion

As I’ve mentioned previously, both these development platforms offer a number of features but neither is perfect. Google’s offering feels more polished in certain scenarios, but Excel Online has other redeeming factors. We have to keep in mind that it will probably be some time until we can expect to have the same amount of flexibility we enjoyed with the desktop version of MS Office. However, both platforms are ready for us to start offering our users a choice and I’m sure that the online offerings will most certainly become the platform of choice for many.

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

You may also be interested in:

• Google add-ons vs Microsoft Office Apps: Read/Write tests
• How to create Excel Apps for Office 365
• Build Google Spreadsheets add-ons