How to Delete Empty Rows and Columns in Google Sheets

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

Delete Empty/Extra Rows and Column

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();
}

How To Add Apps Script Code To a Spreadsheet?

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

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

Thank You for Your Time

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.