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 × maxColumnscells. Hidden tabs count too. usedis the rectangle from A1 to the last cell thatgetLastRow/getLastColumncan 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.shareisfileTotal / 10000000.remainingis 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!
