このページは Cloud Translation API によって翻訳されました。

Google スプレッドシートを使用した Apps Script の基礎 #3: データの操作

1. はじめに

「Google スプレッドシートを使用した Apps Script の基礎」Codelab 再生リストの第 3 部へようこそ。

この Codelab を完了すると、Apps Script でデータ操作、カスタムメニュー、公開 API データ取得を使用して、スプレッドシートの操作性を向上させる方法を学ぶことができます。このプレイリストの前の Codelab で紹介した SpreadsheetApp、Spreadsheet、Sheet、Range の各クラスを引き続き使用します。

学習内容

ドライブの個人用スプレッドシートまたは共有スプレッドシートからデータをインポートする方法。
onOpen() 関数を使用してカスタムメニューを作成する方法。
Google スプレッドシートのセルで文字列データ値を解析して操作する方法。
公開 API ソースから JSON オブジェクトデータを取得して操作する方法。

始める前に

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

必要なもの

この再生リストの以前の Codelab で取り上げた Apps Script の基本的なトピックを理解している。
Apps Script エディタに関する基本的な知識
Google スプレッドシートに関する基礎知識
スプレッドシートの A1 形式を読み取る機能
JavaScript とその String クラスに関する基本的な知識

2. セットアップ

この Codelab の演習では、作業用のスプレッドシートが必要です。次の手順に沿って、これらの演習で使用するスプレッドシートを作成します。

Google ドライブでスプレッドシートを作成します。この操作は、ドライブのインターフェースで [新規] > [Google スプレッドシート] を選択して行うことができます。新しいスプレッドシートが作成され、開きます。ファイルはドライブフォルダに保存されます。
スプレッドシートのタイトルをクリックして、「無題のスプレッドシート」から「Data Manipulation and Custom Menus」に変更します。シートは次のようになります。

スクリプトエディタを開くには、[拡張機能] > [Apps Script] をクリックします。
Apps Script プロジェクトのタイトルをクリックし、「無題のプロジェクト」から「Data Manipulation and Custom Menus」に変更します。[名前を変更] をクリックして、タイトルの変更を保存します。

空白のスプレッドシートとプロジェクトが用意できたら、ラボを開始できます。次のセクションに進み、カスタムメニューについて学習しましょう。

注: gmail.com アカウントを使用している場合、スクリプトを初めて使用するときに「このアプリは確認されていません」というダイアログが表示されることがあります。Google は、この情報を使用して、不明な作成者や信頼できない作成者のコードを使用している可能性のあるユーザーに警告します。このダイアログが表示された場合は、スクリプトの作成者であるため、続行しても問題ありません。画面の指示に沿って、スクリプトの承認を続行します。

未確認アプリのダイアログで、[詳細] をクリックします。
[データ操作とカスタムメニュー（安全でない）に移動] をクリックします。
[許可] をクリックします。

この Codelab では、権限に関するプロンプトがいくつか表示されることがあります。このプロセスの詳細については、Google サービスの承認をご覧ください。

3. 概要: カスタムメニュー項目を使用してデータをインポートする

Apps Script を使用すると、Google スプレッドシートに表示されるカスタムメニューを定義できます。Google ドキュメント、Google スライド、Google フォームでもカスタムメニューを使用できます。カスタムメニュー項目を定義するときは、テキストラベルを作成して、スクリプトプロジェクトの Apps Script 関数に接続します。その後、メニューを UI に追加して、Google スプレッドシートに表示できます。

ユーザーがカスタムメニュー項目をクリックすると、関連付けた Apps Script 関数が実行されます。これは、スクリプトエディタを開かずに Apps Script 関数を実行する簡単な方法です。また、スプレッドシートの他のユーザーは、コードや Apps Script の仕組みについて何も知らなくても、コードを実行できます。ユーザーにとっては、単なるメニュー項目の一つです。

カスタムメニュー項目は、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() 関数（まだ存在しない）を実行しようとします。

結果

この関数を実行して、動作を確認します。

Google スプレッドシートで、スプレッドシートを再読み込みします。注: 通常、これでスクリプトエディタのタブが閉じます。
[ツール] > [スクリプトエディタ] を選択して、スクリプトエディタを再度開きます。

スプレッドシートが再読み込みされると、メニューバーに新しい Book-list メニューが表示されます。

[Book-list] をクリックすると、次のようなメニューが表示されます。

次のセクションでは、loadBookList() 関数のコードを作成し、Apps Script でデータを操作する方法の 1 つとして、他のスプレッドシートを読み取る方法を紹介します。

5. スプレッドシートのデータをインポートする

カスタムメニューを作成したら、メニュー項目をクリックして実行できる関数を作成できます。

現在、カスタムメニュー Book-list には 1 つのメニュー項目 Load Book-list. があります。Load Book-list メニュー項目を選択したときに呼び出される関数 loadBookList(), がスクリプトに存在しないため、[Book-list > Load Book-list] を選択するとエラーがスローされます。

このエラーは、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: 転送先のシートを特定する

1 行目では、SpreadsheetApp.getActiveSheet() を使用して現在のシートオブジェクトへの参照を取得し、変数 sheet に保存します。データがコピーされるシートです。

2: ソースデータを特定する

次の数行では、取得するソースデータを参照する 4 つの変数を設定します。

bookSS には、コードがデータを読み取るスプレッドシートへの参照が格納されます。このコードでは、スプレッドシート ID でスプレッドシートを検索します。この例では、読み取るソーススプレッドシートの ID を指定し、SpreadsheetApp.openById(id) メソッドを使用してスプレッドシートを開いています。
bookSheet には、必要なデータを含む bookSS 内のシートへの参照が格納されます。このコードでは、読み取るシートを名前（codelab-book-list）で識別しています。
bookRange には、bookSheet 内のデータ範囲への参照が格納されます。Sheet.getDataRange() メソッドは、シート内の空でないすべてのセルを含む範囲を返します。これは、空の行と列を含めずに、シート内のすべてのデータをカバーする範囲を確実に取得する簡単な方法です。
bookListValues は、bookRange のセルから取得したすべての値を含む 2 次元配列です。Range.getValues() メソッドは、ソースシートからデータを読み取ってこの配列を生成します。

3: ソースから宛先にデータをコピーする

次のコードセクションでは、bookListValues データを sheet にコピーし、シートの名前を変更します。

Sheet.getRange(row, column, numRows, numColumns) は、sheet 内のデータのコピー先を特定するために使用されます。
Range.getHeight() メソッドと Range.getWidth() メソッドは、データのサイズを測定し、同じディメンションの宛先範囲を定義するために使用されます。
Range.setValues(values) は、bookListValues の 2 次元配列をコピー先の範囲にコピーし、既存のデータを上書きします。

4: リンク先シートの書式を設定する

Sheet.setName(name) は、コピー先シートの名前を Book-list に変更するために使用されます。関数の最後の行では、Sheet.autoResizeColumns(startColumn, numColumns) を使用して、宛先シートの最初の 3 つの列のサイズを変更し、新しいデータを読みやすくしています。

結果

この関数が実際に動作している様子を確認できます。Google スプレッドシートで、[Book-list > Load book-list] を選択して関数を実行し、スプレッドシートにデータを入力します。

これで、書籍のタイトル、著者、13 桁の ISBN コードのリストを含むシートが作成されました。次のセクションでは、文字列操作とカスタムメニューを使用して、この書籍リストのデータを変更および更新する方法について説明します。

6. 概要: スプレッドシートのデータをクリーニングする

これで、シートに書籍の情報が表示されます。各行は特定の書籍を参照し、タイトル、著者、ISBN 番号が別々の列に表示されます。ただし、この生データには次のような問題もあります。

一部の行では、タイトルと著者がタイトル列にまとめて配置され、カンマまたは「 by 」という文字列でリンクされています。
一部の行に書籍のタイトルまたは著者がありません。

次のセクションでは、データをクリーンアップしてこれらの問題を修正します。最初の問題については、タイトル列を読み取り、カンマまたは「by」区切り文字が見つかったときにテキストを分割し、対応する著者とタイトルのサブ文字列を正しい列に配置する関数を作成します。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 とともに、その下に表示されます。

結果

スプレッドシートで Book-list メニューをクリックすると、新しいメニュー項目が表示されます。

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

8. カンマ区切り文字でテキストを分割する

スプレッドシートにインポートしたデータセットには、著者とタイトルがカンマで区切られて 1 つのセルに誤って結合されているセルがいくつかあります。

テキスト文字列を複数の列に分割することは、スプレッドシートでよく行われるタスクです。Google スプレッドシートには、文字列を列に分割する SPLIT() 関数が用意されています。ただし、データセットには、スプレッドシートの組み込み関数では簡単に解決できない問題がよくあります。このような場合は、Apps Script コードを記述して、データのクリーンアップと整理に必要な複雑なオペレーションを実行できます。

まず、カンマが見つかったときに著者とタイトルをそれぞれのセルに分割する splitAtFirstComma() という関数を実装して、データのクリーニングを開始します。

splitAtFirstComma() 関数は、次の手順を実行する必要があります。

現在選択されているセルを表す範囲を取得します。
範囲内のセルにカンマが含まれているかどうかを確認します。
カンマが見つかった場合は、最初のカンマの位置で文字列を 2 つ（2 つのみ）の部分文字列に分割します。わかりやすくするために、カンマは「[著者], [タイトル]」という文字列パターンを示すものとします。セルに複数のカンマが含まれている場合は、文字列の最初のカンマで分割するのが適切であると想定することもできます。
部分文字列を、それぞれのタイトルと著者のセルに新しいコンテンツとして設定します。

実装

これらの手順を実装するには、以前に使用したのと同じスプレッドシートサービスメソッドを使用しますが、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 から抽出されたデータの 2 次元配列です。

2: 各タイトルを調べて、最初に見つかったカンマ区切り文字で分割する

次のセクションでは、titleAuthorValues の値を調べてカンマを探します。JavaScript の For ループを使用して、titleAuthorValues の最初の列のすべての値を調べます。JavaScript String indexOf() メソッドを使用してカンマのサブ文字列（", "）が見つかった場合、コードは次の処理を行います。

セルの文字列値が titlesAndAuthors 変数にコピーされます。
カンマの位置は、JavaScript String indexOf() メソッドを使用して決定されます。
JavaScript String slice() メソッドが 2 回呼び出され、カンマ区切り文字の前の部分文字列と区切り文字の後の部分文字列が取得されます。
サブストリングは titleAuthorValues 2 次元配列にコピーされ、その位置にある既存の値が上書きされます。「[authors], [title]」パターンを想定しているため、2 つの部分文字列の順序が逆になり、タイトルが最初の列に、著者が 2 番目の列に配置されます。

注: コードでカンマが見つからない場合、行のデータは変更されません。

3: 新しい値をシートにコピーする

すべてのタイトルセルの値が調べられると、更新された titleAuthorValues 2 次元配列が Range.setValues(values) メソッドを使用してスプレッドシートにコピーされます。

結果

これで、splitAtFirstComma() 関数の効果を確認できます。[Separate title/author at first comma] メニュー項目を選択してから、... を選択して実行してみてください。

...1 つのセル:

...または複数のセル:

これで、スプレッドシートのデータを処理する Apps Script 関数が作成されました。次に、2 つ目のスプリッタ関数を実装します。

9. 「by」区切り文字でテキストを分割します。

元のデータを見ると、別の問題があることがわかります。一部のデータ形式では、タイトルと著者が 1 つのセルに「[著者], [タイトル]」として表示されますが、他のセルでは著者とタイトルが「[タイトル]（[著者] 著）」として表示されます。

実装

この問題は、前のセクションと同じ手法を使用して解決できます。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) の代わりに JavaScript の String.lastIndexOf(substring) メソッドが使用されています。つまり、元の文字列に複数の「 by 」サブ文字列がある場合、最後の「 by 」を除くすべてがタイトルの一部と見なされます。
文字列を分割した後、最初のサブストリングがタイトルとして設定され、2 番目のサブストリングが著者として設定されます（これは splitAtFirstComma() とは逆の順序です）。

結果

これで、splitAtLastBy() 関数の効果を確認できます。選択後に [Separate title/author at last "by"] メニュー項目を選択して実行してみてください。

...1 つのセル:

...または複数のセル:

この Codelab のこのセクションは終了です。Apps Script を使用してシート内の文字列データを読み取って変更したり、カスタムメニューを使用してさまざまな Apps Script コマンドを実行したりできるようになりました。

次のセクションでは、一般公開 API から取得したデータを使用して空白のセルを埋め、このデータセットをさらに改善する方法について説明します。

10. 概要: 公開 API からデータを取得する

これまでのところ、データセットを絞り込んで、タイトルと著者の形式に関する問題を修正しましたが、データセットにはまだ一部の情報が欠落しています。以下のセルでハイライト表示されている情報です。

現在あるデータに対して文字列オペレーションを使用しても、欠損データを取得することはできません。代わりに、別のソースから欠落しているデータを取得する必要があります。Apps Script で、追加データを提供できる外部 API から情報をリクエストすることで、これを行うことができます。

API は、アプリケーションプログラミングインターフェースです。一般的な用語ですが、基本的には、プログラムやスクリプトが情報をリクエストしたり、特定のアクションを実行したりするために呼び出すことができるサービスです。このセクションでは、一般公開されている API を呼び出して、シートの空のセルに挿入できる書籍情報をリクエストします。

このセクションでは、次の方法について説明します。

外部 API ソースから書籍データをリクエストします。
返されたデータからタイトルと著者の情報を抽出し、スプレッドシートに書き込みます。

11. UrlFetch を使用して外部データを取得する

スプレッドシートを直接操作するコードを詳しく説明する前に、Apps Script で外部 API を操作する方法を学びましょう。具体的には、公開されている Open Library API から書籍情報をリクエストするための専用のヘルパー関数を作成します。

ヘルパー関数 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 番号に対応するデータを返します。

結果

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 配列を更新します。

シート内のすべての行が調べられると、ループが終了します。最後のステップは、更新された bookValues 配列を Range.setValues(values) を使用してスプレッドシートに書き戻すことです。

結果

これで、書籍データのクリーニングを完了できます。手順は次のとおりです。

まだ行っていない場合は、シートの A2:A15 の範囲をハイライト表示し、[Book-list] > [Separate title/author at first comma] を選択して、カンマの問題を解決します。
まだ行っていない場合は、シートの A2:A15 の範囲をハイライト表示し、[Book-list > Separate title/author at last "by"] を選択して、「by」の問題を解決します。
残りのすべてのセルに入力するには、[Book-list] > [Fill in blank titles and author cells] を選択します。

13. まとめ

この Codelab は以上で終了です。Apps Script コードのさまざまな部分を有効にするカスタムメニューを作成する方法を学習しました。また、Apps Script サービスと公開 API を使用して Google スプレッドシートにデータをインポートする方法も学びました。これはスプレッドシート処理で一般的なオペレーションであり、Apps Script を使用すると、幅広いソースからデータをインポートできます。最後に、Apps Script サービスと JavaScript を使用して、スプレッドシートデータの読み取り、処理、挿入を行う方法を学びました。

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

はい

いいえ

学習した内容

Google スプレッドシートからデータをインポートする方法。
onOpen() 関数でカスタムメニューを作成する方法。
文字列データ値を解析して操作する方法。
URL 取得サービスを使用してパブリック API を呼び出す方法。
公開 API ソースから取得した JSON オブジェクトデータを解析する方法。

主なコンセプト:

onOpen() は、メニュー項目の作成に使用されるシンプルなトリガーです。シンプルなトリガーには、遵守する必要があるさまざまな制限があります。
スプレッドシートのセルデータを読み書きする場合は、Range.getValues() または Range.setValues(values) を使用して、範囲全体を一度に読み書きすることをおすすめします。各セルを個別に読み取ったり設定したりすると、処理が遅くなるため、避けてください。
_（アンダースコア）で終わる Apps Script 関数名は、非公開と見なされます。ライブラリに存在する場合、またはサーバーとクライアントの通信を介してクライアントから呼び出される場合、他のスクリプトはこれらの関数を呼び出すことができません。関数が現在のスクリプトでのみ使用されることがわかっている場合は、関数名の末尾に _ を付けることをおすすめします。
外部 API から返されたデータを使用する場合は、使用する前にデータを検証します。これにより、API が予期しない結果を返した場合のコードのエラーが軽減されます。

キーワード:

Active（ステータス）: 指定されたスプレッドシート、シート、範囲、セルが、スプレッドシートのユーザーが現在表示またはハイライト表示しているものであることを示します。
アクティブセル: アクティブシート内でカーソルがフォーカスされている、ハイライト表示された単一のセル。
アクティブな範囲: アクティブなシート内で現在ハイライト表示されている 1 つ以上のセルのグループ。
API: アプリケーションプログラミングインターフェースは、プログラムやスクリプトが呼び出して情報をリクエストしたり、特定のアクションを実行したりできるサービスです。
承認: ユーザーが Apps Script に権限を付与して、関連する Google サービスでユーザーデータにアクセスしたり、ユーザーに代わって操作を行ったりできるようにするプロセス。
コンテナバインドスクリプト: Google スプレッドシートや Google ドキュメントなど、Google Workspace ドキュメントにバインドされ、そこから作成されたスクリプト。
ヘルパー関数: 通常はプライベートな関数で、別の関数から呼び出されて小さなサブタスクを完了します。ヘルパー関数は、サブタスクをさまざまな場所で何度も完了する必要がある場合に使用されます。
JSON: テキストからデータオブジェクトとその特性を定義するためのファイル形式。
onOpen(): スクリプトで定義されている場合、ファイルが開くか再読み込みされるたびに起動するシンプルなトリガーです。
非公開関数: ライブラリの一部として、またはサーバークライアント通信を介してクライアントから呼び出すことができない関数。Apps Script の非公開関数には、_ で終わる名前が付けられます。
範囲**: シート内の 1 つ以上の隣接するセルのグループ。 Range** クラスを使用すると、シート内の範囲を読み取って更新できます。
スクリプトエディタ: Apps Script のブラウザ内コードエディタ。
シンプルなトリガー: 予約済みのトリガー関数名と特定の制限事項がある、Apps Script のトリガーのサブタイプ。
シート**: スプレッドシートの 1 ページ。Google ドライブ内の Google スプレッドシートファイルを指すために使用されることがあります。 Sheet** クラスを使用すると、シートにアクセスして変更できます。
SpreadsheetApp: このクラスは、スプレッドシートサービスの親クラスとして機能し、Google スプレッドシートのデータを読み取ったり操作したりするコードの出発点となります。
スプレッドシート**: Google ドライブに保存されている Google スプレッドシートファイル。 Spreadsheet** クラスを使用すると、スプレッドシートにアクセスして変更できます。
スプレッドシートサービス: スクリプトが Google スプレッドシートファイルの作成、アクセス、変更を行えるようにする Apps Script サービス。
トリガー: Apps Script 関数にキー設定されたイベント。イベント（スプレッドシートを開くなど）が発生すると、トリガーが「起動」し、関連付けられた Apps Script 関数が自動的に実行されます。
URL 取得サービス: スクリプトが URL エンドポイントに接続してリクエストを送信し、レスポンスを受信できるようにする Apps Script サービス。

次のステップ

このプレイリストの次の Codelab では、スプレッドシート内のデータの書式設定について詳しく説明します。

次の Codelab は、データ形式で確認してください。

Google スプレッドシートを使用した Apps Script の基礎 #3: データの操作 コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

1. はじめに

学習内容

始める前に

必要なもの

2. セットアップ

3. 概要: カスタム メニュー項目を使用してデータをインポートする

4. onOpen() 関数

実装

コードレビュー

結果

5. スプレッドシートのデータをインポートする

実装

コードレビュー

1: 転送先のシートを特定する

2: ソースデータを特定する

3: ソースから宛先にデータをコピーする

4: リンク先シートの書式を設定する

結果

6. 概要: スプレッドシートのデータをクリーニングする

7. メニュー項目を追加する

実装

結果

8. カンマ区切り文字でテキストを分割する

実装

コードレビュー

1: ハイライト表示されたタイトルの値を取得する

2: 各タイトルを調べて、最初に見つかったカンマ区切り文字で分割する

3: 新しい値をシートにコピーする

結果

9. 「by」区切り文字でテキストを分割します。

実装

コードレビュー

結果

10. 概要: 公開 API からデータを取得する

11. UrlFetch を使用して外部データを取得する

実装

コードレビュー

1: API リクエスト

2: API レスポンスを解析し、関心のある情報を返す

結果

12. API データをスプレッドシートに書き込む

実装

コードレビュー

1: 既存の書籍情報を読み取る

2: ヘルパー関数を使用して不足している情報を取得する

3: 更新された情報をシートに書き込む

結果

13. まとめ

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

学習した内容

次のステップ

Google スプレッドシートを使用した Apps Script の基礎 #3: データの操作

3. 概要: カスタムメニュー項目を使用してデータをインポートする