Apps Script that removes blank rows in a Google Sheet

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:

  1. Open your Google Sheet: Go to the Google Sheets document where you want to remove blank rows.
  2. Open Script Editor: In the Google Sheets menu, click on Extensions > Apps Script.
  3. 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]);
  }
}
  1. Save and name your project: Click on File > Save, and give your project a name.
  2. 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.
  3. 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.