App script, Connect a Google spreadsheet with an API

In this post, we will use App script to connect a google spreadsheet with TheMovieDB API and get a list of movies known for a given actor.

Today, we will discover together some of the basic app script possibilities. At the end, you will be able to extend your knowledge via the documentation provided in the sources section.

Create a TheMovieDB account

https://www.themoviedb.org/signup

Create an API Key

Go to Settings -> API Key

Test the request to the API

Download Postman

https://learning.postman.com/docs/getting-started/installation-and-updates/

Create a collection and add a request inside

Create a app script to interact with the API

Create a new google spreadsheet.

Go to Tools -> Script Editor to create a script and make a request and get data for the actor name passed in query.

function importListOfMoviesForActor() {
    
  /* Get current actorName from cell in the current spreadSheet */
  /* getRange(row, col) */
  
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var actorName = sheet.getRange(1, 2).getValue();
  Logger.log(actorName);
  
  /* In clear here, not very secure */
  let apiKey = 'moviedb_api_key';
  let apiUrl = `https://api.themoviedb.org/3/search/person/?api_key=${apiKey}&query=${actorName}`;
  
  let responseJSON = UrlFetchApp.fetch(apiUrl);
  let responseDataObject = JSON.parse(responseJSON);
  
  Logger.log(responseDataObject['results']);
  
  if(responseDataObject['results'].length > 0){

    movieList = responseDataObject['results'][0]['known_for'];

    for(var index in movieList){
      Logger.log(movieList[index]['title']);
      sheet.getRange(3 + parseInt(index), 2).setValue(movieList[index]['title']);
    }

  }else{

    sheet.getRange(3, 2).setValue('Movies not found');
    sheet.getRangeList(['B4', 'B5']).clearContent();
  }
}

/* importListOfMoviesForActor() */

To debug the procedure and see logs, go to View -> Logs.

Create a button to call the function to get list of movies

Go to Insert -> Drawing.

Now, let’s connect the app script with the button.

Then, Right click on the button -> Assign a script and copy the name of the procedure `importListOfMoviesForActor`.

In the script, one cell is used for the input and a list of cells to display the output with movies name. At the end, you should have something similar to the following sheet.

I hope you enjoyed this post. If you have any questions, please let me know in comments.

If you liked this post, help us by giving a like or sharing this post.

To know more about me: https://www.upidev.com/a-propos/ [FR version]

Sources:

• https://developers.themoviedb.org/3/search/search-people
• https://support.google.com/docs/thread/5809954?hl=en
• https://stackoverflow.com/questions/46693496/for-each-loop-in-google-apps-script
• http://googleappscripting.com/json/