Cell Count By Tab

cellCountByTab(ss) reports how each tab uses the spreadsheet's 10 million cell cap. For every sheet it compares the used rectangle (lastRow × lastColumn) with the full grid (maxRows × maxColumns), sorts the tabs with the largest grids first, and adds the file's share of the 10 million limit.

When to use

  • Finding which tab is pushing a workbook toward the cap
  • Deciding where Trim Sheet To Data will buy the most cells
  • A periodic health check on a log or archive file that only grows

How to use this snippet

/** * Per-tab cell counts versus the 10 million cell spreadsheet cap. * `total` is maxRows * maxColumns (what the cap counts). * `used` is lastRow * lastColumn (the data rectangle, an upper bound * on cells that have values). Tabs are sorted by total, worst first. * * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} [ss] * @return {{cap: number, fileTotal: number, fileUsed: number, share: number, remaining: number, tabs: Array}} */ function cellCountByTab(ss) { ss = ss || SpreadsheetApp.getActive(); var CAP = 10000000; var tabs = ss.getSheets().map(function (sheet) { var maxRows = sheet.getMaxRows(); var maxColumns = sheet.getMaxColumns(); var lastRow = sheet.getLastRow(); var lastColumn = sheet.getLastColumn(); var total = maxRows * maxColumns; var used = lastRow * lastColumn; return { name: sheet.getName(), gid: sheet.getSheetId(), maxRows: maxRows, maxColumns: maxColumns, lastRow: lastRow, lastColumn: lastColumn, total: total, used: used }; }); tabs.sort(function (a, b) { if (b.total !== a.total) return b.total - a.total; return a.name < b.name ? -1 : a.name > b.name ? 1 : 0; }); var fileTotal = 0; var fileUsed = 0; tabs.forEach(function (t) { fileTotal += t.total; fileUsed += t.used; }); return { cap: CAP, fileTotal: fileTotal, fileUsed: fileUsed, share: fileTotal / CAP, remaining: CAP - fileTotal, tabs: tabs }; }

Example

function auditCellBudget() { var report = cellCountByTab(); var rows = [['Tab', 'Grid cells', 'Used rectangle', 'Max rows', 'Max columns', 'Last row', 'Last column']].concat( report.tabs.map(function (t) { return [t.name, t.total, t.used, t.maxRows, t.maxColumns, t.lastRow, t.lastColumn]; }) ); var ss = SpreadsheetApp.getActive(); var out = ss.getSheetByName('Cell budget') || ss.insertSheet('Cell budget'); out.clear(); out.getRange(1, 1, rows.length, 7).setValues(rows); out.getRange('I1:J2').setValues([ ['File grid cells', report.fileTotal], ['Share of 10M cap', report.share] ]); }

Notes

  • The cap counts the grid, not only cells with values. A tab with 120 used rows and 10,000 max rows still spends 10000 × maxColumns cells. Hidden tabs count too.
  • used is the rectangle from A1 to the last cell that getLastRow / getLastColumn can see. A sheet with only A1 and Z100 filled still counts as 100 × 26 in this column. That is enough to see waste; it is not a precise non-empty cell census.
  • share is fileTotal / 10000000. remaining is how many grid cells you can still add before the cap.
  • Trimming empty rows and columns is Trim Sheet To Data. Try that on a copy first.

Happy Coding!

NitroGAS Chrome Extension

Want this snippet handy inside the Apps Script editor? NitroGAS gives you free snippets — plus optional Co-Pilot when you want a boost (Day Pass — $3, or 1-week, 1-month, or yearly passes — no subscription). Happy Coding!

Get the Extension