Message Boards Message Boards

2
|
18666 Views
|
9 Replies
|
13 Total Likes
View groups...
Share
Share this post:

EmbedCode examples and procedures for spreadsheets such as Excel and Google

So, it appears possible in Mathematica 10 to generate VisualBasic code and JavaScript code by using commands such as

 EmbedCode[APIFunction[{"x" -> "Real"}, #x^2 &], "JavaScript"]

But what isn't clear to me is what one does with the output. I would love it -- and it would be extremely useful to a LOT of people, I think -- if someone could either describe or make a video showing how one takes the Embeddable Code and stuffs it into an Excel spreadsheet or Google Spreadsheets and then calls the function from a cell within one of these spreadsheets.

POSTED BY: Seth Chandler
9 Replies

Seth, thanks for following up on this. I agree that not being able to run EmbedCode results in google spreadsheets is a problem. I'll try to get the developers' plans for future development.

Just for fun, I set up some Javascript code to create a function in google spreadsheet. I know almost nothing about Javascript, so perhaps this could be done better. First, I create and deploy an APIFunction in Mathematica.

CloudDeploy[APIFunction["x"->"Number", #x^2 &], Permissions->"Public"]

Now I go to google spreadsheet tools/script editor and create a function to call this API. Note that I have copied and pasted the url from the deployed api and specified the variable's name ("x").

function myapi(x) { 
    var url = "http://www.wolframcloud.com/objects/ed9022d0-e195-4265-96c4-1d7b0d0f68b8";
    var args = {"method" : "POST",
                "payload" : {"x" : x}};
    var response = UrlFetchApp.fetch(url,args).getContentText("UTF-8");
   return response
}

Then in my spreadsheet, I just call the function in a cell with =myapi(-input-), where input can be a number or a cell reference.

Granted, this is a trivial case, since there is a single input variable and the output is just a string. But it does work! I expect that extending to multiple input variables or different types of output would not be too hard.

POSTED BY: Chad Knutson

I have made a bit of "progress" on this issue with the help of a similar posting on Stack Exchange but I regard the matter as (a) important and (b) unresolved. Here's what I've discovered. At present, when you run EmbedCode[APIFunction[something],"JavaScript"], Mathematica returns code that creates a JavaScript object called WolframCloudCall. One can't use the resulting code directly from within Google Spreadsheets. Instead, one has to hand modify it. The idea, apparently is that one writes a little JavaScript function that creates a WolframCloudCall object and then runs the call method on it. (see below) The first parameter to the call method appears to be the data one wants shipped to the WolframCloud and the second parameter to the call method appears to be a function that retrieves the result from the WolframCloud and displays it in Mathematica. Then, within Google Spreadsheet one calls the init function.

function init() { 
wcc = new WolframCloudCall(); 
wcc.call( 
// regular parameters of the deployed function 
// in this case, we want to square this number 
10.22, 
// callback function to retrieve the result returned by the cloud 
function(result) { 
result
} 
); 
} 

Perhaps this degree of indirection is elegant, but it seems to me unnecessarily cumbersome. Why can't the process of generating this little JavaScript function be automated?

The more serious problem, however, is that even after one hand writes this little JavaScript function, it DOES NOT WORK! This is apparently because the communication protocol contained in the JavaScript code generated by EmbedCode uses XMLHttpRequest. But XMLHttpRequest is not supported by Google Spreadsheets (perhaps for security reasons??). There's a discussion of this point here: http://stackoverflow.com/questions/18021422/ajax-calls-made-from-google-spreadsheet-custom-function/18684535#18684535 and discussion of a possible work around here: https://developers.google.com/apps-script/articles/picasa_google_apis.

So, it appears at present that in order to get Google Spreadsheet to communicate with the Wolfram Cloud one is going to have to do a lot of hand coding that requires considerable expertise in JavaScript. I believe what will be required is use of URLFetchApp class in JavaScript and then some sort of parsing of the XML object returned. But, to be honest, this is all a bit over my head.

Recommendations

  1. Wolfram should work on making this much simpler. This would be a great use of the WolframCloud (which generates revenue for Wolfram). Lots of people use Google Spreadsheets. Expanding its capabilities via Wolfram API functions is exactly what Version 10 is about.
  2. If anyone is interested in collaborating with me on modifying the code generated by the current EmbedCode[APIFunction[blah],"JavaScript"] so that it does work in Google Spreadsheets, please communicate with me either via this Community or look me up on the University of Houston website and send me an email.

Note

This post does not discuss the linkage with Excel but it looks as if there is a similar issue. Yes, one can hand write a function to communicate as the poster did above, but that should not be necessary. One should simply be able to write EmbedCode[APIFunction[blah],"Excel",ExcelSpecificOptions->{something}] and the necessary code should be generated. Also, better documentation on how to get Excel to communicate with the Cloud would be helpful. And if there is some money in getting Google Spreadsheet to communicate with the Wolfram Cloud there are even bigger bucks in establishing an EASY TO USE connection between Excel and the Wolfram Cloud. I would urge that version 10.01 contain this functionality and, again, am willing to lend whatever help I can provide in collaborating with anyone in the Community to get such a thing up and running.

Disclaimer

I am not an expert in JavaScript of VBA so it is possible I am misunderstanding something. If so, corrections welcome.

POSTED BY: Seth Chandler
POSTED BY: Pedro Fonseca
Posted 10 years ago

and it would be extremely useful to a LOT of people,

Maybe, but disappointing to see that APIFunction requires the cloud. Does every update of my {desktop} spreadsheet consume cloud credits?

POSTED BY: Douglas Kubler

Hi Chad, Thanks for the tip. It does bring be a step closer but the problem remains. The problem is that VBA code is quite different especially for the `POST´ command. Cheers, Wouter

I'm not familiar with VBA, so I can't provide much guidance for you. I suspect that you could make it work -- using URLFetch with appropriate options in mathematica does return results from deployed FormFunctions.

There is support for VBA in EmbedCode, but so far only for APIFunctions. For example, this returns VB code:

EmbedCode[APIFunction[{"x" -> "Image"}, Blur[#x] &], "VisualBasic"]

I have not yet tested the resulting function (though I plan to do this soon) to ensure that I can send image files through VB.

POSTED BY: Chad Knutson

Dear All, Thank you very much for these pieces of code. They are very helpful! One extra question though: How do I upload a (text) file in VBA using the POST method for instance using the FormFunction as a cloud object.

I've tried a lot of stuff, the form works in my browser (ie it can upload a text file, But when I POST the file in VBA (excel) I get my original form back instead of some response the function should give.

Thanks very much in advance for any assistance!

PS: is it correct there is no support for VBA in the EmbedCode function?

Best regards,

Wouter

Dear Rodrigo, macros are potentially dangerous and may cause harm to user computers. While we are sure your file is fine, we need to be generally cautious to protect Community members from potential harm. Officially we cannot support this format but you perhaps could offer a link to a Dropbox or a similar service.

POSTED BY: Moderation Team

Nice question. Here is a simple implementation on Excel.

Let's create some Cloud API using Wolfram Cloud (the same can be done from desktop version 10):

CloudDeploy[APIFunction[{"x" -> "Integer"}, Prime[#x ] &],Permissions->"Public"]

Save your Cloud Link, something like: "https://www.wolframcloud.com/objects/fc7dae07-d9a0-454f-a8d6-ab12bdbab2b3", to use in your VBA code.

Open Excel, go to VBA (ALT+F11), then create a new Module in your current file.

enter image description here

Then select your module, and create a function to call the information from your brand new Cloud Object as:

Function wolframPrime(x)

    Dim strURL As String
    strURL = "https://www.wolframcloud.com/objects/YourCloudLinkHere"&"?x=" & CStr(x)
    Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "GET", strURL, False
        http.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT" 'prevent cached data
        http.Send
        wolframPrime = 1 * http.responseText
    Set http = Nothing

End Function

Now you can test your new function in Excel:

enter image description here

And get:

enter image description here

Don't forget that each time you use this function, you are consuming your Cloud Credit.

Very cool no?

PS: Unfortunately, the Community do not accept attachments in XLSM format. I created a example file with the embed code. Maybe moderators can change that?

POSTED BY: Rodrigo Murta
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract