Google currency API
Lately for this spot I’ve been selecting APIs that are out of the ordinary, and which allow me to incrementally improve the capability of the REST library. The wrinkle today is that this API does not return valid a jSon. As discussed in a previous post jSon is a subset of javaScript syntax, but some things that are acceptable in javaScript are not part of the jSon specification. If you have a need to check and string for jSon validity, I recommend jsonlint.com.
The Google Currency API is a query by row API, populating each row in a spreadsheet based on a value in a given column. You just name the columns to match any data you want to retrieve and go. Here are the results of a query with a few random currencies. This example can be found in the cDataSet.xlsm and downloaded from here
Library entry
In common with an older version of the google wire protocol (which I use to get data from Google Docs), this API returns key/value pairs as key: “value” instead of the valid jSon syntax of “key” : “value”, so to deal with this I introduced a new option in the library entry syntax ‘wire’. If set to true, then this kind of response will be accepted and dealt with.
With .add("googlecurrencyconverter") .add "restType", erRestType.erQueryPerRow .add "url", "http://www.google.com/ig/calculator?hl=en&q=1USD=?" .add "results", "" .add "treeSearch", False .add "ignore", vbNullString .add "wire", True End With
Public Sub testGoogleCurrencyConverter() generalDataSetQuery "googlecurrencyconverter", "googlecurrencyconverter", "currency" End Sub
and for Google apps script
function testGoogleCurrencyConverter() { mcpher.generalDataSetQuery ( "googlecurrencyconverter", "googlecurrencyconverter", "currency"); }