Advanced rest library usage

These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects.

restQuery arguments

restQuery is the main interface into the restLibrary module, although simpler versions with less arguments for completely standard behavior are also available, as per Rest to Excel library
Here are the arguments for all functions in restLibrary

Arguments for restQuery

Argument	Purpose
sName	The worksheet Name that will be populated (and will provide the input data if needed as for an erQueryPerRow type)
sEntry	The given name to this query in the restLibrary. If not supplied, then sRestUrl should contain the URL of an adhoc rest query.
sQuery	The query string to use for an erSingleQuery type.
sQueryColumn	The name of the column that contains the query data for an erQueryPerRow type.
sRestUrl	The Url of an adhoc rest query. Not needed if you supply sEntry to specify an already known library query
sResponseResults	The container name for the results returned from the query. Not needed if you supply sEntry to specify an already known library query
bTreeSearch	Whether to search the tree beyond the first level of sResponseResults to try to match column names. Not needed if you supply sEntry to specify an already known library query.
bPopulate	Whether or not to actually populate the worksheet.
bClearMissing	This will clear cells that are missing from a response in an erQueryPerRow type.
complain	This says whether to complain about errors that are found or to deal with them silently.
queryCanBeBlank	It’s ok for the sQuery argument to be blank
sFix	This is a value containing a jSon string. You’d use if you didnt actually want to make an API call, but already had the string, say in a spreadsheet cell
user	restQuery can do basic http authentication – this is the user name if you are using it
pass	password for basic http authentication
append	whether or not to append data to the existing sheet
stampQuery	You may want to stamp each row with the query used. This is the name of the column to stamp. Often used in conjunction with appending data
appendQuery	You may have an additional query to add to the end of a url. Specify it here
collectionNeeded	By default, cRest will maintain a collection of each cJobject processed in case you need to do further processing. If you dont need it, then setting this to false can use less memory.

Why execute a query without populating the sheet.

In addition to populating a sheet, the cRest.execute method also maintains the cRest.jObjects property, which is a collection of cJobjects representing the response to every query that was executed. This means that you can use this class to do more than just filling sheets. You may want to manipulate the responsese before filling sheets or use them in some other way. Here is an example where no sheets are populated, and the jOBjects property is processed to produce each response’s jSon representation.

<pre>Public Sub testNoPop()
    Dim cr As cRest, jo As cJobject
    Set cr = restQuery("Geocoding", "yahoo geocode", , "input address", , , , False)
    If Not cr Is Nothing Then
        For Each jo In cr.jObjects
            Debug.Print jo.Serialize(True)
        Next jo
    End If
End Sub</pre>

here is a small snippet of the output, cleaned up by the cJobject.Serialize method.

<pre>{
   "_deserialization":{
      "ResultSet":{
         "version":"1.0",
         "Error":"0",
         "ErrorMessage":"No error",
         "Locale":"us_US",
         "Quality":"60",
         "Found":"1",
         "Results":[            {
               "quality":"60",
               "latitude":"57.033726",
               "longitude":"-3.218625",
               "offsetlat":"57.034569",
               "offsetlon":"-3.217470",
               "radius":"1600",
               "name":"",
               "line1":"",
               "line2":"Ballater",
               "line3":"AB35 5TB",
               "line4":"United Kingdom",
               "house":"",
               "street":"",
               "xstreet":"",
               "unittype":"",
               "unit":"",
               "postal":"AB35 5TB",
               "neighborhood":"",
               "city":"Ballater",
               "county":"Grampian",
               "state":"Scotland",
               "country":"United Kingdom",
               "countrycode":"GB",
               "statecode":"SCT",
               "countycode":"",
               "uzip":"AB35 5TB",
               "hash":"",
               "woeid":"26830356",
               "woetype":"11"
            }
          ]
      }
   }
 }
  {
   "_deserialization":{
      "ResultSet":{
         "version":"1.0",
         "Error":"0",</pre>

Summary

Being able to access the cJobjects collection is probably the most useful capability in this section. In the meantime why not join our forum to ensure you get updates when they are available. You can also submit anything you want considered for publication on this site (for example if you add interesting Rest calls to the restLibrary) to our forum.

Now let’s look at Using cRest directly