Rest – jSon – Excel in one line of code
My recent post on how to Excel-jSon-Excel in one line of code, seems to have generated a lot of pageviews, so clearly there are a lot of you who are interested in excel-json-conversion.
It occurred to me though, that this post was kind of specialized, meaning that the data is expected to be in a table format – essentially an image of the Excel sheet.
Normally, the reason for jSon -> Excel conversion is because you need to deal with the results from a web service, each of which will have its own data structure, which isn’t generally a two dimensional structure. For that type of problem, it is better to use the rest to excel library which I covered in this post.
In the spirit of Excel-jSon-Excel in one line of code, here is Rest-jSon-Excel in one line of code, using the IMDB database to populate details in an Excel table with details about a list of movies.
Public Sub testIMDBoneline() Dim cr As New cRest restQuery "imdb", , , "title", _ "http://www.imdbapi.com/?tomatoes=true&t=", , False End Sub
Which will fill in a worksheet with selected details (you select by simply adding column names that match some of the fieldnames in the rest query response) about films, given their titles in column A, as below
To try this out you can download the cDataSet workbook at the ramblings site, or read more about the cRest Excel library here.
I have now added the IMBD query to the library, and its nicer calling method is simply this
Public Sub testIMDB() generalDataSetQuery "imdb", "imdb by title", "title" End Sub
If anyone has any libraries they have added to Excel Rest library, or that you would like me to add, please leave a comment or contact me on our forum.
If you are interested in the google apps script version of this try Ramblings.mcpher.com For how…