Getting data about a book using the ISBN number get it now
In this section we will look at how the Google Books API responds to a request for book details given an ISBN number. Using this data we will complete columns as defined in your sheet, and will use Data Manipulation Classes and Excel JSON conversion. Although this method works, you can also access the Books API and many more through the Rest to Excel library
Google provides a rich and underused API to get data about books. In this example we are going to use a small section of it to solve a specific problem that librarians often have - How to get data about a list of books into an Excel sheet given its ISBN number.
Let's say that we have a table of ISBN numbers and want to fill in various columns with other attributes about the book referred to by that number. A completed table might look something like this.
Our implementation will expect a column named isbn which it will use as the request to google books. Any other columns which match the field names returned by Google will be populated. In the case where there are potentially multiple values (for example authors), each value will be separated by commas. Any columns whose heading does not matching the fields returned by Google books will be ignored and their contents left intact.
This has been implemented in the Module 'isbnExample' in the cDataSet.xlsm sheet and can be downloaded as a fully working example.
The first step, as with most of the examples on this site, is to get the input/output table abstracted from its physical position using Data Manipulation Classes. For getting started with these see How to use cDataSet. Next we check that we have an ISBN column, process the table against the Google Books API, and finally commit the updates.
Const cISBNGoogleBooks = "https://www.googleapis.com/books/v1/volumes?q=isbn:"
Const cISBNColumnHeading = "isbn"
Public Sub isbnExample()
Dim dSet As cDataSet
' get ISBN book data
' load to a dataset
Set dSet = New cDataSet
' create a dataset from the isbn worksheet
.populateData wholeSheet("isbn"), , "isbn", , , , True
If .Where Is Nothing Then
MsgBox ("No data to process")
'check we have the isbn column present
If .HeadingRow.Validate(True, cISBNColumnHeading) Then
' if there were any updates then commit them
If processISBN(dSet) > 0 Then
Set dSet = Nothing
Each row in the sheet is passed off to the API with a query on ISBN number, and will return a jSon format string of everything Google knows about that book. We already have the cBrowser class and the cJobject class from previous articles, so along with cDataSet this is pretty trivial.
Private Function processISBN(ds As cDataSet) As Long
Dim dSet As cDataSet, cb As cBrowser, dr As cDataRow
Dim jo As cJobject, job As cJobject, n As Long
Dim sWire As String
' gets a book details by isbn number
Set cb = New cBrowser
Set jo = New cJobject
n = 0
For Each dr In ds.Rows
sWire = cb.httpGET(cISBNGoogleBooks & .toString)
Set job = jo.deSerialize(sWire)
If Not job.isValid Then
MsgBox ("Badly formed jSon returned for ISBN" & .toString & "-" _
ElseIf Not job.ChildExists("error") Is Nothing Then
MsgBox ("Google books refuses to co-operate for ISBN " _
& .toString _
& "-" & job.Child("error").Serialize)
ElseIf job.ChildExists("Items") Is Nothing _
Or job.ChildExists("totalItems") Is Nothing Then
MsgBox ("Could find no data for ISBN " _
& .toString _
& "-" & job.Serialize)
ElseIf job.Child("totalItems").Value <> 1 Then
MsgBox ("Multiple entries for " _
& .toString _
& "-" & job.Child("totalItems").Serialize)
' fill in this row
n = n + rowProcess(dr, job.Child("Items"))
processISBN = n
For each row that there was an executable response to our Google query, we can check for any matching column name to query response field names, and fill them in, dealing with those multiple columns by separating the multiple values by commas.
Private Function rowProcess(dr As cDataRow, job As cJobject) As Long
Dim hc As cCell, n As Long, jo As cJobject, jom As cJobject
n = 0
For Each hc In dr.Parent.Headings
' any headings that are present in the dataset
' and also in the returned json get populated
If .toString <> cISBNColumnHeading Then
Set jo = job.find(.toString)
If Not jo Is Nothing Then
' if multiple then include the array separated by commas
If jo.isArrayRoot Then
.Value = vbNullString
If jo.hasChildren Then
For Each jom In jo.Children
If .toString <> vbNullString Then
.Value = .Value & ","
.Value = .Value & jom.toString
.Value = jo.Value
n = n + 1
rowProcess = n
There may be a list of these in the API documentation, but you can just enter this example
https://www.googleapis.com/books/v1/volumes?q=isbn:9780470044025 to see the jSon response and you will see the key:value pairs that you could include as column names.
Some example valid column names are in the example in the isbn tab of the cDataSets.xlsm workbook which is downloadable here