I am using Google Sheets more often recently and it's not for the reasons you might expect. Among business and finance professionals, Google Sheets is laughable when compared to Microsoft Excel and I agree. I am not going to convince you to use Sheets over Excel.
I do want to share one killer feature in Sheets that you can quickly add to your workflow. Google "Apps Script" is the scripting platform for Sheets and was introduced way back in 2009. Until recently, I had never heard of it. Not a single colleague I know uses it and I discovered it by accident.
Microsoft Excel - the king of spreadsheets
In the battle for desktop productivity software, Microsoft Office is the undisputed leader and has been for a very, very long time. Within Office, Excel is also the leader in spreadsheet software. For decades, analysts cited "first-mover" advantage, market share and learning curve as to the reasons why this is the case. With respect to "learning curve" - I don't think the majority of users use Excel correctly, but that is a discussion for another day.
There was a slight hiccup between 2006-2010 where I was skeptical that Office would survive the wave of software cloud-ification. Sure enough, Microsoft brute-forced its way into the cloud with Office 365. Although the transition was a little bumpy, the current offering is exceptional (sorry - Sharepoint is still awful). Files can be stored locally or in the cloud (Microsoft OneDrive) and you can choose between desktop or online versions of Office products. They work as expected and are reliable.
Google Sheets - the underdog
Google Sheets is often quoted as an alternative to Excel but has always lagged behind Excel's feature set. The convenience and cost (free) made it a hit when it was first released. While many praised Google Sheets for its collaboration features, Excel caught up within a few years. Sheets does the basics well and for amateur users it "just works" - Google knows this and has embraced it.
Google has carved out a niche where power users are not welcome. The web-only interface is clunky, a lot of the expected features are missing and the Microsoft ".xlsx" filetype is not cross-compatible. The last point is probably not within Google's control.
Great - so why continue this discussion if Microsoft Excel is the best and Google Sheets is inferior? Well, the Google Sheets scripting environment (Apps Script) is so powerful and so easy to set up that it is a very attractive alternative for simple (and complex) tasks.
"Apps Script" Features
Google describes "Apps Script" as a "rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite." You write code in modern JavaScript and have access to built-in libraries. There's nothing to install because Google gives you a code editor right in the browser, and your scripts run on Google's servers.
- Language: Javascript
- Libraries: Built in
- IDE: Built in or local
- Execution: Google servers
It uses a language I already know and requires zero setup time. There is really nothing more to ask for.
Okay, but Excel has that too!
Yes, this is partially true. Microsoft is making a lot of progress in this area.
For Microsoft Excel, VBA is the legacy programming and scripting language. It had an extremely painful learning curve and few used it for anything critical. Because of the learning curve, the average team could not use VBA effectively unless there were dedicated experts. It is important to note that any expert will always prefer a professional stack that doesn't include VBA ie. separate relational/object databases, custom server setups etc.
Fortunately, Microsoft embraced Javascript as the successor to VBA a few years ago with Office.js and now a suite of Office Add-in APIs built on Javascript. The problem is that the Add-in API is not for "scripting" and using the Microsoft Graph API takes time to set up.
Not convinced?
The key advantage for Apps Script that it is fast. I timed it and the setup time is < 5 seconds. I will be sharing specific examples in the future but in the mean time you should give it a try:
All you need to do is open a Google Sheet then navigate to "Tools > Script Editor" from the main menu. You should be presented with a screen that looks like this:
That's it, you are set up!
Still not convinced?
You should be. If you cannot wait for me to share examples, here is a simple one:
function myFunction() {
var response = UrlFetchApp.fetch("https://api.exchangeratesapi.io/latest");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var object = Utilities.jsonParse(response.getContentText());
var objRates = object["rates"];
for (var i in objRates) {
if(i="USD") {
sheet.getRange(1, 1).setValue(objRates[i]);
}
}
}What does it do?
This script pulls the latest EUR/USD exchange rate and places it in cell A1. This simple example is so powerful because it covers an HTTP GET request and manipulation of spreadsheet cell data in 11 lines of code using no library includes.
Note: If the above is not self explanatory then you might need to brush up on your programming knowledge before taking full advantage of this feature.