1. はじめに
「Google スプレッドシートでの Apps Script の基礎」Codelab 再生リストの第 4 部へようこそ。
この Codelab を完了すると、Apps Script でスプレッドシートのデータをフォーマットする方法を学び、公開 API から取得したフォーマット済みデータで整理されたスプレッドシートを作成する関数を作成できるようになります。
学習内容
- Apps Script で Google スプレッドシートのさまざまな書式設定操作を適用する方法。
- Apps Script を使用して、JSON オブジェクトとその属性のリストを整理されたデータシートに変換する方法。
始める前に
これは、Google スプレッドシートを使用した Apps Script の基礎の再生リストの 4 番目の Codelab です。この Codelab を開始する前に、次の Codelab を完了してください。
必要なもの
- この再生リストの以前の Codelab で取り上げた Apps Script の基本的なトピックを理解している。
- Apps Script エディタに関する基本的な知識
- Google スプレッドシートに関する基礎知識
- スプレッドシートの A1 形式を読み取る機能
- JavaScript とその
Stringクラスに関する基本的な知識
2. セットアップ
続行する前に、データを含むスプレッドシートが必要です。以前と同様に、これらの演習用にコピーできるデータシートを用意しています。手順は次のとおりです。
- このリンクをクリックしてデータシートをコピーし、[コピーを作成] をクリックします。新しいスプレッドシートが Google ドライブ フォルダに作成され、「データ形式のコピー」という名前が付けられます。
- スプレッドシートのタイトルをクリックし、「データ形式のコピー」から「データ形式」に変更します。シートは次のようになっているはずです。最初の 3 つのスター・ウォーズ映画に関する基本情報が入力されています。
- [拡張機能] > [Apps Script] を選択して、スクリプト エディタを開きます。
- Apps Script プロジェクトのタイトルをクリックし、「無題のプロジェクト」から「Data Formatting」に変更します。[名前を変更] をクリックして、タイトルの変更を保存します。
このスプレッドシートとプロジェクトで、Codelab を開始する準備が整いました。次のセクションに進んで、Apps Script の基本的な書式設定について学習しましょう。
3. カスタム メニューを作成する
Apps Script では、いくつかの基本的な書式設定方法をスプレッドシートに適用できます。次の演習では、データをフォーマットするいくつかの方法を示します。書式設定アクションを制御するために、必要な項目を含むカスタム メニューを作成しましょう。カスタム メニューの作成プロセスについては、データの操作の Codelab で説明しましたが、ここでは改めて概要を説明します。
実装
カスタム メニューを作成しましょう。
- 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();
}
- スクリプト プロジェクトを保存します。
- スクリプト エディタで、関数リストから
onOpenを選択し、[実行] をクリックします。これにより、onOpen()が実行されてスプレッドシートのメニューが再構築されるため、スプレッドシートを再読み込みする必要がなくなります。
コードレビュー
このコードを確認して、その仕組みを理解しましょう。onOpen() では、最初の行で getUi() メソッドを使用して、このスクリプトがバインドされているアクティブなスプレッドシートのユーザー インターフェースを表す Ui オブジェクトを取得します。
次の行では、メニュー（Quick formats）を作成し、メニュー項目（Format row header、Format column header、Format dataset）をメニューに追加してから、メニューをスプレッドシートのインターフェースに追加します。これには、それぞれ createMenu(caption)、addItem(caption, functionName)、addToUi() の各メソッドを使用します。
addItem(caption, functionName) メソッドは、メニュー項目のラベルと、メニュー項目が選択されたときに実行される Apps Script 関数との間に接続を作成します。たとえば、Format row header メニュー項目を選択すると、スプレッドシートは formatRowHeader() 関数（まだ存在しない）を実行しようとします。
結果
スプレッドシートで Quick formats メニューをクリックすると、新しいメニュー項目が表示されます。
これらの項目をクリックすると、対応する関数が実装されていないためエラーが発生します。次は、その関数を実装しましょう。
4. ヘッダー行の書式を設定する
スプレッドシートのデータセットには、各列のデータを識別するためのヘッダー行が含まれていることがよくあります。ヘッダー行の書式を設定して、スプレッドシートの残りのデータと視覚的に区別することをおすすめします。
最初の Codelab では、ヘッダーのマクロを作成し、そのコードを調整しました。ここでは、Apps Script を使用してヘッダー行をゼロからフォーマットします。作成するヘッダー行では、ヘッダー テキストが太字になり、背景が濃い青緑色になり、テキストが白色になり、実線の境界線が追加されます。
実装
書式設定オペレーションを実装するには、以前に使用したのと同じ スプレッドシート サービスのメソッドを使用しますが、今回はサービスの書式設定メソッドも使用します。手順は次のとおりです。
- 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);
}
- スクリプト プロジェクトを保存します。
コードレビュー
多くの書式設定タスクと同様に、これを実装する Apps Script コードは簡単です。最初の 2 行では、以前に見たメソッドを使用して、現在アクティブなシート（sheet）とシートの最上行（headerRange)）への参照を取得します。Sheet.getRange(row, column, numRows, numColumns) メソッドは、データを含む列のみを含む最上行を指定します。Sheet.getLastColumn() メソッドは、シート内でデータを含む最後の列の列インデックスを返します。この例では、E 列（url）です。
残りのコードは、さまざまな Range メソッドを呼び出して、headerRange のすべてのセルに書式設定の選択肢を適用します。コードを読みやすくするために、メソッド チェーンを使用して、各フォーマット メソッドを次々に呼び出します。
Range.setFontWeight(fontWeight)は、フォントの太さを太字に設定するために使用されます。Range.setFontColor(color)は、フォントの色を白に設定するために使用されます。Range.setBackground(color)は、背景色を濃い青緑色に設定するために使用されます。setBorder(top, left, bottom, right, vertical, horizontal, color, style)は、範囲のセルを黒い実線で囲みます。
最後のメソッドには複数のパラメータがあります。それぞれの役割を確認しましょう。ここで最初の 4 つのパラメータ（すべて true に設定）は、Apps Script に、範囲の上、下、左、右に枠線を追加するよう指示しています。5 番目と 6 番目のパラメータ（null と null）は、選択した範囲内の罫線を変更しないように Apps Script に指示します。7 番目のパラメータ（null）は、枠線の色をデフォルトで黒に設定することを示します。最後のパラメータは、SpreadsheetApp.BorderStyle で提供されるオプションから取得した、使用するボーダー スタイルのタイプを指定します。
結果
次の手順で、書式設定関数の動作を確認できます。
- まだ行っていない場合は、Apps Script エディタでスクリプト プロジェクトを保存します。
- [クイック フォーマット] > [行ヘッダーの書式設定] メニュー項目をクリックします。
結果は次のようになります。
これで、書式設定タスクが自動化されました。次のセクションでは、同じ手法を適用して、列ヘッダーの別の形式スタイルを作成します。
5. 列見出しの形式を指定する
行ヘッダーをカスタマイズできる場合は、列ヘッダーもカスタマイズできます。列見出しを使用すると、特定のデータセットの読みやすさが向上します。たとえば、このスプレッドシートの [タイトル] 列は、次の形式の選択肢で強化できます。
- テキストを太字にする
- テキストを斜体にする
- セルの枠線を追加する
- url 列の内容を使用してハイパーリンクを挿入する。これらのハイパーリンクを追加したら、url 列を削除してシートを整理できます。
次に、formatColumnHeader() 関数を実装して、これらの変更をシートの最初の列に適用します。コードを読みやすくするために、2 つのヘルパー関数も実装します。
実装
以前と同様に、列ヘッダーの書式設定を自動化する関数を追加する必要があります。手順は次のとおりです。
- 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);
}
- スクリプト プロジェクトの末尾（
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;
}
- スクリプト プロジェクトを保存します。
コードレビュー
3 つの関数のコードをそれぞれ見てみましょう。
formatColumnHeader()
おそらく予想どおり、この関数の最初の数行では、対象のシートと範囲を参照する変数が設定されています。
- アクティブなシートは
sheetに保存されます。 - 列ヘッダーの行数が計算され、
numRowsに保存されます。ここで、コードは 1 を減算して、行数に列ヘッダーtitleが含まれないようにしています。 - 列ヘッダーをカバーする範囲は
columnHeaderRangeに保存されます。
次に、コードは formatRowHeader() と同様に、列ヘッダーの範囲に罫線と太字を適用します。ここでは、Range.setFontStyle(fontStyle) を使用してテキストを斜体にしています。
ヘッダー列にハイパーリンクを追加するのはより複雑なため、formatColumnHeader() は hyperlinkColumnHeaders_(headerRange, numRows) を呼び出してタスクを処理します。これにより、コードを整理して読みやすくすることができます。
hyperlinkColumnHeaders_(headerRange, numRows)
このヘルパー関数は、まずヘッダー（インデックス 1 と想定）と url 列の列インデックスを特定します。columnIndexOf_('url') を呼び出して、URL 列のインデックスを取得します。url 列が見つからない場合、メソッドはデータを変更せずに終了します。
この関数は、ヘッダー列の行に対応する URL をカバーする新しい範囲（urlRange）を取得します。これは Range.offset(rowOffset, columnOffset) メソッドで行われます。このメソッドは、2 つの範囲が同じサイズになることを保証します。次に、headerColumn 列と url 列の両方の値が取得されます（headerValues と urlValues）。
次に、この関数は各列ヘッダー セルの値をループ処理し、ヘッダーと url 列の内容で構成された =HYPERLINK() スプレッドシートの数式に置き換えます。変更されたヘッダー値は、Range.setValues(values) を使用してシートに挿入されます。
最後に、シートをクリーンに保ち、冗長な情報を削除するために、Sheet.deleteColumn(columnPosition) を呼び出して url 列を削除します。
columnIndexOf_(colName)
このヘルパー関数は、シートの最初の行で特定の名前を検索するだけのシンプルなユーティリティ関数です。最初の 3 行では、すでに説明したメソッドを使用して、スプレッドシートの 1 行目から列ヘッダー名のリストを取得しています。これらの名前は、変数 columnNames に格納されます。
関数は、各名前を順番に確認します。検索対象の名前と一致する列が見つかると、検索を停止して列のインデックスを返します。名前が見つからないまま名前リストの末尾に達すると、-1 を返して名前が見つからなかったことを通知します。
結果
次の手順で、書式設定関数の動作を確認できます。
- まだ行っていない場合は、Apps Script エディタでスクリプト プロジェクトを保存します。
- [クイック フォーマット] > [列ヘッダーの書式設定] メニュー項目をクリックします。
結果は次のようになります。
これで、別の書式設定タスクが自動化されました。列ヘッダーと行ヘッダーの書式設定が完了したら、次のセクションでデータの書式設定方法について説明します。
6. データセットの形式を設定する
ヘッダーが作成されたので、シートの残りのデータをフォーマットする関数を作成しましょう。次の書式設定オプションを使用します。
- 行の背景色を交互に設定する（バンディングとも呼ばれます）
- 日付形式を変更する
- 枠線を適用する
- すべての列と行のサイズを自動調整する
次に、これらの形式をシートデータに適用する関数 formatDataset() と追加のヘルパー メソッドを作成します。
実装
以前と同様に、データ形式を自動化する関数を追加します。手順は次のとおりです。
- 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());
}
- スクリプト プロジェクトの末尾（
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)");
}
- スクリプト プロジェクトを保存します。
コードレビュー
この 2 つの関数のコードをそれぞれ見てみましょう。
formatDataset()
この関数は、すでに実装した以前の形式関数と同様のパターンに従います。まず、アクティブなシート（sheet）とデータ範囲（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) を使用して書式設定を適用します。
結果
次の手順で、書式設定関数の動作を確認できます。
- まだ行っていない場合は、Apps Script エディタでスクリプト プロジェクトを保存します。
- [クイック フォーマット] > [データセットのフォーマット] メニュー項目をクリックします。
結果は次のようになります。
これで、また 1 つの書式設定タスクを自動化できました。書式設定コマンドが使用可能になったので、これらのコマンドを適用するデータを追加しましょう。
7. API データを取得してフォーマットする
この Codelab では、ここまでで、スプレッドシートの書式設定の代替手段として Apps Script を使用する方法を学びました。次に、公開 API からデータを取得し、スプレッドシートに挿入して、読みやすいように書式設定するコードを作成します。
前回の Codelab では、API からデータを取得する方法を学習しました。ここでは同じ手法を使用します。この演習では、一般公開されている Star Wars API（SWAPI）を使用してスプレッドシートにデータを入力します。具体的には、API を使用して、スター・ウォーズのオリジナル三部作に登場する主要なキャラクターに関する情報を取得します。
コードは API を呼び出して大量の JSON データを取得し、レスポンスを解析して、新しいシートにデータを配置し、シートの書式を設定します。
実装
このセクションでは、メニュー項目を追加します。各メニュー項目は、項目固有の変数をメイン関数（createResourceSheet_()）に渡すラッパー スクリプトを呼び出します。この関数と 3 つの追加のヘルパー関数を実装します。これまでと同様に、ヘルパー関数はタスクの論理的に区分された部分を分離し、コードの読みやすさを維持するのに役立ちます。
次の操作を行います。
- 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();
}
- スクリプト プロジェクトを保存します。
- スクリプト エディタで、関数リストから
onOpenを選択し、[実行] をクリックします。これにより、onOpen()が実行され、追加した新しいオプションでスプレッドシートのメニューが再構築されます。 - Apps Script ファイルを作成するには、[ファイル] の横にある [ファイルを追加]
> [スクリプト] をクリックします。 - 新しいスクリプトに「API」という名前を付けて、Enter キーを押します。（Apps Script は、スクリプト ファイル名に
.gs拡張子を自動的に付加します）。 - 新しい 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();
}
- 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);
}
- スクリプト プロジェクトを保存します。
コードレビュー
大量のコードを追加しました。各関数の仕組みを理解するために、各関数を個別に見ていきましょう。
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) が呼び出され、メニュー項目に適したパラメータが渡されます。この場合、シート作成関数に、スター・ウォーズの映画の 1 つの主要なキャラクター データが入力されたシートを作成するようリクエストします。
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() を使用して宛先シートがクリアされます。最後に、新しい値がシートに書き込まれます。
結果
作業の結果を確認するには、次の操作を行います。
- まだ行っていない場合は、Apps Script エディタでスクリプト プロジェクトを保存します。
- [クイック フォーマット] > [キャラクター シートを作成] > [エピソード IV] メニュー項目をクリックします。
結果は次のようになります。
これで、データをスプレッドシートにインポートして自動的に書式設定するコードを作成しました。
8. まとめ
この Codelab は以上で終了です。Apps Script プロジェクトに含めることができるスプレッドシートの書式設定オプションの一部を確認し、大規模な API データセットをインポートして書式設定する優れたアプリケーションを構築しました。
この Codelab は役に立ちましたか？
学習した内容
- Apps Script を使用してスプレッドシートのさまざまな書式設定操作を適用する方法。
onOpen()関数を使用してサブメニューを作成する方法。- 取得した JSON オブジェクトのリストを Apps Script で新しいデータシートにフォーマットする方法。
次のステップ
このプレイリストの次の Codelab では、Apps Script を使用してデータをグラフで可視化し、グラフを Google スライドのプレゼンテーションにエクスポートする方法について説明します。
次の Codelab は、スライドでデータをグラフ化して表示するで確認できます。