Skip to content

Instantly share code, notes, and snippets.

@boringparty
Last active June 4, 2022 08:12
Show Gist options
  • Save boringparty/1918dc98bbf286711b87768be660a46f to your computer and use it in GitHub Desktop.
Save boringparty/1918dc98bbf286711b87768be660a46f to your computer and use it in GitHub Desktop.
TMDB Script w/ Google Apps Script

I have this script, which works well for OMDB. However, I want to modify it to also work with TMDB.

Notes:

  • I can sort of cobble things together, but I can't code otherwise.
  • This script checks for an ID (D3:D) or a title (C3:C) and optional year (B3:B)
  • Once all of the input data is entered (see below), boxes in A3:A are checked and those items are processed at once -- essentially in a flash, unlike with IMPORTJSON, which processes one row at a time.
  • I can't provide a demo sheet, since I've got API keys in the mix, but I will provide sample output for absolutely everything, so the post will be long.
  • This is an input sheet for a media dataset (static). Films are searched then the values are copy and pasted as values into their respective sheets. The current version uses IMPORTJSON and pulls from OMDB, TMDB, TVMaze, and also scrapes some basics from Letterboxd. IMPORTSON is very slow compared to the script below.

Input Sample

The input layout looks like this

x (A) Year (B) Title (C) IMDB ID (D)
tt0077416
1999 Fight Club
tt0047478
tt0110912
tt0108052
tt0055233
tt0099685
tt0111161
tt0468569

Current IMDB Script I'd like to convert

Notes:

  • The OMDB API has a private server for Patreon subscribers (e.g. private.omdbapi.com vs omdbapi.com.) I won't need this variable with TMDB.
  • The script below checks B3:D, but for TMDB, it will only be referencing AL3:AL (e.g. =ARRAYFORMULA(IF(A3:A=FALSE,,TMDB(AL3:AL))))
  • I've included a JSON output from TMDB after the script

The TMDB URL is structured like this. I'll be searching TMDB by IMDB ID and nothing else.

https://api.themoviedb.org/3/find/${id}?api_key=${tmdbKey}&external_source=imdb_id

The items I'd like to return are:

  • movie_results/id
  • movie_results/original_title
  • movie_results/title
  • movie_results/vote_average
  • movie_results/overview

Here is the script that a buddy wrote for me.

/**
* Returns omdb information
*
* @param {A2:C} data
* @param {"public"|"private"} type
* @return {array} range of information from API
* @customfunction
*/
function OMDB(data, type = 'private') {
  if (data[0].length != 3) {
    throw new Error('No valid range of data is given')
  }

  let key

  if (type == 'private') {
    type = 'private.'
    key = `&apikey=${omdbKey}`
  }
  
  var omdbKey = 'abcd1234';
  const urls = data.map(d => {
    const [year, title, id] = d
    if (id != "") {
      return `https://${type}omdbapi.com/?i=${id}&plot=full&apikey=${omdbKey}`
    } else if (title != "" && year != "") {
      return `https://${type}omdbapi.com/?t=${title}&y=${year}&plot=full&apikey=${omdbKey}`
    } else if (title != "") {
      return `https://${type}omdbapi.com/?t=${title}&plot=full&apikey=${omdbKey}`
    } else {
      return `http://google.com`
    }
  })

  const requests = UrlFetchApp.fetchAll(urls)
  return requests.map(request => {
    try {
      const data = JSON.parse(request.getContentText())
      const { Type, imdbID, Year, Title, imdbRating, Metascore, Plot, Language, Director, Actors, Awards, Runtime, Genre, Writer, Rated } = data
      return [Type, imdbID, Year, Title, imdbRating, Metascore, Plot, Language, Director, Actors, Awards, Runtime, Genre, Writer, Rated]
    } catch (err) {
      return ["", "", "", "", "", "", "", "", "", "", "", "", "", "", ""]
    }
  })
}

Instead of using the columns above for the input, the TMDB script will be using AL3:AL, which is the destination of the OMDB script output.

The URL for the TMDB API call is

https://api.themoviedb.org/3/find/tt0077416?api_key=APIKEYGOESHERE&external_source=imdb_id

The TMDB script will only be using IMDB IDs for the lookup. The OMDB script will output IMDB IDs into AL3:AL, so the future TMDB script will result in a formula, =TMDB(AL3:AL)

TMDB JSON Output

{
   "movie_results":[
      {
         "adult":false,
         "backdrop_path":"/jtRqGkW6ywSyKlnx7k399OW69Ur.jpg",
         "genre_ids":[
            18,
            10752
         ],
         "vote_count":2938,
         "original_language":"en",
         "original_title":"The Deer Hunter",
         "id":11778,
         "title":"The Deer Hunter",
         "video":false,
         "vote_average":8,
         "poster_path":"/bbGtogDZOg09bm42KIpCXUXICkh.jpg",
         "overview":"A group of working-class friends decide to enlist in the Army during the Vietnam War and finds it to be hellish chaos -- not the noble venture they imagined. Before they left, Steven married his pregnant girlfriend -- and Michael and Nick were in love with the same woman. But all three are different men upon their return.",
         "release_date":"1978-03-09",
         "popularity":14.789
      }
   ],
   "person_results":[],
   "tv_results":[],
   "tv_episode_results":[],
   "tv_season_results":[]
}

The output in the sheet with inputs for The Deer Hunter and Seven Samurai would be

ID Original Title Title Vote Avg Overview
11778 The Deer Hunter The Deer Hunter 8.0 A group of working-class friends...
346 七人の侍 Seven Samurai 8.5 A samurai answers a village's request...

For television, it is essentially the same, but instead of movie_results it is tv_results. But I am only concerned about movies right now.

Link to demo sheet without API keys -- you can see the final output in the movie sheet.

Any help is appreciated.

Here is the overall workflow

Link to workflow image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment