Sheets - Optimize spreadsheet by removing unused columns and rows

PHOTO EMBED

Tue Jul 20 2021 04:22:50 GMT+0000 (UTC)

Saved by @dsottimano

/**
 * Remove All Empty Columns & Rows in the Entire Spreadsheets
 */
function removeUnusedCells() {
  let ss = SpreadsheetApp.getActive();
  let allsheets = ss.getSheets();
  for (let s in allsheets){
    let sheet = allsheets[s];
    let maxColumns = sheet.getMaxColumns(); 
    let lastColumn = sheet.getLastColumn() || 1;
    let maxRows = sheet.getMaxRows(); 
    let lastRow = sheet.getLastRow() || 1;
    if (maxColumns-lastColumn != 0) sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);
    if (maxRows-lastRow != 0) sheet.deleteRows(lastRow+1, maxRows-lastRow);
    
  }
}
content_copyCOPY