はじめに

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万個を大きく超えてしまうため、結果としてエラーになってしまうようです。
1. 100列×10万行（＝1000万個）のセルに値を書き込む。
2. シートをクリアした際にセル中の値は消されるものの、確保したセル範囲（100列×10万行）はそのまま表示されている。
3. 次に20列×50万行（＝1000万個）のセルに値を書き込もうとするが、この時に表示されるセル範囲（※図の赤＋青＋点線部）は5000万個となるためにエラーとなる。
また、セル数の上限である1000万個は「1シートあたり1000万セル」ではなく、「1ファイルあたり1000万セル」となるようです。
- シートを分割すれば回避できると思いましたが無理でした...

その他

例えば「あるシートを変更する前に、シートをコピーして履歴として保持する」というようなプログラムを組んでいると、ファイル全体で使っているセル数が急増するため、今回の問題が生じる可能性が高いかもしれません。
以前に【Google Spread Sheet】RDBのように複数シートを紐づける方法という記事を書きましたが、スプレッドシートでは大規模なデータを保持するのには向いていないと感じました。
- 1000万個の上限の前に、そもそもスプレッドシート側のパフォーマンスが問題になりそうですが...

【Google Spread Sheet】セル数の上限とは何を指しているのか？

はじめに

調査1：まっさらなシートに100列×10万行のセルに書き込む

調査2：100列×10万行のセルに書き込んだ後、20列×50万行のセルに書き込む

結論：［表示されているセル数＞1000万］の時にエラーとなる

その他