Parsing Google Books JSON to obtain book info by entering ISBN in EXCEL with VBA

Question

I built a VBA code in order to obtain data from Google Books API by entering the ISBN code of a book that I scanned previously with a bar scanner app by using my mobile phone. With VBA-JSON library everything seems ok, but I have still one object that I cannot import.

The JSON file that I use to check if the code works is this:

https://www.googleapis.com/books/v1/volumes?q=isbn:9780553897852

And this is the code I use now to pick the data:

Public Sub exceljson()

'Error message if active cell is empty
If ActiveCell.Value = 0 Then
     MsgBox "Select cell with ISBN", vbExclamation
     Exit Sub
End If

'Error message if there is no match
On Error GoTo ErrMsg

Dim http As Object, JSON As Object, i As Integer, subitem As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://www.googleapis.com/books/v1/volumes?q=isbn:" & ActiveCell.Value, False
http.send
Set JSON = ParseJson(http.responseText)

i = ActiveCell.Row
For Each Item In JSON("items")
Set subitem = Item("volumeInfo")
Sheets(1).Cells(i, ActiveCell.Column + 1).Value = subitem("publishedDate")
Sheets(1).Cells(i, ActiveCell.Column + 2).Value = subitem("title")
Sheets(1).Cells(i, ActiveCell.Column + 3).Value = subitem("subtitle")
Sheets(1).Cells(i, ActiveCell.Column + 4).Value = subitem("pageCount")

'To obtain ISBN-10 and ISBN-13
j = 5
For Each Child In subitem("industryIdentifiers")
Sheets(1).Cells(i, ActiveCell.Column + j).Value = Child("identifier")
j = j + 1
Next
i = i + 1

'To end with success
Next
MsgBox ("Process complete"), vbInformation
Exit Sub

'To en with an error message
ErrMsg:
MsgBox ("No match obtained"), vbCritical

End Sub

This is the resulted EXCEL sheet I made

Actually I have the fields: year of publication, title, subtitle, pages, ISBN-10, ISBN-13 presented in the subsequent cells of the ActiveCell I write the ISBN. However I have no idea of how to collect data from "authors" array. Is the only field I miss of the data so I really appreciate if you could help me. Thanks in advance.

No, It doesn' work. Due to the fact that there is no curly brackett and no equal inside seems like library does not parse the array. Anyway thanks for the idea. — elAntu, Jan 12 '19 at 01:07

score 0 · Answer 1 · answered Jan 12 '19 at 00:33

0

Looking at the data from the webpage you're retrieving from, "authors" looks like a subitem of Item("volumeInfo"). If "authors" is reading as an array, you could pull it into a cell using

Sheets(1).Cells(i, ActiveCell.Column + 5).Value = Join(subitem("authors"),",")

Or if it's just a string then you could use

Sheets(1).Cells(i, ActiveCell.Column + 5).Value = subitem("authors")

And then make j = 6 to prevent overwriting the output.

answered Jan 12 '19 at 00:33

Tate Garringer

1,509
1
6
9

Not working because library seems to not to parse it as no equal or brackett is found. Or at least is what I think with my short knowledge in VBA programming :-( – elAntu Jan 12 '19 at 00:52
That's not exactly how it works. If you'll notice, the curly brackets in front of `volumeInfo` actually encompass the entire set of data. The brackets more or less establish a hierarchy in the metadata. By your logic, you shouldn't be able to extract `pageCount` either. Why don't you try adding `Debug.Print subitem("authors")` and step through the code. Or, better yet, you could add `subitem` to your watch list, step through the code, and as soon as a value is assigned, go to the watch window, expand it and browse the data. – Tate Garringer Jan 12 '19 at 01:08
I see what you mean @Tate Garringer, but I still don't understand why for example I can obtain items from the object "industryIdentifiers" by searching for Child in "volumeInfo" but this logic doesn't work with "authors". I am new with this so for me is very triky. Sorry. – elAntu Jan 12 '19 at 01:32

QHarr · Accepted Answer · 2019-01-12T08:49:21.873

0

The following shows the correct path to the author. The {} indicates dictionary accessed by key and the [] indicates collection accessed by index. Note that the 0 index base in the image is actually a 1 base for the library used.

Option Explicit

Public Sub GetInfo()
    Const URL As String = "https://www.googleapis.com/books/v1/volumes?q=isbn:9780553897852"
    Dim json As Object
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With
    Debug.Print json("items")(1)("volumeInfo")("authors")(1)
End Sub

You can also view that path here:

With your logic you would need

Option Explicit
Public Sub GetInfo()
    Const URL As String = "https://www.googleapis.com/books/v1/volumes?q=isbn:9780553897852"
    Dim json As Object
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With
    'Debug.Print json("items")(1)("volumeInfo")("authors")(1)
    Dim item As Object, item2 As Variant, subItem As Object, r As Long, c As Long
    For Each item In json("items")
        Set subItem = item("volumeInfo")
        If subItem.Exists("authors") Then
            r = r + 1: c = 1
            For Each item2 In subItem("authors")
                ActiveSheet.Cells(r, c) = item2
                c = c + 1
            Next
        End If
    Next
End Sub

edited Jan 12 '19 at 08:49

answered Jan 12 '19 at 01:05

QHarr

83,427
12
54
101

Might want to add a method to iterate all authors if there is more than 1. Same goes for Items. – Ryan Wildry Jan 12 '19 at 01:12
@RyanWildry The bottom version should account for that, no? It’s late here so my thinking ain’t great. – QHarr Jan 12 '19 at 01:16
oh I see what you mean! – QHarr Jan 12 '19 at 01:17
1

Wow thank you very much @QHarr. I just put: Sheets(1).Cells(i, ActiveCell.Column + 5).Value = json("items")(1)("volumeInfo")("authors")(1) below "pageCount" and it works. I just change j = 6 to avoid overlapping and it works perfectly!! Thank you very much. However I still don't know why this happens jiji – elAntu Jan 12 '19 at 01:18
Thanks to @RyanWildry I have added a line to account for possible multiple authors in the collection under authors. – QHarr Jan 12 '19 at 01:21
Thanks again @QHarr and RyanWildry. Now I was planning to put collected data from several authors in just one row but I don't figure it out yet. I was trying to use the Join function but I think I don't really understand the syntaxis yet. Could you help me? – elAntu Jan 12 '19 at 02:33
Just increment the columns argument of cells and keep the row constant to put multiple authors on one line. – QHarr Jan 12 '19 at 08:47

Parsing Google Books JSON to obtain book info by entering ISBN in EXCEL with VBA

2 Answers2

Linked