When a user deletes a list item (row), is there a way for the corresponding row in Google Sheets to also be automatically deleted?

Essentially after a user deletes a list item, it removes all the content from the corresponding Google Sheet row, but the row itself remains (empty). Any way for the row itself to get automatically deleted?

Use google script

1 Like

Combined a few scripts (and setup a corresponding onChange trigger) for a working solution:

function deleteEmptyRows() {
  const spreadsheet = SpreadsheetApp.getActive();
  const sheet = spreadsheet.getSheetByName("Main");
  const startingRow = 2;
  const range = sheet.getRange(startingRow, 1, sheet.getLastRow() - startingRow + 1, sheet.getLastColumn()).getValues();
  let deleteCounter = 0;

  range.forEach(function(r, i){
    if (r.join('').length == 0) {
      sheet.deleteRow(i + startingRow - deleteCounter++)
    }
  });

  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();

  if (maxRows - lastRow != 0) {
    sheet.deleteRows(lastRow + 1, maxRows - lastRow);
  }

}

That code will delete non-empty rows if you have 2 or more empty rows.

Why?

Because you read the entire sheet into memory, and then iterate from the top down. That approach is fine as long as you only ever have one empty row in your sheet. But as soon as you have two or more, then you have a problem. The problem is that as soon as you delete the first row, the index numbers of all subsequent rows will be offset by 1. But, you’re still iterating though your copy of the sheet in memory, and the row counts no longer match. So when you get to the next empty row and delete it, you’ll actually be deleting the wrong row.

The simple way to avoid that problem is to iterate from the bottom up. Row numbers will still change as you delete rows, but the numbers that change will be for rows that you’ve already processed. So it won’t matter.

1 Like

@Darren_Murphy I’m still a beginner with Apps Script, so I just winged it to get a working solution.

Row 1 is “frozen” for column names/titles and is also bold, and row 2 will always either contain data, or be an empty row. Essentially row 2 never gets deleted. Because if row 2 were to get deleted, and only row 1 is remaining (the names/titles row), then if I use Glide to add a list item (row) it inherits the text styling from Row 1 (and is bold as a result). So at this moment, the script works as it should and avoids that issue (although it avoids it by allowing the script to fail), so when I delete the last list item (which would be row 2) from within Glide, the script fails, and Glide clears row 2, and since the script fails, it leaves row 2 empty and all is well.

If I then add a list item (within Glide), the data gets added into row 2, and again, all is well for any following list items that are added.

If you are able to rework my script and iterate how you mentioned (from the bottom) and take into consideration the things I mentioned about row 2, please share, I’d love to get the script as efficient as possible, thanks.

Something like this would be safer:

function remove_empty_rows(sheetname) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  var row = sheet.getLastRow();
  while (row > 2) {
    var rec = data.pop();
    if (rec.join('').length === 0) {
      console.log("Deleting row %s", row);
      sheet.deleteRow(row);
    }
    row--;
  }
}

The important thing - as I mentioned - is to iterate from the bottom upwards. This eliminates the risk of the wrong rows being deleted.

@Darren_Murphy The script you posted, definitely work’s, but the one thing it wasn’t doing was deleting any empty rows that come after the last data containing row.

That’s why I have this part at the end of my script:

var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();

if (maxRows - lastRow != 0) {
  sheet.deleteRows(lastRow + 1, maxRows - lastRow);
}

Is there a better way to integrate that into your script, or should I just paste it at the bottom of your script?

function remove_empty_rows(sheetname) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getRange(2, 1, sheet.getMaxRows() - 1, sheet.getLastColumn()).getValues();
  var row = sheet.getMaxRows();
  while (row > 2) {
    var rec = data.pop();
    if (rec.join('').length === 0) {
      console.log("Deleting row %s", row);
      sheet.deleteRow(row);
    }
    row--;
  }
}

Only problem with doing it the way you just suggested is that lets say there are 100 or 1,000 empty rows after the last data containing row, then your script would iterate through each row one at a time and remove them one at a time, which is very slow at scale.

The way I have it deletes any number of empty rows after the last data containing row all at once.

Yes, that’s true.
I normally do these as two separate housekeeping scripts.

Cool, then in the end, at least for my use case, I’m going to use your bottom up iterating script, and paste that last part at the bottom of it. Thanks for all the assistance, appreciate it :slight_smile:

1 Like

Solution (with corresponding onChange trigger):

function deleteEmptyRows(sheetname) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetname);
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  var row = sheet.getLastRow();

  while (row > 2) {
    var rec = data.pop();
    if (rec.join('').length === 0) {
      sheet.deleteRow(row);
    }
    row--;
  }

  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();

  if (maxRows - lastRow != 0) {
    sheet.deleteRows(lastRow + 1, maxRows - lastRow);
  }
}

Contributors: @Darren_Murphy, @osxzxso

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.