GAS:GASでSpreadSheetの組み込み関数（Query関数）を使う

GASは、私の勘違いでなければ

• SpreadSheetでGASで書いた独自の関数を呼び出すことができる
• SpreadSheetの組み込み関数をGASのスクリプトで呼び出すことができない（シート内だけで呼び出せる）

という仕様になっているようです（ちょっと信じられないんですが。勘違いかもしれません）。つまりシートの検索で使ってみたいQuery関数などは直接呼び出せないということになっているようです。で、その回避方法を思いついたので、、、

まずQuery関数はArrayで結果を返してくれるんですが、これをシート内で呼び出した場合、結果が複数あるとシートに複数行にわけて出力されます。これが大きなお世話な機能なんですが、この結果を１つのセルにおさめることができたらよくて、ここで独自の関数を組み合わせれば解決します。例えば、下記のようにArrayをCSVに変換する関数を用意します。

	function ArrayToCSV(array){
	var csv="";
	for(var i=0;i<array.length;i++){
	csv+=array[i]+",";
	}
	csv=csv.slice(0,-1);
	return csv;
	}

view raw ArrayToCSV.js hosted with ❤ by GitHub

このQuery関数の結果をこの関数に渡すようにシートの式の記述をすればOKなわけです。例えば下記のようにします。

	var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
	var documentSheet = spreadSheet.getSheetByName("Words");
	var range =documentSheet.getRange("F8");
	var query = '=ArrayToCSV(query(E12:F27,"select F where E > ' +"'cc'"+'"))';
	range.setFormula(query);
	Browser.msgBox(range.getValue());
	range.setFormula("");

view raw SpreadSheetQuery.gs hosted with ❤ by GitHub

上記では、setFormulaを利用して一時的にあるセルにToCSVとqueryを組みあせた式を登録してます。（検索クエリの書き方が変なのは許してください。）こうすることで、シートの見た目が変わることなくquery関数を利用できます。 この方法を利用すれば、GASで直接SpreadSheetをデータベース的に使うことができます。まあ、GASにはDB機能もありますので、こういう使い方は今更かもしれませんが、、、、お試しあれ。