Empty rows and columns at the edge of a tab still count toward the spreadsheet's 10 million cell limit (maxRows × maxColumns on every tab). trimSheetToData(sheet, {padRows, padCols}) deletes unused rows and columns past the last cell with data, and can leave a little padding.
When to use
- A tab that grew to thousands of empty rows after imports or paste
- Getting a workbook back under the 10M-cell cap
- Tidying a sheet before you share it
How to use this snippet
/**
* Delete unused rows and columns past the last data cell, keeping optional padding.
* Always leaves at least one row and one column. padRows/padCols default to 0.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {{padRows?: number, padCols?: number}} [opts]
* @return {{deletedRows: number, deletedCols: number, rows: number, cols: number}}
*/
function trimSheetToData(sheet, opts) {
opts = opts || {};
var padRows = opts.padRows == null ? 0 : Math.max(0, Number(opts.padRows) || 0);
var padCols = opts.padCols == null ? 0 : Math.max(0, Number(opts.padCols) || 0);
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
var keepRows = Math.max(1, lastRow + padRows);
var keepCols = Math.max(1, lastCol + padCols);
var maxRows = sheet.getMaxRows();
var maxCols = sheet.getMaxColumns();
var deletedRows = 0;
var deletedCols = 0;
if (maxRows > keepRows) {
deletedRows = maxRows - keepRows;
sheet.deleteRows(keepRows + 1, deletedRows);
}
if (maxCols > keepCols) {
deletedCols = maxCols - keepCols;
sheet.deleteColumns(keepCols + 1, deletedCols);
}
return {
deletedRows: deletedRows,
deletedCols: deletedCols,
rows: sheet.getMaxRows(),
cols: sheet.getMaxColumns()
};
}
Example
function trimWeekly() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Weekly');
var result = trimSheetToData(sheet, { padRows: 20, padCols: 2 });
Logger.log(result);
}
Notes
- "Last data" is
getLastRow()/getLastColumn()— the last cell that has a value. Formatting-only cells further down do not count as data, so they can be deleted. That is usually what you want for the cell-limit problem. - Deleting is done with one
deleteRowsand onedeleteColumnscall, not a loop. - Charts, filters, or named ranges that pointed into the empty area may need a look afterwards. Try it on a copy first.
Happy Coding!
