Google スプレッドシートによる Apps Script の基礎 #4: データ形式

1. はじめに

「Apps の基礎(Google スプレッドシートを使用)」の Codelab の第 4 部へようこそ。

この Codelab を完了すると、Apps Script でスプレッドシート データを書式設定する方法と、公開 API から取得した書式設定されたデータを含んだ整理スプレッドシートを作成する方法を学びます。

学習内容

  • Apps Script でさまざまな Google スプレッドシートの書式設定オペレーションを適用する方法
  • Apps Script を使用して JSON オブジェクトとその属性のリストをデータ体系に分割する方法。

始める前に

これは「Google スプレッドシートを使用するアプリ スクリプトの基礎」再生リストの 4 番目の Codelab です。この Codelab を開始する前に、前の Codelab を完了してください。

  1. マクロとカスタム関数
  2. スプレッドシート、スプレッドシート、範囲
  3. データの操作

必要なもの

  • この再生リストの前の Codelab で説明した Apps Script の基本的なトピックについての理解。
  • Apps Script エディタの基本的な知識
  • Google スプレッドシートの基本事項
  • スプレッドシートの A1 表記を読む機能
  • JavaScript とその String クラスに関する基本的な知識

2. 設定

続行する前に、データを含むスプレッドシートが必要です。すでに実施したように、これらの演習ではデータシートをコピーできます。次の手順を行います。

  1. こちらのリンクをクリックしてデータシートをコピーし、[コピーを作成] をクリックします。新しいスプレッドシートは Google ドライブ フォルダに保存され、「データ形式のコピー」という名前が付けられます。
  2. スプレッドシートのタイトルをクリックして「Copy of Data Formatting」から「Data Formatting」に変更します。最初の 3 本のスター映画については、シートに以下のような情報が表示されます。

c4f49788ed82502b.png

  1. [拡張機能] > [Apps Script] の順に選択して、スクリプト エディタを開きます。
  2. Apps Script プロジェクトのタイトルをクリックし、「無題のプロジェクト」から「データ形式」に変更します。[名前を変更] をクリックしてタイトルの変更を保存します。

このスプレッドシートとプロジェクトを使用して、Codelab を開始できます。次のセクションに進み、Apps Script の基本的な形式について確認しましょう。

3. カスタム メニューを作成する

Apps Script の基本的な書式をいくつかの方法でスプレッドシートに適用できます。次の演習では、データを書式設定する方法をいくつか説明します。書式設定の操作を制御するため、必要な項目を含むカスタム メニューを作成しましょう。カスタム メニューの作成プロセスは、Codelab のデータ操作で説明されていますが、ここではもう一度まとめます。

実装

カスタム メニューを作成しましょう。

  1. Apps Script エディタで、スクリプト プロジェクトのコードを次のコードに置き換えます。
/**
 * 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();
}
  1. スクリプト プロジェクトを保存します。
  2. スクリプト エディタで、関数のリストから onOpen を選択し、[実行] をクリックします。これにより onOpen() が実行され、スプレッドシートのメニューが再作成されるため、スプレッドシートを再読み込みする必要はありません。

コードレビュー

このコードを確認して仕組みを理解しましょう。onOpen() の 1 行目では、getUi() メソッドを使用して、このスクリプトがバインドされているアクティブなスプレッドシートのユーザー インターフェースを表す Ui オブジェクトを取得しています。

次の行でメニュー(Quick formats)を作成し、メニュー項目(Format row headerFormat column headerFormat dataset)をメニューに追加してから、スプレッドシートのインターフェースにメニューを追加します。これは、それぞれ createMenu(caption)addItem(caption, functionName)addToUi() メソッドで行います。

addItem(caption, functionName) メソッドは、メニュー項目のラベルと、メニュー項目が選択されたときに実行される Apps Script 関数の間の接続を作成します。たとえば、Format row header メニュー項目を選択すると、スプレッドシートで formatRowHeader() 関数の実行が試みられます(この機能はまだ存在しません)。

Results

スプレッドシートで Quick formats メニューをクリックし、新しいメニュー項目を表示します。

1D639a41f3104864.png

これらの項目をクリックすると、対応する関数が実装されていないためエラーが発生します。次に、この操作を行います。

4. ヘッダー行の形式を設定する

スプレッドシートのデータセットには、多くの場合、各列のデータを識別するヘッダー行が含まれています。ヘッダー行の形式を設定して、スプレッドシート内の他のデータと視覚的に区別することをおすすめします。

最初の Codelab では、ヘッダー用のマクロを作成し、コードを調整しました。ここでは、Apps Script を使用してヘッダー行をゼロからフォーマットします。作成するヘッダー行では、ヘッダー テキストを太字にし、背景を濃い青緑色にし、テキストを白色にし、単色の枠線を追加します。

実装

書式設定の操作を実装するには、これまでと同じスプレッドシート サービス メソッドを使用しますが、今後は一部のサービスの書式設定メソッドも使用します。次の手順を行います。

  1. Apps Script エディタで、スクリプト プロジェクトの末尾に次の関数を追加します。
/**
 * 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);

}
  1. スクリプト プロジェクトを保存します。

コードレビュー

多くの書式設定タスクと同様に、Apps Script も簡単に導入できます。最初の 2 行では、これまでに確認したメソッドを使用して、現在のアクティブ シート(sheet)と、そのスプレッドシートの一番上の行(headerRange))への参照を取得します。Sheet.getRange(row, column, numRows, numColumns) メソッドは、データを含む行のみを含め、一番上の行を指定します。Sheet.getLastColumn() メソッドは、シート内のデータを含む最後の列の列インデックスを返します。この例では、列 E(url)です。

コードの残りの部分では、さまざまな Range メソッドを呼び出して、headerRange 内のすべてのセルに書式設定の選択肢を適用します。コードを読みやすくするために、メソッド チェーンを使用して各書式設定メソッドを 1 つずつ呼び出しています。

最後のメソッドには複数のパラメータがあるため、それぞれが何をしているか確認しましょう。最初の 4 つのパラメータ(すべて true に設定)は、境界線の上下左右に境界線を追加するように Apps Script に指示します。5 番目と 6 番目のパラメータ(nullnull)は、選択した範囲内の枠線を変更しないよう Apps Script に指示します。7 番目のパラメータ(null)は、枠線の色をデフォルトで黒にする必要があります。最後に、使用する枠線のスタイルのタイプを最後のパラメータで指定します(SpreadsheetApp.BorderStyle のオプションから取得)。

Results

次の手順でフォーマット関数の動作を確認できます。

  1. スクリプト プロジェクトをまだ Apps Script エディタに保存していない場合は、保存します。
  2. [クイック フォーマット] メニュー項目をクリックします。

結果は次のようになります。

a1a63770c2c3becc.gif

書式設定タスクを自動化しました。次のセクションでは、同じ手法を適用し、列見出しに異なる書式スタイルを作成します。

5. 列見出しの形式

カスタマイズした行見出しを作成できる場合は、列見出しも作成できます。列見出しを使用すると、特定のデータセットが読みやすくなります。たとえば、このスプレッドシートの [タイトル] 列は、次の形式を選択することで拡張できます。

  • 太字機能
  • テキストを斜体にする
  • セルの枠線の追加
  • url 列の内容を使用してハイパーリンクを挿入する。これらのハイパーリンクを追加したら、[url] 列を削除してシートをクリーンアップできます。

次に、formatColumnHeader() 関数を実装して、これらの変更をシートの最初の列に適用します。コードを少し読みやすくするために、2 つのヘルパー関数も実装します。

実装

先ほどと同様に、列見出しの形式を自動化する関数を追加する必要があります。次の手順を行います。

  1. Apps Script エディタで、スクリプト プロジェクトの末尾に次の formatColumnHeader() 関数を追加します。
/**
 * 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); 
}
  1. スクリプト プロジェクトの最後に formatColumnHeader() 関数の後に、次のヘルパー関数を追加します。
/**
 * 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; 
}
  1. スクリプト プロジェクトを保存します。

コードレビュー

これら 3 つの関数のコードを個別にレビューしましょう。

formatColumnHeader()

ご想像のとおり、この関数の最初の数行は、対象のシートと範囲を参照する変数を設定しています。

  • アクティブ シートは sheet に保存されています。
  • 列ヘッダーの行数が計算され、numRows に保存されます。ここでは、列見出し title を含まない行数が減算されています。
  • 列見出しの範囲は columnHeaderRange に格納されます。

次に、formatRowHeader() の場合と同様に、列ヘッダーの範囲に枠線と太字を適用します。ここでは、テキストを斜体にするために Range.setFontStyle(fontStyle) も使用しています。

ヘッダー列へのハイパーリンクの追加はより複雑なため、formatColumnHeader()hyperlinkColumnHeaders_(headerRange, numRows) を呼び出してタスクを処理します。これにより、コードがわかりやすく読みやすくなります。

hyperlinkColumnHeaders_(headerRange, numRows)

このヘルパー関数は、最初にヘッダーの列インデックス(インデックス 1 と想定)と url 列を識別します。columnIndexOf_('url') を呼び出して URL 列のインデックスを取得します。url 列が見つからない場合、メソッドはデータを変更せずに終了します。

この関数は、ヘッダー列の行に対応する URL を含む新しい範囲(urlRange)を取得します。これは、2 つの範囲が同じサイズであることを保証する Range.offset(rowOffset, columnOffset) メソッドで行います。次に、headerColumn 列と url 列の両方の値を取得します(headerValuesurlValues)。

この関数は次に、各列ヘッダーのセル値をループして、ヘッダーと url 列のコンテンツを含む =HYPERLINK() スプレッドシートの数式に置き換えます。変更後のヘッダー値は、Range.setValues(values) を使用してシートに挿入されます。

最後に、シートをクリーンに保ち、冗長な情報を排除するために、Sheet.deleteColumn(columnPosition) を呼び出して url 列を削除します。

columnIndexOf_(colName)

このヘルパー関数は、シートの最初の行で特定の名前を検索するシンプルなユーティリティ関数です。最初の 3 行では、これまで見てきた方法で、スプレッドシートの 1 行目から列ヘッダー名のリストを取得します。これらの名前は変数 columnNames に格納されます。

この関数は、各名前を順番に確認します。検索対象の名前と一致するものが見つかると、その列は停止し、その列のインデックスが返されます。名前が見つからない場合は、名前リストの最後に -1 を返して、名前が見つからなかったことを示します。

Results

次の手順でフォーマット関数の動作を確認できます。

  1. スクリプト プロジェクトをまだ Apps Script エディタに保存していない場合は、保存します。
  2. [クイック フォーマット] > [メニュー列見出し] メニュー項目をクリックします。

結果は次のようになります。

7497cf1b982aeff6.gif

別の書式設定タスクを自動化しました。列見出しと行見出しの形式を整えたら、次のセクションでデータを書式設定する方法を説明します。

6. データセットのフォーマット

ヘッダーを作成したら、シートの残りのデータを書式設定する関数を作成します。次の書式設定オプションが使用されます。

  • 交互の行の背景色(バンディング
  • 日付形式の変更
  • 枠線の適用
  • すべての列と行の自動サイズ調整

次に、関数 formatDataset() と、これらの形式をシートデータに適用する追加のヘルパー メソッドを作成します。

実装

先ほどと同様に、データ形式を自動化する関数を追加します。次の手順を行います。

  1. Apps Script エディタで、スクリプト プロジェクトの末尾に次の formatDataset() 関数を追加します。
/**
 * 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());
}
  1. スクリプト プロジェクトの最後に、formatDataset() 関数の後に、次のヘルパー関数を追加します。
/** 
 * 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 column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}
  1. スクリプト プロジェクトを保存します。

コードレビュー

これら 2 つの関数のコードを個別に確認してみましょう。

formatDataset()

この関数は、すでに実装した以前の関数と同様のパターンに従います。まず、アクティブなシート(シート)とデータ範囲(fullDataRange)への参照を保持する変数を取得します。

次に、Range.offset(rowOffset, columnOffset, numRows, numColumns) メソッドを使用して、列および行の見出しを除くシート内のすべてのデータを網羅する範囲(noHeadersRange)を作成します。次に、Range.getBandings() を使用して、この新しい範囲に既存のバンディングがあるかどうかを確認します。なぜなら、新しいバンディングが存在する場合に Apps Script でエラーがスローされるためです。縞模様が存在しない場合、この関数は Range.applyRowBanding(bandingTheme, showHeader, showFooter) を使用して薄い灰色の縞模様を追加します。それ以外の場合は、関数は先に進みます。

次のステップでは、formatDates_(colIndex) ヘルパー関数を呼び出して「release_date'」というラベルの付いた列で日付の形式を設定します(下記をご覧ください)。この列は、前に実装した columnIndexOf_(colName) ヘルパー関数を使用して指定します。

最後に、先ほどと同様の枠線を付け、Sheet.autoResizeColumns(columnPosition) メソッドと Sheet.autoResizeColumns(columnPosition) メソッドを使用して、すべての列と行のサイズを調整します。

formatDates_(colIndex)

このヘルパー関数は、指定された列インデックスを使用して、特定の日付形式を列に適用します。具体的には、日付の値を「日、年(曜日)」の形式にします。

まず、提供された列インデックスが有効(0 以上)であることを確認します。それ以外の場合は、何もせずに戻ります。このチェックを行うことで、たとえばシートに「release_date'」列がない場合に発生する可能性のあるエラーを回避できます。

列インデックスが検証されると、関数はその列を含む範囲(ヘッダー行を除く)を取得し、Range.setNumberFormat(numberFormat) を使用して書式設定を適用します。

Results

次の手順でフォーマット関数の動作を確認できます。

  1. スクリプト プロジェクトをまだ Apps Script エディタに保存していない場合は、保存します。
  2. [クイック フォーマット > フォーマット データセット] メニュー項目をクリックします。

結果は次のようになります。

3cfedd78b3e25f3a.gif

もう 1 つのフォーマット タスクを自動化しました。このフォーマット コマンドが利用できるようになったので、それらを適用するデータを追加しましょう。

7. API データを取得してフォーマットする

ここまでの Codelab では、スプレッドシートを書式設定する別の方法として Apps Script を使用する方法を確認しました。次に、公開 API からデータを pull してスプレッドシートに挿入し、読み取り可能な形式でフォーマットするコードを記述します。

前回の Codelab では、API からデータを取得する方法を学習しました。ここでは同じ方法を使用します。この演習では、一般公開されている Star Wars API(SWAPI)を使用してスプレッドシートにデータを入力します。具体的には、この API を使用して 3 本のオリジナル スター映画に登場する主要キャラクターに関する情報を取得します。

コードは API を呼び出して大量のデータ JSON データを取得し、レスポンスを解析してデータを新しいシートに配置して、シートをフォーマットします。

実装

このセクションでは、その他のメニュー項目を追加します。各メニュー項目は、項目固有の変数をメイン関数(createResourceSheet_())に渡すラッパー スクリプトを呼び出します。この関数と 3 つの追加のヘルパー関数を実装します。以前と同様に、ヘルパー関数はタスクの論理的に区切った部分を分離し、コードを読み取れるようにします。

次の操作を行います。

  1. Apps Script エディタで、スクリプト プロジェクトの onOpen() 関数を次のように更新します。
/**
 * 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')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}
  1. スクリプト プロジェクトを保存します。
  2. スクリプト エディタで、関数のリストから onOpen を選択し、[実行] をクリックします。これにより onOpen() が実行され、追加した新しいオプションでスプレッドシート メニューが再構築されます。
  3. Apps Script ファイルを作成するには、[ファイル] の横にある「ファイルを追加」アイコン ファイルを追加 > Script をクリックします。
  4. 新しいスクリプトに「API」という名前を入力して Enter キーを押します。(Apps Script は自動的にスクリプト ファイル名に .gs を追加します)。
  5. 新しい API.gs ファイル内のコードを次のコードで置き換えます。
/**
 * 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 formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data 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 film episode number.
 *   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.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data 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're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's 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();

}
  1. API.gs スクリプト プロジェクト ファイルの末尾に、次のヘルパー関数を追加します。
/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}
  1. スクリプト プロジェクトを保存します。

コードレビュー

多くのコードを追加しました。それぞれの機能を詳しく見ていきましょう。

onOpen()

いくつかのメニュー項目が Quick formats メニューに追加されました。セパレータの行を設定し、Menu.addSubMenu(menu) メソッドを使用して、3 つの新しい項目を含むネストされたメニュー構造を作成しました。新しい項目は Menu.addItem(caption, functionName) メソッドで追加します。

ラッパー関数

追加されたメニュー項目はすべて似たような処理をしており、SWAPI から取得したデータでシートを作成しようとしています。唯一の違いは、それぞれ異なる映画に重点を置いていることです。

シートを作成する関数を 1 つ作成し、その関数で使用するパラメータを指定する関数を使用すると便利です。ただし、Menu.addItem(caption, functionName) メソッドでは、メニューからの呼び出し時にパラメータを渡すことはできません。では、同じコードを 3 回書かない方がよいでしょうか。

その答えはラッパー関数です。これらは、特定のパラメータが設定された別の関数をすぐに呼び出すことができる軽量の関数です。

ここでは、createPeopleSheetIV()createPeopleSheetV()createPeopleSheetVI() の 3 つのラッパー関数を使用しています。メニュー項目はこれらの関数にリンクされています。メニュー項目をクリックすると、ラッパー関数が実行され、すぐにメインシート作成ツールの createResourceSheet_(resourceType, idNumber, episodeNumber) が呼び出され、メニュー項目に適したパラメータが渡されます。この場合、シート作成ツールでスター ウォーズの映画の主人公データで埋め込んだシートを作成することになります。

createResourceSheet_(resourceType, idNumber, episodeNumber)

これは、この演習のメインシート作成ツールです。いくつかのヘルパー関数を利用して、API データを取得して解析し、シートを作成して API データをシートに書き込み、前のセクションで作成した関数を使ってシートをフォーマットします。詳細を確認しましょう。

まず、関数は fetchApiResourceObject_(url) を使用して、基本的な映画情報を取得する API をリクエストします。API レスポンスには、映画から特定の人物(ここではリソース)の詳細情報を取得するためにコードで使用できる URL のコレクションが含まれます。コードは、すべてを resourceUrls 配列にすべて収集します。

次に、このコードは fetchApiResourceObject_(url) を繰り返し使用して、resourceUrls 内のすべてのリソース URL に対して API を呼び出します。結果は resourceDataList 配列に格納されます。この配列のすべての要素は、映画とは異なるキャラクターを記述するオブジェクトです。

リソース データ オブジェクトには、その文字に関する情報にマップされる複数の共通キーがあります。たとえば、「name'」というキーは映画のキャラクター名に対応します。各リソースデータ オブジェクトのキーは共通のオブジェクト構造を使用するため、すべて同じであると仮定します。キーのリストは後で必要になるため、コードは JavaScript の Object.keys() メソッドを使用してキーのリストを resourceObjectKeys に保存します。

次に、ビルダー関数は createNewSheet_(name) ヘルパー関数を呼び出して、新しいデータが配置されるシートを作成します。このヘルパー関数を呼び出すと、新しいシートが有効になります。

シートが作成されると、ヘルパー関数 fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) が呼び出され、すべての API データがシートに追加されます。

最後に、先ほど作成したすべてのフォーマット関数が呼び出され、新しいデータに同じフォーマット ルールが適用されます。新しいシートがアクティブなものであるため、コードに変更を加えずに再利用できます。

fetchApiResourceObject_(url)

このヘルパー関数は、前の Codelab データの処理で使用した fetchBookData_(ISBN) ヘルパー関数に似ています。指定された URL を受け取り、UrlFetchApp.fetch(url, params) メソッドを使用してレスポンスを取得します。その後、HTTPResponse.getContextText() メソッドと JavaScript JSON.parse(json) メソッドを使用してレスポンスが JSON オブジェクトに解析されます。結果の JSON オブジェクトが返されます。

createNewSheet_(name)

このヘルパー関数はきわめて単純です。まず、スプレッドシートに指定した名前のシートが存在するかどうかを確認します。存在する場合、関数はシートをアクティブにして返します。

シートが存在しない場合、その関数は Spreadsheet.insertSheet(sheetName) で作成され、有効になり、新しいシートが返されます。

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

このヘルパー関数は、新しいシートに API データを入力する役割を担います。パラメータとして新しいシート、オブジェクト キーのリスト、API リソース オブジェクトのリストを使用します。各オブジェクト キーは新しいシート内の列を表し、各リソース オブジェクトは行を表します。

まず、新しい API データの表示に必要な行数と列数を計算します。これは、リソースリストとキーリストのサイズです。次に、データを配置する出力範囲(resourceRange)を定義し、列ヘッダーを保持する行を追加します。変数 resourceValues は、resourceRange から抽出された 2D 値の配列を保持しています。

この関数はその後、objectKeys リスト内のすべてのオブジェクトキーをループします。キーは列ヘッダーとして設定され、2 番目のループがすべてのリソース オブジェクトを通過します。(行、列)ペアごとに、対応する API 情報を resourceValues[row][column] 要素にコピーします。

resourceValues に入力すると、Sheet.clear() を使用してデスティネーション シートが消去されます。これには、以前のメニュー項目のクリックデータも含まれます。最後に、新しい値がシートに書き込まれます。

Results

次の手順で処理の結果を確認できます。

  1. スクリプト プロジェクトをまだ Apps Script エディタに保存していない場合は、保存します。
  2. [クイック フォーマット > キャラクター シートの作成 > エピソード IV] メニュー項目をクリックします。

結果は次のようになります。

d9c472ab518d8cef.gif

スプレッドシートにデータをインポートして自動的に書式を設定するコードを記述しました。

8. おわりに

この Codelab を完了しました。Apps Script プロジェクトに含めることができる Google スプレッドシートの書式設定オプションをいくつか確認し、大規模な API データセットをインポートして書式設定する印象的なアプリケーションを構築しました。

この Codelab はお役に立ちましたか。

はい いいえ

学習した内容

  • Apps Script でスプレッドシートの各種書式設定オペレーションを適用する方法。
  • onOpen() 関数を使用してサブメニューを作成する方法。
  • Apps Script を使用して、取得した JSON オブジェクトのリストを新しいデータのシートにフォーマットする方法。

次のステップ

この再生リストの次の Codelab では、Apps Script を使用してデータをグラフで視覚化したり、グラフを Google スライドのプレゼンテーションに書き出したりする方法を説明します。

次の Codelab のスライドでデータをグラフ化して表示するをご覧ください。