環境

2018/1/7 時点のGoogleAppsScript

はじめに

スプレッドシートでツールを作る時に、使っている汎用クラスのまとめです。
ライブラリ化してしまったほうが良いのかも...。

先にまとめ

//////////////////////////////////////////////////////////////////////////////////
// 配列とhashに関する便利クラス
//////////////////////////////////////////////////////////////////////////////////

/**
* シートから連想配列を作成する
*
* @param {sheet} sh　sheetクラス
* @param {int} lastRowNum shの最終行番号
* @param {int} lastColumNum shの最終列番号
* @param {int} keyColumNum keyにする列の番号
* @return {array} 連想配列 key:id value:id以外の列
*/
function sheetToHash(sh, lastRowNum, lastColumnNum, keyColumNum) {
  var idArray = sh.getRange(2, keyColumNum, lastRowNum, 1).getValues();
  var allArray = sh.getRange(2, 1, lastRowNum, lastColumnNum - 1).getValues();
  var hash = {};
  allArray.forEach(function(value, i) {
    hash[idArray[i]] = value;
  })
  return hash;
}

/**
* カラムのid名から列番号を取得する
*
* @param {array} hash　連想配列
* @param {int} columnNum カラム数
* @param {string} name カラムのid名
* @param {string} keyName hashのkey名
* @return {int} targetColumn 列番号
*/
function getTargetColumn(hash, columnNum, name, keyName){
  var targetColumn = -1;
  var headerRow = hash[keyName];
  for(var i = 0; i < columnNum; i++){
    if(headerRow[i] === name){
      targetColumn = i;
    }
  }
  return targetColumn;
}

/**
* 連想配列を2次元配列へ変換する
*
* @param {array} hash　連想配列
* @param {int} columnNum 列数
* @return {array} ms2DimensionArray 2次元配列
*/
function hashTo2DimensionArray(hash, columnNum){
  var ms2DimensionArray = new Array();
  var count = 0;
  for(var key in hash){
    ms2DimensionArray[count] = new Array();
    for(var i = 0; i < columnNum - 1; i++){
      ms2DimensionArray[count][i] = hash[key][i];
    }
    count++;
  }
  return ms2DimensionArray
}

解説

シートから連想配列を作成する

シートからgetRangeで取得したデータは2次元配列になっています。
そのままループして目的のデータを検索・加工しようとすると時間がかかり、データが多いとタイムアウトしてしまいます。
そこで2次元配列を特定の列をキーとした連想配列に変換します。

/**
* シートから連想配列を作成する
*
* @param {sheet} sh　sheetクラス
* @param {int} lastRowNum shの最終行番号
* @param {int} lastColumNum shの最終列番号
* @param {int} keyColumNum keyにする列の番号
* @return {array} 連想配列 key:id value:id以外の列
*/
function sheetToHash(sh, lastRowNum, lastColumnNum, keyColumNum) {
  var idArray = sh.getRange(2, keyColumNum, lastRowNum, 1).getValues();
  var allArray = sh.getRange(2, 1, lastRowNum, lastColumnNum - 1).getValues();
  var hash = {};
  allArray.forEach(function(value, i) {
    hash[idArray[i]] = value;
  })
  return hash;
}

注）getRange(2,×,×,×) と2行目から取得しています。
　　今のチームは1行目をシートのタイトル記載部分として確保しているためです。
　　適宜変更してください。

カラムのid名から列番号を取得する

特定の列名が何番目にあるかを取得します。
シートの列数が変わっても対応できるようにするためです。

/**
* カラムのid名から列番号を取得する
*
* @param {array} hash　連想配列
* @param {int} columnNum カラム数
* @param {string} name カラムのid名
* @param {string} keyName hashのkey名
* @return {int} targetColumn 列番号
*/
function getTargetColumn(hash, columnNum, name, keyName){
  var targetColumn = -1;
  var headerRow = hash[keyName];
  for(var i = 0; i < columnNum; i++){
    if(headerRow[i] === name){
      targetColumn = i;
    }
  }
  return targetColumn;
}

連想配列を2次元配列へ変換する

シートに貼り付けたい時に使用します。
連想配列のままだとsetValues(配列)を使うことができないので2次元配列に戻します。

/**
* 連想配列を2次元配列へ変換する
*
* @param {array} hash　連想配列
* @param {int} columnNum 列数
* @return {array} ms2DimensionArray 2次元配列
*/
function hashTo2DimensionArray(hash, columnNum){
  var ms2DimensionArray = new Array();
  var count = 0;
  for(var key in hash){
    ms2DimensionArray[count] = new Array();
    for(var i = 0; i < columnNum - 1; i++){
      ms2DimensionArray[count][i] = hash[key][i];
    }
    count++;
  }
  return ms2DimensionArray
}

（ms2DimensionArrayって何でこんな名前にしたんだっけ...）

使用例

/**
* 対象のシートの加工後の2次元配列を取得する
*
* @param {string} spreadsheetId スプレッドシートのid
* @param {string} sheetName シート名
* @return {array} processedArray 加工後の2次元配列
*/
function getProcessedArray(spreadsheetId, sheetName){
  // シート取得
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  if(sheet == null){
    Browser.msgBox(sheetName + "のシートが見つかりませんでした。");
  }
  // シートのhash取得
  var lastRowNum = sheet.getLastRow();
  var lastColumnNum = sheet.getLastColumn();
  var keyColumnNum = 2;
  var sheetHash = sheetToHash(sheet, lastRowNum, lastColumnNum, keyColumnNum);

  // 検索する列番号取得
  var nameRowNum = getTargetColumn(sheetHash, lastColumnNum, "name", "id");
  var dateRowNum = getTargetColumn(sheetHash, lastColumnNum, "date", "id");
  var flagRowNum = getTargetColumn(sheetHash, lastColumnNum, "flag", "id");

  // ここでsheetHashを加工する
  // sheetHashをループ for(var id in sheetHash) しながら、
  // sheetHash[id][nameRowNum] このように特定列にアクセスします

  //2次元配列に変換する
  var processedArray = hashTo2DimensionArray(sheetHash, lastColumnNum);
  return processedArray;
}

さいごに

gasはブラウザ上で完結して開発できて楽しいです！

スプレッドシートの私的Utilクラス

環境