To create an Apps Script that removes blank rows in a Google Sheet, you can follow the steps below. This script will scan through the sheet for any rows that are entirely blank and remove them. Here’s a simple script to do that:
- Open your Google Sheet: Go to the Google Sheets document where you want to remove blank rows.
- Open Script Editor: In the Google Sheets menu, click on
Extensions
>Apps Script
. - Replace the code: Delete any code in the script editor and replace it with the script provided below. This script checks every row in the active sheet for any content. If a row is completely empty, it marks it for deletion. After checking all rows, it deletes all marked rows starting from the bottom to avoid indexing issues.
function removeBlankRows() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getDataRange();
const values = range.getValues();
let blankRows = [];
// Loop through all rows in reverse order to handle deletion correctly
for (let i = values.length - 1; i >= 0; i--) {
const row = values[i];
const isBlank = row.every(cell => !cell);
if (isBlank) {
// If the row is blank, mark it for deletion
blankRows.push(i + 1); // Add 1 because sheet rows are 1-indexed
}
}
// Delete all marked rows, starting from the bottom
for (let i = 0; i < blankRows.length; i++) {
sheet.deleteRow(blankRows[i]);
}
}
- Save and name your project: Click on
File
>Save
, and give your project a name. - Run the script: Click on the play (▶) button next to the
removeBlankRows
function in the Apps Script editor to execute the script. You might need to authorize the script to run under your Google account the first time. - Setting up a trigger (optional): If you want this script to run automatically at certain intervals or based on specific triggers:
- Click on the clock icon on the left sidebar (Triggers).
- Click on
+ Add Trigger
in the lower right corner. - Choose the
removeBlankRows
function to run. - Select the event source (e.g., time-driven to run it daily, weekly, etc.).
- Configure the trigger details as needed.
- Click
Save
.
That’s it! Now you have a script that can remove all blank rows from the active sheet in your Google Sheets document. Remember that running this script will permanently remove blank rows, so make sure this is what you intend before executing the script.