Get movie details from OMDB

getMovies.js

function updateMovieDetails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:A"); // Assumes titles start from A2
  var values = range.getValues();
  
  var apiKey = "XXXXXXXX"; // your API key goes here
  var baseUrl = "http://www.omdbapi.com/?apikey=" + apiKey + "&t=";
  
  for (var i = 0; i < values.length; i++) {
    var title = values[i][0];
    
    // Handle "comma the" format (e.g., "Cable Guy, The" to "The Cable Guy")
    title = title.replace(/, The$/, "").replace(/^(.+), The$/, "The $1");

    if (title) {
      var url = baseUrl + encodeURIComponent(title);
      var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
      var content = response.getContentText();

      // Check if the response is valid JSON
      try {
        var data = JSON.parse(content);

        if (data && data.Response === "True") {
          var parentalRating = data.Rated; // Get the parental rating
          var genre = data.Genre; // Get the genre
          var actors = data.Actors; // Get the star actors
          var plot = data.Plot; // Get the movie plot/synopsis
          var poster = data.Poster; // Get the poster URL
          var releaseYear = data.Year; // Get the release year

          sheet.getRange(i + 2, 3).setValue(parentalRating); // Puts the parental rating in column C
          sheet.getRange(i + 2, 4).setValue(genre); // Puts the genre in column D
          sheet.getRange(i + 2, 5).setValue(actors); // Puts the actors in column E
          sheet.getRange(i + 2, 6).setValue(plot); // Puts the plot in column F
          if (poster !== "N/A") {
            sheet.getRange(i + 2, 7).setFormula('=IMAGE("' + poster + '")'); // Puts the actual image in column G
          } else {
            sheet.getRange(i + 2, 7).setValue("No Image");
          }

          // Generate a random 13-digit number
          var randomNumber = Math.floor(Math.random() * 1e13).toString().padStart(13, '0');
          sheet.getRange(i + 2, 8).setValue(randomNumber); // Puts the random number in column H

          // Select a random rent code from "X", "NR", "W"
          var rentCodes = ["X", "NR", "W"];
          var randomRentCode = rentCodes[Math.floor(Math.random() * rentCodes.length)];
          sheet.getRange(i + 2, 9).setValue(randomRentCode); // Puts the rent code in column I
          
          // Add release year in column J
          sheet.getRange(i + 2, 10).setValue(releaseYear); // Puts the release year in column J
        } else {
          sheet.getRange(i + 2, 3).setValue("N/A");
          sheet.getRange(i + 2, 4).setValue("N/A");
          sheet.getRange(i + 2, 5).setValue("N/A");
          sheet.getRange(i + 2, 6).setValue("N/A");
          sheet.getRange(i + 2, 7).setValue("N/A");
          sheet.getRange(i + 2, 8).setValue("N/A");
          sheet.getRange(i + 2, 9).setValue("N/A");
          sheet.getRange(i + 2, 10).setValue("N/A");
        }
      } catch (e) {
        Logger.log("Failed to parse response for " + title + ": " + content);
        sheet.getRange(i + 2, 3).setValue("Error");
        sheet.getRange(i + 2, 4).setValue("Error");
        sheet.getRange(i + 2, 5).setValue("Error");
        sheet.getRange(i + 2, 6).setValue("Error");
        sheet.getRange(i + 2, 7).setValue("Error");
        sheet.getRange(i + 2, 8).setValue("Error");
        sheet.getRange(i + 2, 9).setValue("Error");
        sheet.getRange(i + 2, 10).setValue("Error");
      }
    }
  }
}