【GASで確認】使用しているセルの数を確認する

スプレッドシートのセル数上限に対して、どのぐらい使用しているのかを確認できるGoogle Apps Scriptです。シート単位の使用数も出るので肥大化しているシートもわかります。

こんな悩みを解決します

• 規模の大きなデータを扱おうと思っているが、セルの上限数が気になる。手動で計算するのはめんどくさい。
• セルの上限に達してしまったがどのシートが原因なのか、調べるのが大変。

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• セルの仕様上の上限に対して、現在どのぐらい消費しているのか計算します。
• シート単位の使用数も表示します。

使い方

1.事前準備

今回のスクリプトは、「ワークブック内のセル数が10000000の制限を超えてしまいます」というエラーに予期せず遭遇しないようにするためのものです。

まずは事前準備です。1つのワークブック内のセル数上限はときどき増加します。
本記事作成時点での上限は1000万セルですが、変更された場合は変数cell_maxの値を変更してください。
変更が終わったら関数「onOpen」を一度実行しておいてください。

2.スクリプトの実行

シートを新しく作ると、デフォルトではセル番地「Z1000」までの範囲が利用可能です。
なので26×1000で26,000セルが消費されているはずです。確認してみましょう。

上のメニューにある「スクリプト実行」から「セルの利用状況をチェックする」を実行します。

3.結果の確認

少し待つと、メッセージが表示されます。
想定通り26,000セルが消費されていますね。

適当にシートと行数を増加させてみました。
シート3は「非表示のシート」にしてありましたが、問題なく集計されています。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00148/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('セルの利用状況をチェックする', 'check_cell_usage');

  // メニューを画面に追加する
  menu.addToUi();
}


function check_cell_usage() {

  // ########## 事前設定エリア ここから ##########

  // セルの上限数を定義する。2022/9時点での上限は1000万セル。
  const cell_max = 10000000;

  // ########## 事前設定エリア ここまで ##########


  // 開いているスプレッドシートのオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // すべてのシートを取得
  const sheets = ss.getSheets();

  // セルの合計使用数を記録する変数
  let cell_used = 0;

  // シートごとの使用数を記録する配列
  let sheet_info = [];

  // シートの数だけループ
  for (let i = 0; i < sheets.length; i++) {

    // 使用可能な最後の行を取得
    let rows = sheets[i].getMaxRows();

    // 使用可能な最後の列を取得
    let cols = sheets[i].getMaxColumns();

    // 行と列を掛け算して使用しているセルを計算
    let cells = rows * cols;

    // シート名を取得
    let sheet_name = sheets[i].getName();

    // シート名とそのシートで使っているセル数を配列に記録
    sheet_info.push([sheet_name, cells])

    // 合計で使っているセルの数を記録
    cell_used += cells;

  }

  // メッセージを表示するためにUIオブジェクトを取得
  const ui = SpreadsheetApp.getUi();

  // セルの利用率を計算する。現在の使用数/上限値
  let  usage_ratio = (cell_used / cell_max) * 100;

  // 小数第2位までにする。100を掛けて小数点以下を消し、100で割って戻す。
  usage_ratio = Math.round(usage_ratio * 100) / 100;
  
  // 結果のメッセージ用の変数
  let message = '';

  // シートごとの使用数を記録した配列をループ
  for(let i = 0; i < sheet_info.length; i++){

    // 「シート名:使用セル数」のフォーマットでメッセージに追加
    message += sheet_info[i][0] + ':' + sheet_info[i][1] + '\n';

  }

  // メッセージ全体の組み立て
  message = `現在 ${cell_used}個のセルを使用中です。
  使用率は ${usage_ratio}% です。
  
  【シートごとの使用セル】
  ${message}`

  // アラートを使い、メッセージを表示する
  ui.alert('セルの利用率', message, ui.ButtonSet.OK);

}

関連するアプリ