Docs: Size My Sheet add-on

Spreadsheets gSheets Templates
1

The Size My Sheet add-on allows you to see how close you are to hitting Google Sheets’ 10 million cell limit.

Install the add-on at https://workspace.google.com/marketplace/app/size_my_sheet/391759237464

After install you can launch the add-on via the Extensions menu to view a gauge in the sidebar.

The Size My Sheet add-on also allows you to insert a function within your sheet to retrieve the number of cells used or the percent used of the cell maximum that Google has set. It’s useful if you’d like to see these numbers in cells on the sheet rather than using the gauge in the sidebar.

To use this feature simply type one of the functions below into an empty cell of a Google Sheet with the Size My Sheet add-on installed.

Retrieve the number of cells used:
=tillerCellsUsed()

Retrieve the percentage used:
=tillerCellsUsed(“Percent”)

What slows down downloading transactions for Google sheets?
3

Dear Heather,
Thank you so much for your developments, it is important and necessary. Rarely, but usually so unexpectedly happens - oops and not enough space.
Thanks to your development it is now possible to conveniently manage the size of tables.
Google changes dimites from time to time, do you keep track of these fluctuations?

BR,
Oleg

4

Welcome @oleg.romanov - we don’t keep track of when Google changes the limit, no.

5

As an enhancement, can we get the number of cells for each sheet? Right now we get the total and the amount of cells in the largest sheet. If that capability is only done in the function (to save GUI work), that’s fine. Thanks!

6

@ChrisMildebrandt try the sheet that @Mark.S linked in this post. Improving Performance Example I’m using it currently to slim down my sheet going into the new year.

3 Likes
7

Thanks, that’s a great script to have around.

2 Likes
8

Thanks @bentyre1 for sharing that here.:+1:t5:

1 Like
9

i downloaded Size My Sheet app and in Google sheets it shows its there in Extensions but the only option is “help” not start so i can’t get started with it - HELP!

10

@heather Thanks for sharing this. Very helpful.

I like the suggestion by @ChrisMildebrandt to show cell count by sheet. You already show the largest (mine is the Transaction sheet- probably similar to others), so hopefully, you could just expand on that. This would help identify any large sheets that users may want to remove or re-write due to their large size.

Thanks again.

11

hi @pshell3 can you please write in to Support if you haven’t already? You can send a new message using the blue chat widget in the lower right corner of your Console at https://my.tiller.com or write to support@tiller.com

12

Usually you just need to refresh the browser window.

Also, “Size My Sheet” is sort of just a “experimental add-on” and not something we’re officially supporting, maintaining, or updating.

14

This Apps Script takes a different approach to tracking Google Sheets’ 10-million-cell limit. It creates a new tab listing each sheet in a workbook with its row count, column count, and total cells — providing a clear picture of what’s eating the quota.

My workbook had crept up to 9.4 million cells over about 20 tabs. Running this script made it easy to spot the worst offenders. A few minutes of pruning — deleting redundant columns, trimming rows, and cutting tabs that duplicated functions elsewhere — brought the total down to 1 million.

// 2026-04-24 creates a sheet showing cells that count towards 10M max allowed by Google sheets
// Counts reserved cells in each tab of the active Google workbook.
// Creates a fresh "Sheet Size Audit" tab each time. Replaces older versions of the "Sheet Size Audit"

function auditSheetSize() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var data = [];

  sheets.forEach(function(sheet) {
    var name = sheet.getName();

    // Do not include a prior audit sheet in the audit
    if (name === "Sheet Size Audit") return;

    var sheetId = sheet.getSheetId();

    var lastRow = sheet.getLastRow();           // used rows
    var lastCol = sheet.getLastColumn();        // used columns

    var maxRows = sheet.getMaxRows();           // total rows allocated
    var maxCols = sheet.getMaxColumns();        // total columns allocated

    var totalCells = maxRows * maxCols;         // counts toward limit
    var usedCells = lastRow * lastCol;          // approximate used cells

    data.push([
      name,
      sheetId,
      lastRow,
      lastCol,
      maxRows,
      maxCols,
      usedCells,
      totalCells
    ]);
  });

  // Sort by Total Cells descending
  data.sort(function(a, b) {
    return b[7] - a[7];
  });

  var sheetName = "Sheet Size Audit";
  var output = ss.getSheetByName(sheetName);

  if (output) ss.deleteSheet(output);
  output = ss.insertSheet(sheetName);

  // Row 1: date/time script was run
  output.getRange("A1").setValue("Script run: " + new Date());

  // Row 2: column titles
  output.getRange(2, 1, 1, 7).setValues([[
    "Sheet name",
    "Used Rows",
    "Used Columns",
    "Max Rows",
    "Max Columns",
    "Used Cells",
    "Total Cells (Counts Toward Limit)"
  ]]);

  // Prepare regular numeric/text output, excluding the sheet-name hyperlink column
  var outputData = data.map(function(row) {
    return [
      row[2], // Used Rows
      row[3], // Used Columns
      row[4], // Max Rows
      row[5], // Max Columns
      row[6], // Used Cells
      row[7]  // Total Cells
    ];
  });

  if (data.length > 0) {
    // Add rich text hyperlinks in column A
    var richTextLinks = data.map(function(row) {
      var name = row[0];
      var sheetId = row[1];
      var url = "#gid=" + sheetId;

      return [
        SpreadsheetApp.newRichTextValue()
          .setText(name)
          .setLinkUrl(url)
          .build()
      ];
    });

    output.getRange(3, 1, richTextLinks.length, 1).setRichTextValues(richTextLinks);

    // Add the remaining numeric columns in B:G
    output.getRange(3, 2, outputData.length, 6).setValues(outputData);

    // Add total row for Total Cells
var totalRow = data.length + 3;

var grandTotalCells = data.reduce(function(sum, row) {
  return sum + row[7];
}, 0);

output.getRange(totalRow, 1).setValue("Total");
output.getRange(totalRow, 7).setValue(grandTotalCells);

// Format total row
output.getRange(totalRow, 1, 1, 7)
  .setFontWeight("bold")
  .setBorder(true, false, false, false, false, false);

  }

  // Format column titles in row 2
  output.getRange(2, 1, 1, 7)
    .setFontWeight("bold")
    .setHorizontalAlignment("center")
    .setWrap(true);

  // Freeze top 2 rows
  output.setFrozenRows(2);

  // Format numeric columns
  if (data.length > 0) {
    output.getRange(3, 2, data.length + 1, 6).setNumberFormat("#,##0");
  }

  // Optional sizing
  output.autoResizeColumns(1, 7);
}
```
1 Like
15

Hey @scottc!

Can you run the steps to install it for people that wouldn’t know how?

16

Ditto to what @PCB said.

17

Step-by-Step Instructions to add Apps Script to Google Sheet

How to Add This Apps Script to a Google Sheet

Before you begin: These instructions walk you through adding a script that runs directly from the Apps Script code editor. Once the script finishes running, you’ll switch back to your Google Sheet to see the results — the script creates a new tab named Sheet Size Audit.

Step 1 — Open Your Google Sheet

Open the Google Sheet where you want to run the script.

Step 2 — Open the Apps Script Editor

Go to Extensions > Apps Script in the menu bar. (Be careful — you want Apps Script, not AppSheet, which appears just below it.)

A new browser tab will open with the Apps Script editor.

Step 3 — Check for Existing Scripts

Before doing anything else, look at the left sidebar under Files. You’ll see at least one file listed — usually named Code.gs.

If you see only Code.gs with the default placeholder code (it typically looks like an empty function myFunction() {}), you’re clear to proceed.

If you see multiple files, or Code.gs already contains real code, stop. This sheet already has an Apps Script attached to it. Do not delete or overwrite existing code — you could break something that’s already working. Instead, add a new script file: click the + icon next to Files in the left sidebar and choose Script. Give it a descriptive name (e.g., auditSheetSize). Your new code will live there without disturbing anything else.

Step 4 — Name Your Project

Near the top of the page, you’ll see the project name — it likely says Untitled project. Click on it and rename it to something meaningful, like auditSheetSize. This just helps you recognize the project later; it doesn’t affect how the script runs.

Step 5 — Clear the Placeholder Code

Click on Code.gs (or your newly created script file) in the left sidebar. You’ll see a small block of placeholder code in the editor — something like:

function myFunction() {

}

Select all of it and delete it. The editor should now be completely empty.

Step 6 — Paste the Script Code

Paste the Apps Script code from this post into the now-empty editor.

Step 7 — Save the Script

Click the Save button (the floppy disk icon in the toolbar), or press ⌘S on macOS / Ctrl+S on Windows. You should see the save indicator confirm the file was saved.

Step 8 — Confirm the Function Name

In the toolbar near the top of the editor, there’s a dropdown showing the function to be run. It should already display auditSheetSize — that’s the only function in this script, so no selection is needed.

Step 9 — Authorize the Script (First Run Only)

Click the Run button.

The first time you run any Apps Script, Google will ask you to authorize it. This is normal and expected. Work through the steps as follows:

  • Click Review permissions

  • Choose your Google account

  • You may see a warning that says “Google hasn’t verified this app.” This is standard for personal scripts. Click Advanced, then Go to [project name] (unsafe) to continue.

  • Click Allow

This authorization happens only once. Future runs won’t ask again.

Step 10 — View the Results

Watch the Execution log panel at the bottom of the editor for progress updates. When it confirms the script has completed, switch back to your Google Sheet tab in the browser. You’ll find a new tab at the bottom of the sheet named Sheet Size Audit — that’s where your results are.

Depending on the size of your sheet (number of tabs, volume of data), execution may take anywhere from a few seconds to a minute or more. Larger files take longer.

Troubleshooting tip: If you see an error in the execution log, the most common causes are a missed step during authorization or a copy/paste error in the code. Re-check that the code was pasted completely and try again.

18

The Apps Script posted above creates a new tab in your Google Sheet named Sheet Size Audit. The attached screenshot shows a portion of a sample report.

What the report shows

Each row in the report represents one tab in your workbook. Column G shows the total number of cells that count toward Google’s 10 million cell maximum, sorted from largest to smallest. This makes it easy to identify which tabs are consuming the most capacity.

What the script does not do

The script identifies the biggest contributors to your cell count — it doesn’t make any changes to your sheet. The actual cleanup is manual. You’ll need to open each high-count tab and decide which rows and columns can be safely deleted.

How cells get overcounted

A tab’s cell count is determined by the total number of rows and columns Google has allocated for that tab — whether they contain data, formatting, or nothing at all. Even a brand new blank tab counts against the limit because Google pre-allocates a default grid the moment it’s created. Tabs that were once used and then cleared — but not deleted — can have an even larger allocated grid, silently consuming capacity.

This is why the audit is useful: the report surfaces every tab consuming significant capacity, including ones that appear empty. And it’s why deleting excess rows and columns — not just clearing their contents — is what actually brings the count down.

Before you delete anything

Proceed carefully, especially with columns. Workbooks like Tiller often contain helper columns to the right of visible data — columns that feed calculations displayed elsewhere on the tab, or that are hidden from view. Deleting them can silently break formulas on that tab or others.

Before deleting any columns, scroll right past your visible data to check for hidden or helper columns. If you’re not sure what a column does, leave it alone.

After any deletion, immediately scan the tab — and any tabs that reference it — for #REF! errors or other unexpected changes. If something breaks, use Undo (⌘Z on macOS / Ctrl+Z on Windows) right away to restore what you deleted.

The same caution applies to rows, though helper rows below data are less common.

1 Like
19

Thank you Scott for creating the solution and adding the detailed setup steps.