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);
}
}
var shell = SheetUtil.Shell.attach("Sheet01");
var join = function(x){ return x.join("\n"); };
var source = shell.sheet.getDataRange().getFormulas().map(join).join("====\n");
Logger.log(source);
// テーブルが多い時は、ドライブに保存する
// DriveApp.createFile("dump.txt", source);
数式の一括出力…と、SheetUtil.Shell で提供されてない API を使う時のサンプル。
.sheet プロパティが Sheetオブジェクトを保持しています。
オブジェクトが持つプロパティは Shell.gs の行頭の関数内で確認出来ます。(http://bit.ly/SheetUtil)
[リソース][ライブラリ] から、ライブラリ名をクリックするとドキュメントを表示する機能がありますが、
ドキュメントはまだ書いてません。(TODO)
ついでに、SheetUtilの仕様の説明
SheetUtil.Shell.attach は、ライブラリ内でキャッシュを持ちます。
２回目以降の同じ名前のシートを呼び出す時、API 呼び出しは発生しません。
SheetUtil.Pipe も同様、キャッシュを共有してます。
キャッシュ化の反作用として、オブジェクトを長期間保持することになる為、
長時間実行されるプログラムでは、その間リソースが開放されないといった面もありますが、
Google App Script として実行する限りでは、実行時間が短いので、顕在化する問題ではありません。
※ キャッシュから解放する dettach メソッドも用意しました。
通常は自動的に開放されるので、明示的な dettach は不要です。
// var ss = SpreadsheetApp.getActiveShreadsheet();
// var sheet = ss.getSheetByName("Sheet01");
var sheet = SheetUtil.getSheet("Sheet01");
ShellやPipeを必要としない、従来のSheetオブジェクトが必要な場合でも、
SheetUtilの提供する関数を使う事で、キャッシュ化の恩恵を得られます。
画像アップロードのテストを兼ねて… Google Spreadsheet カスタム関数の作り方メモ
組み込みのJOINは一次元配列のみを対象としていて２行以上の範囲には使えません。
そこで、２行以上の範囲の連結に対応した RANGEJOIN カスタム関数を作ります。
/**
* RANGEJOIN
*
* @param {string} separator 区切り文字
* @param {Range} range 範囲
* @return {string}
* @customfunction
*/
function RANGEJOIN(sep, range) {
return range.join(sep);
}
Custom Functions in Google Sheets の簡単な訳
function myFunction()で定義してください。
var myFunction= new Function()は、カスタム関数としては呼び出せません。
組み込み関数の様に、カスタム関数も引数を取ることができます
カスタム関数は、セルに表示する値を返す必要があります
Google Spreadsheet では、データを異なる形で保有している為、 カスタム関数として呼び出された場合とApp Scriptとして呼び出された場合で異なるデータになる場合があります。
入力補完。記述しておくと、セル編集時に、関数名の候補や引数の説明を表示してくれるようになります。
より汎用的にするなら、JOIN関数と組み合わせて使うことができるように、
複数行の範囲を、一次元の配列にするカスタム関数を作る。
function FLATTEN(range){
return range;
}
例 =JOIN(",", FLATTEN(A1:B2))
var pipe = SheetUtil.Pipe.from("Sheet01").to("Sheet02");
pipe.copy("something_long_named_range_name");
ライブラリのアップデート方法は、[リソース][ライブラリ] から、バージョン番号を選択するだけ。
問題があれば以前のバージョンに戻せるように、現在のバージョン番号は控えておく。
第二引数に関数を渡してルート要素を操作できるように変更。ここでヘッダ情報を追加する。
特定の情報のみに対応した場合、他の情報を追加したい時、ライブラリ側の対応が必要になるので、
コールバック関数を渡す形式にして、自由に情報を追加できるようにした。
var addHeaderCallback = function(root) {
// ヘッダ情報のコンテナとなる identification要素を作成
var identification = LibMusicXML.createIdentificationElement();
// エンコーディングに関する情報
var encoding = LibMusicXML.createEncodingElement({
encoder: "",
software: "",
description: "",
});
// 独自情報を追記
var misc = LibMusicXML.createMiscElement({
"rm-version": "1.02",
"rm-firstkey": "C",
"rm-lastkey": "Cm",
});
// 各要素を親へ追加する
identification.addContent(encoding);
identification.addContent(misc);
root.addContent(0, identification);
// 題名と作業番号
root.addContent(0, LibMusicXML.createWorkElement("TITLE", "1");
};
var xml = LibMusicXML.toMusicXML(LibMusicXML._flatten(rows), addHeaderCallback);
identification 要素へ追記するメタ情報としては、他に、"source", "rights", "relation" 等が有り、
Dublin Core を参考にしている。
参考サイト: Dublin Core: メタデータを記述するボキャブラリ
関数名が長いけど補完が効くので、識別子の部分を短くすれば、キータイプ量は減らせます。