How to insert the Title Value from an ISBN in Sheets using Scripts, JSON and Books API

Question

First time coding in Google App Scripts, so I apologize for my ignorance in advance.

I saw this great script on Digital Inspiration but I'm stumped on trying to get it to write to the sheet.

I have created a Google Sheet with the following 3 columns of data: title|authors|isbn I have 2 values in C2 and C3 for isbns: 9781471178412 & 9780669397666

In Google App Scripts, I am using the Google Books API to pull the title for the books but keep getting hung up on how to insert the "title" value from Google's API into the Google Sheet for my isbn. It's logging the correct title but when I call it in the function "fillInTheBlanks", it says it's undefined.

Any guidance you can offer this novice is greatly appreciated! Thank you!

function getBookDetails(isbn) {

 var ISBN_COLUMN = 2;

 var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
 var bookValues = dataRange.getValues();

 for(var row = 1; row < bookValues.length; row++){   
     var isbn = bookValues[row][ISBN_COLUMN];

    var url = "https://www.googleapis.com/books/v1/volumes?country=US&q=isbn:" + isbn;

    var response = UrlFetchApp.fetch(url);
    var results = JSON.parse(response);

    if (results.totalItems) {
      // There'll be only 1 book per ISBN
       var book = results.items[0];
       var title = (book["volumeInfo"]["title"]);
       var authors = (book["volumeInfo"]["authors"]);
  
       // For debugging
       Logger.log(book);
       Logger.log(title);
       Logger.log(authors);

     }
  }
}

function fillInTheBlanks(){
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
var bookValues = dataRange.getValues();

for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title1 = bookValues[row][TITLE_COLUMN];
    var author1 = bookValues[row][AUTHOR_COLUMN];

    if(isbn != "" && (title1 === "" || author1 === "") ){
      var runresults = getBookDetails(isbn);
      
     if(title1 === "" && runresults.title){
        bookValues[row][TITLE_COLUMN] = runresults.title; 
      
    }
  }
  
}
dataRange.setValues(bookValues);   
}

Answer 1

Figured it out!! I'm sure my code isn't as elegant as most but I'm happy with being able to get it working for my first foray in JSON/App Scripts.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('GoogleDataPull', 'fillInTheBlanks')
    .addSeparator()
    .addItem('OpenLibraryDataPull', 'fillInTheBlanksOL')
    .addToUi();
}

function getBookDetails(isbn) {
   var url = "https://www.googleapis.com/books/v1/volumes?country=US&q=isbn:" + isbn;
        var response = UrlFetchApp.fetch(url);
      var bookData = JSON.parse(response);
      if (bookData.totalItems) {
      // There'll be only 1 book per ISBN
       var book = bookData.items[0];
       var title = (book["volumeInfo"]["title"]);


       // For debugging
       Logger.log(book);
       Logger.log(title);
       return title;
  }
}
function fillInTheBlanks(){
  var TITLE_COLUMN = 0;
  var ISBN_COLUMN = 1;





var dataRange = SpreadsheetApp.getActiveSpreadsheet();
var sheet = dataRange.getSheets()[0];
// This represents ALL the data
var range = sheet.getDataRange();
var bookValues = range.getValues();


 // var dataRange = SpreadsheetApp.getActiveSpreadsheet()
 //   .getDataRange();
 // var bookValues = dataRange.getValues();
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title1 = bookValues[row][TITLE_COLUMN];
    
    if(isbn != "" && title1 === "" ){
     // var bookData = getBookDetails(isbn);
      
      
      if(title1 === "" && getBookDetails(isbn)){
        bookValues[row][TITLE_COLUMN] = getBookDetails(isbn); 
        range.setValues(bookValues); 
      
      }
  }
  
}
//dataRange.setValues(bookValues);   
}