Spotted a bug? Have a great idea? Help us make google.dev great!

Welcome to the third part of Fundamentals of Apps Script with Sheets playlist!

By completing this codelab, you can learn how to employ data manipulations, custom menus, and public API data retrieval in Apps Script to improve your Sheets' experience. And, you can continue working with the SpreadsheetApp, Spreadsheet, Sheet, and Range classes that the previous codelabs in this playlist introduced.

What you'll learn

  • How to import data from a personal or shared spreadsheet in Drive.
  • How to create a custom menu with the onOpen() function.
  • How to parse and manipulate string data values in Google Sheet cells.
  • How to pull and manipulate JSON object data from a public API source.

Before you begin

This is the third codelab in the Fundamentals of Apps Script playlist. Before starting this codelab, be sure to complete the previous codelabs:

  1. Macros and Custom Functions
  2. Spreadsheets, Sheets, and Ranges

What you'll need

  • An understanding of the basic Apps Script topics explored in the previous codelabs of this playlist.
  • Basic familiarity with the Apps Script editor
  • Basic familiarity with Google Sheets
  • Ability to read Sheets A1 Notation
  • Basic familiarity with JavaScript and its String class

The exercises in this codelab require spreadsheet to work in. Follow these steps to create a new spreadsheet to use in these exercises:

  1. Create a new spreadsheet in your Google Drive. You can do this from the Drive interface by selecting New > Google Sheets. The new spreadsheet is placed in your root Drive by default.
  2. Click the spreadsheet title and change it from "Untitled spreadsheet" to "Data Manipulation and Custom Menus". Your sheet should look like this:

  1. Select Tools > Script Editor to open the script editor.
  2. Click the project title and change it from "Untitled Project" to "Data Manipulation and Custom Menus". Click OK to save the title change:

With a blank spreadsheet and project, you're ready to start the lab! Move to the next section to start learning about custom menus.

Apps Script gives you the ability to define custom menus that can appear in Google Sheets. You can also use custom menus in Google Docs, Google Slides, and Google Forms. When you define a custom menu item, you create a text label and connect it to an Apps Script function in your script project. When you add the menu to the UI, it appears in Google Sheets:

When a user clicks a custom menu item, the Apps Script function you associated with it executes. This is a handy way of causing Apps Script functions to run without having to open the script editor. It also allows other users of the spreadsheet to execute your code without having to know anything about how it or Apps Script works—to them, it's just another menu item.

Custom menu items are defined in the onOpen() simple trigger function, which you'll learn about in the next section.

Triggers in Apps Script provide a way of running specific Apps Script code in response to specific conditions or events. When you create a trigger, you define what event causes the trigger to fire and provide an Apps Script function that runs when that event happens.

onOpen() is an example of a simple trigger. Simple triggers are easy to set up—all you have to do is write an Apps Script function named onOpen()and Apps Script will run it every time the associated spreadsheet is opened or reloaded:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Implementation

Let's create a custom menu!

  1. Replace the code in your script project with the following:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}
  1. Save your script project.

Code review

Let's review this code to understand how it works. In onOpen(), the first line uses the getUi() method to acquire a Ui object representing the user interface of the active spreadsheet this script is bound to.

The next three lines create a new menu (Book-list), add a menu item (Load Book-list) to that menu, and then add the menu to the spreadsheet's interface. This is done with the createMenu(caption), addItem(caption, functionName), and addToUi() methods, respectively.

The addItem(caption, functionName) creates a connection between the menu item label and an Apps Script function that runs when that menu item is selected. In this case, selecting the Load Book-list menu item causes Sheets to attempt to run the loadBookList() function (which doesn't exist yet).

Results

Let's run this function now to see that it works! Do the following:

  1. In Google Sheets, reload your spreadsheet. Note: this usually closes the tab with your script editor.
  2. Reopen your script editor by selecting Tools > Script editor.

After your spreadsheet reloads, you'll see a new menu called Book-list should appear on your menu bar:

By clicking Book-list, you can see the resulting drop-down:

And that's how you can create your own custom menu in Sheets! The next section defines the loadBookList() function and introduces one way you can interact with data in Apps Script: reading other spreadsheets.

Now that you have created a custom menu, you can create functions that can be run from just clicking the menu item itself.

Right now, the custom menu Book-list has one menu item: Load Book-list. But, the function called when you choose the Load Book-list menu item, loadBookList(), doesn't exist in your script, so selecting Book-list > Load Book-list throws an error:

You can fix this error by implementing the loadBookList() function.

Implementation

You want the new menu item to fill the spreadsheet with data to work with, so you'll implement loadBookList() to read data from another spreadsheet and copy it into this one:

  1. Add the following code to your script under onOpen():
/** 
 * Creates a template book list based off of the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive via the
  // spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values that already
  // exist there. 
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}
  1. Save your script project.

Code review

So how does this function work? The loadBookList() function uses methods primarily from the Spreadsheet, Sheet, and Range classes which the previous codelabs introduced. With those concepts in mind, you can break down the loadBookList() code into the following four sections:

1: Identify the destination sheet

The first line uses SpreadsheetApp.getActiveSheet() to get and store a reference to the current sheet object into the variable sheet. This is where the data will be copied into.

2: Identify the source data

The next few lines establish four variables that refer to the source data you are retrieving:

  • bookSS stores a reference to the spreadsheet the code is reading data from. The code finds the spreadsheet by its spreadsheet ID. In this example, we provided the ID of a source spreadsheet to read from, and open the spreadsheet using the SpreadsheetApp.openById(id) method.
  • bookSheet stores a reference to a sheet within bookSS that contains the data you want. The code identifies the sheet to read from by its name, codelab-book-list.
  • bookRange stores a reference to a range of data in bookSheet. The method Sheet.getDataRange() returns the range containing all the non-empty cells in the sheet. It's a handy way of making sure you get a range covering all the data in a sheet without including lots of empty rows and columns.
  • bookListValues is a 2D array containing all the values taken from the cells in bookRange. The Range.getValues() method generates this array by reading data from the source sheet.

3: Copy the data from source to destination

The next code section copies the bookListValues data into sheet, and then renames the sheet as well:

4: Format the destination sheet

The Sheet.setName(name) is used to change the destination sheet name to Book-list. The last line in the function uses Sheet.autoResizeColumns(startColumn, numColumns) to resize the first three columns in the destination sheet, allowing you to read the new data more easily.

Results

You can see this function in action! In Google Sheets, select Book-list > Load book-list to run the function to fill your spreadsheet:

You now have a sheet with a list of book titles, authors, and 13-digit ISBN numbers! In the next section you'll learn how to modify and update the data in this book list using string manipulations and custom menus.

You can now see book information on your sheet. Each row refers to a specific book, listing its title, author, and ISBN number in separate columns. However, you can also see some problems with this raw data:

  1. For some rows the title and author are placed in the title column together, linked by a comma or the string " by ".
  2. Some rows are missing information for their book's titles and authors.

In the next sections you'll correct these issues by cleaning the data. For the first issue you'll create functions that read the title column and split the text whenever a comma or " by " delimiter is found, placing the corresponding author and title substrings in the correct columns. For the second issue, you'll write code that automatically looks up missing book information using an external API, and fills that information into your sheet.

You'll want to create three new menu items to control the data cleaning operations you'll be implementing.

Implementation

Let's update onOpen() to include the extra menu items you'll need. Do the following:

  1. In your script project, update your onOpen() code to match the following:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is usually used to add custom
 * menu items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}
  1. Save your script project.
  2. In the script editor, select onOpen in the taskbar drop-down and then click the button. This will run onOpen() to rebuild the the spreadsheet menu, so that you don't have to reload the spreadsheet.

In this new code the Menu.addSeparator() method creates a horizontal divider in the dropdown menu so you can keep groups of related menu items visually organized. The new menu items are then added below it, with the labels Separate title/author at first comma, Separate title/author at last "by", and Fill in blank titles and author cells.

Results

In your spreadsheet you can click the Book-list menu to see the new menu items:

Clicking on these items now will cause an error since you haven't yet implemented their corresponding functions, so let's do that next.

The dataset you imported into your spreadsheet has a few cells where the author and title are incorrectly combined in one cell using a comma:

Splitting text strings into separate columns is a common spreadsheet task. Google Sheets provides a SPLIT() function that divides strings into columns. However, often datasets have issues that can't be easily solved with Sheets functions. In these cases you can write Apps Script code to perform the complex operations needed to clean and organize your data.

You start cleaning your data by first implementing a function called splitAtFirstComma() that splits the author and title into their respective cells when commas are found.

The splitAtFirstComma() function should take the following steps:

  1. Get the range representing the currently selected cells.
  2. Check if cells in that range have a comma.
  3. Where commas are found, split the string into two (and only two) substrings at the location of the first comma. To make things simpler, you can assume any comma indicates a "[authors], [title]" string pattern. You can also assume that if multiple commas appear in the cell, it's appropriate to split on the first comma in the string.
  4. Set the substrings as the new contents of the respective title and author cells.

Implementation

To implement these steps, you'll use the same Spreadsheet service methods you've used before, but you'll also need to use some simple JavaScript to manipulate the string data. Take the following steps:

  1. In the Apps Script editor, add the following function to the end of your script project:
/** 
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present. 
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }


  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Save your script project.

Code review

Let's review the new code. The code consists of three main sections:

1: Retrieve the highlighted title values

The first three lines establish three variables that refer to the current data in the sheet:

  • activeRange represents the range the user currently has highlighted when the splitAtFirstComma() function was called. To keep this exercise simple, we can assume the user only does this when highlighting cells in column A.
  • titleAuthorRange represents a new range that covers the same cells as activeRange, but also includes one more column to the right. titleAuthorRange is created using the Range.offset(rowOffset, columnOffset, numRows, numColumns) method. The code needs this expanded range because it needs a place to put any discovered authors it finds in the title column.
  • titleAuthorValues is a 2D array of data that has been extracted from titleAuthorRange using Range.getValues().

2: Examine each title and split on any comma delimiter found

The next section examines the values in titleAuthorValues to look for commas. A JavaScript for loop is used to examine all the values in the first column of titleAuthorValues. When a comma substring is found (", ") with the JavaScript String indexOf() method, the code does the following:

  1. The cell string value is copied to the titlesAndAuthors variable.
  2. The comma location is determined using the JavaScript String indexOf() method.
  3. The JavaScript String slice() method is used twice to obtain the substring prior to the comma delimiter and the substring after the delimiter.
  4. The substrings are copied back into the titleAuthorValues 2D array, overwriting the existing values at that position. Since we are assuming a "[authors], [title]" pattern, the order of the two substrings is reversed to put the title in the first column and the authors in the second.

Note: When the code doesn't find a comma, it leaves the data in that row unchanged.

3: Copy the new values back into the sheet

Once all the title cell values have been examined, the updated titleAuthorValues 2D array is copied back into the spreadsheet using

the Range.setValues(values) method.

Results

You can now see the effects of the splitAtFirstComma() function in action! Try running it by selecting the Separate title/author at first comma menu item after selecting...

...one cell:

...or multiple cells:

You've now built an Apps Script function that processes Google Sheets data! Next you'll implement the second splitter function.

Taking another look at the original data, you can see another problem. Just as some of the data formats titles and authors in a single cell as "[authors], [title]", other cells format author and title as "[title] by [authors]:"

Implementation

You can solve this problem using the same technique used in the last section, creating a new function called splitAtLastBy(). This function has a very similar job to splitAtFirstComma()—the only real difference is that it is looking for a slightly different pattern of text. Implement this function by doing the following:

  1. In the Apps Script editor, add the following function to the end of your script project:
/** 
 * Reformats title and author columns by splitting title column
 * at the last instance of the string " by ".
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates an "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // our array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Save your script project.

Code review

There are a few key differences between this code and splitAtFirstComma():

  1. The substring " by " is used as a string delimiter, instead of ", ".
  2. Here the JavaScript String.lastIndexOf(substring) is used instead of String.indexOf(substring). This means that if there are multiple " by " substrings in the initial string, all but the last " by " are assumed to be part of the title.
  3. After splitting the string, the first substring is set as the title and the second as the author string (this is the opposite order from the one used in splitAtFirstComma()).

Results

You can now see the effects of the splitAtLastBy() function in action! Try running it by selecting the Separate title/author at last "by" menu item after selecting...

...one cell:

...or multiple cells:

You've completed this section of the codelab. You can now use Apps Script to read and modify the string data in a sheet, and use custom menus to execute different Apps Script commands!

In the next section you'll learn how to further improve this dataset by filling in the blank cells with data drawn from a public API.

So far you've refined your dataset to fix some title and author formatting issues, but the dataset still is missing some information, highlighted in the cells below:

The missing data can't be obtained by string operations on the data you do have here. Instead, you'll need to get the missing data from another source. In this case, you can do this in Apps Script by requesting information from an external API that can provide your script additional book information.

APIs are application programming interfaces. It's a pretty general term, but basically means that someone has provided a service that your programs and scripts can call to do specific things like request information or take certain actions. In this section, you are calling a publicly-available API to request additional book information, which you then put into the vacant cells in your sheet.

This section teaches you how to:

  • Request book data from an external API source.
  • Extract title and author information from the returned data and write that information to your spreadsheet.

Before delving into code that works directly with your spreadsheet, you can learn about the specifics of working with external APIs in Apps Script by creating a helper function specifically for requesting book information from the public Open Library API.

This helper function, fetchBookData_(ISBN) takes a 13-digit ISBN number of a book as a parameter and returns data about that book by connecting to and retrieving information from the Open Library API and then parsing the returned JSON object.

Implementation

Implement this helper function by doing the following:

  1. In the Apps Script editor, add the following code to the end of your script:
/*
 * Helper function that retrieves book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to look up.
 * @return {object} The book's data, in JSON format. 
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in. 
  return bookData['ISBN:' + ISBN];
}
  1. Save your script project.

Code review

This code is divided into two main sections:

1: Making an API request

In the first two lines fetchBookData_(ISBN) connects to the public Open Library API, using the API's URL endpoint and Apps Script's UrlFetch service.

The url variable is just a URL string, like a web address. It points to a location on the Open Library servers. It also includes three parameters (bibkeys, jscmd, and format) that tell the Open Library servers what information you are requesting and how to structure the response. In this case, you provide the book's ISBN number to identify it, and ask for detailed information to be returned in JSON format.

Once you have built the URL string, the code sends a request to that location and receives a response. This is done with the UrlFetchApp.fetch(url, params) method. This method sends an information request to the external URL you provide and stores the resulting response in the response variable. In addition to the URL, the code sets the optional parameter muteHttpExceptions as true. This setting just means your code won't halt if the requests results in an API error; instead the error response is returned.

The request returns a HTTPResponse object which is stored in the response variable. HTTP responses include a response code, HTTP headers, and the main response content. The information of interest here is the main JSON content, so the code must extract that and then parse the JSON to locate and return the desired information.

2: Parse the API response and return the information of interest

In the last three lines of the code, the HTTPResponse.getContentText() method returns the main content of the response as a string. This string is in JSON format, but the exact content and format is defined by the Open Library API. The JSON.parse(jsonString) method converts the JSON string to a JavaScript object so that different parts of the data can be easily extracted. Finally, the function returns the part of the data corresponding to the ISBN number for the book of interest.

Results

Now that you've implemented fetchBookData_(ISBN), other functions in your code can quickly look up book information for any book using it's ISBN number. You'll use this function to help fill in the blanks in your spreadsheet.

You can now implement a new fillInTheBlanks() function that will do the following:

  1. Identify the missing title and author data within the active data range.
  2. Retrieve a specific book's missing data by calling the Open Library API via the fetchBookData_(ISBN) helper method.
  3. Update the missing title and/or author values in their respective cells.

Implementation

Implement this new function by doing the following:

  1. In the Apps Script editor, add the following code to the end of your script project:
/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the current book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they are found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row further.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not have a title, so only fill it in
      // if the API returns one and the title is blank in the
      // sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not have an author name, so only fill it in
      // if the API returns one and the author is blank in the
      // sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Put the updated book data values back into the spreadsheet.
  dataRange.setValues(bookValues);   
}
  1. Save your script project.

Code review

This code is divided into three sections:

1: Reading the existing book information

The first three lines of the function define some constants that help make the code more human-readable. In the next two lines the bookValues variable is used to maintain a local copy of the sheet's book information. The code will read information from bookValues, use the API to fill in missing information, and then write these values back to the spreadsheet.

2: Fetching missing information using the helper function

The code then loops over each row in bookValues to look for missing titles or authors. In order to reduce the number of API calls the code must make (and thus avoid wasting time), the code only calls the API if the following are true:

  1. The row has a value in the ISBN column (that is, the book's ISBN is known).
  2. Either the title or author cell in that row is empty.

If the conditions are all true the code calls the API using the fetchBookData_(isbn) helper function you implemented, and stores the result in the bookData variable. This variable should now have the missing information you want to write back into the sheet.

The only task now left is to add the bookData information to our spreadsheet. However, there is a caveat. Unfortunately, public APIs like the Open Library Book API sometimes don't have the information you request, or occasionally may have some other issue that prevents it from providing that information. If you blindly assume every API request will succeed, your code won't be robust enough to handle unexpected errors.

To make sure your code is robust in the face of API errors, the code must check that the API response is valid before attempting to use it. Once the code has bookData, it conducts a simple check verifies that bookData and bookData.details exist before attempting to read from them. If either is missing, that means the API didn't have the data you wanted. In that case, the continue command tells the code to skip that row—you can't fill in the missing cells, but at least your script won't crash.

3: Writing updated information back into the sheet

The next part of the code has similar checks that verify the API returned title and author information. The code only updates the bookValues array if the original title or author cell was empty and the API returned a value you can place there.

The loop exits after all rows in the sheet are examined. The last step is to write the now-updated bookValues array back to the spreadsheet using Range.setValues(values).

Results

Now you can finish cleaning your book data! Do the following:

  1. If you haven't yet, highlight the A2:A15 range in your sheet, and then select Book-list > Separate title/author at first comma to clean the comma problems.
  2. If you haven't yet, highlight the A2:A15 range in your sheet, and then select Book-list > Separate title/author at last "by" to clean the "by" problems.
  3. Select Book-list > Fill in blank titles and author cells to have your code fill in all the remaining cells:

Congrats on completing this codelab! You've learned how to create custom menus to activate different parts of your Apps Script code. You have also seen how to import data into Google Sheets using Apps Script services and public APIs. This is a very common operation in spreadsheet processing, and Apps Script enables you to import data from a wide range of sources. Finally, you've seen how you can use Apps Script services and JavaScript to read, process, and write spreadsheet data!

Did you find this codelab helpful?

Yes No

What you learned

  • How to import data from a Google spreadsheet.
  • How to create a custom menu in onOpen().
  • How to parse and manipulate string data values.
  • How to call public APIs using the UrlFetch service.
  • How to parse JSON object data that is retrieved from a public API source.

What's next

The next codelab in this playlist goes more in depth on how to format data within a spreadsheet.

Find the next codelab at Data formatting!