fetch movie info from omdb/tmdb based on a list of movies (title in colB, year in colC, as formatted by Letterboxd exports)

fetch-movie-data.EXCEL.yaml

name: fetch-movie-data
description: >-
  fetch movie info from omdb/tmdb based on a list of movies (title in colB, year
  in colC, as formatted by Letterboxd exports)
host: EXCEL
api_set: {}
script:
  content: |
    // fetch movie metadata from a list of movies
    // titles must be in colB, year in colC, similar to letterboxd csv exports
    // config: create a worksheet named 'config' and put apikey in B3 and B4

    $("#fetch").click(() => tryCatch(fetchData));
    $("#keywords").click(() => tryCatch(fetchKeywordsOnly));
    $("#byimdbid").click(() => tryCatch(fetchbyimdbid));

    async function fetchData() {
      await Excel.run(async (context) => {
        const cfg = context.workbook.worksheets.getItem("config");
        let apikey = cfg.getRange("B3:B4");
        apikey.load("values");
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const selrange = context.workbook.getSelectedRange();
        // must load() and sync() BEFORE data is available
        selrange.load();
        await context.sync();

        // apikeys
        const omdbapikey = apikey.values[0][0];
        const tmdbapikey = apikey.values[1][0];

        //buid range address
        var srow = selrange.rowIndex + 1;
        var start = "A" + srow; //title in A2
        var end = "B" + (selrange.rowIndex + selrange.rowCount); // year in B2
        var addr = start + ":" + end;
        console.log("will use this range address :" + addr);
        const range = sheet.getRange(addr);
        // must load() and sync() BEFORE data is available
        range.load("values");
        await context.sync();

        //console.log("values: " + JSON.stringify(range.values, null, 4));
        // values = array of array [[title1,year],[title2,year],...]
        console.log("parsing title...");
        for (let i = 0; i < selrange.rowCount; i++) {
          var curow = srow + i;
          //range.values.forEach(async (e, i) => {
          console.log(`fetching ${range.values[i][0]}...`);
          var fetchResult = await fetch(
            `https://www.omdbapi.com/?apikey=${omdbapikey}&plot=full&t=${range.values[i][0]}&y=${range.values[i][1]}`
          );
          var json: JSONData = await fetchResult.json();
          //console.log(JSON.stringify(json));

          if (json.Response === "True") {
            //delete unused properties :
            delete json.Title;
            delete json.Year;
            delete json.Language;
            delete json.Ratings;
            delete json.Metascore;
            delete json.imdbVotes;
            delete json.DVD;
            delete json.Production;
            delete json.Website;
            delete json.Response;

            var data = Object.values(json);
            //remap order of key:values to match excel order
            // 0: "R"
            // 1: "12 Nov 1993"
            // 2: "144 min"
            // 3: "Crime, Drama, Thriller"
            // 4: "Brian De Palma"
            // 5: "Edwin Torres, David Koepp"
            // 6: "Al Pacino, Sean Penn, Penelope Ann Miller"
            // 7: "A Puerto Rican former convict, just released from prison, pledges to stay away from drugs and violence despite the pressure around him and lead on to a better life outside of N.Y.C."
            // 8: "United States"
            // 9: "2 wins & 9 nominations"
            // 10: "https://m.media-amazon.com/images/M/MV5BYzBhY2E0OWYtYzRmNC00ZGY3LTllODYtNTExM2FmYTFkMGUwXkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg"
            // 11: "7.9"
            // 12: "tt0106519"
            // 13: "movie"
            // 14: "$36,948,322"
            const reorder = [13, 3, 4, 5, 6, 8, 9, 11, 1, 2, 0, 14, 12, 10, 7];
            const dataline = [reorder.map((i) => data[i])];
            let leftcorner = "G" + curow; //map reorder[0] in G cell
            let cell = sheet.getRange(leftcorner);
            let fillrange = cell.getResizedRange(0, data.length - 1);
            fillrange.values = dataline;
            //tmdb original title
            var tmdbres = await tmdb(data[12], tmdbapikey);
            console.log("tmdbres:" + tmdbres, "otitle def:" + tmdbres.ot);
            let cellot = sheet.getRange("C" + curow); // original title
            cellot.values = tmdbres.ot;
            // tmdb keywords
            var tmdbkeycell = sheet.getRange("V" + curow);
            tmdbkeycell.load("values");
            await context.sync();
            if (tmdbkeycell.values[0][0] == "") {
              tmdbkeycell.values = tmdbres.keyw.toString();
              console.log("keyw def:" + tmdbres.keyw);
            } else {
              console.log(tmdbkeycell.values);
            }
          }
        }
      });
    }
    async function tmdb(imdbid, tmdbapikey) {
      const tmdbfetch = await fetch(
        `https://api.themoviedb.org/3/find/${imdbid}?api_key=${tmdbapikey}&language=en-US&external_source=imdb_id`
      );
      const tmdbinfo = await tmdbfetch.json();
      console.log("tmdb :", tmdbinfo);
      // original-title
      if (
        tmdbinfo["movie_results"].length > 0 &&
        tmdbinfo["movie_results"][0]["original_title"] !== tmdbinfo["movie_results"][0]["title"]
      ) {
        originalTitle = tmdbinfo["movie_results"][0]["original_title"];
      } else {
        originalTitle = "";
      }

      // keywords
      const keywlist = new Array();
      if (tmdbinfo["movie_results"].length > 0) {
        let tmdbid = tmdbinfo["movie_results"][0]["id"];
        const tmdbkeyfetch = await fetch(`https://api.themoviedb.org/3/movie/${tmdbid}/keywords?api_key=${tmdbapikey}`);
        const tmdbkeywords = await tmdbkeyfetch.json();
        console.log("tmdbkeywords :" + tmdbkeywords, tmdbkeywords["keywords"]);
        if (tmdbkeywords["keywords"].length > 0) {
          tmdbkeywords["keywords"].forEach((e, i) => {
            keywlist.push(e["name"]);
            console.log("keyw:" + keywlist);
            return { ot: originalTitle, keyw: keywlist };
          });
        }
      } else {
        keywlist = [];
      }
      return { ot: originalTitle, keyw: keywlist };
    }
    async function fetchKeywordsOnly() {
      await Excel.run(async (context) => {
        const cfg = context.workbook.worksheets.getItem("config");
        let apikey = cfg.getRange("B3:B4");
        apikey.load("values");
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const selrange = context.workbook.getSelectedRange();
        selrange.load();
        await context.sync();

        // apikeys
        const tmdbapikey = apikey.values[1][0];
        //buid range address
        var srow = selrange.rowIndex + 1;
        var addr = "S" + srow + ":S" + (selrange.rowIndex + selrange.rowCount); // imdbID
        const range = sheet.getRange(addr);
        range.load("values");
        await context.sync();

        for (let i = 0; i < selrange.rowCount; i++) {
          var curow = srow + i;
          var tmdbres = await tmdb(range.values[i], tmdbapikey);
          // tmdb keywords
          var tmdbkeycell = sheet.getRange("V" + curow);
          tmdbkeycell.load("values");
          await context.sync();
          if (tmdbkeycell.values[0][0] == "") {
            tmdbkeycell.values = tmdbres.keyw.toString();
            console.log("keyw def:" + tmdbres.keyw);
          } else {
            console.log(tmdbkeycell.values);
          }
        }
      });
    }
    async function fetchbyimdbid() {
      await Excel.run(async (context) => {
        const cfg = context.workbook.worksheets.getItem("config");
        let apikey = cfg.getRange("B3:B4");
        apikey.load("values");
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const selrange = context.workbook.getSelectedRange();
        // must load() and sync() BEFORE data is available
        selrange.load();
        await context.sync();

        // apikeys
        const omdbapikey = apikey.values[0][0];
        const tmdbapikey = apikey.values[1][0];

        //buid range address
        var srow = selrange.rowIndex + 1;
        var start = "S" + srow; //imdbID in column S
        var end = "S" + (selrange.rowIndex + selrange.rowCount); 
        var addr = start + ":" + end;
        console.log("will use this range address :" + addr);
        const range = sheet.getRange(addr);
        // must load() and sync() BEFORE data is available
        range.load("values");
        await context.sync();

        //console.log("values: " + JSON.stringify(range.values, null, 4));
        // values = array of array [[title1,year],[title2,year],...]
        console.log("parsing title...");
        for (let i = 0; i < selrange.rowCount; i++) {
          var curow = srow + i;
          //range.values.forEach(async (e, i) => {
          console.log(`fetching ${range.values[i][0]} ${range.values[i][1]}...`);
          var fetchResult = await fetch(
            `https://www.omdbapi.com/?apikey=${omdbapikey}&plot=full&i=${range.values[i][0]}`
          );
          var json: JSONData = await fetchResult.json();
          //console.log(JSON.stringify(json));

          if (json.Response === "True") {
            //delete unused properties :
            delete json.Title;
            delete json.Year;
            delete json.Language;
            delete json.Ratings;
            delete json.Metascore;
            delete json.imdbVotes;
            delete json.DVD;
            delete json.Production;
            delete json.Website;
            delete json.Response;

            var data = Object.values(json);
            //remap order of key:values to match excel order
            // 0: "R"
            // 1: "12 Nov 1993"
            // 2: "144 min"
            // 3: "Crime, Drama, Thriller"
            // 4: "Brian De Palma"
            // 5: "Edwin Torres, David Koepp"
            // 6: "Al Pacino, Sean Penn, Penelope Ann Miller"
            // 7: "A Puerto Rican former convict, just released from prison, pledges to stay away from drugs and violence despite the pressure around him and lead on to a better life outside of N.Y.C."
            // 8: "United States"
            // 9: "2 wins & 9 nominations"
            // 10: "https://m.media-amazon.com/images/M/MV5BYzBhY2E0OWYtYzRmNC00ZGY3LTllODYtNTExM2FmYTFkMGUwXkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg"
            // 11: "7.9"
            // 12: "tt0106519"
            // 13: "movie"
            // 14: "$36,948,322"
            const reorder = [13, 3, 4, 5, 6, 8, 9, 11, 1, 2, 0, 14, 12, 10, 7];
            const dataline = [reorder.map((i) => data[i])];
            let leftcorner = "G" + curow; //map reorder[0] in G cell
            let cell = sheet.getRange(leftcorner);
            let fillrange = cell.getResizedRange(0, data.length - 1);
            fillrange.values = dataline;
            //tmdb original title
            var tmdbres = await tmdb(data[12], tmdbapikey);
            console.log("tmdbres:" + tmdbres, "otitle def:" + tmdbres.ot);
            let cellot = sheet.getRange("C" + curow); // original title
            cellot.values = tmdbres.ot;
            // tmdb keywords
            var tmdbkeycell = sheet.getRange("V" + curow);
            tmdbkeycell.load("values");
            await context.sync();
            if (tmdbkeycell.values[0][0] == "") {
              tmdbkeycell.values = tmdbres.keyw.toString();
              console.log("keyw def:" + tmdbres.keyw);
            } else {
              console.log(tmdbkeycell.values);
            }
          }
        }
      });

    }
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        console.error(error);
      }
    }
  language: typescript
template:
  content: "<div class=\"info\">\n\t<h2>MEDIA SHEET</h2>\n</div>\n<div class=\"buttons\">\n\t<button id=\"fetch\" class=\"ms-Button\">\n      <span class=\"ms-Button-label\">Fetch Data by Name/Year</span>\n  </button>\n\t<button id=\"keywords\" class=\"ms-Button\">\n      <span class=\"ms-Button-label\">Fetch TMDB Keywords Only</span>\n  </button>\n\t<button id=\"byimdbid\" class=\"ms-Button\">\n      <span class=\"ms-Button-label\">Fetch info by ImdbID</span>\n  </button>\n</div>\n<div class=\"info\">\n\tv20240411<br>\n\t<h2></h2>\n\t  1. go to the 'Letterboxd RSS' worksheet<br>\n\t  2. fetch new content from Letterboxd RSS : menu `Developer > Refresh Data`<br>\n\t  3. copy new rows from `Letterboxd RSS` to `watched` worksheet<br>\n\t  4. go to the 'watched' worksheet and select rows<br>\n\t  5. click a button below to fetch metadata from omdb/tmdb\n</div>"
  language: html
style:
  content: |-
    :root {
      font-family: arial;
      color: white;
    }
    body {
     background-color: #202428} 
    }
    section.samples {
        margin-top: 20px;
    }
    .info{
      display:block;
      position: relative;
      margin-top: 20px;
    }
    .buttons {
      display: block;
      position: relative;
      margin-top: 50px;
    }
    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
    .ms-Button {
      border-radius: 5px;
      border: 1px solid #161616;
      background-color: #0066aa;
    }
    .ms-Button-label {
      color: #eee;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1