×

[PR]この広告は3ヶ月以上更新がないため表示されています。
ホームページを更新後24時間以内に表示されなくなります。


Googleを追いかけろ!

.

チュートリアル 中級者 2-1: スプレッドシートの読み取り

  • スプレッドシートへのアクセスを容易にする JavaScript Objects を利用してデータ構造を読み込みます。 getRowsData は今後もチュートリアルで扱う標準的で利用価値のある関数です。 この作業はおよそ30分で完成します。

§1 スプレッドシートとサンプルのセッティング

§2 テーブルを一行ずつJavaScript Objectsに読み込ませる

§3 ”getRowsData関数”の役割を理解する

§4 練習 ; テーブルをカラム毎に読み込む

§5 完成されたコード

チュートリアル 中級者/目次 参照ページ(Goolge)


WWW を検索 Googleを追いかけろ! を検索


§1 スプレッドシートとサンプルのセッティング

  1. あらかじめ自分のスプレッドシートにログインしておきます。今回のチュートリアルのためのテンプレートを入手します。【テンプレートの取得】をクリックして適当な名前でログインしてください。表示されたスプレッドシート(このシートでのスクリプトの実行はできません)のファイルメニューから 【コピーを作成】 をクリックしてコピーをとります。 (* テンプレートの取得)
何らかの理由で作業ができない場合はスプレッドシートにCSVファイルをアップロードしてこのページにある完成コードをエディタに貼り付けてもできます。
  1. 以上でシートの編集とスクリプトを書くことができます。
  2. 【Script Editor】で reading_data Script を開きます。
  3. runExample を実行すると、テーブル内の3行目の従業員情報がメッセージボックスに表れます。
つぎに getRowsData 関数の内容を見てみましょう。

§2 テーブルを一行ずつJavaScript Objectsに読み込ませる

ここはあまり深入りしない程度でこのチュートリアルから実用的な関数を説明します。先ず【Script Editor】で runExample に関するコードを探してください。
// 従業員データの保存されているセル範囲を取得
var employeeDataRange = ss.getRangeByName("employeeData");
名前の付いたセルの範囲を指定します。ここでは employeeData の範囲は A1:E100 に設定されています。 スプレッドシートに戻り 'Edit', 'Named Ranges', 'employeeData'などをクリックすれば確認できます。
// 従業員データのある各行について従業員オブジェクトを生成
var employeeObjects = getRowsData(sheet, employeeDataRange);
getRowsData 関数は runExample を制御する最も重要な働きをします。今後、スクリプトを書くときにはこの関数を積極的に利用することをお勧めします。
  var thirdEmployee = employeeObjects[2];
この時点でテーブル上のすべてのデータが getRowsData に読み込まれています。すべての行について JavaScript object が生成されているのです。 上のコードはテーブルの3番目の従業員(Maryanne Packard)の情報を検索します。配列は0からなので、一番目のエントリーは employeeObjects[0] であることに注意してください。
var stringToDisplay = "The third row is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
stringToDisplay += thirdEmployee.department + " department and with phone number ";
stringToDisplay += thirdEmployee.phoneNumber;
上のコードは従業員オブジェクトからのデータを使って文字変換をしているだけです。たて列名がオブジェクトプロパティと組み合わされてどのようにオブジェクトとなり得るのかに気付いてください。

例えば First Name(たて列)からのデータは object.firstName を利用して読み出せます。 サンプル上のすべての関数を他のスプレッドシートに(runExample を含まない)コピーすれば getRowsData はどのようなシートにも利用できます。

§3 ”getRowsData”の役割を理解する

ここでは実際に getRowsData がどのよに動くのかを詳しく説明します。また、【Spreadsheet API】や【一般的なJavaScriptの概念】についても触れます。
  1. getRowsData関数
// getRowsDataは入力範囲の行をループしてオブジェクトの配列を返す
// それぞれのオブジェクトは与えられた行データをすべて含み、たて列の正規化によりインデックスが作られる
// 引数:
//   - sheet: 処理すべきデータを持つシートオブジェクト
//   - range: データが保存されている正確なセル範囲
//   - columnHeadersRowIndex: たて列名が保存されている行番号を指定
//       この引数はオプションでデフォルトはその行の直前(上)の行
// オブジェクト配列を返す

function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
getRowsData は3つの引数を持っています。最後の引数 columnHeadersRowIndex はオプションで、初期値は現在の行にそれより一段上の行の範囲のインデックスが与えられています。 これは getRowsData 関数の一行目の()内のコードで提供されています。

もし columnHeadersRowIndex が設定されずに関数が実行された場合は range.getRowIndex()-1 が自動的に与えられ、getRowIndex 関数は Range object (範囲オブジェクト)の一行目のインデックスを返します。

var numColumns = range.getEndColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
一行分の関連のあるすべてのたて列名とデータを保持する Range objectが作られます。
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));
ここで、関連データを表示するメッセージボックスを作成することで問題を簡単にデバッグすることができます。つまり上のコードに headers変数 に格納したたて列名リストをコピーしたコードを挿入すればよいのです。配列を連続させる toSource() 関数(標準的なJavaScript関数)を呼び出すことです。 最終的には下のようなコードになります。
var headers = headersRange.getValues()[0];
Browser.msgBox(headers.toSource());
return getObjects(range.getValues(), normalizeHeaders(headers));

getObjects 関数は次の2つのパラメーターと共に呼び出されます。
  • データ範囲のすべての値を持つJavaScriptの2次元配列。具体的には range.getValues()[0][1] ならば一行目から2行目の Bergerまでのカラムです。
  • 正規化されたたて列名の配列。これについては後述の normalizeHeaders 関数で補足します。
  1. getObjects 関数
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}
data の第2配列の各行からループが始まります。次は行中のカラムへのループとセルへのプロセスデータです。こうして空ではない生成されたすべてのオブジェクトが配列オブジェクトとして追加され返されます。
object[keys[j]] = cellData;
プロパティに keys[j] 名を持つオブジェクトに cellData の値を設定する重要な宣言になります。
  1. カラム名の正規化関数
// 単語に分けるために英数字を破棄し、小文字を使いながら文字の正規化
// アウトプットはいつも小文字で始まる
// この関数はJavaScript objectのオブジェクトプロパティ名を作るようにデザインされている
// 引数:
//   - header: 正規化される文字列
// 例:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"

function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // 最初の文字は文字でなければならない(数字はダメ)
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

この関数は純粋なJavaScriptです。基本的には英数字以外の文字列を取り除きながら header に文字列入力を繰り返します。また、JavaScript object のプロパティの定義づけに使える文字を返します。
// 正規化された文字列の配列を返す
// 引数:
//   - headers: 正規化する文字列配列

function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}
headers 配列で定義された文字列を一文字ずつすべて正規化している関数です。

§4 練習 ; テーブルをカラム毎に読み込む

getRowsData 関数にも慣れたと思うのでここでチャレンジをしてみましょう。行毎ではなくカラム毎(たて列)にデータを読み込むにはどうしたらよいでしょうか? テンプレートの2番目のシートに同じデータを用意してありますがカラムがありません。
getColumnsData をどのように改良すればよいでしょうか。可能な解答はたくさんありますが、前述のコードを再利用して作ってみてください。 サンプルコードを読む前に挑戦してみましょう。
function runExercise() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];

  // 従業員データの保存されたセル範囲を得る
  var employeeDataRange = sheet.getRange("B1:F5");

  // 従業員データのあるすべての行に対して従業員オブジェクトを生成
  var employeeObjects = getColumnsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];
  var stringToDisplay = "The third column is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;
  ss.msgBox(stringToDisplay);
}

// この関数はJavaScriptの二次元配列を得て変換されたテーブルを返す
// 引数:
//   - data: JavaScriptの二次元配列
// JavaScriptの二次元配列を返す
// 例: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].

function arrayTranspose(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }

  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }

  return ret;
}

// getColumnsData は入力範囲上でカラム毎にループして配列オブジェクトを返す
// 各オブジェクトは与えられたコラムのデータを持ち、正規化された行名によってインデックスをつけられる
// 引数:
//   - sheet: 処理すべきデータのあるシートオブジェクト
//   - range: データ入力されている正確なセル範囲
//   - rowHeadersColumnIndexは行名の保存されているカラム番号を指定する
//       この引数はオプション。デフォルトは範囲の左隣のカラムに決まる
// 配列オブジェクトを返す

function getColumnsData(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
  return getObjects(arrayTranspose(range.getValues()), headers);
}
runExercise を実行してみるとはじめのプログラムと同じ結果になることが分かるでしょう。

§5 完成されたコード

完成例
function runExample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // 従業員情報の保存されているセル範囲を取得
  var employeeDataRange = ss.getRangeByName("employeeData");

  // 従業員データのすべての行に対して従業員オブジェクトを生成
  var employeeObjects = getRowsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];

  var stringToDisplay = "The third row is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;

  Browser.msgBox(stringToDisplay);
}

function runExercise() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];

  // 従業員情報の保存されているセル範囲を取得
  var employeeDataRange = sheet.getRange("B1:F5");

  // 従業員データのすべての行に対して従業員オブジェクトを生成
  var employeeObjects = getColumnsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];
  var stringToDisplay = "The third column is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;
  ss.msgBox(stringToDisplay);
}
Library
// getRowsData は入力された行範囲をループしてオブジェクト配列を返す
// それぞれのオブジェクトは行に与えられたすべてのデータを含み、正規化されたカラム名によってインデックス化される
// 引数:
//   - sheet: 処理すべきデータをもつシートオブジェクト
//   - range: データが保存されている正確なセル範囲
//   - columnHeadersRowIndex: は行名の保存されているカラム番号を指定する
//       この引数はオプション。デフォルトは範囲の真上のカラムに決まる
// 配列オブジェクトを返す
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

// getColumnsData は入力された行範囲をループしてオブジェクト配列を返す
// それぞれのオブジェクトは行に与えられたすべてのデータを含み、正規化されたカラム名によってインデックス化される
// Arguments:
//   - sheet: 処理すべきデータをもつシートオブジェクト
//   - range: データが保存されている正確なセル範囲
//   - rowHeadersColumnIndex: は行名の保存されているカラム番号を指定する
//       この引数はオプション。デフォルトは範囲の左隣のカラムに決まる
// 配列オブジェクトを返す

function getColumnsData(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
  return getObjects(arrayTranspose(range.getValues()), headers);
}


// データの中のあらゆる行データについてデータを持つオブジェクトが生成される
// オブジェクトフィールドの名前はそれぞれkeyとして定義される
// 引数:
//   - data: JavaScript 二次配列
//   - keys: 生成すべきオブジェクトが持つプロパティ名を定義付ける文字列の配列

function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// 正規化された文字列の配列を返す
// 引数:
//   - headers: 正規化すべき文字列配列

function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

// 単語に分けるためにすべての英数字を破棄し小文字を利用して文字を正規化する
// アウトプットはいつも小文字で始まる
// この関数はJavaScript objectのオブジェクトプロパティ名を作るようにデザインされている
// 引数:
//   - header: 正規化すべき文字列
// 例:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"

function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// 読み込まれたセルデータが空のときtrueを返す
// 引数:
//   - cellData: 文字列

function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// 文字chrがアルファベット順ならばtrue、そうでないならfalseを返す
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// 文字が数字ならtrue、そうでないならfalseを返す
function isDigit(char) {
  return char >= '0' && char <= '9';
}

// JavaScript二次配列を与えられたこの関数は転置(入れ替え)表を返す
// 引数:
//   - data: JavaScript二次配列
// JavaScript二次配列を返す
// 例: Transpose配列([[1,2,3],[4,5,6]]) は [[1,4],[2,5],[3,6]]を返す

function arrayTranspose(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }

  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }

  return ret;
}
これでどのようなスプレッドシートでも【名前付範囲】(Named Ranges)と【getRowsData関数】【getColumnsData関数】をリユースすればデータをJavaScript objectsに組み込むことができます。 次はJavaScript Objectsのデータをセルに移す方法です。




    * チュートリアル 中級者/目次
    1. スプレッドシートの読み取り - JavaScript オブジェクトを利用してシート内のデータ構造を読み取る
    2. スプレッドシートへの書き込み - シートに構築されたデータを読み取り他のシートへ移植
    3. トーナメント表の作成 - バスケットのトーナメント表をスプレッドシートで作成
    4. Google Site の作成 - Gmail や Calendar からデータを収集してサッカーチーム用のサイトを作成
    5. Docs Listとの連携を図る - Docs List上のファイル検索やCSVファイルのインポート(エクスポート)
    6. Google Sites にApps Script を埋め込む - Google SitesにGoogle Apps Scriptを実装する
    7. デバッガーの利用 - YouTubeのVideoによる Tutorial
    8. Google Sites に Google DocList を埋め込む - ファイルキャビネットとリストページを利用してフォルダーを作成する
    9. 読書リストからスプレッドシートを作成する - 未読URLリストからタスクアイテムを作成するための同期関数を作成するGoogle APIの統合
    10. Gmail受信トレイの統計レポート - Gmail内の毎月の送受信とそのトップ5の統計情報
    --チュートリアル 中級者 2-1.スプレッドシートを読み込む : end -- 2011/10/30