Excel Macros with Javascript

Question

I wish to manipulate excel spreadsheets using macros in Javascript rather than the default VBA. I can execute javascript code using the following VBA code

'javascript to execute
Dim b As String
b = "function meaningOfLife(a,b) {return 42;}"

'VBA tool to run it
Dim o As New ScriptControl
o.Language = "JScript"
o.AddCode b
MsgBox o.Run("meaningOfLife", 0, 1)

this enables me to execute arbitrary javascript, however I do not have access to the excel spreadsheet from within the javascript environment. Is there any way I can set and get worksheet values in the active worksheet from within javascript?

Answer 1

For people using Excel 2016 or later version, there is an Excel add-in called Funfun in the add-in store that actually allows you to write and run JavaScript code directly in Excel. And of course, your JavaScript code also has access to the data stored in the spreadsheet. Here is a screenshot of how it looks like in Excel 2016.

Well in the middle of the interface you have a section in which you could write JavaScript, CSS and HTML code. It is pretty much like a playground built into the Excel. But the Funfun also has an online editor in which you could test with your code. You could see it in the pic below. I also posted the link of the example in the first picture so you could play with.

https://www.funfun.io/1/#/edit/5a4e0d461010eb73fe125c4e

What is special about the Funfun online editor is that it contains a 'spreadsheet' just like Excel. Though you can't actually do any formatting in here, you could copy your data into the cells and test your code directly.

To use the data stored in the spreadsheet, all you need to do is to write some configuration in the short.io file of Funfun to tell JavaScript which area in the spreadsheet that contains your data. For example, in the example that I posted, all you need to write is

{
    "data": "=A2:B9"
}

And in the JavaScript code, an object called $internal is used to read the data. So in order to read the data that stored in A2:B9, you need to write

var data = $internal.data;

And its done. You could go to the documentation of Funfun if you want to know more.

If you are satisfied with the result you achieved in the online editor, you could easily load the result into you Excel using the URL above. Of couse first you need to insert the Funfun add-in from Insert - My add-ins. Here are some screenshots showing how you could do this.

Disclosure: I'm a developer of Funfun