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

**URL:** <https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102>\
**Category:** Ask for Help\
**Created:** [March 25, 2022, 8:36pm UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102 "2022-03-25T20:36:23Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 25, 2022, 8:36pm UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/1 "2022-03-25T20:36:23Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [March 25, 2022, 8:53pm UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/2 "2022-03-25T20:53:16Z")

</div>

Use google script

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 25, 2022, 10:02pm UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/3 "2022-03-25T22:02:43Z")

</div>

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

```auto
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);
  }

}

```

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 26, 2022, 2:38am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/4 "2022-03-26T02:38:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 26, 2022, 4:08am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/5 "2022-03-26T04:08:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 26, 2022, 4:21am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/6 "2022-03-26T04:21:58Z")

</div>

Something like this would be safer:

```auto
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.

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 26, 2022, 4:44am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/7 "2022-03-26T04:44:20Z")

</div>

@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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 26, 2022, 4:49am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/8 "2022-03-26T04:49:18Z")

</div>

```auto
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--;
  }
}

```

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 26, 2022, 4:54am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/9 "2022-03-26T04:54:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [March 26, 2022, 4:57am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/10 "2022-03-26T04:57:08Z")

</div>

> [@osxzxso](#):
>
> 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.

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

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 26, 2022, 4:59am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/11 "2022-03-26T04:59:13Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![osxzxso](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/osxzxso/32/7356_2.png) [@osxzxso](https://community.glideapps.com/u/osxzxso)\
**Post date:** [March 26, 2022, 5:03am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/12 "2022-03-26T05:03:16Z")

</div>

Solution (with corresponding onChange trigger):

```auto
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

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [March 27, 2022, 5:03am UTC](https://community.glideapps.com/t/when-a-user-deletes-a-list-item-row-is-there-a-way-for-the-corresponding-row-in-google-sheets-to-also-be-automatically-deleted/40102/13 "2022-03-27T05:03:40Z")

</div>

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