×

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


Googleを追いかけろ!

.

チュートリアル 中級者 2-2.スプレッドシートへの書き込み

  • スプレッドシートから構造化されたデータを読み込み、データの異なるビューを持つ他のシートに移植する方法です。

    チュートリアルの他の分野でも再利用されている便利で標準的な関数である setRowsData 関数を扱います。 およそ20分の作業です。 始める前にgetRowsData関数を理解しておきましょう。

§1 準備とサンプルプログラムの実行

§2 サンプルコードの内容

§3 ”setRowsData関数”の動き

§4 アイディアと練習

§5 完成されたコード

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


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


* 準備とサンプルプログラムの実行

  1. 新規のスプレッドシートを用意します。
  2. 次に【Script Editor】の準備をします。
  1. 今回のコードをコピー&ペーストします。 * 完成されたコード (コード内容はこの後の解説が終わるまで読まないようにしましょう。)
以下、このプログラムの目標とするところです。
  • 他のシートからデータを読み取る。ここではこのシートになります。
  • 部署名別のシートを動的に作成します。
  • 適当なシートに従業員の First Name, Last Name , Department データを挿入します。
用意ができたら runExample関数 を実行して結果を検討します。
  • アルファベット順に並んだ Engineering, Legal, Marketing, Sales の5枚のシートが追加されたことを確認してください。
  • それぞれのシートには部署別の従業員情報が入力されています。
コードの解読をします。

§2 サンプルコードの内容

下に runExampl サンプルの完成コードを示します。
function runExample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var dataSheet = dataSs.getSheets()[0];

  // データを拾い集める
  var data = getRowsData(dataSheet);

  // 表示しようとしているデータ
  var columnNames = ["First Name", "Last Name", "Department"];
  
  // 部署名によるデータにインデックスをつける
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }
  
  departments.sort();
  var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
  for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) || 
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
}
コード内のコメント箇所についてさらに補足します。
var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
データを含むシートの開き方です。DATA_SPREADSHEET_ID とはこの場合 こちらのUrlから引用しているUrlの最後の部分である rKTOLH41HOK2gLosf7d6P1gのことです。
var data = getRowsData(dataSheet);
すべての行データについてJavaScriputoオブジェクトが生成されています。ここは2-1.スプレッドシートを読み込むに詳しく解説されています。 引数 dataSheet とはアクティブなシートのことではありません。ですから実際にデータは下のようになっています。
 [ {firstName: "John",    lastName: "Berger", employeeId: 3512, department: "Sales",       phoneNumber: "(212) 123-4560"},
    {firstName: "Patrick", lastName: "Benson", employeeId: 1342, department: "Engineering", phoneNumber: "(212) 123-4561"},
    ...
  ]

  // 部署名によるデータにインデックスをつける
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }
部署名別にオブジェクトのリストを作成しているのが下のコードです。
departments.sort();
部署名のリストはアルファベット順に新しいシートにソートされます。
var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
シート上のヘッダーセルの背景色は動的に生成されたシートで再利用されたものが引用されます。
 for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) || 
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
どの部署名もシートの中にあります。
  • シート間を移動したり新規に作成したりしてみましょう。
  • シート内のコンテンツをクリヤーしてみます。
  • シート最上段のカラム名をセットし背景色を決めてください。
  • dataByDepartment[departments[i]] 内のすべてのオブジェクトに対してシート上で一行分のデータを埋めてみましょう。
次は setRowsData を見てみます。

§3 ”setRowsData関数”の動き

ここでは実際に setRowsDataがどのよに動くのかを詳しく説明します。

// setRowsData は配列オブジェクト中で定義付けられたオブジェクトそれぞれについて一行分の行データを埋める
// さらにデータオブジェクトが各コラムに値を定義付けたかどうかチェック
// 引数:
//   - sheet: これからデータが書き込まれるシートオブジェクト
//   - objects: それぞれが行データを持つ配列オブジェクト
//   - optHeadersRange: カラムヘッダーが定義付けられた(複数の)セルの範囲
//     これはシートの最初の行にエントリーするように定義されている
//   - optFirstDataRowIndex: データが書き込まれるべき最初の行のインデックス
//     これはヘッダーの真下の行とされている

function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
空でないカラムヘッダーを含むセルの範囲を算出します。空ではない最初のデータ行のインデックスを作ります。
  var headers = normalizeHeaders(headersRange.getValues()[0]);
正規化されたオブジェクトのプロパティ名を持ちます。文字列配列は空文字も含みます
 var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }
書き込もうとするセルの値に対応したJavaScriptの2次配列のループです。それぞれのたて列がたて列名に対応したプロパティが定義されているかどうか、すべてのオブジェクトがチェックされます。 正しければプロパティ値が、そうでなければ空文字が与えられます。
 var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
一行目のデータインデックスとオブジェクト番号に基づいて打ち出されたセル範囲を算出します。
destinationRange.setValues(data);
セルに上で生成されたJavaScriptの2次配列の値をセットします。

§4 アイディアと練習

setRowsData 関数にも慣れ、多くの要素からなるスプレッドシートにアクセスすることが容易になったので練習をしてみましょう。 複数のシートを一つにまとめるにはどうすればよいでしょうか。たとえば3番目のシートに 'Employee Id''Salary'のカラムを備えるようにしたければどうすればよいでしょう。 また、部署シートのデータから salaries を表示することができますか?

2種類のコードを示します。 ソースとなる一つあるいはそれ以上のスプレッドシートから読み込まれたデータのスナップショットのモデルを取り出しましょう。そして一つのシートに加工したうえで表示します。

目標となるひとつあるいは複数のスプレッドシートへ向けて配信されているデータをプッシュします。たとえば Human Resources 部署には Name, Phone Number, SSN, salary といった従業員情報すべてを保持させたいと思います。 このデータはHRに制限されていますが基本的な name, phone number , email adress 情報のヴァージョンを自動的にメンテナンスし、すべての従業員に対して入手可能なものとします。

§5 完成されたコード

サンプル
// ここの例で使用されているデータは次のURLから引用されている : https://spreadsheets.google.com/ccc?key=rKTOLH41HOK2gLosf7d6P1g
var DATA_SPREADSHEET_ID = "rKTOLH41HOK2gLosf7d6P1g"

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

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var dataSheet = dataSs.getSheets()[0];

  // Fetch all the data
  var data = getRowsData(dataSheet);

  // This is the data we want to display
  var columnNames = ["First Name", "Last Name", "Department"];
  
  // Index data by department name
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }
  
  departments.sort();
  var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
  for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) || 
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
}
SetRowsData
// setRowsData は配列オブジェクト中で定義付けられたオブジェクトそれぞれについて一行分の行データを埋める

// さらにデータオブジェクトが各コラムに値を定義付けたかどうかチェック
// 引数:
//   - sheet: これからデータが書き込まれるシートオブジェクト
//   - objects: それぞれが行データを持つ配列オブジェクト
//   - optHeadersRange: カラムヘッダーが定義付けられた(複数の)セルの範囲
//     これはシートの最初の行にエントリーするように定義されている
//   - optFirstDataRowIndex: データが書き込まれるべき最初の行のインデックス
//     これはヘッダーの真下の行とされている
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}
チュートリアルで利用したスプレッドシートのデータを読むコードの再利用
// getRowsData は入力範囲の行ごとにループしてオブジェクトの配列を返す
// 与えられた行のデータすべてを持つそれぞれのオブジェクトはカラム名の正規化によりインデックス化される
// 引数:
//   - sheet: 処理されるべきデータを持つシートオブジェクト
//   - range: データが保存されている正確なセル範囲
//       (この引数はオプション) 一行目を除いたすべてのセルについて定義されている
//       またはcolumnHeadersRowIndex(もし定義されているならば)より下の行すべてのセルが対象になる
//   - columnHeadersRowIndex: カラム名が保存された行番号を指定する
//       (この引数はオプション)初期値では真上の行
// オブジェクトの配列を返す
function getRowsData(sheet, range, columnHeadersRowIndex) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range || 
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.getValues(), normalizeHeaders(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;
}
//  正規化された文字列の配列を返す
// 空白部分は正規化されない文字列として返される
// 引数:
//   - 正規化された文字列の配列
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  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);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}
以上で多要素のスプレッドシートの構造化されたデータをすばやく読み取ることできるようになりました。




    * チュートリアル 中級者/目次
    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-2.スプレッドシートへの書き込み : end --2011/10/30