How to extract object properties from a JSON response

Question

I have a large amount of books and I want to build a database to manage them. My idea is to scan all their barcodes, put them in Google Sheets then use OpenLibrary API to retrieve the corresponding meta data (title, authors etc.) to avoid typing it all in.

The API structure is simple enough and I'm able to retrieve the information by passing over the barcode (ISBN number):

var url = "https://openlibrary.org/api/books?bibkeys=ISBN:" + numisbn +"&jscmd=data&format=json";
var response = UrlFetchApp.fetch(url);

But the JSON structure of the response gives me trouble:

{
    "ISBN:9782505061373": {
        "url": "https://openlibrary.org/books/OL32231855M/Undertaker_-_Tome_1_-_Le_Mangeur_d'or",
        "key": "/books/OL32231855M",
        "title": "Undertaker - Tome 1 - Le Mangeur d'or",
        "authors": [
            {
                "url": "https://openlibrary.org/authors/OL8653266A/Xavier_Dorison",
                "name": "Xavier Dorison"
            },
            {
                "url": "https://openlibrary.org/authors/OL9159488A/Ralph_Meyer",
                "name": "Ralph Meyer"
            }
        ],
        "number_of_pages": 56,
        "identifiers": {
            "isbn_10": [
                "2505061378"
            ],
            "isbn_13": [
                "9782505061373"
            ],
            "openlibrary": [
                "OL32231855M"
            ]
        },
        "publishers": [
            {
                "name": "DARGAUD"
            }
        ],
        "publish_date": "Jan 30, 2015",
        "notes": "Source title: Undertaker - Tome 1 - Le Mangeur d'or (Undertaker, 1) (French Edition)",
        "cover": {
            "small": "https://covers.openlibrary.org/b/id/10867977-S.jpg",
            "medium": "https://covers.openlibrary.org/b/id/10867977-M.jpg",
            "large": "https://covers.openlibrary.org/b/id/10867977-L.jpg"
        }
    }
}

What would be the right syntax to retrieve the properties of the first object without (ISBN:...) without having to hard code it, since it's changing dynamically for each query.

If I'm retrieving the title, for instance, I'll use

results["ISBN:9782505061373"]["title"]; 

But what can I do to automatically retrieve that "ISBN:..." part of the object?

My goal is then to write the values of the selected properties as an array in the sheet.

I'm adapting the code from a snippet I found online that was written for the Google Books API. But I find that many of my books are missing from Google Books and I'd like to use OpenLibrary instead.

The full snippet, partially adapted is here below. I'm trying to adapt the portion with the properties to match what OpenLibrary provides and what is relevant to me. If you have any other suggestions, I'm all ears :-)

Thanks a lot for your support.

s = SpreadsheetApp.getActiveSheet();

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('ISBN')
      .addItem('Chercher par ISBN', 'getBookDetails')
      .addToUi();
}

function getBookDetails(numisbn) {
  
  // Query the book database by ISBN code.
  activeCell = s.getActiveCell();
  value = activeCell.getValue();
  numisbn = numisbn || value.toString(); 
 
  // Not a valid ISBN if not 13 or 10 digits long.
  if(numisbn.match(/(\d{13}|\d{10})/) == null){
    throw new Error( "Not a valid ISBN: " + numisbn);
  }
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:" + numisbn +"&jscmd=data&format=json";
  var results = UrlFetchApp.fetch(url);
  
  if (results.totalItems) {

    // There'll be only 1 book per ISBN
    var book = results.items[0];

    var title = (book["volumeInfo"]["title"]);
    var subtitle = (book["volumeInfo"]["subtitle"]);
    var authors = (book["volumeInfo"]["authors"]);
    var printType = (book["volumeInfo"]["printType"]);
    var pageCount = (book["volumeInfo"]["pageCount"]);
    var publisher = (book["volumeInfo"]["publisher"]);
    var publishedDate = (book["volumeInfo"]["publishedDate"]);
    var webReaderLink = (book["accessInfo"]["webReaderLink"]);
  }
    s.getRange(activeCell.getRow(), activeCell.getColumn() + 1, 1, results[0].length).setValues(results);
}

Answer 1

Here is a solution for retrieving all datas in the json. Hope that it will fit your needs

let resultat = []; 

/**
 * Permet d'explorer un json.
 * Retourne attribut ou objet / valeur 
 *
 * @param {texte ou cellule} url url du site hébergeant le json ou cellule de la feuille contenant le json
 * @customfunction
 */
function getISBNInfos(url) {
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  resultat.push(['niveau','champs','valeurs']);
  getData(1,eval(data),'data')
  return resultat
}
function getData(niv,obj,id) {
  const regex = new RegExp('[^0-9]+');
  for (let p in obj) {
    var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        resultat.push([niv,  p, obj[p]]);
      }
      if (typeof obj[p] == 'object') {
        if (obj[p].length){
          resultat.push([niv,  p + '[0-' +(obj[p].length-1)+ ']', '']);
        }else{
          resultat.push([niv,  p, '']);
        }
        niv+=1;
        getData(niv, obj[p], newid );
        niv-=1
      }
    }
  }
}

Take a copy ... https://docs.google.com/spreadsheets/d/1I8wog8ZSCQW1oTSsUCBnt1D2vZZHGb3hBGb5Yz2k42Y/copy

Answer 2

I will assume we are starting with these lines:

var url = "https://openlibrary.org/api/books?bibkeys=ISBN:" + numisbn +"&jscmd=data&format=json";
var response = UrlFetchApp.fetch(url);

And I will assume that the JSON you receive looks like this:

{
    "ISBN:9782505061373": {
        "url": "https://openlibrary.org/books/OL32231855M/Undertaker_-_Tome_1_-_Le_Mangeur_d'or",
        "key": "/books/OL32231855M",
        "title": "Undertaker - Tome 1 - Le Mangeur d'or",
        "authors": [{
            "url": "https://openlibrary.org/authors/OL8653266A/Xavier_Dorison",
            "name": "Xavier Dorison"
        }, {
            "url": "https://openlibrary.org/authors/OL9159488A/Ralph_Meyer",
            "name": "Ralph Meyer"
        }],
        "number_of_pages": 56,
        "identifiers": {
            "isbn_10": ["2505061378"],
            "isbn_13": ["9782505061373"],
            "openlibrary": ["OL32231855M"]
        },
        "publishers": [{
            "name": "DARGAUD"
        }],
        "publish_date": "Jan 30, 2015",
        "notes": "Source title: Undertaker - Tome 1 - Le Mangeur d'or (Undertaker, 1) (French Edition)",
        "cover": {
            "small": "https://covers.openlibrary.org/b/id/10867977-S.jpg",
            "medium": "https://covers.openlibrary.org/b/id/10867977-M.jpg",
            "large": "https://covers.openlibrary.org/b/id/10867977-L.jpg"
        }
    }
}

From there, you can access the JSON payload of the HTTP response object as follows:

var results = JSON.parse(response.getContentText());

The above line reads the response into a string containing the JSON data, and then parses that into a JavaScript object.

You need to get the name used for the key of embedded object "ISBN:9782505061373". To do this, use the following:

let isbnKey = Object.keys(results)[0];

The Object.keys() function gets all the provided keys - but in our case, we assume there is always exactly one such key.

Now we have isbnKey containing the string ISBN:9782505061373.

We can use this as follows to drill down into the details:

let isbnUrl = results[isbnKey].url;
let title = results[isbnKey].title;

... and so on.

Answer 3

Solution:

Since you have a variable for the ISBN that you pass to the API, you can use the same variable to compute the property name and use it as reference:

results["ISBN:" + numisbn]["title"];

This is possible since ES6 JavaScript.

Reference:

Computed Property Names