How to delete blank rows in Google Sheets

I have an app that uses parent and child records with IsPartOf turned on. When I delete a parent record, it only clears the records in Google Sheets, leaving the sheet with many large gaps over time, which gets unsightly and annoying for my OCD :laughing:

So I write a Google Apps Script that triggers once a week to delete all the blank rows in each table, to clean them up. If you’re interested, here’s the code:

function deleteBlankRows() {
  
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get sheets
  var sheets = SS.getSheets();
  
  // Loop through sheets. Delete blank rows in each sheet.
  for (var s=0; s < sheets.length; s++) {
    var currentSheet = sheets[s];
    var sheetName = sheets[s].getName();
    var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.
    var searchValues = searchDataRange.getValues();
    var numRows = searchValues.length;
    var numCols = searchDataRange.getNumColumns();
    var rowsToDel = [];
    var delRow = -1;
    var prevDelRow = -2;
    var rowClear = false;
    
    // Loop through Rows in this sheet
    for (var r=0; r < numRows; r++) {
      
      // Loop through columns in this row
      for (var c=0; c < numCols; c++) {
        if (searchValues[r][c].toString().trim() === "") {
          rowClear = true;
        } else {
          rowClear = false;
          break;
        }
      }
      
      // If row is clear, add it to rowsToDel
      if (rowClear) {
        if (prevDelRow === r-1) {
          rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;
        } else {
          rowsToDel.push([[r+1],[1]]);
          delRow += 1;
        }
        prevDelRow = r;
      }
    }
    
    
    Logger.log("numRows: " + numRows);
    Logger.log("rowsToDel.length: " + rowsToDel.length);
    
    // Delete blank rows in this sheet, if we have rows to delete.
    if (rowsToDel.length>0) {
      // We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.
      if (numRows === rowsToDel[0][1]) {
        // This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).
        // Delete all but the first row.
        if (numRows > 1) {
          currentSheet.deleteRows(2,numRows-1);
        }
      } else {
        // Go through each set of rows to delete them.
        var rowsToDeleteLen = rowsToDel.length;  
        for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {
          currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);
        }
      }
    }
  }
}

To make this run once a week, you need to open the spreadsheet containing your data. Go to Tools > Script Editor in the menu and add the code I pasted above. I named the file “deleteBlankRows”. Now you need to set the trigger. From the script editor menu, select Edit > Current project’s triggers. In the lower right corner, click the blue button, Add Trigger. On the pop-up form, select Time Driven for the Event Source. The other options are pretty clear.

This has been running flawlessly for a few weeks now for me. I hope it help someone else too!

15 Likes

:heart: :heart: :heart: :heart: :heart:

This is what I’ve been looking for years !

However, I cannot open the “Current project’s triggers”.

1 Like

You are amazing @Greg_L!!!

One sleight request that might make this even more flexible:

  • include a custom menu with an option to trigger this script?

Glad to help @MultiTech_Visions

You can add this code to the top of the script file.

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Clean up')
    .addItem('Delete blank rows', 'deleteBlankRows')
    .addToUi();
}
6 Likes

It would have driven me crazy if I could not clean up the empty records!!

I don’t know why you can’t open “Current project triggers”. It should be available under the Edit menu in the Scripts editor, not the menu for the sheet itself.

CurrentProjectTriggers

capture

I found the problem. It’s because of a Chrome’s extension. I’ve to allow pop up on script.google.com in order to show the triggers tab.

2 Likes

I can not help myself to share and to add more flexibility on this discussion, you might want to have several menu in the sheet itself.
Such as:
image

after copy and paste below script and save the script, refresh the google sheet, you will see the menu in google sheet:

  1. Click “Authorize and Install Trigger”, for initialization. The trigger will automatic installed and triggered every Sunday at 09:00 and a pop-up will appeared on the google sheet for successful installation.

  2. Sometime you also want to manually delete the blank rows (if you don’t want to wait for Sunday to come. Click “Delete Blank Row Manually”.

  3. Sometime you just want to remove the trigger.

    function onOpen() {
          var ui = SpreadsheetApp.getUi();
          ui.createMenu('Delete Blank Rows')
          .addItem('Authorize and Install Trigger', 'configure')
          .addItem('Delete Blank Row Manually','deleteBlankRows' )
          .addItem('Remove Trigger', 'reset')
          .addToUi();
     }
    
    function deleteBlankRows() {
    
        var SS = SpreadsheetApp.getActiveSpreadsheet();
    
        // Get sheets
        var sheets = SS.getSheets();
    
         // Loop through sheets. Delete blank rows in each sheet.
        for (var s=0; s < sheets.length; s++) {
        var currentSheet = sheets[s];
        var sheetName = sheets[s].getName();
        var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.
        var searchValues = searchDataRange.getValues();
        var numRows = searchValues.length;
        var numCols = searchDataRange.getNumColumns();
        var rowsToDel = [];
        var delRow = -1;
        var prevDelRow = -2;
        var rowClear = false;
     
     // Loop through Rows in this sheet
     for (var r=0; r < numRows; r++) {
       
       // Loop through columns in this row
       for (var c=0; c < numCols; c++) {
         if (searchValues[r][c].toString().trim() === "") {
           rowClear = true;
         } else {
           rowClear = false;
           break;
         }
       }
       
       // If row is clear, add it to rowsToDel
       if (rowClear) {
         if (prevDelRow === r-1) {
           rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;
         } else {
           rowsToDel.push([[r+1],[1]]);
           delRow += 1;
         }
         prevDelRow = r;
       }
     }
     
     
     //Logger.log("numRows: " + numRows);
     //Logger.log("rowsToDel.length: " + rowsToDel.length);
     
     // Delete blank rows in this sheet, if we have rows to delete.
     if (rowsToDel.length>0) {
       // We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.
       if (numRows === rowsToDel[0][1]) {
         // This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).
         // Delete all but the first row.
         if (numRows > 1) {
           currentSheet.deleteRows(2,numRows-1);
         }
       } else {
         // Go through each set of rows to delete them.
         var rowsToDeleteLen = rowsToDel.length;  
         for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {
           currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);
         }
       }
      }
     }
    }
    
    function configure() {  
        reset();
        ScriptApp.newTrigger('deleteBlankRows')
        .timeBased()
        .onWeekDay(ScriptApp.WeekDay.SUNDAY)
        .atHour(9)
        .create();
        Browser.msgBox("Initialized", "Blank Row will be deleted with trigger every Sunday at 09:00", Browser.Buttons.OK)
    
    }
    
    function reset() {
    
        var triggers = ScriptApp.getProjectTriggers();  
        for (var i = 0; i < triggers.length; i++) {
        ScriptApp.deleteTrigger(triggers[i]);    
     }
     
    }
    

Hope this will add value to the discussion.

9 Likes

Thank you @Heru!

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Delete Blank Rows’)
.addItem(‘Authorize and Install Trigger’, ‘configure’)
.addItem(‘Delete Blank Row Manually’,‘deleteBlankRows’ )
.addItem(‘Remove Trigger’, ‘reset’)
.addToUi();
}

variable of “ui” is generated by the script nicely, so custom menu should come up!!!

I m impressed.

Thank you all.

4 Likes

This is why the community rocks and why everyone here is SO FREAKIN AMAZING!!! :nerd_face: :metal:

One person’s like, “Hey check this out.” Then everyone else is like, “Yes, and here’s more!!!”

9 Likes