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
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111 Option ExplicitConst 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 datasetSet dSet = New cDataSetWith dSet' create a dataset from the isbn worksheet.populateData wholeSheet("isbn"), , "isbn", , , , TrueIf .Where Is Nothing ThenMsgBox ("No data to process")Else'check we have the isbn column presentIf .HeadingRow.Validate(True, cISBNColumnHeading) Then' if there were any updates then commit themIf processISBN(dSet) > 0 Then.bigCommitEnd IfEnd IfEnd IfEnd WithSet dSet = NothingEnd SubPrivate Function processISBN(ds As cDataSet) As LongDim dSet As cDataSet, cb As cBrowser, dr As cDataRowDim jo As cJobject, job As cJobject, n As LongDim sWire As String' gets a book details by isbn numberSet cb = New cBrowserSet jo = New cJobjectn = 0For Each dr In ds.RowsWith dr.Cell(cISBNColumnHeading)sWire = cb.httpGET(cISBNGoogleBooks & .toString)Set job = jo.deSerialize(sWire)If Not job.isValid ThenMsgBox ("Badly formed jSon returned for ISBN" & .toString & "-" _& sWire)ElseIf Not job.ChildExists("error") Is Nothing ThenMsgBox ("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 ThenMsgBox ("Could find no data for ISBN " _& .toString _& "-" & job.Serialize)ElseIf job.Child("totalItems").Value <> 1 ThenMsgBox ("Multiple entries for " _& .toString _& "-" & job.Child("totalItems").Serialize)Else' fill in this rown = n + rowProcess(dr, job.Child("Items"))End IfEnd WithNext drprocessISBN = nEnd FunctionPrivate Function rowProcess(dr As cDataRow, job As cJobject) As LongDim hc As cCell, n As Long, jo As cJobject, jom As cJobjectn = 0For Each hc In dr.Parent.Headings' any headings that are present in the dataset' and also in the returned json get populatedWith hcIf .toString <> cISBNColumnHeading ThenSet jo = job.find(.toString)If Not jo Is Nothing ThenWith dr.Cell(.Column)' if multiple then include the array separated by commasIf jo.isArrayRoot Then.Value = vbNullStringIf jo.hasChildren ThenFor Each jom In jo.ChildrenIf .toString <> vbNullString Then.Value = .Value & ","End If.Value = .Value & jom.toStringNext jomEnd IfElse.Value = jo.ValueEnd IfEnd Withn = n + 1End IfEnd IfEnd WithNext hcrowProcess = nEnd Function
There is now an article available on this post here https://ramblings.mcpher.com/Home/excelquirks/books