getRange('A1:B') keeps going for ages - part of delete rows when cell does not contain value

Question

My sample sheet

I have 2 columns, A contains units & B data. I want to delete entire rows from A & B where text in B does not contain '-'.

I have other data in the other columns in the sheet, so this should only apply to A & B.

I normally manually filter and delete these rows.

Following the script in this question, when I used exactly this but with "| My Text Here" replace with "-", it worked, but a lot of the data that should be there were also deleted. So when I should have had 300 rows of data left, I only had 124 after running the script. How to delete rows that do not containing specific text within A column using Google Sheets App?

function deleteFunction(){
  //declarations
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Sheet1');
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  for(var i=values.length-1;i>0;i--){
    var myValue=values[i][0];
          Logger.log(i);
    if( myValue.indexOf("-")==-1){
      Logger.log(myValue);
      sheet.deleteRow(i+1);
    }
  }
}

When I change the dataRange to just getRange('A1:B'); the function keeps going for ages without results.

I changed it to .getRange(1, 2, dataRange.getLastRow(), dataRange.getLastColumn()); and that deletes ALL the data in the range.

Basically, at this point I'm confused, I keep changing the ranges to try to see results.

I'm a newbie to this so I'm not sure where I go wrong.

Answer 1

Try it this way:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const shsr = 3;//data startrow atleast in your sample
  const sh = ss.getSheetByName('Sheet1');
  const rg = sh.getRange(shsr, 1 , sh.getLastRow() - shsr + 1, 2);
  const vs = rg.getValues();
  let d = 0;
  vs.forEach((r, i) => {
    if (!~r[1].toString().indexOf('-')) {
      sh.deleteRow(i + shsr - d++)
    }
  });
}

You need to count the number of rows that you delete because all of the row move up as you delete them. So you have to keep subtracting a larger offset as you delete more and more lines because indexing of the array does not change. Also you were using values[i][0] which is column one and your data indicate it should have been column 2. And finally by using sh.getDataRange() you start running your code at line one instead of line 3 where your data starts.

or this way

This method is a little quicker because you are not deleting one row at a time.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const shsr = 3;
  const sh = ss.getSheetByName('Sheet1');
  const rg = sh.getRange(shsr, 1 , sh.getLastRow() - shsr + 1, 2);
  const vs = rg.getValues();
  let d = 0;
  let o = [];
  vs.forEach((r, i) => {
    if (~r[1].toString().indexOf('-')) {
      o.push(r)
    }
  });
  rg.clearContent();
  sh.getRange(shsr, 1, o.length,o[0].length).setValues(o);
}

In the latter case we are keeping load the rows that we want into an output array and then removing all lines from the shsr to the bottom and replacing them with the wanted lines.