The excel- rest library is a way to easily populate Excel worksheets with data returned from Rest APIS. Here I will cover the migration from Excel to Google Apps Script. You can get the shared restLibrary Google Workbook here. To access the scripts, go to file and make a copy.
Google Apps and Rest
Apps Script Version | VBA Version |
---|---|
function testYahooGeocode() { generalDataSetQuery ("geocoding", "yahoo geocode", "input address"); } | Public Sub testYahooGeocode() generalDataSetQuery "geocoding", "yahoo geocode", "input address" End Sub |
Here’s another – an interactive query on the iTunes Database
Apps Script Version | VBA Version |
---|---|
function testItunesMovie() { generalQuery ("itunesmovie", "itunes movie", InputBox("Enter your itunes movie search query (eg. artist name)")); } | Public Sub testItunesMovie() generalQuery "itunesmovie", "itunes movie", _ InputBox(prompt:="Enter your itunes movie search query (eg. artist name)", _ Title:="itunes API query:results to the itunesmovie worksheet") End Sub |
Apps Script Version | VBA Version |
---|---|
f w = cj.add("yahoo geocode"); w.add ("restType", ERRESTTYPE.erQueryPerRow); w.add ("url", "http://where.yahooapis.com/geocode?flags=J&location="); w.add ("results", "Resultset.Results"); w.add ("treeSearch", true); w.add ("ignore"); w = cj.add("itunes movie"); w.add ("restType", ERRESTTYPE.erSingleQuery); w.add ("url", "http://itunes.apple.com/search?entity=movie&media=movie&term="); w.add ("results", "results"); w.add ("treeSearch", false); w.add ("ignore"); | With .add("yahoo geocode") .add "restType", erQueryPerRow .add "url", "http://where.yahooapis.com/geocode?flags=J&location=" .add "results", "resultset.results" .add "treeSearch", True .add "ignore", vbNullString End With With .add("itunes movie") .add "restType", erSingleQuery .add "url", "http://itunes.apple.com/search?entity=movie&media=movie&term=" .add "results", "results" .add "treeSearch", False .add "ignore", vbNullString End With |
Implementation
Since this has been implemented in the same way as the rest-excel library, you can read about how it works there. The GAS specific version can be accessed in the shared restLibrary Google Workbook here.
This makes use of previously converted libraries such as Data manipulation Classes in Google Apps Script and cJobject Class in Google Apps Script as well as the common libraries mentioned in From VBA to Google Apps Script.
cBrowser Class
The Rest to Excel library makes use of the cBrowser class, first introduced in Cookies and Excel. Much of that is irrelevant in the GAS version, which has a bare bones GAS version as below. Note that I have not yet implemented Basic HTTP Authentication from VBA in GAS, but I will take care of that shortly.
// translation of VBA cbrowser class. much of this is not relevant in gas var cBrowser = function(){ return this; }; cBrowser.prototype.httpGET = function ( fn,optAuthUser,optAuthPass) { authUser = fixOptional ( optAuthUser, ''); authPass = fixOptional ( optAuthPass, ''); this.xHtml = fn; this.xResult = UrlFetchApp.fetch(this.xHtml); return this.xResult.getContentText(); }
Next Steps
Take a look at From VBA to Google Apps Script to follow along with other conversion projects. In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.
Here’s an example of mashup using rest library converted from VBA to GAS.