Click the spreadsheet title and change it from "Copy of Data Formatting" to "Data Formatting." Your sheet should look like the following image, with some basic information about the first three Star Wars films:

Select Tools > Script Editor to open the script editor.

Click the "Untitled project" title and change it to "Data Formatting."

Click OK to save the title change.

With a new spreadsheet and project, you're ready to start the codelab! Move to the next section to start learning about basic formatting in Apps Script.

You can apply several basic formatting methods in Apps Script to Sheets. The following exercises demonstrate a few ways of formatting data. To help control your formatting actions, create a custom menu with the items that you'll need.

Implementation

Create a custom menu.

In the Apps Script editor, replace the code in your script project with the following:

/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the Ui object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addToUi();
}

Save your script project.

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

Code review

Review the 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 that the script is bound to.

The next lines create a new menu (Quick formats), add menu items (Format row header, Format column header, and Format dataset) to that menu, and add the menu to the spreadsheet's interface. That 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. For example, selecting the Format row header menu item causes Sheets to attempt to run the formatRowHeader() function, which doesn't exist yet.

Results

In your spreadsheet, you can click the Quick formats menu to see the new menu items.

Clicking on any of those items now will cause an error because you haven't yet implemented their corresponding functions. The next sections cover the implementation of those functions.

Datasets in spreadsheets often have header rows that identify the data in each column. It's usually a good idea to format header rows to separate them visually from the rest of the data in the spreadsheet.

In the first codelab, you built a macro for your header and adjusted its code. Here, you will format a header row from scratch using Apps Script. The header row you'll create here will bold the header text, color the background a dark blue-green, color the text white, and add some solid borderlines.

Implementation

To implement the formatting operation, you'll use the Spreadsheet Service in Apps Script. Take the following steps:

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

Code review

Like many formatting tasks, the Apps Script code needed to implement it is straightforward.

The first two lines use methods you've seen before to get a reference to the current active sheet (sheet) and the top row of the sheet (headerRange). The Sheet.getRange(row, column, numRows, numColumns) method specifies the top row, including only those columns with data in them. The Sheet.getLastColumn() method returns the column index of the last column that contains data in the sheet. In your example, that's column E (url).

Note: This assumes that the header row that you want to format is always in row 1, and that your data starts in A1 and extends for some number of contiguous columns. That's fine for this exercise, but generally you want to avoid those assumptions by allowing the user to specify exactly which cells to format (in case the structure of their spreadsheet differs).

The rest of the code simply calls various Range methods to apply formatting choices to all cells in headerRange. To keep the code easy to read, you use method chaining to call each of the following formatting methods:

The first four parameter choices (all set to true) tell Apps Script that the border should be added above, below, and to the left and right of the range.

The fifth and sixth parameters (null and null) direct Apps Script to avoid changing any border lines that happen to be within the selected range.

The seventh parameter (null) indicates that the color of the border should default to black.

Finally, the last parameter indicates the type of border style to use, taken from the options provided by SpreadsheetApp.BorderStyle.

Results

You can see your formatting function in action by doing the following:

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

Click the Quick formats > Format row header menu item.

Note: If you're using a Gmail account, then you might see an "This app isn't verified" dialog box when you first use your script. Google uses this to warn users who may be using code from unknown or untrusted authors. If you see this dialog, it's okay to proceed because you are the script author. Continue authorizing the script by doing the following:

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

Click Go to Data Formatting (unsafe).

In the next screen, click Allow.

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

The results should look like the following image:

You automated a formatting task! The next section applies the same technique to create a different format style for column headers.

If you can make a personalized row header, then you can make a column header, too! Column headers increase the readability for certain datasets. For example, the titles column in the spreadsheet can be enhanced with the following format choices:

Bolding the text

Italicizing the text

Adding cell borders

Inserting hyperlinks using the URL column contents (After you add these hyperlinks, you can actually remove the URL column to clean up the sheet.)

Now, you'll implement a formatColumnHeader() function to apply those changes to the first column in the sheet! To help make the code a bit easier to read, you'll also implement two helper functions.

Implementation

As before, here you only need to add a function to automate the column header formatting. Take the following steps:

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

Code review

formatColumnHeader()

The first few lines of this function set variables that reference the sheet and range:

The active sheet is stored in sheet.

The number of rows in the column header is calculated and saved in numRows. (Here the code subtracts one so that the row count doesn't include the column header: title.)

The range covering the column header is stored in columnHeaderRange.

The code then applies the borders and bolding to the column header range, similar to formatRowHeader(). Here, Range.setFontStyle(fontStyle) is also used to make the text italicized.

Getting the hyperlinks added to the header column is more complex, so formatColumnHeader() calls hyperlinkColumnHeaders_(headerRange, numRows) to take care of that task. That helps with keeping the code tidy and readable.

hyperlinkColumnHeaders_(headerRange, numRows)

This function first identifies the column indices of the header (assumed to be index 1) and the URL column. It calls columnIndexOf_('URL') to get the URL column index. If a URL column isn't found, then the method exits without modifying any data.

The function gets a new range (URLRange) that covers the URLs corresponding to the header column rows. This is done with the Range.offset(rowOffset, columnOffset) method, which guarantees the two ranges will be the same size. The values in both the headerColumn and the URL column are then retrieved (headerValues and urlValues, respectively).

The function then loops over each column header cell value and replaces it with a =HYPERLINK() Sheets formula constructed with the header and URL column contents. The modified header values are then pushed to the sheet using Range.setValues(values).

columnIndexOf_(colName)

This helper function is a simple utility function that searches the first row of the sheet for a specific name. The first three lines use methods you've already seen to get a list of column header names from row 1 of the spreadsheet. These names are stored in the variable columnNames.

The function then looks at each name in order. If it finds one that matches the name being searched for, it stops and returns that column's index. If it reaches the end of the name list without finding the name, it returns -1 to signal that the name wasn't found.

Results

You can see your formatting function in action by doing the following:

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

Click the Quick formats > Format column header menu item.

The results should look like the following:

You've automated another formatting task! Now that the column and row headers are formatted, the next section will show how to format the data itself.

Now that you have headers, you can make a function that formats the rest of the data in your sheet. Use the following formatting options:

Alternating row background colors (known as banding)

Changing date formats

Applying borders

Autosizing all columns and rows

You will now create a function formatDataset() and an additional helper method that can apply these formats to your sheet data.

Implementation

As before, here you just need to add a function to automate the data formatting. Take the following steps:

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

The next step calls the formatDates_(colIndex) function to format the dates in the column labeled 'release_date.' The column is specified using the columnIndexOf_(colName) helper function that you implemented earlier.

formatDates_(colIndex)

The helper function applies a specific date format to a column indicated by the provided column index. Specifically, it formats date values as "Month Day, Year (Day of Week)".

Note: There are a lot of different date and number formats you can use in your scripts and spreadsheets. For more information, see Date and Number Formats.

First, the function checks to see if the provided column index is valid (that is, 0 or greater). If not, it returns without doing anything else. That check prevents errors that might be caused if, for example, the sheet didn't have a 'release_date' column.

After the column index is validated, the function gets the range covering that column (excluding its header row) and uses Range.setNumberFormat(numberFormat) to apply the formatting.

Results

You can see your formatting function in action by doing the following:

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

Click the Quick formats > Format dataset menu item.

The results should look like this:

You automated yet another formatting task! Now that you have these formatting commands available, add a bunch more data to apply them to!

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

In this exercise, you will use the public Star Wars API (SWAPI) to retrieve information to fill your spreadsheet. Specifically, you'll use the API to get lots of information about the major characters that appear in each of the original three Star Wars films. Your code will call the API to get a large amount of JSON data, parse the response, place the data in a new sheet, and then format that sheet.

Implementation

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

Select onOpen in the taskbar drop-down and then click the button. This will run onOpen() to rebuild the spreadsheet menu with the new options you added.

Select File > New > Script file from the editor's menu. Name the new script "API" and click OK.

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

/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode IV.
*/
function createPeopleSheetIV() {
createResourceSheet_('characters', 1, "IV");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode V.
*/
function createPeopleSheetV() {
createResourceSheet_('characters', 2, "V");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode VI.
*/
function createPeopleSheetVI() {
createResourceSheet_('characters', 3, "VI");
}
/**
* Creates a new formatted sheet filled with user-specified
* information from the Star Wars API. If the sheet with this data
* already exists, the sheet is overwritten with the API
* information.
*
* @param {string} resourceType The type of resource.
* @param {number} idNumber The identification number of the film.
* @param {number} episodeNumber The Star Wars episode number
* of the film. This is only used in the sheet name.
*/
function createResourceSheet_(
resourceType, idNumber, episodeNumber) {
// Fetch the basic film data from the API.
var filmData = fetchApiResourceObject_(
"https://swapi.co/api/films/" + idNumber);
// Extract the API URLs for each resource so the code can
// call the API to get more information about each individually.
var resourceUrls = filmData[resourceType];
// Fetch each resource from the API individually and push
// them into a new object list.
var resourceDataList = [];
for(var i = 0; i < resourceUrls.length; i++){
resourceDataList.push(
fetchApiResourceObject_(resourceUrls[i])
);
}
// Get the keys used to reference each part of data within
// the resources. The keys are assumed to be identical for
// each object since they are all the same resource type.
var resourceObjectKeys = Object.keys(resourceDataList[0]);
// Create the new sheet with an appropriate name. It
// automatically becomes the active sheet when it is created.
var resourceSheet = createNewSheet_(
"Episode " + episodeNumber + " " + resourceType);
// Add the API data to the new sheet, using each object
// key as a column header.
fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
// Format the new sheet using the same styles the
// 'Quick Formats' menu items apply. These methods all
// act on the active sheet, which is the one just created.
formatRowHeader();
formatColumnHeader();
formatDataset();
}

Now you need to add the helper functions. Add the following code to the end of the API.gs script project file:

Code review

You added quite a lot of code! Go over each function that you added to understand how they work:

onOpen()

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

Wrapper functions

All of the menu items you added are doing the same thing—trying to create a new sheet with data pulled from SWAPI. The only difference is that they are each focusing on a different film.

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

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

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

Note: If you want, you can create more wrapper functions to create different types of sheets! You only need to know what resource type to ask for and what film ID to use. Example resource types include 'planets' and 'starships,' and the API accepts the numbers one through seven as film IDs.

createResourceSheet_(resourceType, idNumber, episodeNumber)

This is the main sheet builder function for this exercise. With the assistance of some helper functions, it gets the API data, parses it, creates a new sheet, writes the API data to that sheet, and formats the sheet using the functions you constructed in the previous sections.

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

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

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

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

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

Finally, all the formatting functions you built previously are called to apply the same formatting rules to the new data. Because the new sheet is the active one, the code can reuse the functions without modification!

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

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

First, the function calculates the number of rows and columns needed to present the new API data. That is simply the size of the resource and keys list, respectively.

The function then defines an output range (resourceRange) where the data will be placed, adding an additional row to hold the column headers. The variable resourceValues holds a 2D values array extracted from resourceRange.

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

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

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

Results

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

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

You wrote code that imports data into Sheets and automatically formats it! Great job!

Congrats! You completed this codelab. You saw some of the Sheets formatting options that you can include in your Apps Script projects, and built an impressive application that imports and formats a large set of API data.