自分がそれなりに考えた GAS で Google Spread Sheets をうまく使うための設計手法

考え方としては Java/Selenide の Page Objects と似ている。

シートには特定の用途があり各セルを自由にめちゃくちゃに使っているわけではない。

大体の操作において、行を取り出したり入れたり、値を検索したりするだけ。

なのでそれをメソッド化してやる

function HogeSheet(){
  var ID_COLUMN = 1;
  var NAME_COLUMN = 2;
  var AGE_COLUMN = 3;
 
  var HEADER_ROW = 1;
  var DATA_ROW = 2;
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("ほげしーと");
}

このクラスはシート操作の定形ロジックを実装して仕様部分は内部に持ち込まないようにする。

シートはある特定の用途のために作られていて、そのそれぞれの行はほぼ同じ形をしている。

なので

function HogeRow(_id, _name, _age, _row){
  var id = _id;
  var name = _name;
  var age = _age;
  var row = _row;
}

このように実装できるはずなのである。

まず最初に意味有る行を全行、行オブジェクトで取得するメソッドを実装するとよい。

2018-09-17 現在 GAS は Sheet に対して検索するメソッドは用意されていない。 GAS の API の呼び出しは非常に遅いのでセル1つ1つを読み出すのは現実的ではない、 普通に JS 側で駆動したほうが速いのである。

なので各個別行操作というものはほぼ無く、全行取得してから何かやるということが多くなる。 なのでこれをまず実装するとよい

var getAll = function(){
  var cells = s.getRange(DATA_ROW, ID_COLUMN, s.getLastRow() - DATA_ROW + 1, AGE_COLUMN);
  var data = cells.getValues();
  var list = [];
 
  for(var i = 0; i < data.length; i++){
    list.push(new HogeRow(
      data[i][ID_COLUMN - 1],
      data[i][NAME_COLUMN - 1],
      data[i][AGE_COLUMN - 1],
      // 配列インデックスから行番号を出す
      i + DATA_ROW
    );
  }
  return list;
};

Range.setValues には若干クセがあって1行分配列を行配列につっこんだ2次元配列と Range で指定している範囲がぴったり一致しないといけない。

結構面倒なのでそれを吸収するメソッドを作っておく。

この場合 Range 範囲にデータが足りないなら足す、オーバーしているなら切るという動作

function mySetValues(range, values){
  // 範囲の行数取得
  var numRows = range.getNumRows();
  // 範囲の列数取得
  var numCols = range.getNumColumns();
 
  // 最終的に整形したデータ入れ
  var vs = []
  // 行方向を整形する
  if(values.length >= numRows){
    // ピッタリもしくは多い場合は入れられる分だけやる
    // そのまま全部詰め替える
    for(var i = 0; i < numRows; i++){
      // 配列をコピー
      vs.push(values[i].concat());
    }
  }else if(values.length < numRows){
    // 少ない場合足す
    for(var i = 0; i < values.length; i++){
      // 配列をコピー
      vs.push(values[i].concat());
    }
    // オーバー分を足す
    for(var i = values.length; i < numRows; i++){
      vs.push([]);
    }
  }
  // 列方向に整形する
  for(var i = 0; i < numRows; i++){
    // 強制的に大きさ指定
    vs[i].length = numCols;
  }
  // undef の値を整形する
  for(var i = 0; i < vs.length; i++){
    for(var j = 0; j < vs[i].length; j++){
      // 中に undefined があるとセルにそのまま "undefined" と出てしまうので null にする
      if(vs[i][j] === (void 0)){
        vs[i][j] = null;
      }      
    }
  }  
  // 形が同一になったので突っ込める。
  range.setValues(vs);
}

実際はこっちのほうがよく使うかも。 範囲に合わせてデータを切るのではなく、データに合わせて範囲を自動拡大する。

これならばデータ書き込みの開始セルだけ指定すれば後は自動なので都合がよい

function mySetValuesOver(range, values){
  // データの最大範囲を求める
  var maxRow = values.length;
  var maxCol = 0;
  for(var i = 0; i < values.length; i++){
    if(values[i].length > maxCol){
      maxCol = values[i].length;
    }
  }
  // 書き込みゼロならやらない。
  if(maxCol <= 0){
    return;
  }
  // 最大範囲を取得し
  var rangeOver = range.getSheet().getRange(range.getRow(), range.getColumn(), maxRow, maxCol);
  // 埋め付き処理に回す
  mySetValues(rangeOver, values);
}