How to Import TMDb Data into Google Sheets Using Apps Script: Automate Your Movie Database

Imagine having a movie database that updates itself with the latest top-rated films, providing you with up-to-date information on the best movies. With Google Sheets and Google Apps Script, you can automate this process, making your movie database management a breeze. In this tutorial, we’ll guide you through importing movie data from TMDb (The Movie Database) into Google Sheets using Google Apps Script. Let’s get started!

Prerequisites: Before we dive into the tutorial, here’s what you’ll need:

A Google Account
Access to Google Sheets
A TMDb API Key (Don’t have one? You can get it here)

Securely Storing Your TMDb API Key: One of the key aspects of this tutorial is ensuring the security of your API key. We’ll use Google Apps Script’s Project Properties to store it securely. Here’s how:

Open your Google Sheets document.
Click on “Extensions” in the top menu, and select “Apps Script.”
In the Apps Script editor, click on the “File” menu, then select “Project properties.”
In the “Script Properties” tab, add a new property with the name “TMDB_API_KEY” and paste your TMDb API key as the value.
Click “Save.”

Your API key is now securely stored, and you can access it programmatically in your Google Apps Script.

Fetching TMDb Data: Now, let’s fetch movie data from TMDb using the API. Below is the code snippet to achieve this:

function importTMDbData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "20 Top Rated"; // Name of the sheet where you want to add the data
  var sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    // If the sheet doesn't exist, create it
    sheet = spreadsheet.insertSheet(sheetName);
  }
  
  // Retrieve the API key from project properties
  var apiKey = PropertiesService.getScriptProperties().getProperty('TMDB_API_KEY');

  if (!apiKey) {
    sheet.getRange("A1").setValue("Error: API Key not found.");
    return;
  }

  var url = 'https://api.themoviedb.org/3/movie/top_rated?api_key=' + apiKey + '&language=en-US&page=1&per_page=100';

  var headers = {
    'Accept': 'application/json'
  };

  var options = {
    'method': 'get',
    'headers': headers
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response.getContentText());

    // Check if the 'results' array exists
    if (data.results && data.results.length > 0) {
      // Clear any previous data in the sheet
      sheet.clear();
      
      // Write headers
      sheet.getRange("A1").setValue("Title");
      sheet.getRange("B1").setValue("Overview");
      sheet.getRange("C1").setValue("Poster Path");
      sheet.getRange("D1").setValue("Vote Average");
      
      // Loop through the results and write data to the sheet
      for (var i = 0; i < data.results.length; i++) {
        var result = data.results[i];
        var row = i + 2; // Start from the second row
        
        sheet.getRange("A" + row).setValue(result.title);
        sheet.getRange("B" + row).setValue(result.overview);
        sheet.getRange("C" + row).setValue("https://image.tmdb.org/t/p/original" + result.poster_path);
        sheet.getRange("D" + row).setValue(result.vote_average);
      }
    } else {
      sheet.getRange("A1").setValue("No results found.");
    }
  } catch (e) {
    sheet.getRange("A1").setValue("Error: " + e.toString());
  }
}

function importTMDbData() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var sheetName = "20 Top Rated"; // Name of the sheet where you want to add the data

var sheet = spreadsheet.getSheetByName(sheetName);

if (!sheet) {

// If the sheet doesn't exist, create it

sheet = spreadsheet.insertSheet(sheetName);

}

// Retrieve the API key from project properties

var apiKey = PropertiesService.getScriptProperties().getProperty('TMDB_API_KEY');

if (!apiKey) {

sheet.getRange("A1").setValue("Error: API Key not found.");

return;

}

var url = 'https://api.themoviedb.org/3/movie/top_rated?api_key=' + apiKey + '&language=en-US&page=1&per_page=100';

var headers = {

'Accept': 'application/json'

};

var options = {

'method': 'get',

'headers': headers

};

try {

var response = UrlFetchApp.fetch(url, options);

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

// Check if the 'results' array exists

if (data.results && data.results.length > 0) {

// Clear any previous data in the sheet

sheet.clear();

// Write headers

sheet.getRange("A1").setValue("Title");

sheet.getRange("B1").setValue("Overview");

sheet.getRange("C1").setValue("Poster Path");

sheet.getRange("D1").setValue("Vote Average");

// Loop through the results and write data to the sheet

for (var i = 0; i < data.results.length; i++) {

var result = data.results[i];

var row = i + 2; // Start from the second row

sheet.getRange("A" + row).setValue(result.title);

sheet.getRange("B" + row).setValue(result.overview);

sheet.getRange("C" + row).setValue("https://image.tmdb.org/t/p/original" + result.poster_path);

sheet.getRange("D" + row).setValue(result.vote_average);

}

} else {

sheet.getRange("A1").setValue("No results found.");

}

} catch (e) {

sheet.getRange("A1").setValue("Error: " + e.toString());

}

Simply copy and paste this code into your Google Apps Script editor. This script fetches data from the TMDb API and populates your Google Sheet with movie details, including title, overview, poster path, and vote average.

Customization and Error Handling: Feel free to customize the code to suit your needs. You can modify the API endpoint to fetch different types of movie data or adjust the formatting of your Google Sheet.

The code also includes error handling to ensure that your script can gracefully handle potential issues, such as API key not found or network errors.

With Google Sheets and Google Apps Script, you can automate the process of importing TMDb data into your spreadsheet. This allows you to keep your movie database up-to-date without manual data entry. Start streamlining your movie management today!

Video Tutorial

Leave a Reply Cancel reply