1. スクリプトの呼び出しが多すぎる場合に表示されるエラー
Google スプレッドシートで、カスタム関数を呼び出す回数が多いと、エラーが表示される。
例えば、前回定義したカスタム関数 hello を一つのシートの中で 1000 回呼び出してみる。セル A1 に
=hello()
を入力。下方向に 1000 行コピーした。その結果、正常に 1000 回 Hello World! は表示されなかった。途中でエラーが表示される。エラーの内容は、
エラー: Script invoked too many times per second for this Google user accout.
エラーが出る原因は、短時間でで多くのスクリプトを実行しているため。
Quotas for Google Services - Google Apps Script — Google Developers によると、
Script invoked too many times per second for this Google user account.This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.
スプレッドシートを再読み込みしたり、関数を再定義すると、再び各セルで計算が行われる。エラーが表示されるセルは毎回変わる。
（※ エラーばかり表示されるようになってしまったら、シートを別シートにコピーすると計算が行われる場合がある。）
2. 「配列を返す」関数に変更する
エラーが出ないようにするために、関数が「配列を返す」ように変更してみた。
具体的には、各々のセルの中で関数 hello を呼び出すのではなく、「配列を返す」関数の中で hello 関数を繰り返し呼び出す。そして、結果を配列にして返すことにより、複数のセルに値を設定する。
Array formulas - 配列数式
「配列を返す」ことにより、複数のセルに値を設定する関数は、「配列数式」と呼ばれる。
例えば、組込みの関数の中に「文字列を分割する関数」 split がある。関数 split をあるセルに適用すると、結果は、複数のセルに設定される。
このように、複数のセルに値を設定したい関数が必要な場合、「配列の配列」（2次元）を返すように定義すれば良い。
Custom Functions in Spreadsheets - Google Apps Script — Google Developers によると、
Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing
return [[1,2],[3,4]];.
3. 「配列を返す」カスタム関数の例
配列を返す関数
最初に、配列を返す関数を呼び出したときの結果がシートにどのように表示されるか確認しておく。
function returnArray01(){
return [1,2,3,4,5,6];
}
結果は、列方向に結果が表示された。
「配列の配列」（2次元）
次に、「配列の配列」（2次元）を返してみる。
function returnArray02(){
return [[1,2,3],
[4,5,6]];
}
結果は、表が返された。「配列の配列」は、２次元の表データに対応する。
ちなみに、上記 returnArray01 関数を、以下のように「配列の配列」で返すように変更しても、「配列を返す」関数と同じ結果が得られる。
function returnArray011(){
return [[1,2,3,4,5,6]];
}
「配列」を返す関数は、「配列の配列」を返す関数の「1番目の要素」に値が設定されている、簡易的な書き方と考えれば良いのかな？
「配列の配列の配列」（3次元）
ついでに、「配列の配列の配列」を返す関数を定義してみる。
function returnArray03(){
return [[[1,2,3],
[4,5,6]],
[[10,20,30],
[40,50,60]]
];
}
結果は、何か良く分からない表示となった。 (+_+)
4. 関数 hello を繰り返し呼び出す関数を定義する
では、関数 hello を複数のセルで呼び出した結果と同じになるように、関数 helloList を定義してみる。この関数では、hello を呼び出す回数を引数として与えるものとする。
function helloList(num){
var i, result = [];
for (i = 0; i < num; i++){
result[i] = [hello()];
}
return result;
}
関数 helloList を用いて、10,000 回 hello 関数を呼び出すには、セルA1 に以下のように入力。
=helloList(1000)
結果、エラーは全く表示されなくなった。 また、引数として 10,000 を与えても、エラーは全く表示されない。
このことから、たくさんのセルでカスタム関数を呼び出したい場合、カスタム関数の中で必要な処理を行い、結果を配列にして返し、セルに値を設定するのが良いようだ。
CONTINUE 関数
セルA1 に配列を返す関数を入力すると、セルA2 には CONTINUE 関数が自動的に入る。
=CONTINUE(A1, 2, 1)
CONTINUE 関数は、「配列を返す」関数を使用した場合に、関数を呼び出したセルと、そのセルからの相対的な行・列番号が入力されている。
CONTINUE - Drive Help によると、
CONTINUE(source_cell, row, column)
source_cell- The cell containing the array formula for which the cell containingCONTINUErepresents a part of the result.
row- The row relative to the result of the formula insource_cellthat thisCONTINUEcall represents.
column- The column relative to the result of the formula insource_cellthat thisCONTINUEcall represents.
5. 新しい Google スプレッドシートではエラーが表示されない
2013年12月から、Google スプレッドシートがバージョンアップされた。
Google Enterprise Japan 公式ブログ: 新しい Google スプレッドシートのご紹介: より速く、パワフルに。そしてオフラインでも。 によると、
より大きなデータを速く
新しい Google スプレッドシートでは、より多くのセルをサポートしています。複雑なスプレッドシートでも、スクロールや再計算の反応が改善されています。
バージョンアップされた当初、関数 hello を 1,000 セルで呼び出すとエラーとなった。また、上記のように「配列を返す」関数に変換してもエラーが表示された。
しかし、2014年1月現在、関数 hello を 10,000 セルで呼び出してもエラーは表示されなくなった。カスタム関数を多くのセルで利用する場合、新しい Google スプレッドシートを利用したほうが良いようだ。
新しい Google スプレッドシートを使うためには、以下を参照。
0コメント:
コメントを投稿