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 
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’s the results of 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

And the execution code looks like this

Public Sub testGoogleCurrencyConverter()
    generalDataSetQuery “googlecurrencyconverter”, “googlecurrencyconverter”, “currency”
End Sub

and for Google apps script
function testGoogleCurrencyConverter() {
    mcpher.generalDataSetQuery ( “googlecurrencyconverter”, “googlecurrencyconverter”, “currency”);       
}

The rest library is itself implemented as REST API and can be queried like this.
For more stuff like this, visit the ramblings site or the associated blog. If you have suggestion for particular API, vote for it on google moderator or contact me on our forum

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *