Google apps script Rest Library migration

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

Google Docs, as an online service, based on javaScript, and with a whole bunch of web friendly capabilities is much easier to integrate with jSon based web services than Excel. However, my intention here is to create functionality that can co-exist as very similar solutions in both GAS and VBA. The first step in this process was to create a jSon/ heirarchical object (cJobject) which has already been implemented in VBA. With this, i can manage REST queries and responses in the same way in both VBA and GAS. See below for the difference - this is a simple geoCoding query using Yahoo geocoding rest API.

apps script version
function testYahooGeocode() {
    generalDataSetQuery ("geocoding", "yahoo geocode", "input address");
}

VBA version
Public Sub testYahooGeocode()
    generalDataSetQuery "geocoding", "yahoo geocode", "input address"
End Sub

Here's another - an interactive query on the iTunes Database

apps script version
function testItunesMovie() {
    generalQuery ("itunesmovie", "itunes movie", 
        InputBox("Enter your itunes movie search query (eg. artist name)"));
}

VBA version
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

Of course Rest Queries needs a little setup in the rest library (which is itself a cJobject), but this is also pretty straightforward, and similar as you can see from the code fragments below. 

apps script version
  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");

VBA version
        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

In a future version, these will have the option of taking the library from an external source itself via a REST query, which will be the same for either GAS or VBA.


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


Comments