この Codelab について
1. はじめに
「Google スプレッドシートを使用するアプリ スクリプトの基礎」の第 3 部の Codelab の再生リストへようこそ。
この Codelab を完了すると、データ操作、カスタム メニュー、公開 API を使用して Apps Script でデータを取得する方法を学習できます。この再生リストで前の Codelab で紹介した Codelab の SpreadsheetApp、Spreadsheet、Sheet、Range も引き続き使用します。
学習内容
- ドライブにある個人用スプレッドシートまたは共有スプレッドシートからデータを読み込む方法。
onOpen()関数を使用してカスタム メニューを作成する方法。- Google スプレッドシートのセルで文字列データ値を解析し操作する方法。
- 公開 API ソースから JSON オブジェクト データを pull して操作する方法。
始める前に
これは「Google スプレッドシートを使用するアプリ スクリプトの基礎」再生リストの 3 番目の Codelab です。この Codelab を開始する前に、前の Codelab を完了してください。
必要なもの
- この再生リストの前の Codelab で説明した Apps Script の基本的なトピックについての理解。
- Apps Script エディタの基本的な知識
- Google スプレッドシートの基本事項
- スプレッドシートの A1 表記を読む機能
- JavaScript とその
Stringクラスに関する基本的な知識
2. 設定
この Codelab の演習では、スプレッドシートを使用する必要があります。以下の演習で使用するスプレッドシートを作成します。
- Google ドライブにスプレッドシートを作成する。その場合は、ドライブのインターフェースの [新しい Google スプレッドシート] を選択します。新しいスプレッドシートが作成されて開きます。ファイルはドライブ フォルダに保存されます。
- スプレッドシートのタイトルをクリックし、[無題のスプレッドシート] から [データ操作とカスタム メニュー] に変更します。シートは次のようになります。
- [拡張機能] > [Apps Script] をクリックしてスクリプト エディタを開きます。
- Apps Script プロジェクトのタイトルをクリックし、「無題のプロジェクト」から「データ操作とカスタム メニュー」に変更します。[名前を変更] をクリックしてタイトルの変更を保存します。
空のスプレッドシートとプロジェクトがあれば、ラボを開始できます。カスタム メニューの詳細については、次のセクションをご覧ください。
3. 概要: カスタム メニュー項目を使用してデータをインポートする
Apps Script では、Google スプレッドシートに表示できるカスタム メニューを定義できます。Google ドキュメント、Google スライド、Google フォームでカスタム メニューを使用することもできます。カスタム メニュー項目を定義する際に、テキストラベルを作成し、スクリプト プロジェクトの Apps Script 関数に接続します。Google スプレッドシートにメニューが表示されるように、UI にメニューを追加します。
ユーザーがカスタム メニュー項目をクリックすると、関連付けられている Apps Script 関数が実行されます。こうすると、スクリプト エディタを開かずに Apps Script 関数を簡単に実行できます。また、スプレッドシートの他のユーザーは、コードや Apps Script の仕組みを知らなくてもコードを実行できます。これらは、あくまでも 1 つのメニュー項目です。
カスタム メニュー項目は onOpen() シンプル トリガー関数で定義します。これについては、次のセクションで説明します。
4. onOpen() 関数
Apps Script のシンプルなトリガーにより、特定の条件やイベントに応じて特定の Apps Script コードを実行できます。トリガーを作成するときに、トリガーをトリガーするイベントを定義し、イベントで実行される Apps Script 関数を指定します。
onOpen() は単純なトリガーの例です。これらの設定は簡単です。onOpen() という名前の Apps Script 関数を記述するだけで、関連するスプレッドシートが開かれたり再読み込みされたりするたびに、Apps Script によって実行されます。
/**
* 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() {
/* ... */
}
実装
カスタム メニューを作成しましょう。
- スクリプト プロジェクトのコードを次のコードで置き換えます。
/**
* 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();
}
- スクリプト プロジェクトを保存します。
コードレビュー
このコードを確認して仕組みを理解しましょう。onOpen() の最初の行で、getUi() メソッドを使用して、このスクリプトがバインドされているアクティブなスプレッドシートのユーザー インターフェースを表す Ui オブジェクトを取得します。
次の 3 行でメニュー（Book-list）を作成し、そのメニュー項目（Load Book-list）をメニュー項目に追加して、スプレッドシートのインターフェースにメニューを追加します。これは、それぞれ createMenu(caption)、addItem(caption, functionName)、addToUi() メソッドで行います。
addItem(caption, functionName) メソッドは、メニュー項目のラベルと、メニュー項目が選択されたときに実行される Apps Script 関数間の接続を作成します。この場合、Load Book-list メニュー項目を選択すると、スプレッドシートで loadBookList() 関数の実行が試みられます（この機能はまだ存在しません）。
Results
この関数を実行して、結果を確認します。
- Google スプレッドシートで、スプレッドシートを再読み込みします。注: 通常はスクリプト エディタでタブを閉じます。
- [ツール > スクリプト エディタ] を選択して、スクリプト エディタを再度開きます。
スプレッドシートが再読み込みされると、メニューバーに新しい Book-list メニューが表示されます。
[書籍リスト] をクリックすると、次のようなメニューが表示されます。
次のセクションでは、loadBookList() 関数のコードを作成し、Apps Script のデータを操作する 1 つの方法として、他のスプレッドシートを読み取る方法を紹介します。
5. スプレッドシートからデータをインポートする
カスタム メニューを作成したので、メニュー アイテムをクリックして実行できる関数を作成できます。
現在、カスタム メニュー Book-list には 1 つのメニュー項目があります。Load Book-list メニュー項目 loadBookList(), を選択したときに呼び出された関数が、スクリプトに存在しないため、[書籍リスト > 書籍リストを読み込む] を選択すると、次のエラーがスローされます。
このエラーを修正するには、loadBookList() 関数を実装します。
実装
新しいメニュー項目にスプレッドシートを機能させるためのデータを入力する必要があります。そのため、loadBookList() を実装して、別のスプレッドシートから書籍データを読み取り、このスプレッドシートにコピーします。
onOpen()の下にあるスクリプトに次のコードを追加します。
/**
* Creates a template book list based on the
* provided 'codelab-book-list' sheet.
*/
function loadBookList(){
// Gets the active sheet.
var sheet = SpreadsheetApp.getActiveSheet();
// Gets a different spreadsheet from Drive using
// the spreadsheet's ID.
var bookSS = SpreadsheetApp.openById(
"1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo"
);
// Gets the sheet, data range, and values of the
// spreadsheet stored in bookSS.
var bookSheet = bookSS.getSheetByName("codelab-book-list");
var bookRange = bookSheet.getDataRange();
var bookListValues = bookRange.getValues();
// Add those values to the active sheet in the current
// spreadsheet. This overwrites any values already there.
sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth())
.setValues(bookListValues);
// Rename the destination sheet and resize the data
// columns for easier reading.
sheet.setName("Book-list");
sheet.autoResizeColumns(1, 3);
}
- スクリプト プロジェクトを保存します。
コードレビュー
この関数の仕組みは次のとおりです。loadBookList() 関数は、主に前の Codelab で導入された Spreadsheet、Sheet、Range クラスのメソッドを使用します。これらのコンセプトを念頭に置いて、loadBookList() コードを次の 4 つのセクションに分けます。
1: リンク先のシートを特定する
最初の行は SpreadsheetApp.getActiveSheet() を使用して現在のシート オブジェクトへの参照を取得し、変数 sheet に格納します。これは、データがコピーされるシートです。
2: ソースデータを特定する
次の数行では、取得するソースデータを参照する 4 つの変数を設定します。
bookSSは、コードがデータを読み取るスプレッドシートへの参照を格納します。このコードは、スプレッドシート ID でスプレッドシートを検索します。この例では、読み取り元のソース スプレッドシートの ID を指定し、SpreadsheetApp.openById(id)メソッドを使用してスプレッドシートを開き、bookSheetは、必要なデータを含むシートの参照をbookSSに保存します。このコードは、読み取り対象のシートをcodelab-book-listという名前で識別しています。bookRangeはbookSheetにデータの範囲への参照を格納します。メソッドSheet.getDataRange()は、シート内の空でないセルをすべて含む範囲を返します。こうすることで、空の行と列を含まないシート内のすべてのデータをカバーする範囲を簡単に確認できます。bookListValuesは、bookRangeのセルから取得したすべての値を含む 2D 配列です。Range.getValues()メソッドは、ソースシートからデータを読み取って、この配列を生成します。
3: ソースから宛先にデータをコピーする
次のコード セクションで、bookListValues データを sheet にコピーし、シートの名前も変更します。
Sheet.getRange(row, column, numRows, numColumns)を使用して、sheetのデータのコピー先を特定します。Range.getHeight()メソッドとRange.getWidth()メソッドは、データのサイズを測定し、同じディメンションの宛先範囲を定義するために使用されます。Range.setValues(values)はbookListValuesの 2D 配列をコピー先の範囲にコピーし、すでに存在するデータを上書きします。
4: リンク先ページの書式を設定する
Sheet.setName(name) を使用して、コピー先のシート名を Book-list に変更します。関数の最後の行で、Sheet.autoResizeColumns(startColumn, numColumns) を使用してコピー先シートの最初の 3 列のサイズを変更し、新しいデータを読みやすくします。
Results
この関数の動作を確認できます。Google スプレッドシートで [書籍リスト > 書籍リストを読み込む] を選択して、スプレッドシートを埋めるように関数を実行します。
書籍名、著者、13 桁の ISBN 番号が記載されたシートができました。次のセクションでは、文字列操作とカスタム メニューを使って、この書籍リスト内のデータを変更および更新する方法を学習します。
6. 概要: スプレッドシートのデータを削除
これで、スプレッドシートに書籍情報が追加されました。各行は特定の書籍を示します。書籍名、著者、ISBN 番号が別々の列に表示されます。ただし、次のような未加工のデータでも問題が発生する可能性があります。
- 一部の行では、タイトルと作成者が一緒にタイトル列に配置され、カンマまたは文字列「"」でリンクされます。
- 一部の行には書籍名や著者が指定されていません。
次のセクションでは、データをクリーニングしてこれらの問題を修正します。最初の問題では、タイトル列を読み取って、カンマまたは「&」による区切り文字を見つけるたびにテキストを分割する関数を作成し、対応する作成者とタイトルの部分文字列を正しい列に配置します。2 つ目の問題では、外部 API を使用して不足している書籍情報を自動的に検索し、その情報をシートに追加するコードを記述します。
7. メニュー項目を追加する
実装するデータ クリーニング操作を制御する 3 つのメニュー項目を作成します。
実装
必要なメニュー項目を追加するよう onOpen() を更新しましょう。手順は次のとおりです。
- スクリプト プロジェクトで、
onOpen()コードを次のように更新します。
/**
* 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')
.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();
}
- スクリプト プロジェクトを保存します。
- スクリプト エディタで、関数のリストから
onOpenを選択し、[実行] をクリックします。これにより、onOpen()が実行され、スプレッドシートのメニューが再構築されます。スプレッドシートを再読み込みする必要はありません。
この新しいコードでは、Menu.addSeparator() メソッドによりメニューに水平分割線を作成し、関連するメニュー項目のグループを視覚的に整理できるようになっています。その下に、Separate title/author at first comma、Separate title/author at last "by"、Fill in blank titles and author cells のラベルが付いた新しいメニュー項目が追加されます。
Results
スプレッドシートで Book-list メニューをクリックし、新しいメニュー項目を表示します。
これらの新しい項目をクリックすると、対応する関数が実装されていないためエラーが発生します。次に、この操作を行います。
8. カンマでテキストを分割する
スプレッドシートにインポートしたデータセットには、作成者とタイトルがカンマを使用して 1 つのセルに正しく結合されていない複数のセルがあります。
テキスト文字列を別々の列に分割することは、一般的なスプレッドシートの作業です。Google スプレッドシートには、文字列を列に分割する SPLIT() 関数が用意されています。ただし、多くの場合、データセットには組み込み関数では簡単に解決できない問題があります。このような場合、Apps Script コードを記述することで、データのクリーンアップや整理に必要な複雑な操作を行うことができます。
データのクリーンアップを開始するには、まず splitAtFirstComma() という関数を実装します。この関数は、カンマが見つかったときに、作成者とタイトルをそれぞれのセルに分割します。
splitAtFirstComma() 関数は次の手順を実施します。
- 現在選択されているセルを表す範囲を取得します。
- 範囲内のセルにカンマが含まれているかどうかを確認します。
- カンマが見つかった場合は、最初のカンマの位置で文字列を 2 つ（および 2 つの部分文字列）に分割します。簡潔にするため、カンマは「authors, [title]」の文字列パターンであることを示すことができます。セルに複数のカンマがある場合は、文字列の最初のカンマで分割するのが適切と想定できます。
- 部分文字列をそれぞれのタイトルと作成者のセルの新しいコンテンツとして設定します。
実装
これらのステップを実装するには、前と同じ スプレッドシート サービス メソッドを使用しますが、文字列データを操作する際にも JavaScript を使用する必要があります。次の手順を行います。
- Apps Script エディタで、スクリプト プロジェクトの末尾に次の関数を追加します。
/**
* Reformats title and author columns by splitting the title column
* at the first comma, if present.
*/
function splitAtFirstComma(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where commas are found. Assumes the presence
// of a comma indicates an "authors, title" pattern.
for (var row = 0; row < titleAuthorValues.length; row++){
var indexOfFirstComma =
titleAuthorValues[row][0].indexOf(", ");
if(indexOfFirstComma >= 0){
// Found a comma, so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(indexOfFirstComma + 2);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(0, indexOfFirstComma);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- スクリプト プロジェクトを保存します。
コードレビュー
3 つのメイン セクションで構成される新しいコードを見てみましょう。
1: ハイライト表示されたタイトルの値を取得する
最初の 3 行では、シート内の現在のデータを参照する変数が 3 つ設定されています。
activeRangeは、splitAtFirstComma()関数が呼び出されたときにユーザーがハイライト表示した範囲を表します。この演習は単純にするために、ユーザーが列 A のセルをハイライト表示する場合にのみ実行すると仮定します。titleAuthorRangeは、activeRangeと同じセルをカバーする新しい範囲を表し、さらに右側に 1 つの列が含まれます。titleAuthorRangeは、Range.offset(rowOffset, columnOffset, numRows, numColumns)メソッドを使用して作成されます。コードには、見つかった著者をタイトル列に入れる場所が必要なため、この範囲を拡張する必要があります。titleAuthorValuesは、Range.getValues()を使用してtitleAuthorRangeから抽出したデータの 2D 配列です。
2: 各タイトルを確認し、最初に見つかったカンマで分割します
次のセクションでは、titleAuthorValues の値を調べてカンマを見つけます。JavaScript のループを使用して、titleAuthorValues の最初の列のすべての値を調べます。JavaScript String indexOf() メソッドを使用して、カンマ部分文字列（", "）が見つかった場合、コードは次の処理を行います。
- セルの文字列値が
titlesAndAuthors変数にコピーされます。 - カンマの場所は、JavaScript String indexOf() メソッドを使用して決定されます。
- JavaScript String slice() メソッドを 2 回呼び出して、カンマより前の部分文字列と区切り文字の後の部分文字列を取得します。
- 部分文字列は titleAuthorValues 2D 配列にコピーされ、その位置の既存の値が上書きされます。「[authors], [title]」のパターンを想定しているため、2 つの部分文字列の順序を逆にして、1 列目にタイトルを配置し、2 列目に著者を配置します。
注: コードでカンマが見つからない場合、行内のデータは変更されません。
3: 新しい値をシートにコピーし直します
すべての title セル値が検査されると、更新された titleAuthorValues 2D 配列が、Range.setValues(values) メソッドを使用してスプレッドシートにコピーされます。
Results
splitAtFirstComma() 関数の動作を確認できます。実行した後、メニュー項目 [を上回る] を選択します。
...1 つのセル:
...または複数のセル:
ここでは、スプレッドシート データを処理する Apps Script 関数を作成しました。次に、2 番目のスプリッター関数を実装します。
9. 「区切り文字」でテキストを分割
元のデータには別の問題があります。一部のセルでは、タイトルと作成者を 1 つのセルに「authors, [title]"」のように書式設定しますが、他のセルには「[authors]」に「&title」と書式設定して記入します。
実装
この問題を解決するには、前のセクションと同じ方法で splitAtLastBy() という関数を作成します。この関数は splitAtFirstComma() と同様の仕事をします。唯一の違いは、わずかに異なるテキスト パターンを検索することです。次の手順で関数を実装します。
- Apps Script エディタで、スクリプト プロジェクトの末尾に次の関数を追加します。
/**
* Reformats title and author columns by splitting the title column
* at the last instance of the string " by ", if present.
*/
function splitAtLastBy(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where " by " substrings are found. Assumes
// the presence of a " by " indicates a "title by authors"
// pattern.
for(var row = 0; row < titleAuthorValues.length; row++){
var indexOfLastBy =
titleAuthorValues[row][0].lastIndexOf(" by ");
if(indexOfLastBy >= 0){
// Found a " by ", so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(0, indexOfLastBy);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(indexOfLastBy + 4);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- スクリプト プロジェクトを保存します。
コードレビュー
このコードと splitAtFirstComma() には主に次のような違いがあります。
- 部分文字列 "
by" は "," ではなく、文字列区切り文字として使用されます。 - ここでは、
String.indexOf(substring)の代わりに JavaScriptString.lastIndexOf(substring)メソッドを使用します。つまり、最初の文字列内に複数の「by」部分文字列がある場合、最後の「&」よりbyの部分のみがタイトルの一部とみなされます。 - 文字列を分割した後、最初の部分文字列はタイトルとして設定され、2 番目の部分文字列は作成者として設定されます（これは
splitAtFirstComma()と逆の順序になります）。
Results
splitAtLastBy() 関数の動作を確認できます。選択してから [最後のタイトル/著者] を選択し、実行してみてください。
...1 つのセル:
...または複数のセル:
Codelab のこのセクションを完了しました。Apps Script を使って、シート内の文字列データを読み取り、変更したり、カスタム メニューを使用してさまざまな Apps Script コマンドを実行したりできるようになりました。
次のセクションでは、公開 API から取得したデータを空白のセルに入力し、このデータセットをさらに改善する方法について説明します。
10. 概要: 公開 API からデータを取得する
データセットを修正してタイトルと著者の書式設定の問題を解決しました。ただし、以下のセルには、データセットに含まれる一部の情報が不足しています。
欠落しているデータは、現在のデータで文字列オペレーションを使用して取得することはできません。代わりに、不足しているデータを別のソースから取得する必要があります。これを行うには、Apps Script で、追加データを提供できる外部 API から情報をリクエストします。
API はアプリケーション プログラミング インターフェースです。これは一般的な用語ですが、基本的には、プログラムやスクリプトが情報を要求したり、特定のアクションを実行したりするために呼び出すことができるサービスです。このセクションでは一般公開されている API を呼び出し、スプレッドシートの空きセルに挿入できる書籍情報をリクエストします。
このセクションでは、次の方法について説明します。
- 外部の API ソースから書籍データをリクエストします。
- 返されたデータからタイトルと著者情報を抽出し、スプレッドシートに書き込みます。
11. UrlFetch で外部データを取得する
スプレッドシートで直接機能するコードを詳しく確認する前に、Apps Script で外部 API を操作する方法を学習できます。そのためには、公開されている Open Library API から書籍情報をリクエストするためのヘルパー関数を作成します。
Google のヘルパー関数 fetchBookData_(ISBN) は、書籍の 13 桁の ISBN コードをパラメータとして受け取り、その書籍に関するデータを返します。Open Library API に接続して情報を取得し、返された JSON オブジェクトを解析します。
実装
次の手順に従って、このヘルパー関数を実装します。
- Apps Script エディタで、スクリプトの末尾に次のコードを追加します。
/**
* Helper function to retrieve book data from the Open Library
* public API.
*
* @param {number} ISBN - The ISBN number of the book to find.
* @return {object} The book's data, in JSON format.
*/
function fetchBookData_(ISBN){
// Connect to the public API.
var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
+ ISBN + "&jscmd=details&format=json";
var response = UrlFetchApp.fetch(
url, {'muteHttpExceptions': true});
// Make request to API and get response before this point.
var json = response.getContentText();
var bookData = JSON.parse(json);
// Return only the data we're interested in.
return bookData['ISBN:' + ISBN];
}
- スクリプト プロジェクトを保存します。
コードレビュー
このコードは、次の 2 つのメイン セクションに分かれています。
1: API リクエスト
最初の 2 行では、fetchBookData_(ISBN) が、API の URL エンドポイントと Apps Script の URL 取得サービスを使用して、一般公開の Open Library API に接続します。
url 変数は、ウェブアドレスのような URL 文字列です。Open Library サーバー上の場所を指定します。また、リクエストする情報とレスポンスの構成方法を Open Library サーバーに伝えるための 3 つのパラメータ（bibkeys、jscmd、format）も含まれています。この場合、書籍の ISBN 番号を指定し、詳細情報を JSON 形式で返すようにリクエストします。
URL 文字列を作成すると、コードはその場所にリクエストを送信し、レスポンスを受信します。これには UrlFetchApp.fetch(url, params) メソッドを使用します。指定された外部 URL に情報リクエストを送信し、その結果のレスポンスを response 変数に格納します。このコードでは、URL に加えて、省略可能なパラメータ muteHttpExceptions を true に設定します。この設定を使用すると、リクエストで API エラーが発生した場合にコードは停止されません。代わりに、エラー レスポンスが返されます。
リクエストは、response 変数に格納されている HTTPResponse オブジェクトを返します。HTTP レスポンスには、レスポンス コード、HTTP ヘッダー、メインのレスポンス コンテンツが含まれます。ここで注目すべき情報は JSON のメイン コンテンツです。そのため、コードでコンテンツを抽出し、JSON を解析して必要な情報を見つけて返す必要があります。
2: API レスポンスを解析し、関心のある情報を返す
最後の 3 行のコードで、HTTPResponse.getContentText() メソッドはレスポンスのメイン コンテンツを文字列として返します。この文字列は JSON 形式ですが、Open Library API によって正確なコンテンツと形式が定義されます。JSON.parse(jsonString) メソッドは、JSON 文字列を JavaScript オブジェクトに変換して、データのさまざまな部分を簡単に抽出できるようにします。最後に、この関数は書籍の ISBN 番号に対応するデータを返します。
Results
fetchBookData_(ISBN) を実装したので、コード内の他の関数が ISBN 番号を使用して書籍情報を検索できます。この関数を使用すると、スプレッドシートのセルに入力できます。
12. スプレッドシートに API データを書き込む
次のことを行う fillInTheBlanks() 関数を実装できるようになりました。
- アクティブなデータ範囲内で欠落しているタイトルと著者データを特定します。
- 特定の書籍の欠落データを取得するには、
fetchBookData_(ISBN)ヘルパー メソッドを使用して Open Library API を呼び出します。 - セルごとに欠落しているタイトルまたは著者値を更新します。
実装
この新しい関数を実装する手順は次のとおりです。
- Apps Script エディタで、スクリプト プロジェクトの末尾に次のコードを追加します。
/**
* 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 existing 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're 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.
if (!bookData || !bookData.details) {
continue;
}
// The API might not return a title, so only fill it in
// if the response has one and if the title is blank in
// the sheet.
if(title === "" && bookData.details.title){
bookValues[row][TITLE_COLUMN] = bookData.details.title;
}
// The API might not return an author name, so only fill it in
// if the response has one and if 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;
}
}
}
// Insert the updated book data values into the spreadsheet.
dataRange.setValues(bookValues);
}
- スクリプト プロジェクトを保存します。
コードレビュー
このコードは次の 3 つのセクションに分かれています。
1: 既存の書籍情報を読み取る
関数の最初の 3 行では、コードを読みやすくするための定数が定義されています。次の 2 行では、bookValues 変数を使用して、シートの書籍情報のローカルコピーを保持しています。このコードは bookValues から情報を読み取り、API を使用して不足している情報を入力し、その値をスプレッドシートに書き込みます。
2: ヘルパー関数を使用して不足している情報を取得する
このコードでは、bookValues の各行をループして、欠落しているタイトルや著者を見つけます。効率を高めると同時に API 呼び出しの回数を減らすために、次の条件に当てはまる場合にのみコードから API を呼び出します。
- 行の ISBN 列に値があります。
- 行のタイトルまたは作成者のセルのいずれかが空です。
条件が true の場合、コードは以前に実装した fetchBookData_(isbn) ヘルパー関数を使用して API を呼び出し、結果を bookData 変数に格納します。これで、シートに挿入する情報が不足しています。
あとは bookData の情報をスプレッドシートに追加するだけです。ただし、いくつか注意点があります。残念ながら、Open Library Book API などの公開 API では、必要な情報が提供されないことや、情報提供の妨げとなるその他の問題が発生することがあります。すべての API リクエストが成功した場合、コードは予期しないエラーを処理するのに十分な堅牢性を失います。
コードで API エラーを処理できるようにするため、コードを使用する前に API レスポンスが有効であることを確認する必要があります。コードが bookData を取得すると、bookData と bookData.details が存在するかどうかを簡単に検証できます。いずれかが欠けている場合は、API が必要なデータを持っていなかったことを意味します。この場合、continue コマンドは、行をスキップするようにコードに指示します。欠落しているセルは入力できませんが、少なくともスクリプトはクラッシュしません。
3: 更新した情報をシートに書き戻す
コードの最後の部分も同様に、API が返されたタイトルと著者情報を検証するためのチェックを行います。このコードは、元のタイトルまたは著者のセルが空で、API が使用できる値を返した場合にのみ、bookValues 配列を更新します。
ループは、シート内のすべての行を調査した後で終了します。最後に、Range.setValues(values) を使用して、更新した bookValues 配列をスプレッドシートに書き戻します。
Results
これで、書籍データのクリーニングが完了します。手順は次のとおりです。
- まだ指定していない場合は、シートの A2:A15 範囲をハイライト表示し、[書籍リスト> 最初のタイトルをカンマで区切って入力する] を選択してカンマの問題を修正します。
- まだ指定していない場合は、シートの A2:A15 範囲をハイライト表示し、[書籍リスト>最後のタイトル/作成者を分割] を選択して「問題」をクリーンアップします。
- 残りのセルをすべて入力するには、[書籍リスト > 空のタイトルと著者のセルを入力] を選択します。
13. おわりに
この Codelab を完了しました。カスタム メニューを作成して Apps Script コードのさまざまな部分を有効にする方法を学習しました。Apps Script サービスと公開 API を使用して Google スプレッドシートにデータをインポートする方法も確認しました。これはスプレッドシート処理で一般的な操作です。Apps Script では、幅広いソースからデータをインポートできます。最後に、Apps Script サービスと JavaScript を使ってスプレッドシートのデータの読み取り、処理、挿入を行う方法を確認しました。
この Codelab はお役に立ちましたか。
学習した内容
- Google スプレッドシートからデータをインポートする方法。
onOpen()関数内でカスタム メニューを作成する方法- 文字列データ値を解析して操作する方法。
- URL 取得サービスを使用して公開 API を呼び出す方法
- 公開 API ソースから取得した JSON オブジェクト データを解析する方法。
次のステップ
この再生リストの次の Codelab では、スプレッドシート内のデータの形式を設定する方法をさらに詳しく説明します。
次の Codelab のデータ形式をご覧ください。