Custom scripts for querying data from Google Books and writing it to a spreadsheet

bookshelf.gs

function getBookInfo(bookTitle) {
  var scriptProperties = PropertiesService.getScriptProperties();
  // Put your api key from google books into script properties with key "GOOGLE_BOOKS_API_KEY"
  var apiKey = scriptProperties.getProperty('GOOGLE_BOOKS_API_KEY');
  var url = 'https://www.googleapis.com/books/v1/volumes?country=US&q=intitle:' + encodeURIComponent(bookTitle) + '&key=' + apiKey;
  var response = UrlFetchApp.fetch(url);
  
  var json = response.getContentText();
  var data = JSON.parse(json);
  if (data.totalItems > 0) {
    for (var i = 0; i < data.items.length; i++) { 
      var bookInfo = data.items[i].volumeInfo;
      var categories = bookInfo.hasOwnProperty('categories');
      var authors = bookInfo.hasOwnProperty('authors');
      var industryIdentifiers = bookInfo.hasOwnProperty('industryIdentifiers');
      if (categories && authors && industryIdentifiers) {
        return bookInfo;
      }
    }
    throw new Error(`Could not find author and genre for ${bookTitle}`, data)
  } else {
    throw new Error(`Could not find volumeInfo for ${bookTitle}`, data)
  }
}

function getISBN(bookInfo) {
  var isbnInfo = bookInfo.industryIdentifiers;
  var isbn = "";
  for (var i = 0; i < isbnInfo.length; i++) {
    if (isbnInfo[i].type === "ISBN_13") {
      isbn = isbnInfo[i].identifier;
      break;
    } else if (isbnInfo[i].type === "ISBN_10") {
      isbn = isbnInfo[i].identifier;
    }
  }
  return isbn;
}

function getAuthor(bookInfo) {
  return bookInfo.authors[0];
}

function getGenre(bookInfo) {
  return bookInfo.categories ? bookInfo.categories[0] : "No genre";
}

function getPublishYear(bookInfo) {
  return bookInfo.publishedDate.split("-")[0];
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Bookshelf Tools')
    .addItem('Populate current row', 'populateCurrentRow')
    .addToUi();
}

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() == 'genre') {
    sheet.getRange(1, 1, sheet.getLastRow(), 1).sort(1)
  }
}

function populateCurrentRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRow = sheet.getActiveRange().getRow();
  var values = sheet.getRange(activeRow, 1, 1, 2).getValues();
  var title = values[0][0]
  var subtitle = values[0][1]
  var bookTitle = subtitle == "" ? title : `${title}: ${subtitle}`
  var bookInfo = getBookInfo(bookTitle);
  var genre = getGenre(bookInfo);
  var isbn = getISBN(bookInfo);
  var author = getAuthor(bookInfo);
  var publishYear = getPublishYear(bookInfo);

  // I've hard coded the ordering of my columns because it's easy. If your spreadsheet
  // looks different, you'll need to update these ranges.
  // isbn is column q
  var genreRange = sheet.getRange(`q${activeRow}`)
  genreRange.setValue(genre);

  // isbn is column d
  var isbnRange = sheet.getRange(`d${activeRow}`)
  isbnRange.setValue(isbn);

  // created is column g
  var date = new Date();
  var dateRange = sheet.getRange(`g${activeRow}`)
  dateRange.setValue(date);

  // author is column c
  var authorRange = sheet.getRange(`c${activeRow}`)
  authorRange.setValue(author);

  // publish year is column i
  var publishYEarRange = sheet.getRange(`i${activeRow}`)
  publishYEarRange.setValue(publishYear);

  SpreadsheetApp.getActiveSpreadsheet().toast(`${bookTitle} ISBN is ${isbn} and genre is ${genre}`)
}