A Spreadsheet About Books

I wrote a spreadsheet that gets book data, and I want to share it with you, because it was mildly difficult.

First, I opened Google Sheets.

Google Sheets has a cool feature not many people know about called Google Apps Script built in. It was released in 2011, and it has become a tool I have used off and on to create custom scripts for spreadsheets to do very custom things.

You can look into it's reference here, I am not going to go too deeply into its syntax in this post.

Effectively, it is server side javascript that can write to a spreadsheet. It's pretty neat.

Anyways, I made this so my lovely significant other can catalog books.

So here it is.
VIDEO

And here is the code you would put in Script Editor to make it work.



function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  
  var range = e.range;
  
  if(range.getColumn() == 1 && range.getRow() > 1){
    range.setFontColor("red");
    var ss = e.source;
    var sheet = ss.getSheets()[0];
    
    var book = getBookDetails(range.getValue());
    var title = (book["volumeInfo"]["title"]);
    var link = (book["volumeInfo"]["previewLink"]);
    var subtitle = (book["volumeInfo"]["subtitle"]);
    var categories = (book["volumeInfo"]["categories"]);
    var searchInfo = (book["searchInfo"]["textSnippet"]);
    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"]);
    var desc = (book["volumeInfo"]["description"]);
    var thumb = (book["volumeInfo"]["imageLinks"]["thumbnail"]);
    
    
    var titleRange = sheet.getRange(range.getRow(),range.getColumn()+1);
    var subtitleRange = sheet.getRange(range.getRow(),range.getColumn()+2);
    var authorRange = sheet.getRange(range.getRow(),range.getColumn()+3);
    var pubRange = sheet.getRange(range.getRow(),range.getColumn()+4);
    var pageRange = sheet.getRange(range.getRow(),range.getColumn()+5);
    var descRange = sheet.getRange(range.getRow(),range.getColumn()+6);
    var thumbRange = sheet.getRange(range.getRow(),range.getColumn()+7);
    var categRange = sheet.getRange(range.getRow(),range.getColumn()+8);

    
    range.setFontColor("black");
    titleRange.setFormula("=HYPERLINK(\""+link+"\",\""+title+"\")").setFontColor("black");
    if(subtitle!=undefined){
    subtitleRange.setValue(subtitle).setFontColor("black");
    }else{
      subtitleRange.setValue(searchInfo).setFontColor("black");
    }
    authorRange.setValue(authors.join()).setFontColor("black");
    pubRange.setValue(publisher).setFontColor("black");
    pageRange.setValue(pageCount).setFontColor("black");
    descRange.setValue(desc).setFontColor("black");
    thumbRange.setFormula("=HYPERLINK(\""+thumb+"\",IMAGE(\""+thumb+"\"))");
    categRange.setValue(categories.join()).setFontColor("black");
    
    
  }
}


function getBookDetails(isbn) {
  
  // Query the book database by ISBN code.
  
  var key = Goto https://developers.google.com/apis-explorer/ to get a key;
  
  var url = 'https://www.googleapis.com/books/v1/volumes/?key='
  + key
  +'&country=US'
  +'&q=' + isbn;
  
  if(isbn){
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var results = JSON.parse(response);
  }
  if (results!=undefined) {
    
    // There'll be only 1 book per ISBN
    var book = results.items[0];
    // For debugging
    Logger.log(book);
    return book;
  }else{
   return "Waiting for ISBN" 
  }
  
}

OK, so let's break down some of the code.
getBookDetails(isbn) is the most important part. It goes out and grabs book details from Google's book lookup service. It returns a JSON array that looks like this, which looks pretty impenetrable to most people, but it's actually just structured data. The title is under the section labeled "volumeInfo", and then the title is the value labeled "title", thereby - books["volumeInfo"]["title"] is how you grab the title. Rinse and repeat for the rest of the values you want.

Then, we put it onto the sheet. So I do the lookup on the ISBN on the A row, so I hacked together making it so that the next couple of columns simply have the data dumped into it. Title, Subtitle, Author, Publisher, Pages, Desc, Cover, Categories, seems reasonable.

Later I will make the code more modular, maybe do a lookup based on the titles of the columns, and make it so the dumps of data are not hard coded, but rather allows for some flexibility. But that is the code and it works so I like it.

Pretty neat, huh? Simple, quick and spreadsheets. Many of the things in life that I love.