How to delete blank rows in Google Sheets

Greg_L · 03-02-2020 03:30 PM

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

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!

Swoopy

This is what I’ve been looking for years !

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

Greg_L

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.

Swoopy

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.

MultiTech

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?

Greg_L

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();
}

hru

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:

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

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.
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”.

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.

MultiTech

Thank you @Heru!

Koichi_Tsuji

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.

Lucinda_Mason

Thank you so much Heru and @Greg_L. This is exactly what I needed. I just tested it on several different sheets and it works perfectly. Now, I just have to be brave enough to put it on my production sheet with real data.

Much appreciated.

MultiTech

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

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

Jordan_Davis1

Greg_L:

/**
 * 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();
}

Does anyone get the error message " Exception: You can’t delete all the rows on the sheet." when running this? It works but I always get this error when running it manually. After looking it up, it appears the error may stem from the ‘for’ loop.

mike11

I am on an iPhone so I can’t check script, but are you trying to execute this on a sheet that has no content below a header row? I didn’t see any error checking so you might be trying to delete ALL rows in your sheet which is not allowed (must have at least one row). Add a few rows to your sheet and see if it runs. Just something to check.

Greg_L

Hi @Jordan_Davis1,

I’m not 100% sure, but I think you would get that error when the sheet has no data in it. The problem is that the script wants to delete all the rows in that case, but at least one row is always required.

Jordan_Davis1

Hey Greg and Mike, thanks for the feedback. I have about 10 sheets in the whole workbook and they all have hundreds of rows of data including header columns so I’m not sure why I’m getting this error.

hru

Is it possible, there is a hidden empty sheet?

Jordan_Davis1

Wow, i didn’t know about hidden sheets. You found the issue, I had a blank hidden sheet. Thanks so much!!! It works now.

Steven_Aung

If you don’t want to loop through each rows and rely mostly on build in function, you can try following script. Note that this script is only for one sheet. You can modify it to cover all sheets.

function RemoveEmptyRows() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .whenCellEmpty()
  .build();
  
  /* Use following code if you don't have key columns. It will filter empty for all columns.
  var maxColumn = sheet.getMaxColumns();
  for(var i = 1; i <= maxColumn; i++){
    sheet.getFilter().setColumnFilterCriteria(i, criteria);
  }*/
  var keyColumns = [1,3]; // !!!CHANGE THIS ARRAY FOR KEY COLUMNS
  for(var i = 0; i < keyColumns.length; i++){
    sheet.getFilter().setColumnFilterCriteria(keyColumns[i], criteria);
  }
  
  spreadsheet.getRange('1:1').activate();
  sheet.insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1); // insert an empty row after row 1. So we know the row 2 is empty
  spreadsheet.getRange('2:2').activate();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  
  sheet.deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
  sheet.getFilter().remove();
};

thiavila

Holy sh** man, that was a killer code. Seriously

dbaum

Glad to know someone else finds this approach intuitive also. Curious to know whether anyone has found it to be problematic--whether due to the risk that @LeventK notes that you can "run out of Google’s execution limits" or due to any other reasons.

FWIW, here's the version I had come up with. It's slightly slimmed down compared to @Steven_Aung's post because it assumes column A is only ever blank for rows that should be deleted. In my sheets, column A is always the key or a non-blank part of a composite key.

function iterateSheets() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getSheets().forEach(Deleteemptyrows)

}

function Deleteemptyrows(sheet) {
  sheet.insertRowsAfter(1, 1);
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  sheet.getActiveRange().createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .whenCellEmpty()
  .build();
  sheet.getFilter().setColumnFilterCriteria(1, criteria);
  sheet.getRange(2, 1).activate();
  sheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  sheet.deleteRows(sheet.getActiveRange().getRow(), sheet.getActiveRange().getNumRows());
  sheet.getFilter().remove();

};

casitasrd

@dbaum you are wizard too! I started doing research for this solution, and after reading everyones solution for this, I decided to give yours a try. and I just have to say, thank you, this is amazing! This is working wow! I really don't know how you guys do this, but I'm a fan. Big fan.

casitasrd

@dbaum why wouldn't this feature be automatically applied to appsheet as default? Who would want the extra spaces in their database?

dbaum

I don't know. Potentially, some app designs depend on stable row number values. That doesn't seem to me like a good practice, but maybe if it's in use that's the impediment to changing AppSheet functionality--i.e., to avoid disruption to such apps.

Keep in mind that you could do something like call a script every time a row is deleted or run a script nightly. That would keep blank rows from accumulating almost as if AppSheet deleted them directly.

Denny774

Thanks @dbaum

Your script works like a charme.
I tweaked it a lil, so you can run it from within an AppSheet (bot) workflow, which makes more sense to me.

Cheers

function OpenSheet(sheetName) {
  // Open the spreadsheet and get the sheet
  var spreadsheet = SpreadsheetApp.openById("<HERE GOES THE ID OF YOUR GSHEET>");
  var sheet = spreadsheet.getSheetByName(sheetName);
  Deleteemptyrows(sheet);
}

function Deleteemptyrows(sheet) {
  sheet.insertRowsAfter(1, 1);
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  sheet.getActiveRange().createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .whenCellEmpty()
  .build();
  sheet.getFilter().setColumnFilterCriteria(1, criteria);
  sheet.getRange(2, 1).activate();
  sheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  sheet.deleteRows(sheet.getActiveRange().getRow(), sheet.getActiveRange().getNumRows());
  sheet.getFilter().remove();
};

LeventK

The scripts proposed under this post will quickly run out of Google’s execution limits provided you have a lot of columns and rows in your gSheet. The best and the quickest way to handle the issue is using Google’s Visualization Query. For example; I use below gs code to return some user data from the gSheet. With a gSheet of 30-35 columns and over 50K rows, the script’s return runtime is approx. 4-5 secs. You can alter the code below to remove empty rows as well. In terms of script execution time, it’ll be much more quicker.

GAS CODE

function verifyUserCredentials(spreadsheetID, sheetName, queryColumnLetterStart, queryColumnLetterEnd, queryColumnLetterSearch, query) {

  // SQL like query
  myQuery = "SELECT * WHERE " + queryColumnLetterSearch + " = '" + query + "'";

  // the query URL
  var qvizURL = 'https://docs.google.com/spreadsheets/d/' + spreadsheetID + '/gviz/tq?tqx=out:json&headers=1&sheet=' + sheetName + '&range=' + queryColumnLetterStart + ":" + queryColumnLetterEnd + '&tq=' + encodeURIComponent(myQuery);

  // fetch the data
  var ret = UrlFetchApp.fetch(qvizURL, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getContentText();

  // remove some crap from the return string
  var response = JSON.parse(ret.replace("/*O_o*/", "").replace("google.visualization.Query.setResponse(", "").slice(0, -2));
  if (typeof response.table.rows[0] != 'undefined') {
    var data = response.table.rows[0].c[1].v;
    return [true, data];
  } else {
    return [false,""];
  }
}

MultiTech

@LeventK You are a wizard!

Michael2

@LeventK I am be very interested in using your proposed script to delete the empty rows. Unfortunately, I have no idea how to modify it to delete the rows as you suggest.

When running the script as it is, I get a ‘server error’ with no other information as to what is wrong.

Vincent_Le

How do we run the script on one sheet?

Basically I want to run the script on the sheet im on, not all the sheets.

LeventK

You can set the sheet name in the script as a global variable.

dbaum

Has anyone already synthesized into a single script the various examples in this thread, including the portions to loop through sheets, identify column range, use @LeventK's Visualization Query technique to identify blank rows (e.g., "SELECT * WHERE A IS NULL"), and then delete the rows?

Djigi

@Greg_L Amazing! 😄

I was wonder if this does not make conflicts. For example, if someone updates a row with appsheet, while a row is removed. Does appsheet work well together with the script? Does this happen serial?

I have the same question about using a filter in sheets and changing something to alphabetic order while somebody updates a row at the same time. Don't know if that will conflict as well, but that was not my initial question :d

Does anyone know if there will be a conflict if appsheet and the script are being used at the same time?

KayPringle

Hey! This works super awesome thank you so much for creating this! Do you have any guidance on making this work for empty columns? I'm just getting back into the computer science world and coding, so I just managed to get it to work with very little understanding of what's actually going on! Thanks so much!