Random blog posts

Retrieving data from Google Books

August 31, 2011 Json 1

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

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

brucemcp says:

September 1, 2011 at 9:44 am

There is now an article available on this post here https://ramblings.mcpher.com/Home/excelquirks/books

Reply

Desktop Liberation

The definitive resource for Google Apps Script and Microsoft Office automation

Retrieving data from Google Books

1 Comment

Leave a Reply Cancel reply