TMDB Script w/ Google Apps Script

tmdb.md

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