Fundamentals of Apps Script with Google Sheets #4: Data Formatting

1. Introduction

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

By completing this codelab, you can learn how to format your spreadsheet data in Apps Script, and write functions to create organized spreadsheets full of formatted data fetched from a public API.

What you'll learn

How to apply various Google Sheets formatting operations in Apps Script.
How to transform a list of JSON objects and their attributes into an organized sheet of data with Apps Script.

Before you begin

This is the fourth 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

Before you continue, you need a spreadsheet with some data. As before, we've provided a data sheet you can copy for these exercises. Take the following steps:

Click this link to copy the data sheet and then click Make a copy. The new spreadsheet is placed in your Google Drive folder and named "Copy of Data Formatting".
Click the spreadsheet title and change it from "Copy of Data Formatting" to "Data Formatting". Your sheet should look like this, with some basic information about the first three Star Wars films:

Select Extensions > Apps Script to open the script editor.
Click the Apps Script project title and change it from "Untitled project" to "Data Formatting." Click Rename to save the title change.

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

3. Create a custom menu

You can apply several basic formatting methods in Apps Script to your Sheets. The following exercises demonstrate a few ways of formatting data. To help control your formatting actions, let's create a custom menu with the items you'll need. The process for creating custom menus was described in the Working with data codelab, but we'll summarize it here again.

Implementation

Let's 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 spreadsheet's user-interface 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 from the functions list and click Run. This runs onOpen() to rebuild the spreadsheet menu, so you don't have to reload the spreadsheet.

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 lines create a menu (Quick formats), add menu items (Format row header, Format column header, and Format dataset) to the 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 an Apps Script function that runs when the 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, click the Quick formats menu to view the new menu items:

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

4. Format a header row

Datasets in spreadsheets often have header rows to identify the data in each column. It's a good idea to format header rows to visually separate them 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'll format a header row from scratch using Apps Script. The header row you'll create 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 same Spreadsheet service methods you've used before, but now you'll also use some of the service's formatting methods. Take the following steps:

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

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

Save your script project.

Code review

Like many formatting tasks, the Apps Script code 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 our example, it's column E (url).

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, we use method chaining to call each formatting method one after the other:

Range.setFontWeight(fontWeight) is used to set the font weight to bold.
Range.setFontColor(color) is used to set the font color to white.
Range.setBackground(color) is used to set the background color to a dark blue-green.
setBorder(top, left, bottom, right, vertical, horizontal, color, style) puts a solid black border around the range's cells.

The last method has several parameters, so let's review what each is doing. The first four parameters here (all set to true) tell Apps Script 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 within the selected range. The seventh parameter (null) indicates the color of the border should default to black. Finally, the last parameter specifies 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, save your script project in the Apps Script editor.
Click the Quick formats > Format row header menu item.

The results should look like the following:

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

5. Format a column header

If you can make a personalized row header, you can make a column header too. Column headers increase the readability for certain datasets. For example, the titles column in this 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. Once you've added these hyperlinks, you can remove the url column to help clean up the sheet.

Next you'll implement a formatColumnHeader() function to apply these 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, you 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:

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

Add the following helper functions to the end of your script project, after the formatColumnHeader() function:

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

Save your script project.

Code review

Let's review the code in each of these three functions separately:

`formatColumnHeader()`

As you've probably come to expect, the first few lines of this function set variables that reference the sheet and range we're interested in:

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 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, just like in formatRowHeader(). Here, Range.setFontStyle(fontStyle) is also used to make the text italicized.

Adding the hyperlinks to the header column is more complex, so formatColumnHeader() calls hyperlinkColumnHeaders_(headerRange, numRows) to take care of the task. This helps keep the code tidy and readable.

`hyperlinkColumnHeaders_(headerRange, numRows)`

This helper 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, 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).

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 inserted into the sheet using Range.setValues(values).

Finally, to help keep the sheet clean and to eliminate redundant information, Sheet.deleteColumn(columnPosition) is called to remove the url column.

`columnIndexOf_(colName)`

This helper function is just 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 reviews each name in order. If it finds one that matches the name being searched for, it stops and returns the column's index. If it reaches the end of the name list without finding the name, it returns -1 to signal 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 now automated another formatting task. With the column and row headers formatted, the next section shows how to format the data.

6. Format your dataset

Now that you have headers, let's make a function that formats the rest of the data in your sheet. We'll use the following formatting options:

Alternating row background colors (known as banding)
Changing date formats
Applying borders
Autosizing all columns and rows

You'll now create a function formatDataset() and an extra helper method to apply these formats to your sheet data.

Implementation

As before, 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:

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

Add the following helper function at the end of your script project, after the formatDataset() function:

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the

Fundamentals of Apps Script with Google Sheets #4: Data Formatting Stay organized with collections Save and categorize content based on your preferences.

1. Introduction

What you'll learn

Before you begin

What you'll need

2. Set up

3. Create a custom menu

Implementation

Code review

Results

4. Format a header row

Implementation

Code review

Results

5. Format a column header

Implementation

Code review

formatColumnHeader()

hyperlinkColumnHeaders_(headerRange, numRows)

columnIndexOf_(colName)

Results

6. Format your dataset

Implementation

Fundamentals of Apps Script with Google Sheets #4: Data Formatting

`formatColumnHeader()`

`hyperlinkColumnHeaders_(headerRange, numRows)`

`columnIndexOf_(colName)`