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

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

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 プロジェクトのタイトルをクリックし、「無題のプロジェクト」から「データ操作とカスタムメニュー」に変更します。[名前を変更] をクリックしてタイトルの変更を保存します。

空のスプレッドシートとプロジェクトがあれば、ラボを開始できます。カスタムメニューの詳細については、次のセクションをご覧ください。

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

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

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

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 スプレッドシートで、スプレッドシートを再読み込みします。注: 通常はスクリプトエディタでタブを閉じます。
[ツール &gt スクリプトエディタ] を選択して、スクリプトエディタを再度開きます。

スプレッドシートが再読み込みされると、メニューバーに新しい 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) の代わりに JavaScript String.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 オブジェクトデータを解析する方法。

主なコンセプト:

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

主な用語:

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

次のステップ

この再生リストの次の Codelab では、スプレッドシート内のデータの形式を設定する方法をさらに詳しく説明します。

次の Codelab のデータ形式をご覧ください。

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

1. はじめに

学習内容

始める前に

必要なもの

2. 設定

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

4. onOpen() 関数

実装

コードレビュー

Results

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

実装

コードレビュー

1: リンク先のシートを特定する

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

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

4: リンク先ページの書式を設定する

Results

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

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

実装

Results

8. カンマでテキストを分割する

実装

コードレビュー

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

2: 各タイトルを確認し、最初に見つかったカンマで分割します

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

Results

9. 「区切り文字」でテキストを分割

実装

コードレビュー

Results

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

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

実装

コードレビュー

1: API リクエスト

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

Results

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

実装

コードレビュー

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

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

3: 更新した情報をシートに書き戻す

Results

13. おわりに

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

学習した内容

次のステップ

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

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