My best days at work are spent hacking with startups - and Y Combinator (S 14) startup Blockspring might just be one of the coolest things I came across. Blockspring enables users to create and share custom functions for Google Sheets and Office in Node.js, Python, R, JavaScript, PHP and Ruby, running complex calculations in the cloud and pushing the results to your spreadsheet. You can easily create a spreadsheet that searches Twitter for “#Windows10”, take all the names of people who wrote tweets, find their LinkedIn profiles, and run image analysis on their profile pics. Or do anything else: Blockspring has thousands of functions at this point.
It's More Difficult if it's Cross-Platform
Plugins for Office are not necessarily a new idea, but the old plugin model is gone - with Office now running on tablets and computers as well as on all major systems (iOS, Android, Windows), the Office team was forced to rethink the interaction between add-ins and Office entirely. The result is OfficeJS, a clever, but still limited approach to building addons with HTML5 and JavaScript. One of the things still missing from the cross-platform OfficeJS: Custom functions.
We started with various hacks around the current platform limitation, but Blockspring ended up with a smart implementation: Instead of using the official function call =function(param), they are now simply using function(param), thus not baiting Office into complaining about an incorrect function call.
Observing the whole table or getting access to the table data remains tricky, but OfficeJS does allow developers to read and write to the current selection. The add-in now features a simple flow: Select a function, insert the function call into a cell, and execute the calculation by clicking a "Run function in Current Cell" button.
Getting Cell Data
function getDataFromSelection(callback) {
Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix,
function (result) {
if (result.status === Office.AsyncResultStatus.Succeeded) {
callback(null, result);
} else {
callback(result.error);
}
}
)
};
Setting Cell Data
function setDataForSelection (newValue, callback) {
Office.context.document.setSelectedDataAsync(newValue, callback);
};
In order to parse and execute a "custom function" in a cell, one simply has to get the current selection, parse the content with regular expressions (or anything else that runs in JavaScript) and send the result off to a web service running the actual function in a language of your choice. Once the service returns, you can overwrite the cell with the new value.
Keeping the Functions Intact
OfficeJS does currently not support custom functions - the old plugin model does, though. So how do you make sure that more powerful installations of Excel are presented with a spreadsheet with functions intact?
To help developers cope with the missing access to the whole spreadsheet, OfficeJS allows the creation of named bindings. Those bindings are references to certain areas in the spreadsheet (cells, matrixes) that can be stored as part of the document. Even though the cell might contain raw text, the binding can be used to persist information about the original function.
Get it!
All in all, Blockspring is an impressive showcase of how to use OfficeJS. If you want to turn your spreadsheets into magic, go get the add-in for Office here, for Google Sheets here, or visit their homepage here.