Fundamentals of Apps Script with Google Sheets #3: Working with Data

1. Introduction

Welcome to the third part of the Fundamentals of Apps Script with Google Sheets codelab playlist.

By completing this codelab, you can learn how to use data manipulation, custom menus, and public API data retrieval in Apps Script to improve your Sheets' experience. You'll continue working with the SpreadsheetApp, Spreadsheet, Sheet, and Range classes 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 with Google Sheets playlist. Before starting this codelab, be sure to complete the previous codelabs:

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

2. Set up

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

Create a spreadsheet in your Google Drive. You can do this from the Drive interface by selecting New > Google Sheets. This creates and opens your new spreadsheet. The file is saved to your Drive folder.
Click the spreadsheet title and change it from "Untitled spreadsheet" to "Data Manipulation and Custom Menus". Your sheet should look like this:

To open the script editor, click Extensions> Apps Script
Click the Apps Script project title and change it from "Untitled Project" to "Data Manipulation and Custom Menus." Click Rename 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.

3. Overview: Import data with a custom menu item

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. You can then add the menu to the UI so 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 quick way of running Apps Script functions 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 how Apps Script works. For 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.

4. The onOpen() function

Simple triggers in Apps Script provide a way of running specific Apps Script code in response to certain 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 for the event.

onOpen() is an example of a simple trigger. They're easy to set up—all you have to do is write an Apps Script function named onOpen() and Apps Script runs 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.

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();
}

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 the 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) method creates a connection between the menu item label and the Apps Script function that runs when the 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

Run this function now to see that it works:

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

After your spreadsheet reloads, the new Book-list menu should appear on your menu bar:

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

The next section creates the code for the loadBookList() function and introduces one way you can interact with data in Apps Script: reading other spreadsheets.

5. Import spreadsheet data

Now that you've created a custom menu, you can create functions that can be run from clicking the menu item.

Right now, the custom menu Book-list has one menu item: Load Book-list. The function called when you select 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 book data from another spreadsheet and copy it into this one:

Add the following code to your script under onOpen():

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // 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 already 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);
}

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 the previous codelabs introduced. With these 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 a reference to the current sheet object and stores it in the variable sheet. This is the sheet the data will be copied to.

2: Identify the source data

The next few lines establish four variables that refer to the source data you're 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 an easy way of making sure you get a range covering all the data in a sheet without including 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:

Sheet.getRange(row, column, numRows, numColumns) is used to identify where to copy the data in sheet.
The Range.getHeight() and Range.getWidth() methods are used to measure the size of the data and define a destination range of the same dimensions.
Range.setValues(values) copies the 2D array of bookListValues into the destination range, overwriting any data already there.

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 manipulation and custom menus.

6. Overview: Clean the spreadsheet data

You now have 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:

For some rows, the title and author are placed in the title column together, linked by a comma or the string " by ".
Some rows are missing the book's title or author.

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 searches for missing book information using an external API, and adds that information into your sheet.

7. Add menu items

You'll want to create three 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:

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 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();
}

Save your script project.
In the script editor, select onOpen from the functions list and click Run. This will run onOpen() to rebuild the spreadsheet menu so you don't have to reload the spreadsheet.

In this new code, the Menu.addSeparator() method creates a horizontal divider in the menu to 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, click the Book-list menu to view the new menu items:

Clicking these new items causes an error since you haven't implemented their corresponding functions, so let's do that next.

8. Split text on comma delimiters

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, datasets often have issues that can't be easily solved with Sheets' built-in functions. In these cases, you can write Apps Script code to do the complex operations needed to clean and organize your data.

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

The splitAtFirstComma() function should take the following steps:

Get the range representing the currently selected cells.
Check if cells in the range have a comma.
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 an "[authors], [title]" string pattern. You can also assume if multiple commas appear in the cell, it's appropriate to split on the first comma in the string.
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 JavaScript to manipulate the string data. Take the following steps:

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);
}

Save your script project.

Code review

Let's review the new code that 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 authors it finds in the title column.
titleAuthorValues is a 2D array of data extracted from titleAuthorRange using Range.getValues().

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

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

The cell string value is copied to the titlesAndAuthors variable.
The comma location is determined using the JavaScript String indexOf() method.
The JavaScript String slice() method is called twice to obtain the substring before the comma delimiter and the substring after the delimiter.
The substrings are copied back into the titleAuthorValues 2D array, overwriting the existing values at that position. Since we're assuming an "[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 the row unchanged.

3: Copy the new values back into the sheet

Once all the title cell values are 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 Sheets data. Next, you'll implement the second splitter function.

9. Split text on "by" delimiters

Looking 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 from the last section, creating a function called splitAtLastBy(). This function has a similar job to splitAtFirstComma()—the only real difference is it's searching for a slightly different pattern of text. Implement this function by doing the following:

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 last instance of the string " by ", if present.
 */
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 a "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
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

Save your script project.

Code review

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

The substring " by " is used as a string delimiter, instead of ", ".
Here the JavaScript String.lastIndexOf(substring) method is used instead of String.indexOf(substring). This means if there are multiple " by " substrings in the initial string, all but the last " by " are assumed to be part of the title.
After splitting the string, the first substring is set as the title and the second as the author (this is the opposite order from 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 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 blank cells with data drawn from a public API.

10. Overview: Get data from public APIs

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

You can't get the missing data by using string operations on the data you currently have. Instead, you'll need to get the missing data from another source. You can do this in Apps Script by requesting information from external APIs that can provide additional data.

APIs are application programming interfaces. It's a general term, but it's basically a service your programs and scripts can call to request information or to take certain actions. In this section, you're calling a publicly available API to request book information you can insert 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 it to your spreadsheet.

11. Fetch external data with UrlFetch

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

Our helper function, fetchBookData_(ISBN), takes a 13-digit ISBN number of a book as a parameter and returns data about that book. It connects to and retrieves information from the Open Library API and then parses the returned JSON object.

Implementation

Implement this helper function by doing the following:

In the Apps Script editor, add the following code to the end of your script:

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @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];
}

Save your script project.

Code review

This code is divided into two main sections:

1: The 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 URL Fetch 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're requesting and how to structure the response. In this case, you provide the book's ISBN number and ask for detailed information to be returned in JSON format.

Once you've built the URL string, the code sends a request to the location and receives a response. This is done with the UrlFetchApp.fetch(url, params) method. It 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 to true. This setting means your code won't halt if the request results in an API error. Instead, the error response is returned.

The request returns an HTTPResponse object that's stored in the response variable. HTTP responses include a response code, HTTP headers, and the main response content. The information of interest here's 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 code, the HTTPResponse.getContentText() method returns the main content of the response as a string. This string is in JSON format, but the Open Library API defines the exact content and format. The JSON.parse(jsonString) method converts the JSON string to a JavaScript object so different parts of the data can be easily extracted. Finally, the function returns the data corresponding to the book's ISBN number.

Results

Now that you've implemented fetchBookData_(ISBN), other functions in your code can find information for any book using its ISBN number. You'll use this function to help fill in the cells in your spreadsheet.

12. Write API data to a spreadsheet

You can now implement a fillInTheBlanks() function that does the following:

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

Implementation

Implement this new function by doing the following:

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 existing 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're 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.
      if (!bookData || !bookData.details) {
        continue;
      }

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

      // The API might not return an author name, so only fill it in
      // if the response has one and if 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; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

Save your script project.

Code review

This code is divided into three sections:

1: Read the existing book information

The first three lines of the function define constants to help make the code more 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 write these values back to the spreadsheet.

2: Fetch missing information using the helper function

The code loops over each row in bookValues to find missing titles or authors. To reduce the number of API calls while improving efficiency, the code only calls the API if the following are true:

The row's ISBN column has a value.
Either the title or author cell in the row is empty.

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

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

To make sure your code can handle API errors, the code must check the API response is valid before attempting to use it. Once the code has bookData, it conducts a simple check to verify bookData and bookData.details exist before attempting to read from them. If either is missing, it means the API didn't have the data you wanted. In this 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: Write updated information back into the sheet

The last part of the code has similar checks to verify the API returned title and author information. The code only updates the bookValues array if the original title or author cell is 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:

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

13. Conclusion

Congrats on completing this codelab. You've learned how to create custom menus to activate different parts of your Apps Script code. You've also seen how to import data into Google Sheets using Apps Script services and public APIs. This is a common operation in spreadsheet processing, and Apps Script allows 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 insert spreadsheet data.

Did you find this codelab helpful?

Yes

What you learned

How to import data from a Google spreadsheet.
How to create a custom menu in the onOpen() function.
How to parse and manipulate string data values.
How to call public APIs using the URL Fetch Service.
How to parse JSON object data retrieved from a public API source.

Key concepts:

onOpen() is a simple trigger used to create menu items. Simple triggers have various restrictions you must adhere to.
When reading or writing spreadsheet cell data, it's best practice to read or write an entire range at once with Range.getValues() or Range.setValues(values). Don't read or set each cell individually, as it will be slower.
Apps Script function names ending in _ (an underscore) are considered private. Other scripts can't call these functions when present in a library, or by clients through server-client communication. If you know a function is only going to be used by the current script, it's best practice to end a function's name with _.
When using data returned by external APIs, validate the data before attempting to use it. This reduces errors in your code if the API returns an unexpected result.

Key terms:

Active (status): Indicates the specified spreadsheet, sheet, range, or cell is the one the user of the spreadsheet is currently viewing or highlighting.
Active cell: The single highlighted cell within the active sheet with the cursor focus.
Active range: A group of one or more cells currently highlighted within the active sheet.
API: An application programming interface is a service that programs or scripts can call to request information or to take certain actions.
Authorization: The process of the user granting permissions to allow Apps Script to access user data in the relevant Google services or otherwise take actions on behalf of the user.
Container-bound script: Any script that is bound to and created from a Google Workspace document, such as a Google Sheet or Google Doc.
Helper function: A function, usually private, that's called by another to complete a small subtask. Helper functions are used when the subtask needs to be completed many times in different places.
JSON: File format for defining data objects and their characteristics from text.
onOpen(): If defined in a script, it's a simple trigger that fires whenever a file opens or reloads.
Private functions: A function that can't be called as part of a library or by clients through server-client communication. Apps Script private functions have names that end in _.
Range**: A group of one or more adjacent cells within a sheet. The Range** class gives you the ability to read and update ranges within a sheet.
Script editor: The in-browser code editor for Apps Script.
Simple triggers: A subtype of triggers in Apps Script with reserved trigger function names and certain restrictions.
Sheet**: A single page of a spreadsheet. Sometimes used to refer to a Google Sheets file within Google Drive. The Sheet** class lets you access and modify sheets.
SpreadsheetApp: This class serves as the parent class for the Spreadsheet service and provides a starting point for code that reads or manipulates Google Sheets data.
Spreadsheet**: A Google Sheets file that resides within Google Drive. The Spreadsheet** class allows you to access and modify spreadsheets.
Spreadsheet service: An Apps Script service that allows scripts to create, access, and modify Google Sheets files.
Triggers: An event keyed to an Apps Script function. When the event (such as a spreadsheet being opened) occurs, the trigger "fires" and automatically executes the associated Apps Script function.
URL Fetch service: The Apps Script service that lets scripts connect to URL endpoints to make requests and receive responses.

What's next

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

Find the next codelab at Data formatting.