Getting REST API JSON data into Excel

cRest primer from Excel Liberation

Excel Liberation for details

cRest purposeREST APIs return all kinds of data shapes. Getting that to a 2 dimensional Excel table can be labor intensive

{ "Definition": "rest definition: repose, sleep; '''specifically'''.", "DefinitionSource": "Merriam-Webster", "Heading": "Rest", "AbstractSource": "Wikipedia", "Image": "", "RelatedTopics": [ { "Result": "<a href=\"http://duckduckgo.com/Rest_(music)\">Rest (music)</a>, a pause in a piece of music", "Icon": { "URL": "https://i.duckduckgo.com/i/1dca4003.png", "Height": "", "Width": "" }, "FirstURL": "http://duckduckgo.com/Rest_(music)", "Text": "Rest (music), a pause in a piece of music" }, { "Result": "<a href=\"http://duckduckgo.com/Leisure\">Leisure</a> - Leisure, or free time, is time spent away from business, work, and domestic chores.", "Icon": { "URL": "https://i.duckduckgo.com/i/753cd753.jpg", "Height": "", "Width": "" }, "FirstURL": "http://duckduckgo.com/Leisure",

cRest is a VBA class that can organize most JSON API data an populate an Excel table

Excel Liberation for details

Pre-requisite classescRest uses two important VBA classes; cJobject (for

organizing data of variable structural depth and converting to and from JSON) and cDataSet (for abstracting Excel data)

You can find write ups and primer decks on Excel Liberation.

How to use cJobject How to use cDataSet

You can also find detail on cRest here.

Excel Liberation for details

REST API callsThese are usually made through a base API URL

with arguments to describe the query.

For example

http://api.duckduckgo.com/?format=json&q=rest

The data format is usually JSON or XML. cRest is designed to deal with JSON. Excel has other tools for manipulating XML

Excel Liberation for details

REST Excel LibrarycRest is generally expecting to use a library (although

you can specify many parameters as arguments). This simplifies re-use of APIS and also encourages the establishment of a base of useful APIs

The library entry – (itself a cJobect) – for the duckduckgo API looks like thisWith .add("duckduckgo")

.add "restType", erSingleQuery

.add "url", "http://api.duckduckgo.com/?format=json&q="

.add "results", "relatedtopics"

.add "treeSearch", True

.add "ignore", vbNullString

End With

Excel Liberation for details

Setting up the output sheetGetting data out of the JSON response and into your

sheet is a simple matter of naming the columns the same thing as the data you want. Anything that matches gets populated, everything else is ignored

{ "Definition": "rest definition: repose, sleep; '''specifically'''.", "DefinitionSource": "Merriam-Webster", "Heading": "Rest", "AbstractSource": "Wikipedia", "Image": "", "RelatedTopics": [ { "Result": "<a href=\"http://duckduckgo.com/Rest_(music)\">Rest (music)</a>, a pause in a piece of music", "Icon": { "URL": "https://i.duckduckgo.com/i/1dca4003.png", "Height": "", "Width": "" }, "FirstURL": "http://duckduckgo.com/Rest_(music)", "Text": "Rest (music), a pause in a piece of music" }, { "Result": "<a href=\"http://duckduckgo.com/Leisure\">Leisure</a> - Leisure, or free time, is time spent away from business, work, and domestic chores.", "Icon": { "URL": "https://i.duckduckgo.com/i/753cd753.jpg", "Height": "", "Width": "" }, "FirstURL": "http://duckduckgo.com/Leisure",

Data starts at .add "results", "relatedtopics“

We only want these 2 fields

Excel Liberation for details

restQuery executionThese can generally be written as a very simple one

liner.

Here is a typical procedure to execute an API call and populate the sheet. The arguments are the sheet to put the result in, the rest library entry to use and some query value

Public Sub testDuckDuckGo()

generalQuery("duckduckgo", "duckduckgo", _

InputBox("Enter your duckduckgo query")).tearDown

End Sub

Excel Liberation for details

Specifying Type of queryThe duckduckgo query was this type. .add "restType", erSingleQuery

That means that for a single query, multiple lines of data are returned. There is another type of query erQueryPerRow. In this case, some variable data in each row is used as input to the query.

Excel Liberation for details

Query per rowIn this example we want to use each value in the

‘domain column’ and execute an API call based on it.

The library entry With .add("page rank") .add "restType", erRestType.erQueryPerRow .add "url", "http://prapi.net/pr.php?f=json&url=" .add "results", "" .add "treeSearch", False .add "ignore", "" End With

The caller Public Sub testPageRank() generalDataSetQuery("page rank", "page rank", "domain").tearDownEnd Sub

Excel Liberation for details

More complicated column headersOf course API data is often not two dimensional.

You can use subclass column titles as wellThe data{ "total_pages": 1, "current_page": 1, "total_records": 73, "records": [ { "id": "51c857b60693b9dd1d06ce24", "field_82": "Fashion/Wedding ", "field_38": [ { "id": "519a2ecd09f3ace950c70276", "identifier": "Shopcade" } ], "field_39": [ { "id": "a", "identifier": "john doe" }, { "id": "b", "identifier": "jane doe" } ],

The column titles Field_38.1.idthe id propertyof the first item in the Field_38 object

Field_39..identifierall properties named identifier in the Field_39 array of objects, separated by commas

Excel Liberation for details

PortabilityThe Google Apps Script version of cRest has the

same syntax (allowing for minor javaScript/VBA linguistic differences)

Examples – Google Apps Script

function testDuckDuckGo() {

generalQuery("duckduckgo", "duckduckgo", _

InputBox("Enter your duckduckgo query")).tearDown();

}

There are other ways of doing this in Google Apps Script but using cJobject in GAS allows portability to and from VBA. It also uses Google Caching Infrastructure is used by default so often results will not need an API call at all.

Excel Liberation for details

ExtensibilityEasy to extend with powerful capabilitiesExamples

Get data directly from Google Fusion and populate a work sheet

getDataFromFusion "Fusion", getFusionKey(), "1pvt-tlc5z6Lek8K7vAIpXNUsOjX3qTbIsdXx9Fo“

Create queries using the Google Wire Protocol (as used by google docs)

With .add("googlecurrencyconverter")

.add "restType", erRestType.erQueryPerRow

.add "url", "http://www.google.com/ig/calculator?hl=en&q=1USD=?"

.add "results", ""

.add "treeSearch", False

.add "ignore", vbNullString

.add "wire", True

End With

Access the cJobject containing returned jSon data

for each job in crest.jobject,children

‘ do some special processing

next job

Access the cDataSet just populated

for each row in crest.dset.rows

‘ do some special processing

next row

Excel Liberation for details

Memory recoveryLike many objects with recursive linking, memory will not be

recovered by the VBA garbage collector simply by going out of scope. A teardown is provided, and should be used for efficient memory recovery.

Examples

Public Sub testFqlStream()

Dim fqlQuery As String

‘ get facebook stream data

fqlQuery = "SELECT message,share_count,likes " & _

"FROM stream WHERE CONTAINS('" & _

InputBox("message contains ?") & "')"

generalQuery("fqlStream", "fql", fqlQuery).tearDown

End Sub

Excel Liberation for details

SummaryThese examples show some of the capabilities

of cRest Excel REST library and how to extend Excel to use JSON REST APIs

For more detail, and to download see Excel Liberation