Mitigating for changes in REST API rules and formats

In Rest-Excel library, I have examples of a large number of REST APIs which get data from a wide range of data sources. I have a similar library for Google Apps Script.

I have to clear out the examples (downloadable cDataSet.xlsm from Excel Liberation site) from time to time because they don’t work any more – they have changed format, have become paid for, now need developer keys or authentication or otherwise have disappeared off the face of the earth.

Using a library

Abstracting the API description into a library has helped to keep the number of changes needed to a minimum (when I know they have changed). However, new visitors download the examples, find they don’t work, and leave it at that. Other times, those that have built the examples into their applications silently adapt them to deal with changes as they occur.

Examples of things that have changed like this over the past few months of this are twitter, facebook, tweet sentiments, crunchbase as well as a host of other less well known ones.

Google Currency calculator

Just yesterday I noticed that the Google Currency API disappeared – that was the one at http://www.google.com/ig/calculator?hl=en&q=1USD=? . I don’t recall seeing any announcements, tweets or anything else about it. It just – disappeared.

Rate Exchange

Luckily I found another one, http://rate-exchange.appspot.com/currency?from=USD&to=GBP, which I actually like better. Like everything else in Rest-Excel library changing the API provider is fairly invisible to apps that use it since the description is in the library,

 it goes from this

        With .add(“currency converter”)
            .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

to this

       With .add(“currency converter”)
            .add “restType”, erRestType.erQueryPerRow
            .add “url”, “http://rate-exchange.appspot.com/currency?from=USD&to=”
            .add “results”, “”
            .add “treeSearch”, False
            .add “ignore”, vbNullString
        End With

Cloud based lists

I also use the same API in Lists and validation in the cloud, where custom functions are available in both Excel and Google Apps Script to lookup currency conversion rates. This is even more invisible, since the functions don’t need to change at all, even though the API behind the scenes has changed provider. The below will still look up the exchange rate for the given currency in both GAS and Excel regardless of how they are found.

=blisterLookup(“blister.currencies”,A1,”ISO”,”USDrate”)

Lessons

The lesson here then is that we should simply expect APIS to change. Abstracting away the ‘getting data’ part from the ‘using data’ part is obvious, but often ignored, and is especially true if you are building stuff for others to use  …..oh and  if you do find any API examples on Excel Liberation that don’t work any more (or would like to know how to add new ones)  – please tell us on our forum

For more stuff like this see Excel Liberation

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/plusone.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

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