8.6.1: Calling APIs from spreadsheet formulas (no programming)
The simplest way to use a web API from an spreadsheet consist in using the functions HYPERLINK and WEBSERVICE to either create a link to access the information or extract the information itself to further process it.
A minor issue is that the function WEBSERVICE was only recently added to the spreadsheet editors. It is only available from Microsoft Excel 2013 and LibreOffice Calc 4.2. If compatibility with previous versions is required, a Basic based solution will have to be used.
An example file called M8_Example1_Spreadsheet.xlsx is provided in https://drive.google.com/open?id=0B5ln9gFZnHD4V25QMUUzM2Foanc.
8.6.2: Calling APIs from VBA (Visual Basic for Applications), the macro language in Microsoft Office
A web API can also be called from VBA by using several techniques. The two of the simplest options are:
●Calling the worksheet function WEBSERVICE from VBA: . Note this will work only for Microsoft Excel and will be equivalent to the solution just discussed.
●Using an OLE HTTP Client: or . This solution will also work on others flavors of VBA and is compatible with previous editions of Excel.
An example file using the second of this options, M8_Example2_VBA.xlsm is available at https://drive.google.com/open?id=0B5ln9gFZnHD4V25QMUUzM2Foanc.
8.6.3: Calling APIs from LibreOffice/OpenOffice Basic
Similarly, LibreOffice/OpenOffice Basic also offer different ways to query a web API. The simplest option consist in opening a file pipe from an URL by using the Open instruction.
An example is presented in M8_Example3_LOBasic.ods, which can be found at https://drive.google.com/open?id=0B5ln9gFZnHD4V25QMUUzM2Foanc.
Other options like accessing the WEBSERVICE function through the UNO (Universal Network Object) interface or using other programming languages like Python are also possible but won’t be considered here.
XMLHttpRequest is the main interface in Ajax programming. An explanation of this interface can be found at https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest.
An example of querying a web service with the XMLHttpRequest method, M8_Example4_JS_xmlHttpRequest.html, is available
The main limitation of this technology is the same-origin policy enforced by the browsers for security reasons.
M8_Example5_JS_JSONP.html, available at https://drive.google.com/open?id=0B5ln9gFZnHD4V25QMUUzM2Foanc contains an example of this technology, which is required when calling Wikipedia web API.
We have created a tutorial for you to work through to learn how to access chemical information from Google spreadsheets - gChem. It is available at http://oruopennotebookscience.wikispaces.com/gChem.