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 script will do three things: bound Create a custom menu in your spreadsheets tabs with the title . Custom Menu 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. 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 tab and then as shown in the image below. Extensions Apps Script Now, similar to the , you can now just: previous blogs Save the code. Reload the spreadsheet, where you'll see the custom menu as shown below 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. Also published . here

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

How to Delete Empty Rows and Columns in Google Sheets

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Access User Location with Permission Handler in Flutter

100 Stories To Learn About Blogging

24 Best JavaScript Blogs and Websites

2018: My Technology Retrospective

4 Simple Ways to Skyrocket Your Blog Readership

Access User Location with Permission Handler in Flutter

100 Stories To Learn About Blogging

24 Best JavaScript Blogs and Websites

2018: My Technology Retrospective

4 Simple Ways to Skyrocket Your Blog Readership

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps