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

ここはあまり深入りしない程度でこのチュートリアルから実用的な関数を説明します。先ず【Script Editor】で runExample に関するコードを探してください。

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

名前の付いたセルの範囲を指定します。ここでは employeeData の範囲は A1:E１00 に設定されています。 スプレッドシートに戻り 'Edit', 'Named Ranges', 'employeeData'などをクリックすれば確認できます。

ここでは実際に getRowsData がどのよに動くのかを詳しく説明します。また、【Spreadsheet API】や【一般的なJavaScriptの概念】についても触れます。

// getRowsDataは入力範囲の行をループしてオブジェクトの配列を返す
// それぞれのオブジェクトは与えられた行データをすべて含み、たて列の正規化によりインデックスが作られる
// 引数:
//   - sheet: 処理すべきデータを持つシートオブジェクト
//   - range: データが保存されている正確なセル範囲
//   - columnHeadersRowIndex: たて列名が保存されている行番号を指定
//       この引数はオプションでデフォルトはその行の直前（上）の行
// オブジェクト配列を返す

function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;

getRowsData は３つの引数を持っています。最後の引数 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 関数は次の２つのパラメーターと共に呼び出されます。

• データ範囲のすべての値を持つJavaScriptの２次元配列。具体的には range.getValues()[0][1] ならば一行目から２行目の Bergerまでのカラムです。
• 正規化されたたて列名の配列。これについては後述の normalizeHeaders 関数で補足します。

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 の第２配列の各行からループが始まります。次は行中のカラムへのループとセルへのプロセスデータです。こうして空ではない生成されたすべてのオブジェクトが配列オブジェクトとして追加され返されます。

object[keys[j]] = cellData;

プロパティに keys[j] 名を持つオブジェクトに cellData の値を設定する重要な宣言になります。

getRowsData 関数にも慣れたと思うのでここでチャレンジをしてみましょう。行毎ではなくカラム毎（たて列）にデータを読み込むにはどうしたらよいでしょうか？ テンプレートの２番目のシートに同じデータを用意してありますがカラムがありません。

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 を実行してみるとはじめのプログラムと同じ結果になることが分かるでしょう。

完成例

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のデータをセルに移す方法です。