Google Currency API: The Daily REST library entry for Excel and GAScript

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");       
}