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.
PermalinkDelete Empty/Extra Rows and Column
The following bound script will do three things:
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();
}
PermalinkHow To Add Apps Script Code To a Spreadsheet?
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.
Now, similar to the previous blogs, you can now just:
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.
PermalinkThank 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.
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
Find the Last Row In Google Sheets With Google Apps Script!
Hello!, I wrote a blog on finding the last non-empty rows a couple of months ago. Since then, I have…
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.…
How to Easily Copy a Column From One Sheet to Another in Google Sheets?
In this blog, I will be sharing a script that allows you to easily select a column in a Google Sheet…
How to see how many words you have on google docs with Google Apps Script?
Are you tired of manually counting the number of words in your Google Docs while working on your blo…
How to Insert Multiple Rows in Google Sheets with Apps Script?
Hello and welcome to our blog series on automating Google Sheets with Apps Script. In this tutorial,…
Removing Duplicates in Google Sheets: A Guide for Non-coders
Ugh.., I have so many duplicate rows in my spreadsheet. Well so do many other people. With the scrip…
Using Apps Script to Count Checkboxes in Google Sheets
You have checkboxes in your spreadsheet, you’ll like to count them easily. Well, this script I’ll pr…
How to Highlight Duplicate Rows in Google Sheets?
You use sheets a lot and sometimes have difficulty identifying duplicate rows. Then after this blog,…
How To Find and Delete Duplicate Files in Google Drive?
You might be running an organization where files are created automatically or by other employees/col…
How to Find the Last Non-Empty Row in A Column With Google Apps Script?
Intro I am writing this short blog to share a solution to a problem that has been bugging me for a w…
Managing Google Drive with Google Apps Script
Intro Let's extend Google Drive with Apps Script to create a simple add-on, use CardService for the …
How to Send Google Forms Responses in an Email Automatically?
Intro Let's create a Google Form for a survey, and collect the responses in google sheets. Aggregate…
How to Write Google Apps Script Code Locally In Your Favorite IDE?
In this blog, you'll learn how to install the Clasp package, clone a Google Apps Script project to y…