Edited at

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

More than 1 year has passed since last update.


環境

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はブラウザ上で完結して開発できて楽しいです!