Y5ShjfpE

1//Global variables
2const app = SpreadsheetApp
3const ss = app.getActiveSpreadsheet();
4const env = PropertiesService.getScriptProperties();
5const token = env.getProperty('TOKEN');
6
7function onOpen(e) {
8  app.getUi().createMenu('IMDB')
9    .addItem('Refresh All', 'refreshAll')
10    .addItem('Refresh Top250Movies', 'top250movies')
11    .addItem('Refresh Top250Tvs', 'top250tvs')
12    .addItem('Refresh MostPopularMovies', 'mostPopularMovies')
13    .addItem('Refresh MostPopularTVs', 'mostPopularTVs')
14    .addSeparator()
15    .addItem('Set API key', 'setAPI')
16    .addToUi();
17}
18
19function setAPI() {
20  const key = app.getUi().prompt('API Key').getResponseText();
21  env.setProperty('TOKEN', key);
22}
23
24function refreshAll() {
25  top250movies()
26  top250tvs()
27  mostPopularTVs()
28  mostPopularMovies()
29}
30
31function top250movies() {
32  getIMDBtops('Top250Movies', 'Top250Movies')
33}
34
35function top250tvs() {
36  getIMDBtops('Top250TVs', 'Top250TVs')
37}
38
39function mostPopularMovies() {
40  getIMDBtops('MostPopularMovies', 'MostPopularMovies')
41}
42
43function mostPopularTVs() {
44  getIMDBtops('MostPopularTVs', 'MostPopularTVs')
45}
46
47function getIMDBtops(endpoint, sheetname) {
48  const url = `https://imdb-api.com/en/API/${endpoint}/${token}`
49  const response = UrlFetchApp.fetch(url)
50  const items = JSON.parse(response.getContentText()).items;
51  const headers = Object.keys(items[0]);
52  const imageIndex = headers.indexOf('image');
53  const data = [];
54
55  items.forEach(movie => {
56    data.push(Object.values(movie))
57  });
58
59  const formulas = data.map(movie => {
60    return [`=IF($B$1 = true,IMAGE("${movie[imageIndex]}"),)`];
61  })
62
63  data.unshift(headers);
64
65  const sheet = ss.getSheetByName(sheetname);
66  sheet.getRange(3, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
67  sheet.getRange(3, 1, data.length, data[0].length).setValues(data);
68  sheet.getRange(3, sheet.getLastColumn() + 1).setValue('Cover');
69  sheet.getRange(4, sheet.getLastColumn(), formulas.length, 1).setFormulas(formulas);
70}
71
72function actorsInSameMovie() {
73  const sheet = ss.getSheetByName('ActorsInSameMovie');
74  const [actorOne, actorTwo] = sheet.getRange(2, 2, 1, 2).getValues().flat();
75  const searchActorOne = `https://imdb-api.com/en/API/SearchName/${token}/${actorOne}`
76  const searchActorTwo = `https://imdb-api.com/en/API/SearchName/${token}/${actorTwo}`
77  const searchResponse = UrlFetchApp.fetchAll([searchActorOne, searchActorTwo]);
78
79  try{
80  var actorIds = searchResponse.map(seach => {
81    return JSON.parse(seach.getContentText()).results[0].id;
82  });
83  } catch (err){
84    app.getUi().alert('One of the actors is not found')
85    return;
86  }
87
88  const [actorIdOne, actorIdTwo] = actorIds;
89
90  const actorInformationOne = JSON.parse(UrlFetchApp.fetch(`https://imdb-api.com/en/API/Name/${token}/${actorIdOne}`).getContentText());
91  const actorInformationTwo = JSON.parse(UrlFetchApp.fetch(`https://imdb-api.com/en/API/Name/${token}/${actorIdTwo}`).getContentText());
92
93  const moviesOne = actorInformationOne.castMovies.filter(movie => movie.role == 'Actor');
94  const moviesTwo = actorInformationTwo.castMovies.filter(movie => movie.role == 'Actor');
95  const matches = moviesOne.filter(m1 => moviesTwo.some(m2 => m1.id === m2.id))
96    .map(match => {
97      return {...match, ...getMovieFromId(match.id)}
98    });
99
100  const pictures = [[`=IFERROR(IMAGE("${actorInformationOne.image}"),)`, `=IFERROR(IMAGE("${actorInformationTwo.image}"),)`]]
101  const keysNotToProcess = ['id', 'image', 'knownFor', 'castMovies', 'errorMessage'];
102  const actorsInfo = [];
103
104  Object.keys(actorInformationOne).forEach(key => {
105    if (!keysNotToProcess.includes(key)) {
106      actorsInfo.push([key.toUpperCase(), actorInformationOne[key], actorInformationTwo[key]])
107    };
108  });
109
110  sheet.getRange(4, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
111
112  if (matches.length > 0) {
113    const matchesToArray = matches.map(match => {
114      return Object.values(match);
115    })
116    matchesToArray.unshift(Object.keys(matches[0]).map(key => key.toUpperCase()));
117    sheet.getRange(4,2,1,2).setFormulas(pictures);
118    sheet.getRange(10, 1, actorsInfo.length, 3).setValues(actorsInfo)
119    sheet.getRange(4, 5, matchesToArray.length, matchesToArray[0].length).setValues(matchesToArray);
120  } else {
121    sheet.getRange(4, 1).setValue('No matches found')
122  }
123
124}
125
126function getMovieFromId(id){
127  const url = `https://imdb-api.com/en/API/Title/${token}/${id}`
128  const data = JSON.parse(UrlFetchApp.fetch(url).getContentText());
129
130  const object = {
131    runtime: data.runtimeMins, 
132    awards: data.awards, 
133    directors: data.directors, 
134    genres: data.genres,
135    rating: data.imDbRating,
136    plot: data.plot
137  }
138
139  return object;
140}
141
142
143