Retrieving data from Google Books

There is a simple API to retrieve google books data. There was an interesting question on our forum yesterday asking about how to quickly populate a spreadsheet with book data based on the ISBN number for a school library project.
Using this API and the cDataSet classes it was very straightforward to put together a quick solution to this.
See the jSon data returned by this example to see the kind of information returned by the API. The idea is that given an ISBN number, simply naming additional columns the same as any fields known by the API will cause theses columns to be filled out.
You can download the example (isbnExample tab) in the cDataSets.xlsm package.
The code is below
Option Explicit
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
With dSet
' create a dataset from the isbn worksheet
.populateData wholeSheet("isbn"), , "isbn", , , , True


If .Where Is Nothing Then
MsgBox ("No data to process")
Else
'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
.bigCommit
End If

End If
End If
End With
Set dSet = Nothing


End Sub


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
With dr.Cell(cISBNColumnHeading)
sWire = cb.httpGET(cISBNGoogleBooks & .toString)
Set job = jo.deSerialize(sWire)
If Not job.isValid Then
MsgBox ("Badly formed jSon returned for ISBN" & .toString & "-" _
& sWire)

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)

Else
' fill in this row
n = n + rowProcess(dr, job.Child("Items"))
End If
End With
Next dr
processISBN = n
End Function


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
With hc
If .toString <> cISBNColumnHeading Then
Set jo = job.find(.toString)
If Not jo Is Nothing Then
With dr.Cell(.Column)
' 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 & ","
End If
.Value = .Value & jom.toString
Next jom
End If
Else
.Value = jo.Value
End If
End With
n = n + 1
End If
End If
End With
Next hc
rowProcess = n
End Function

 

 

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

1 Comment

Comments are closed.