How To Delete Empty Rows and Columns In Google Sheets?

How To Delete Empty Rows and Columns In Google Sheets?

Google Apps Script Is A Tool

Welcome to this very short daily blog where I give a very simple script to automate Google Products. In this chapter, we'll custom menu that'll delete empty rows and columns in current active spreadsheets.

The following bound script will do three things:

  1. Create a custom menu in your spreadsheets tabs with the title Custom Menu.

  2. After you call to select the custom menu, It will check all the extra rows and columns after the last rows and columns with data.

  3. Then delete all those extra rows and columns.

function deleteExteriorRowsNColumns() {
  // get sheets and data
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();

  // determine last row and column
  const lastRow = data.length;
  const lastCol = Math.max(...data.map((arr) => arr.length));

  // get maximum rows and columns sheets
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();

  // only remove rows and columns if there are empty rows or columns beyond last row and columns
  if (maxRows > lastRow) {
    sheet.deleteRows(lastRow + 1, maxRows - lastRow);
  }
  if (maxCols > lastCol) {
    sheet.deleteColumns(lastCol + 1, maxCols - lastCol);
}

}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Custom Menu"); 

  menu.addItem("Delete Empty Rows and Columns", "deleteExteriorRowsNColumns");
  menu.addToUi();
}

If you don't know how to add this script to your sheet then, then just click the Extensions tab and then Apps Script as shown in the image below.

Open Script Editor

Now, similar to the previous blogs, you can now just:

  1. Save the code.

  2. Reload the spreadsheet. Where you'll see the custom menu as shown below

  3. And execute the function from the custom menu as shown in the image below.

Custom Menu Delete Empty Rows

Delete Empty Rows and Columns

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

Subscribe to our newsletter

Read articles from Khadka's Coding Lounge directly inside your inbox. Subscribe to the newsletter, and don't miss out.

 

Article Series

Google Apps Scripts