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