What you'll need

The exercises in this codelab require a spreadsheet with some data. Follow the steps to create a new spreadsheet that you can use:

Create a new spreadsheet in your 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.

Click the spreadsheet title and change it from "Untitled spreadsheet" to "Data Manipulation and Custom Menus". Your spreadsheet should look like this:

Select Tools > Script Editor to open the script editor.

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 codelab! Move to the next section to start learning about custom menus.

Note: If you're using a Gmail account, then you might see a dialog box that says, "This app isn't verified." Google warns users who may be using code from unknown or untrusted authors, but it's okay to proceed because you are the script author. In this case, authorize the script by doing the following:

In the "This app isn't verified" dialog box, click Advanced.

Click Go to Data Manipulation and Custom Menus (unsafe).

In the next screen, click Allow.

Throughout this codelab, you may see several permission prompts. Follow the on-screen prompts to continue authorizing the code. For more information, see Authorization for Google Services.

Apps Script gives you the ability to define custom menus that can appear in Sheets. You can also use custom menus in Docs, Slides, and 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 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() {
/* ... */
}

Key concept: Simple triggers are easy to set up and use, but have a number of restrictions. Because of these limitations, onOpen() is only used to create menu items.

Triggers are a complex topic and, as such, are beyond the scope of this codelab. For now, you can think of onOpen() as an event handler that adds menu items whenever you load a spreadsheet.

Implementation

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

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

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

Run the function to verify that it works! Do the following:

In Sheets, reload your spreadsheet.

Note: That usually closes the tab with your script editor.

Reopen your script editor by selecting Tools > Script editor.

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

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

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

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

Right now, the custom menu Book-list has one menu item: Load Book-list. However, 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 causes an error:

You can fix the 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:

Code review

So how does the function work? The loadBookList() function primarily uses methods from Class Spreadsheet, Class Sheet, and Class Range. 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 spreadsheet object in the variable sheet. That is where the data will be copied to.

2. Identify the source data.

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

bookSS stores a reference to the spreadsheet that the code is reading data from. The code finds the spreadsheet by its spreadsheet ID. This example provides 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 that 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:

Results

You can see the function in action! In 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 to modify and update the data in the book list by using string manipulations and custom menus.s

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 the raw data:

In 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 information for their book's titles and authors.

In the next sections, you'll correct those 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 by using an external API, and fills that information in your sheet.

You need to create three new menu items to control the data-cleaning operations that you'll implement.

Implementation

Update onOpen() to include the extra menu items that you need. Do 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();
}

Save your script project.

In the script editor, select onOpen in the taskbar drop-down and then click the button. That will run onOpen() to rebuild the spreadsheet menu so that you don't have to reload the spreadsheet.

In the new code, the Menu.addSeparator() method creates a horizontal divider in the drop-down menu so that 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.

Note: The menu items that you add will appear in Sheets in the same order that you add them in the onOpen() code.

Results

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

Clicking on those items now will cause an error because you haven't implemented their corresponding functions yet, so do that next.

The dataset that 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. Sheets provides a SPLIT() function that divides strings into columns. However, datasets often have issues that can't be easily solved with Sheets functions. In those 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:

Get the range representing the currently selected cells.

Check if cells in that 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 a "[authors], [title]" string pattern. You can also assume that if multiple commas appear in the cell, then 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 those steps, you'll use the Spreadsheet Service in Apps Script, but you'll also need to use some simple 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:

Code review

Review the new code, which 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, you 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:

The JavaScript String slice() method is used twice to obtain the substring prior to the comma delimiter and the substring after the delimiter.

The substrings are copied into the titleAuthorValues 2D array, overwriting the existing values at that position. Given that 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 into the sheet.

After 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.

Key Point: The code could have updated each spreadsheet cell individually as it found each comma, calling Range.setValue(value) every time a cell needed to be changed. However, that would result in much slower code, as each Range.setValue(value) call requires server communication.

When updating Sheets data, it is almost always better to get the values into a 2D array first, update the array, and then push the data back to the sheet with a single Range.setValues(values) call. That way, you minimize the amount of server communication your code has to do, speeding your code up.

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.

Note: You can select any column of text in your spreadsheet, and use the function to look for commas and split the text according to the rules you set. Be aware that if the code finds a comma delimiter, then it will overwrite the cell to the immediate right of the original.

You built an Apps Script function that processes 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 the problem by using the same technique used in the last section, creating a new function called splitAtLastBy(). The 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 the function by doing the following:

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

After splitting the string, the first substring is set as the title and the second as the author string (that 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 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 the dataset by filling in the blank cells with data drawn from a public API.

So far, you refined your dataset to fix some title and author formatting issues, but it's still missing some information highlighted in the following screenshot:

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

APIs are application programming interfaces. It's a pretty general term, but it basically means that someone has provided a service that your programs and scripts can call to do specific things, such as 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 for requesting book information from the public Open Library API.

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

Implementation

Implement the helper function by doing the following:

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

Note: In Apps Script, functions that have names ending in "_" are considered private. Those functions can't be called by other scripts if present in a library or by clients during server-client communication. Those are advanced topics though, so for now know that it's a best practice to end a function's name in "_" if it's a helper function that only the current script will ever need to use.

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 by using the URL endpoint of the API and the URL FetchService of Apps Script.

The url variable is simply 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), which 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.

Note: Each public API defines its own formats for making requests and returning results. For your code to communicate effectively with an API, you must adhere to the formats it defines. For the Open Library, the request and response formats are described in the Open LIbrary Books API documentation. The response format here is JSON (JavaScript Object Notation), which is simply a particular way of organizing data. JSON is similar to HTML or XML, but tends to be easier for people to read.

After you build the URL string, the code sends a request to that location and receives a response. That is done with the UrlFetchApp.fetch(url, params) method. The method sends an information request to the external URL that 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. The setting simply means that 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. Parsing the API response and returning 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 implemented fetchBookData_(ISBN), other functions in your code can quickly look up information for any book by using it's ISBN number. You'll use that function to fill in the blanks in your spreadsheet.

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

Update the missing title and/or author values in their respective cells.

Implementation

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

Save your script project.

Code review

The 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 readable for people. 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 those values back to the spreadsheet.

Note: This arrangement is different from what you used for splitAtFirstComma() and splitAtLastBy(). In those functions, the code only examined the currently highlighted titles and their rows. Here, you are examining the entire sheet using Spreadsheet.getDataRange().

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:

The row has a value in the ISBN column (that is, the book's ISBN is known).

Either the title or author cell in that row is empty.

If the conditions are all true, then the code calls the API using the fetchBookData_(isbn) helper function that you implemented and stores the result in the bookData variable. The 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 your 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, then 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 to verify that bookData and bookData.details exist before attempting to read from them. If either is missing, then 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 that 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 that 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, then 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 then select Book-list > Separate title/author at last "by" to clean the "by" problems.

Select Book-list > Fill in blank titles and author cells to have your code fill in all the remaining cells as demonstrated in the following image:

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

Did you find this codelab helpful?

What you learned

How to parse JSON object data that is retrieved from a public API source

Key concepts:

onOpen() is a simple trigger that is used to create menu items. Simple triggers have a variety of restrictions that you must adhere to.

When reading or writing spreadsheet cell data, it's best 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 that will be a lot slower.

Apps Script functions that have names ending with "_" are considered private. Those functions can't be called by other scripts if included as part of a library or by clients through client-server communication. If you know a function is only going to be used by the script that it's in, then it's usually a good idea to enter "_" at the end of its name.

When using data returned by external APIs, check that the data returned is valid before attempting to use it. That will make your code less likely to have errors if the API returns an unexpected result.

Key terms:

Active (status): Indicates that the specified spreadsheet, sheet, range, or cell is the one currently being viewed or highlighted by the user of the spreadsheet

Active cell: The single highlighted cell within the active sheet that has the cursor focus

Active range: The group of one or more cells currently highlighted within the active sheet

API: Application programming interface; a service that programs or scripts can contact to retrieve information or take specific actions.

Authorization: The process of the user granting permissions to allow Apps Script to access private data in the relevant Google services.

Container-bound Scripts: Any script that is bound to and created from a document in G Suite, such as one in Sheets or Docs

Helper function: A function, usually private, that is called by another to complete a small subtask, usually used when the subtask needs to be done many times in different places

JSON: File format for defining data objects and their characteristics from text

onOpen(): When defined in a script, a simple trigger that fires whenever a file opens or reloads