I am using the TMDB api to fetch data about a movie into a Google Sheet. This Google Sheet was reworked from Reddit user 6745408’s “MediaSheet 3.0”. The sheet uses a Javascript-based script. I’ve been able to extract values for keys that weren’t in the original script/sheet by following the patterns/code used in the original script. However, I don’t know how to do the following:
There are multiple arrays nested within the “results” key. Each array nested under “result” is a video associated with this movie title. What I want returned into the Google Sheet is the url ending which is (“key”). However I only want one video url / “key” returned into the Google Sheet, I want the script to filter the “results” array for videos that have the values (“site”: “YouTube”) and also (“type”: “trailer) OR (“type”: “teaser”) and then from there, return the first result from the filter and return the (“key”) of that video.
This is the JSON data I am trying to parse from the TMDB api:
{
"id": 290098,
"results": [
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "In the Projection Booth - Park Chan-wook, director of The Handmaiden (contains spoilers)",
"key": "P8g8QJk96M4",
"site": "YouTube",
"size": 1080,
"type": "Featurette",
"official": true,
"published_at": "2017-04-14T08:30:01.000Z",
"id": "65be6283902012012fc9a5a7"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "UK Promo",
"key": "xX7HsdfIYGw",
"site": "YouTube",
"size": 1080,
"type": "Teaser",
"official": true,
"published_at": "2017-03-31T16:51:17.000Z",
"id": "65be628efc6538017cea4ee3"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "UK Trailer",
"key": "wYsdzNIcJNc",
"site": "YouTube",
"size": 1080,
"type": "Trailer",
"official": true,
"published_at": "2017-03-09T12:54:54.000Z",
"id": "65be625ea7e363018454330d"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "Extended Preview",
"key": "5bOWrDriBno",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2017-01-17T17:49:04.000Z",
"id": "65be613143999b0184c5d86e"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "Dress Up (Movie Clip)",
"key": "y43Y20jdctw",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2016-10-24T21:00:04.000Z",
"id": "65be60b512c604017c0096e5"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "The House (Movie Clip)",
"key": "jUP3cPWQBh4",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2016-10-24T17:44:18.000Z",
"id": "65be60bca7e36301b7547dcd"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "The Library (Movie Clip)",
"key": "gvijQuKBz88",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2016-10-24T01:44:31.000Z",
"id": "65be60c4031deb0162ee63e3"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "The Kiss (Movie Clip)",
"key": "6ir3_NoTJOw",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2016-10-22T17:00:18.000Z",
"id": "65be60cca7e363013653c57c"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "The Bath (Movie Clip)",
"key": "O4p9o3aJGj8",
"site": "YouTube",
"size": 1080,
"type": "Clip",
"official": true,
"published_at": "2016-10-21T17:00:08.000Z",
"id": "65be60d443999b0163c50b39"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "Official US Trailer",
"key": "whldChqCsYk",
"published_at": "2016-07-29T17:02:31.000Z",
"site": "YouTube",
"size": 1080,
"type": "Trailer",
"official": true,
"id": "57dbf0eb925141684c008ae7"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "Official International Trailer",
"key": "NoyWKl0e8FI",
"published_at": "2016-05-03T06:15:07.000Z",
"site": "YouTube",
"size": 1080,
"type": "Trailer",
"official": true,
"id": "57dbf22b9251416905008c72"
},
{
"iso_639_1": "en",
"iso_3166_1": "US",
"name": "Official Int'l Special Trailer",
"key": "6sVYumzrKvs",
"published_at": "2016-04-25T12:39:44.000Z",
"site": "YouTube",
"size": 1080,
"type": "Trailer",
"official": true,
"id": "571e5a939251416f2c0003f0"
}
]
}
This is the code I am working on in the Google Sheets app script:
function TMDBmovietrailer123(rows) {
var tmdbKey = PropertiesService.getScriptProperties().getProperty('tmdbkey');
const requests = rows.map(id => {
return {
url: `https://api.themoviedb.org/3/movie/${id}/videos?api_key=${tmdbKey}`,
muteHttpExceptions: true
}
})
const responses = UrlFetchApp.fetchAll(requests)
return responses.map(request => {
try {
const data = JSON.parse(request.getContentText());
const id = data.id
return [id]
} catch (err) {
return ['']
}
})
}
Anita Chew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.