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
- 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.
- 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.
