Fetch and format API data

So far in this codelab, you've seen how you can use Apps Script as an alternative means of formatting your spreadsheet. Next you'll write code that pulls data from a public API, inserts it into your spreadsheet, and formats it so it's readable.

In the last codelab, you learned how to pull data from an API. You'll use the same techniques here. In this exercise, we'll use the public Star Wars API (SWAPI) to populate your spreadsheet. Specifically, you'll use the API to get information about the major characters that appear in the original three Star Wars films.

Your code will call the API to get a large amount of JSON data, parse the response, place the data in a new sheet, and then format the sheet.

Implementation

In this section, you'll add some additional menu items. Each menu item calls a wrapper script that passes item-specific variables to the main function (createResourceSheet_()). You'll implement this function, and three additional helper functions. As before, the helper functions help isolate logically compartmental parts of the task and help keep the code readable.

Take the following actions:

1. In the Apps Script editor, update your onOpen() function in you script project to match the following:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

1. Save your script project.

2. In the script editor, select onOpen from the functions list and click Run. This runs onOpen() to rebuild the spreadsheet menu with the new options you added.

3. To create an Apps Script file, beside Files click Add a file > Script.
4. Name the new script "API" and press Enter. (Apps Script automatically appends a .gs extension to the script file name.)

5. Replace the code in the new API.gs file with the following:

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

1. Add the following helper functions to the end of the API.gs script project file:

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

1. Save your script project.

Code review

You've just added a lot of code. Let's go over each function individually to understand how they work:

onOpen()

Here you've added a few menu items to your Quick formats menu. You've set a separator line and then used the Menu.addSubMenu(menu) method to create a nested menu structure with three new items. The new items are added with the Menu.addItem(caption, functionName) method.

Wrapper functions

The added menu items are all doing something similar: they're trying to create a sheet with data pulled from SWAPI. The only difference is they're each focusing on a different film.

It would be convenient to write a single function to create the sheet, and have the function accept a parameter to determine what film to use. However, the Menu.addItem(caption, functionName) method doesn't let you pass parameters to it when called by the menu. So, how do you avoid writing the same code three times?

The answer is wrapper functions. These are lightweight functions you can call that immediately call another function with specific parameters set.

Here, the code uses three wrapper functions: createPeopleSheetIV(), createPeopleSheetV(), and createPeopleSheetVI(). The menu items are linked to these functions. When a menu item is clicked, the wrapper function executes and immediately calls the main sheet builder function createResourceSheet_(resourceType, idNumber, episodeNumber), passing along the parameters appropriate for the menu item. In this case, it means asking the sheet builder function to create a sheet filled with major character data from one of the Star Wars films.

Note: If you want, you can create more wrapper functions to populate different types of sheets with data. You just need to know what resource type to ask for and what film ID to use. Example resource types include ‘planets' and ‘starships', and the API accepts numbers 1–7 as film IDs.

createResourceSheet_(resourceType, idNumber, episodeNumber)

This is the main sheet builder function for this exercise. With the assistance of some helper functions, it gets the API data, parses it, creates a sheet, writes the API data to the sheet, and then formats the sheet using the functions you constructed in the previous sections. Let's review the details:

First, the function uses fetchApiResourceObject_(url) to make a request of the API to retrieve basic film information. The API response includes a collection of URLs the code can use to get more details about specific people (known here as resources) from the films. The code collects it all in the resourceUrls array.

Next, the code uses fetchApiResourceObject_(url) repeatedly to call the API for every resource URL in resourceUrls. The results are stored in the resourceDataList array. Every element of this array is an object that describes a different character from the film.

The resource data objects have several common keys that map to information about that character. For example, the key ‘name' maps to the name of the character in the film. We assume the keys for each resource data object are all identical, since they're meant to use common object structures. The list of keys is needed later, so the code stores the key list in resourceObjectKeys using the JavaScript Object.keys() method.

Next, the builder function calls the createNewSheet_(name) helper function to create the sheet where the new data will be placed. Calling this helper function also activates the new sheet.

After the sheet is created, the helper function fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) is called to add all the API data to the sheet.

Finally, all the formatting functions you built previously are called to apply the same formatting rules to the new data. Since the new sheet is the active one, the code can re-use these functions without modification.

fetchApiResourceObject_(url)

This helper function is similar to the fetchBookData_(ISBN) helper function used in the previous codelab Working with data. It takes the given URL and uses the UrlFetchApp.fetch(url, params) method to get a response. The response is then parsed into a JSON object using the HTTPResponse.getContextText() and the JavaScript JSON.parse(json) methods. The resulting JSON object is then returned.

Note: If you work a lot with APIs, this function can be handy when you need to retrieve API information. It's generalized to work with almost any API URL, so consider saving it for future use.

createNewSheet_(name)

This helper function is fairly simple. It first verifies if a sheet of the given name exists in the spreadsheet. If it does, the function activates the sheet and returns it.

If the sheet doesn't exist, the function creates it with Spreadsheet.insertSheet(sheetName), activates it, and returns the new sheet.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

This helper function is responsible for filling the new sheet with API data. It takes as parameters the new sheet, the list of object keys, and the list of API resource objects as parameters. Each object key represents a column in the new sheet, and each resource object represents a row.

First, the function calculates the number of rows and columns needed to present the new API data. This is the size of the resource and keys list, respectively. The function then defines an output range (resourceRange) where the data will be placed, adding an extra row to hold the column headers. The variable resourceValues holds a 2D values array extracted from resourceRange.

The function then loops over every object key in the objectKeys list. The key is set as the column header, and then a second loop goes through every resource object. For each (row, column) pair, the corresponding API information is copied to the resourceValues[row][column] element.

Note: Remember that rows and columns in Sheets are 1-indexed, while JavaScript arrays are 0-indexed. Because of this difference, we often have to add or subtract 1 from indices when programming in Apps Script.

After resourceValues is filled, the destination sheet is cleared using Sheet.clear() in case it contains data from previous menu item clicks. Finally, the new values are written to the sheet.

Results

You can see the results of your work by doing the following:

1. If you haven't already, save your script project in the Apps Script editor.

2. Click the Quick formats > Create character sheet > Episode IV menu item.

The results should look like the following:

d9c472ab518d8cef.gif

You've now written code to import data into Sheets and automatically format it.

Reference : https://developers.google.com/codelabs/apps-script-fundamentals-4#6