Exceptions in deleting rows in a spreadsheet

Ask for Help
42

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

43

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.

2 Likes
44

i forgot to add a check for all rows

45

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.

46

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.

1 Like
47

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

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.

1 Like
48

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

49

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

50

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…

1 Like
51

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

53

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

54

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

Hire Us

1 Like
55

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.

1 Like
56

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

3 Likes
57

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…

// (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

2 Likes
58

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.

1 Like
59

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

60

Ok, thank you. This is a drunk script… :rofl: :rofl: :rofl:

2 Likes
61

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

1 Like
62

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