Script to Automatically Delete Empty Rows after Last “Not Empty” Entries in Google Sheet

by

I am trying to create a script for Google Sheet that will automatically delete the last empty row of the active sheet only. The code is working well , but it does delete raws of another sheet tab and not my active one. Also it does not run automatically and I have to go to the app script panel to force a run each time.

I am looking for a way to run this script automatically to delete all empty rows after the last “not empty” row available on that specific active sheet.

//Delete empty rows

function removeEmptyRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allsheets = ss.getSheets();
  for (var s in allsheets){
    var sheet=allsheets[s];
    var maxRows = sheet.getMaxRows(); 
    var lastRow = sheet.getLastRow();
    if (maxRows - lastRow > 1){
      sheet.deleteRows(lastRow + 1, maxRows - lastRow);
    }
  }
}

  • Does Google code work in Excel?

    – 

  • Can I ask you about the details of automatically of run this script automatically you expect? What is the trigger for executing the script?

    – 

  • It’s not exactly what you’ve asked for but in this blog, I’ve provided a script to remove all the empty rows and columns from the sheet here: medium.com/script-portal/…. You can modify it to your specifics and setup a time-driven trigger.

    – 

  • Is your issue resolved, using solution provided below?

    – 

According to provided code, to implement functionality to delete the last empty rows of active sheet only, in google sheets, using google apps script, this code can be used.

function removeEmptyRows() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var maxRows = sheet.getMaxRows();
    var lastRow = sheet.getLastRow();
    if (maxRows - lastRow > 1){
        sheet.deleteRows(lastRow + 1, maxRows - lastRow);
    }
}

To make this code run automatically, the trigger, using event source as “Time-driven” can be set in settings “Triggers” for this project in google apps script editor.

Here’s your code with out the loop and set on the active sheet:

function removeEmptyRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet()
  const maxRows = sh.getMaxRows();
  const lastRow = sh.getLastRow();
  if (maxRows - lastRow > 1) {
    sheet.deleteRows(lastRow + 1, maxRows - lastRow);
  }
}

Leave a Comment