reshapeのみで出来ないかと頑張ったけどエラーが頻出したので諦めた。
カスタム関数で実行される時は範囲オブジェクトが対象なので、GASで配列を対象とする時では型が違うみたい。
/**
* @param {Number} step
* @param {Range} range
* @param {Range} rows each row step by given number.
* @customfunction
*/
function EACH_ROW(step, range) {
return range.filter(function(x, i){ return i % step == 0; });
}
列に対する each col は面倒なので作らない。transposeとの組み合わせで対応。
組み込み関数のみで出来た。範囲からN行毎に行を取り出す
=FILTER(範囲, MOD(ROW(範囲), N) = 0)
組込であると便利な配列の変形関数群
2次元配列を1次元配列に変形
/**
* 2次元配列を配列を平坦にする
*
* @param {Range} range 2-dimensional array
* @return flatted array
* @customfunction
*/
function FLATTEN(range) {
return Array.prototype.concat.apply([], range);
}
1次元配列を n要素の2次元配列へ変形
※ この実装では Math.floorで配列長を決めているため、割り切れない場合の末尾は切り捨て
/**
* 指定された数の要素毎に配列を分割
*
* @param {4} n 分割する要素数
* @param {A1:A12} xs データ・ソースとなる配列
* @return n要素毎に分割された2次元配列
* @customfunction
*/
function CHUNK(n, xs) {
var length = Math.floor(xs.length/n);
var result = new Array(length);
for (var i = 0; i < length; ++i) {
result[i] = xs.slice(i*n, i*n+n);
}
return result;
}
2次元配列の変形
/*
* 2次元配列の変形
*
* @param {Number} row 変形する行数
* @param {Number} col 変形する列数
* @param {Range} range 変換対象とする範囲
* @return 変形後の行列
* @customfunction
*/
function RESHAPE(row, col, range) {
var flatted = FLATTEN(range);
if (Math.floor(flatted.length/col) != row) { throw new Error("サイズが一致しません"); }
return CHUNK(col, flatted);
}
4列で折り返し表示のような場合は、CHUNK。空行を挟みたい場合などは縦横を入れ替える組み込み関数のTRANSPOSEで空列を含めて変形する、等。
プリミティブな操作なので応用範囲は広いはず。下図は、1x16 の範囲を 4x4 へ変形した例。
カスタム関数化とは別のアプローチで、機能修正・追加時の編集コストを抑える方法。
API の setFormula で数式置き換えを検討してたけど、便利な置換機能があった。
正規表現を使って、数式を一括して書き換える。
※ 数式が入れ子になってる場合等は対応できないので、適用できるのは簡単な表現の式のみ。
実験中。カスタム関数化は共通の処理を纏めることで、コードの保守性向上を狙うものだけど、
頻繁に行う処理をカスタム関数にする場合のデメリットが、まだ把握できてない。
例えば、多数のセルを参照する場合等は、数式で書いた方がよさそう。
これを多量の引数を取る関数としてしまった場合、
シート側からロジックが把握しにくくなる等の副作用が出てくる。
整数値でパーセント指定する場合、組み込み関数のRANDBETWEEN(0,99) が使える。
シートで "70%" とパーセンテージ付きの書式で記入すると、
数式上では少数の 0.7 として扱われます。
カスタム関数で確率の判定…するまでもなく、RAND()との比較のみで済みそう。
出番がなさそうだけど。今回は、カスタム関数にする事で注意しないといけない点の考察。
/**
* 与えられた確率でランダムに bool値 を返す関数
*
* @param {float} rate 0.0 以上 1.0 以下の値
* @return {boolean}
* @customfunction
*/
function RANDCHANCE(rate) {
// NOTE: Math.random() は、0 以上 1 未満の値を返します
return Math.random() <= rate;
}
等号を含める理由は、JavaScript の少数の実装では概数で表現されている為、
桁数が増えた場合、例えば 0.99999999999999999 < 1.0 は false になってしまう。
rateに100%を設定したにもかかわらず、falseになる可能性が出てきます。
まるめ誤差の問題もありますが、実際の用途でそれ程細かな精度を必要としない場合は無視できる程度です。
SheetUtil ver7 で、プレフィクス(接頭子)を設定できる PrefixedPipe を追加。
var pipe = SheetUtil.PrefixedPipe.from("Sheet01", "pre_").to("Sheet02");
pipe.copy("name"); // copy "pre_name to name"
数式でランダムに選択する方法のメモ
/**
* picking a random item from a list
*
* @param {"A", "B", "C"} items
* @return one of choosed element
* @customfunction
*/
function RANDCHOICE() {
var xs = Array.prototype.slice.call(arguments);
return xs[Math.floor(Math.random() * xs.length)];
}
Google App Script のトリガーを使って、Google Drive 上で定期バックアップ
function dump_formulas(gridID, sheetName) {
// 座標の配列を生成する関数群
var zip = function(a, b) {
return a.map(function(x,i){ return [x, b[i]]; });
};
var product = function(a, b) {
return a.map(function(x){ return b.map(function(y){ return y+x; }); });
};
var flatten = function(xs) {
return xs.reduce(function(x,y){ return x.concat(y); }, []);
};
var num_range = function(n) {
return Array(n).join(0).split(0).map(function(_,i){ return i+1; })
};
var alpha_range = function(n) {
var xs = "ZABCDEFGHIJKLMNOPQRSTUVWXY";
return num_range(n).map(function(x){
var mod = x % 26,
pow = x / 26 - (mod ? 0 : 1) | 0;
return (pow ? xs[pow] : '') + xs[mod];
});
};
// 数式を所得
var ss = SpreadsheetApp.openById(gridID),
sheet = ss.getSheetByName(sheetName),
formulas = sheet.getDataRange().getFormulas();
// 数式に座標を添え、出力形式を整える
var address = product(num_range(formulas.length), alpha_range(formulas[0].length)),
source = zip(flatten(address), flatten(formulas)) // [座標, 数式] のペアの配列を生成
.filter(function(xs){ return xs[1]; }) // 数式のないセルは無視
.map(function(xs){
return "// " + xs[0] + "\n\n" + xs[1];
}).join("\n\n====\n"); // 区切り線で連結
// 生成日時を行頭へ築城
var timestamp = Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd HH:mm:ss'),
header = "// " + timestamp + "\n";
return header + source;
}
function backup_formulas() {
var content = dump_formulas("XXXXXXXX", "Sheet01");
// ファイル名
var date = Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd_hhmmss'),
path = "dump_" + date + ".txt";
// Backupフォルダ内へファイル出力 ※ Backupフォルダが2つ以上ある場合は注意
var folders = DriveApp.getFoldersByName("Backup");
if (folders.hasNext()) {
folders.next().createFile(path, content);
}
}