はじめに
GASを使って大量のデータを書き込んでいたところ、以下のように「この操作を行うと、ワークブック内のセル数が 10000000 の制限を超えてしまいます」というエラーが発生しました。
データを入れているのは1000万セルも無いのにおかしいと思い、このエラーが出る条件を調べてみることにしました。
調査1：まっさらなシートに100列×10万行のセルに書き込む
- 以下のコードの
test1()を実行して、「シートをクリアして、さらに1行1セルの状態にした後、100列×10万行のセルに書き込む」という操作をしました。 - このコードでは何のエラーも起きず、処理を終了することが出来ました。
const SHEET_NAME = "シート1"
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
// シートをクリアした後、1行1セルにする。
function initSheet() {
SHEET.clear();
const maxRow = SHEET.getMaxRows();
const maxCol = SHEET.getMaxColumns();
if (maxRow > 1) {
SHEET.deleteRows(2, maxRow - 1);
}
if (maxCol > 1) {
SHEET.deleteColumns(2, maxCol - 1);
}
}
// 100列×10万行で1000万セルに書き込む。
function test1() {
initSheet();
const range = SHEET.getRange(1, 1, 100000, 100);
range.setValue("1");
}
調査2：100列×10万行のセルに書き込んだ後、20列×50万行のセルに書き込む
- 以下のコードの
test2()を実行して、「シートをクリアして、さらに1行1セルの状態にした後、100列×10万行のセルに書き込む」という操作をした後、さらに「シートをクリアしてから、20列×50万行で1000万セルに書き込む」という操作をしました。 - このコードを実行すると、
この操作を行うと、ワークブック内のセル数が 10000000 の制限を超えてしまいますというエラーが発生します。
const SHEET_NAME = "シート1"
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
// シートをクリアした後、1行1セルにする。
function initSheet() {
SHEET.clear();
const maxRow = SHEET.getMaxRows();
const maxCol = SHEET.getMaxColumns();
if (maxRow > 1) {
SHEET.deleteRows(2, maxRow - 1);
}
if (maxCol > 1) {
SHEET.deleteColumns(2, maxCol - 1);
}
}
// 100列×10万行で1000万セルに書き込み、シートをクリアした後に20列×50万行で1000万セルに書き込む。
function test2() {
initSheet();
const range1 = SHEET.getRange(1, 1, 100000, 100);
range1.setValue("1");
SHEET.clear();
const range2 = SHEET.getRange(1, 1, 500000, 20);
range2.setValue("1");
}
結論：［表示されているセル数＞1000万］の時にエラーとなる
- 当初は「データの入っているセルが1000万個を超えるとエラーになる」と思っていましたが、上記の調査結果からこの仮説は覆されました。
- 私の勝手な思い込みでした...
- 結論は「シート上に表示されているセル数が1000万個を超えるとエラーになる」というものでした。
-
test1()の結果は以下の図の左側のように、シート上に確保されているセルは1000万個ちょうどなので、エラーが発生することはありませんでした。 -
test2()の場合、以下の様にデータの入っているセルとは別に表示されているセルが1000万個を大きく超えてしまうため、結果としてエラーになってしまうようです。- 100列×10万行（＝1000万個）のセルに値を書き込む。
- シートをクリアした際にセル中の値は消されるものの、確保したセル範囲（100列×10万行）はそのまま表示されている。
- 次に20列×50万行（＝1000万個）のセルに値を書き込もうとするが、この時に表示されるセル範囲（※図の赤＋青＋点線部）は5000万個となるためにエラーとなる。
- また、セル数の上限である1000万個は「1シートあたり1000万セル」ではなく、「1ファイルあたり1000万セル」となるようです。
- シートを分割すれば回避できると思いましたが無理でした...
その他
- 例えば「あるシートを変更する前に、シートをコピーして履歴として保持する」というようなプログラムを組んでいると、ファイル全体で使っているセル数が急増するため、今回の問題が生じる可能性が高いかもしれません。
- 以前に【Google Spread Sheet】RDBのように複数シートを紐づける方法という記事を書きましたが、スプレッドシートでは大規模なデータを保持するのには向いていないと感じました。
- 1000万個の上限の前に、そもそもスプレッドシート側のパフォーマンスが問題になりそうですが...