GAS(Google Apps Script)のスプレッドシート用SheetWrapperクラスを作成～配列処理を分かりやすく

前回の記事（リンク）で、スプレッドシートの全データを配列に格納して処理するクラスを紹介しました。

機能は最低限の代物でしたので、今回は実務に耐えうるよう改良を行いました。

目標は、

• GASで必須の、コード実行高速化のため、配列に格納して処理する
• しかし、配列を一切意識したくない！

また、行削除・行追加・値入力に関して、配列で処理した後、clearContent()→setValues()で更新する方法がありますが、今回はappendRow()、deleteRow()、setValue()で直接書き換えるようにしました。理由は以下です。

• 作っているシステムが、５００件超の読み込みに対し編集は１０件／１回程度と少ないこと
• 共有シートを書き換えるので、もし閲覧中の人がいても、リアルタイムで更新され、clear時のホワイトアウトを発生させないため

動作しない、こんなシートに実装したいけどどうすればいいの？、こうするともっと早くなる、などありましたら、コメント・お問い合わせ（リンク）頂けると嬉しいです。

クラス本体

///// SheetWrapper Class ///// 

  // sheetId:スプレッドシートのURLからIdを指定
  // sheetName:スプレッドシートのシート名
  // tableTopRow:表が始まる行を指定（1列目が項目、2列目がデータの場合、1を指定）
  var SheetWrapper = function(sheetId, sheetName, tableTopRow){
    // スプレッドシートのデータを二次元配列に格納。初期化・行追加・行削除以外でGoogleAppsScriptのAPI関数は使わない
    this.sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
    var values = this.sheet.getDataRange().getValues();
   
    for(var y = 1; y < tableTopRow; y++){
      values.shift(); // 余分な行を詰める
    }
   
    this.shift = tableTopRow;
    this.currentY = 1; // 行方向ポインタ
    this.maxX = values[0].length;
    this.maxY = values.length;
   
    // 列項目名->列毎データの連想配列を作成
    this.columns = {};
    for(var c = 0; c < this.maxX; c++){
     
      var item = values[0][c]; // 列項目名
      this.columns[item] = [c + 1]; // 先頭に列番号を格納
     
      for(var r = 1; r < this.maxY; r++){
        this.columns[item].push(values[r][c]);
      }      
    }
  };
       
  SheetWrapper.prototype = {
    // 表操作関数
    getByItem : function(itemName){
      return this.columns[itemName][this.currentY];
    },
   
    searchItem : function(itemName, val){
      this.currentY = this.columns[itemName].indexOf(val);
      return this.currentY;
    },
   
    moveNext : function(){this.currentY++;},
    moveFirst : function(){this.currentY = 1;},
    EOF : function(){
      return this.currentY > this.maxY;
    },
   
    getRealRow : function(){
      return this.currentY + this.shift;
    },
   
    appendRow : function(arr){
      this.sheet.appendRow(arr)
           
      var a = 0;
      for(var item in this.columns){
        // pushするarrの列数を揃える
        arr.length = Object.keys(this.columns).length;
        this.columns[item].push(arr[a]);
        a++;
      }
     
      this.currentY = this.maxY;
      this.maxY++;
    },
   
    deleteRow : function(){
      this.sheet.deleteRow(this.getRealRow());
     
      for(var item in this.columns){
        this.columns[item].splice(this.currentY, 1);
      }
     
      this.maxY--;
    },
   
   setValue : function(itemName, val){
      this.sheet.getRange(this.getRealRow(), this.columns[itemName][0]).setValue(val);
      this.columns[itemName][this.currentY] = val;
    }
  };
  

Excelでも同じようなクラスを作ったことがあります（記事へのリンク）。あちらは配列に入れなくても十分な早さで動いたのですが…。モデルにしたのはADO(ActiveX Data Objects)で、 列番号や行番号を意識せずに、項目名とcurrentRecordで操作する形にしました。

また、tableTopRow＝表の項目が何行目から始まるか、を最初に指定すれば、後からそのズレを意識しなくて済むようにしました。本当は１行目から始まる綺麗な表に統一したいんですけどね。セル結合を使ってゴテゴテ頭を飾るのが好きな人いるでしょ？(笑)

項目行の次にデータが来ない場合（モット汚い表）は、ifとmoveNext()で抜けるようにしてください…。

変数

• this.shift　…引数tableTopRowを保持し、シートの実際の行数を計算
• this.currentY　…レコードのカレントポジション。
• this.maxX　…最大列数
• this.maxY　…最大行数
• this.columns　…表データをカラム毎の連想配列に変換した結果

関数

• getByItem(itemName)　…カレントレコードの項目名itemNameの値
• searchItem(itemName, val)　…項目名itemNameから値valを検索し、カレントレコードを移動
• moveNext()　…カレントレコードをひとつ後に移動
• moveFirst()　…カレントレコードを最初に移動
• EOF()　…レコードの終わりを返す
• getRealRow()　…シートの実際の行数を返す
• appendRow(arr)　…行をarr配列の値で追加
• deleteRow()　…カレントレコードを削除
• setValue(itemName, val)　…カレントレコードの項目名itemNameに値valをセット

使用例

  function getNew() {

  var mySheet = new SheetWrapper('dummy_sheet_id_1', 'タスク', 6);
  var doneSheet = new SheetWrapper('dummy_sheet_id_2', '完了タスク', 1);

  while(!mySheet.EOF()){
    if(mySheet.getByItem('完了フラグ') == '完了'){
       
      if(doneSheet.searchItem('オーダーID', mySheet.getByItem('オーダーID')) < 0){
        doneSheet.appendRow([Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd'),
                                          mySheet.getByItem('オーダーID')]);
    }  
  }

  mySheet.moveNext();
}