Get Data from Google Books

What can you learn here?

Use Google Books API
Convert jSon
Tailor columns and output

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 Books API

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.

Excel Implementation

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.

Where to get the code

This has been implemented in the Module ‘isbnExample’ in the cDataSet.xlsm sheet and can be downloaded as a fully working example.

Abstract the data

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.

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

Get the Books API response

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
        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

Fill Columns with data returned.

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
        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

Fields returned by Google Books API

There may be a list of these in the API documentation, but you can just enter this examplehttps://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

Summary

As usual, Google have an ‘api for that’. In this case we are just scratching the surface of this excellent API. You could very easily use queries other than ISBN number to create all sorts of sophisticated queries. As always I look forward to your feedback, comments, and questions on our forum.
Subpages