Getting rid of excess rows and/or columns (the easy way)

Ask for Help
1

Anyone that’s been using Glide for a while will know that it’s generally a good practice to remove excess rows from the bottom of your Google Sheets. But doing so manually can be a bit tedious.

This pair of script functions will do the leg work for you. Just run them once in your Google Spreadsheet and they will clean everything up in one quick pass.

The following configuration options are available:

  • process.rows : [true|false], whether or not to prune excess rows (default true)
  • process.columns : [true/false], whether or not to prune excess columns (default false)
  • buffer.rows : how many rows to leave as a buffer (default 1)
  • buffer.columns : how many columns to leave as a buffer (default 1)

To use:

  • set your desired config options in the first function (remove_excess_rows_and_columns())
  • execute that function

How it works:

  • The first function will retrieve a list of all sheets in your spreadsheet file
  • It will then call the second function once for every sheet
  • The second function will process each sheet, removing excess rows and/or columns according to the config options set

Notes & Caveats:

  • Although I have tested these and use them myself, I strongly recommending testing on a copy of your sheet before using it on a “live” spreadsheet.
  • They will not remove excess rows on sheets that contain arrayformulas - you’ll still need to deal with those manually.
  • They will process all sheets, including any sheets that may be hidden.

enjoy :slightly_smiling_face:

First Function: 
function remove_excess_rows_and_columns() {
  var options = {
    process: {
      rows: true,
      columns: false,
    },
    buffer: {
      rows: 1,
      columns: 1,
    }
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.forEach(function (sheet) {
    prune_sheet(sheet, options);
  });
}
Second Function 
function prune_sheet(sheet, options) {
  console.log("Processing %s", sheet.getName());
  if (options.process.rows) {
    var total_rows = sheet.getMaxRows();
    var last_used_row = sheet.getLastRow();
    var rows_to_delete = total_rows - last_used_row - options.buffer.rows;
    if (rows_to_delete > 0) {
      sheet.deleteRows(last_used_row + options.buffer.rows, rows_to_delete);
      console.log("Removed %s rows", rows_to_delete);
    }
    else {
    console.log("No rows to delete.");
    }
  }
  if (options.process.columns) {
    var total_columns = sheet.getMaxColumns();
    var last_used_column = sheet.getLastColumn();
    var columns_to_delete = total_columns - last_used_column - options.buffer.columns;
    if (columns_to_delete > 0) {
      sheet.deleteColumns(last_used_column + options.buffer.columns, columns_to_delete);
      console.log("Removed %s columns", columns_to_delete);
    }
    else {
      console.log("No columns to delete");
    }
  }
}
5 Likes
2

Wow! Thanks for sharing. This is very useful.