What can you learn here?
- Using cDataSet
- Using cRest
- Using cJobject
Doing unusual things with the rest library get it now
Having dealt with Rest to Excel library, Adding to the rest library, Adhoc Rest Queries and Populating sheets with Rest responses let’s take a look at how do modify the usual behavior of the cRest class
Try it out first ?
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