{"id":2172,"date":"2015-07-21T16:34:28","date_gmt":"2015-07-21T16:34:28","guid":{"rendered":"https:\/\/www.microsoft.com\/reallifecode\/index.php\/2015\/07\/21\/wicked-cool-excel-function-with-blockspring\/"},"modified":"2020-03-15T16:04:53","modified_gmt":"2020-03-15T23:04:53","slug":"wicked-cool-excel-function-with-blockspring","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/ise\/wicked-cool-excel-function-with-blockspring\/","title":{"rendered":"Wicked Cool Excel Function with Blockspring"},"content":{"rendered":"

A small Y Combinator startup is trying to completely remove all constraints for functions in spreadsheets: It 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.<\/p>\n

You can easily create a spreadsheet that searches Twitter for \u201c#Windows10\u201d, take all the names of the 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. This case study describes how we helped turn the Blockspring addon into a modern Office Add-In and made it available in Excel 2015, Excel Online, and Excel for iPad.<\/p>\n

\"Image<\/p>\n

It\u2019s More Difficult if it\u2019s Cross-Platform<\/h2>\n

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 operating 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 add-ons with HTML5 and JavaScript. While more and more features are added as time goes on, one thing is still missing from the cross-platform OfficeJS: Custom functions.<\/p>\n

Custom functions seem like a core feature for any startup trying to build a business around user-defined functions, but through clever engineering, we managed to find a way enabling Excel users to use Blockspring\u2019s amazing repository of custom functions.<\/p>\n

Overview of the Solution<\/h2>\n

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)<\/code> they are now simply using function(param), thus not baiting Office into complaining about an incorrect function call.<\/p>\n

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 \u201cRun function in Current Cell\u201d button.<\/p>\n

Implementation<\/h2>\n

Modern Office Add-Ins are basically just small web apps, written in HTML5 and JavaScript. They are displayed in a small WebView next to the spreadsheet and given access to office methods through OfficeJS, which provides various APIs to communicate with the native parts of Office. For those readers familiar with the concept behind Apache Cordova, this approach is very similar.<\/p>\n

Let\u2019s take a look at some code, starting with observing the spreadsheet. As outlined earlier, OfficeJS does not allow observation of the whole spreadsheet, so as a workaround, one has to observe the current user selection and read the data therein.<\/p>\n

Getting Cell Data<\/h5>\n
\n
function getDataFromSelection(callback) {  \r\n  Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix,\r\n    function (result) {\r\n      if (result.status === Office.AsyncResultStatus.Succeeded) {\r\n        callback(null, result);\r\n      } else {\r\n        callback(result.error);\r\n      }\r\n    }\r\n  )\r\n};\r\n<\/code><\/pre>\n<\/div>\n
We can now read function calls from the selected cells, parse them, and kick off an asynchronous computation on Blockspring\u2019s servers. Once the result is returned, we can insert it into the document with a simple call:<\/p>\n
Setting Cell Data<\/h5>\n
\n
function setDataForSelection (newValue, callback) {  \r\n  Office.context.document.setSelectedDataAsync(newValue, callback);\r\n};\r\n<\/code><\/pre>\n<\/div>\n
Keeping the Functions Intact<\/h2>\n
OfficeJS does not currently support custom functions unlike the old plugin model does. So how do you make sure that Desktop installations of Excel are presented with a spreadsheet with functions intact?\nTo help developers cope with the missing access to the whole spreadsheet, OfficeJS allows the creation of\u00a0named 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. Let\u2019s look at some code:<\/p>\n
Creating a Named Binding<\/h5>\n
\n
function bindNamedItem() {\r\n  Office.context.document.bindings.addFromNamedItemAsync('myRange', 'matrix', {\r\n    id:'myMatrix'\r\n  }, function (result) {\r\n    if (result.status === 'succeeded') {\r\n      console.log('Added new binding with type: ' + result.value.type + ' and id: ' + result.value.id);\r\n    } else {\r\n      console.log('Error: ' + result.error.message);\r\n    }\r\n  });\r\n}\r\n<\/code><\/pre>\n<\/div>\n
Since the bindings are stored in the document, they can be accessed by a native plugin \u2013 and used to restore the function inside the cell.<\/p>\n
Opportunities for Reuse<\/h2>\n
While this workaround might feel a bit cumbersome compared to a native \u201ccustom functions\u201d API, it does work for any kind of custom function implementation: In order to parse and execute a \u201ccustom function\u201d 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.<\/p>\n
Check out the Result!<\/h2>\n
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\u00a0for Office here, for\u00a0Google Sheets here, or\u00a0visit their homepage here.<\/p>\n","protected":false},"excerpt":{"rendered":"
A small Y Combinator startup is trying to completely remove all constraints for functions in spreadsheets: It 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.<\/p>\n","protected":false},"author":21345,"featured_media":11228,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[17],"tags":[],"class_list":["post-2172","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-frameworks"],"acf":[],"blog_post_summary":"
A small Y Combinator startup is trying to completely remove all constraints for functions in spreadsheets: It 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.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/2172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/users\/21345"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/comments?post=2172"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/posts\/2172\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media\/11228"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/media?parent=2172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/categories?post=2172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/ise\/wp-json\/wp\/v2\/tags?post=2172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}