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.

Page Content hide
1 Google Apps and Rest
2 Implementation
3 cBrowser Class
4 Next Steps

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

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.