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
Get Data from Google Books
What can you learn here? Use Google Books APIConvert jSonTailor columns and output Getting data about a book using the …
Read More
Get Data From Google Docs
There have been a number of changes to Google Docs over the years each of which has provided more ways …
Read More
db access to a variety of databases from Excel
Here’s a VBA app that uses Database abstraction with google apps script via the DataHandler library. You need to download the latest version of …
Read More
Accessing Google Sheets directly from Excel
Here’s a VBA app that uses Database abstraction with google apps script via the DataHandler library. You need to download the latest version of …
Read More
pollHandler app and library
Here’s an app that uses Database abstraction with google apps script via the DataHandler library. It’s made up of these components DataHandler library, plus all of the …
Read More