# Exceptions in deleting rows in a spreadsheet

**URL:** <https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224>\
**Category:** Ask for Help\
**Created:** [January 15, 2022, 2:20pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224 "2022-01-15T14:20:20Z")\
**Posts on this page:** 20\
**Page:** 3

<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:** [January 16, 2022, 4:47am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/42 "2022-01-16T04:47:29Z")

</div>

my script does not have add row function… only delete… something else is happening

---

<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:** [January 16, 2022, 4:49am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/43 "2022-01-16T04:49:07Z")

</div>

> [@Uzo](#):
>
> Darren’s code is kind of weird… so maybe this one will work good for you

mm, in what way?

That code that you just posted looks like it only deletes empty rows at the end of the sheet.  
The code I gave him will remove empty rows, no matter where they are.

---

<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:** [January 16, 2022, 4:49am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/44 "2022-01-16T04:49:55Z")

</div>

i forgot to add a check for all rows

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 4:51am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/45 "2022-01-16T04:51:12Z")

</div>

Yes, it doesn’t seem to be a problem in the script. Maybe row id too. Hard to trace. Or avoiding the solution does not open up the opportunity to take any means.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 4:56am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/46 "2022-01-16T04:56:29Z")

</div>

All your scripts are great Darren. There may just be behavior that we don’t know about.  
At first, I thought learning to code was something that could provide certainty, but again there is the x-factor (we often call it a mystery) as well as nature.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 10:19am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/47 "2022-01-16T10:19:41Z")

</div>

I guess I figured it out @Darren_Murphy and @Uzo:

1. What you need to know is that my query formula is precisely the column that is used as the basis of the relation. Perhaps this also contributes to the occurrence of time lags and these query rows do not decrease as they are removed.
2. In the first deletion of the last row (##Delete), 500 empty rows were added, followed by regenerating Row ID (if any). There is even a refresh time of the query formula. Maybe out of shock…
3. The second deletion by giving enough time, succeeded in deleting the row with no additional blank lines. There is even a reduction of one line after another for subsequent deletions.
4. The next experiment is to delete quickly. Some have been successfully erased but some have frozen.

![Screen Shot 2022-01-16 at 16.47.29](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/c/fcf4b77578e6d8907325dc234bea621cd3f1add2.png)

Conclusion:

1. The problem is the problem of time and relation in the formula.
2. The addition of 500 lines is probably the buffer time.

I need to rethink using clear value (probably safer) or try deleting all rows in one action.

Very grateful for both of your contributions.

---

<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:** [January 16, 2022, 10:25am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/48 "2022-01-16T10:25:12Z")

</div>

noticed … in your image … it is ETELED not DELETE, it might be the reason why works differently… my script did not fire

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 10:27am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/49 "2022-01-16T10:27:21Z")

</div>

Yes, I used the unique word ##ETELED instead of Delete. That’s just an explanation. Is there anything else that might arise?

---

<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:** [January 16, 2022, 10:29am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/50 "2022-01-16T10:29:53Z")

</div>

I don’t know… I did try on my apps many versions of deleting rows… never have a problem… so I guess, I would have to see your app to know what is the issue… as usual it might be some missing ; or , or arrayformula that you did not pay attention to it…  
in CODING, the Devil always hides in details… LOL  
and I wanna say sorry to @Darren_Murphy for calling his script weird… it is that I would write it much simpler… but I know he is a much better coder than me… so he has his reasons…

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 10:34am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/51 "2022-01-16T10:34:06Z")

</div>

will you? Actually my application to whitelist emails. Or I made a duplicate of it if you’re curious. 😁

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 10:35am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/53 "2022-01-16T10:35:20Z")

</div>

Can you give me a script that applies to multiple sheets rather than all sheets?

---

<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:** [January 16, 2022, 10:38am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/54 "2022-01-16T10:38:48Z")

</div>

I.m drunk and tired… LOL just came back from a party ;-)…  
but anyone can hire me by clicking this button LOL!

[![Hire Us](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/7/272e8444e04d0682c3d77bf774bf076f2c68b736.png)](https://buy.stripe.com/eVa6sb9q3f5b9iw5kt)

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 10:49am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/55 "2022-01-16T10:49:18Z")

</div>

It’s okay, you rest first. Regardless of being resolved or not, this is an interesting phenomenon to talk about.  
I’m not a programmer, learn from trial and error and prefer the errors to be used as material for the next trial.  
And very proud to meet people who are experts in this field.

---

<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:** [January 16, 2022, 11:20am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/56 "2022-01-16T11:20:04Z")

</div>

hehe, nothing to apologise for. I’m always open to criticism and learning. I was just curious to know why you thought it was weird. If it’s badly written, I want to know about that so I can improve it 🙂

---

<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:** [January 16, 2022, 11:44am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/57 "2022-01-16T11:44:28Z")

</div>

if the Greatest responded… @Darren_Murphy … then I have to reply… even when I.m drunk … LOL…  
this is a little code I wrote for you… just copy and paste… replace Sheet 1…2…3 with your own names…

```auto
// (c) StructureArt DIGITAL

function deleteEmptyRows() {

  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var sheetName = ss.getName();
  var valueActiveCell = ss.getActiveCell().getValue(); 

if (sheetName == "Sheet1" || sheetName == "Sheet2" || sheetName == "Sheet3" && valueActiveCell == "") {
  var maxColumns = ss.getMaxColumns();
      
      row = ss.getMaxRows();

for (var i = 0; i < row-2; i++){

var value = ss.getRange(row-i,1,1,maxColumns).getValues();
var stringvalue = value[0][0].toString()

var replaceValue = stringvalue.replace(",","StructureArt DIGITAL")

  if (replaceValue == "" ){
      ss.deleteRow(row-i);
            
      }
  }
}}

```

this is a secret service sheeeet bro! LOL @Himaladin you will never get an empty row in these sheets!

Ps: I know I can reduce this code by 4 lines… but it would not be clear for others… and it will not reduce execution time by a significant number.  
And while I’m still drunk… @Robert_Petitto that will be nice code for academic purposes?  
use this code with the trigger On Change

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 11:58am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/58 "2022-01-16T11:58:20Z")

</div>

Thank you, my friend.  
Maybe it’s better if I decide to use clear value instead of delete row because the original goal is to delete data only and not delete accounts with personal data.  
I will save your script and try it for other uses later.

---

<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:** [January 16, 2022, 12:00pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/59 "2022-01-16T12:00:22Z")

</div>

I guarantee is safe… and I made for 3 sheets as you requested

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 16, 2022, 12:04pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/60 "2022-01-16T12:04:44Z")

</div>

Ok, thank you. This is a drunk script… 🤣 🤣 🤣

---

<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:** [January 16, 2022, 12:05pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/61 "2022-01-16T12:05:46Z")

</div>

but is good, and it will serve you the best… you can delete previous scripts from me and Darren… this one will replace all of them

---

<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:** [January 16, 2022, 12:17pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/62 "2022-01-16T12:17:10Z")

</div>

can you pls explain how this code is deleting personal data???

[Previous page](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224.md?page=2)

[Next page](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224.md?page=4)