CODEWITHSUNDEEP
google-apps-scriptdelete-row
DarthBusiness
DarthBusiness

Reputation: 153

Google Apps Script code to delete rows based on partial cell data

I'm trying to delete rows based on partial cell data contained in cells in column A. For example a cell might contain a value "OX USPEA", cylinder part number, I work in the gas industry, and I want to delete any row that contains this value. There are nearly 200 rows out of about 500 that I'm working with that contain the beginning text "OX USPE" but end in various different text combinations. This is a snipet of the code I have developed so far but I cant get it to work. I get the error "Exception: Those rows are out of bounds. (line 218, file "macros")", line 218 is where the error falls as I have many other lines of code in this same document, here is the code I am focusing on: 

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Fast Fill'), true);
  var snipLoop = spreadsheet.getRange("A:A").getValues();
  for(var i = 0; i<snipLoop.length; i++){
    if(snipLoop.indexOf("ox uspe")){

      spreadsheet.deleteRow(i);

    }
  }

};

Again, when I run this I get the error "Exception: Those rows are out of bounds. (line 218, file "macros")". What am I doing wrong?

Edit:

Based on input for user ADW I have updated my code to the following

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Fast Fill'), true);
  var snipLoop = spreadsheet.getRange("A:A").getValues();
  for(var i = (snipLoop.length - 1); i >= 0; i--){
    if(snipLoop.indexOf("OX USPE") > -1){

      spreadsheet.deleteRow(i);

    }
  }

};

I need to further explain that the rows I am intending to delete have data values stored in columns A through E if this makes a difference.

When I run this code I get no errors and the code runs completely, however nothing happens to the activated sheet. Here I have copied and pasted the sheet name, Fast Fill, so I am certain of its accuracy in the code. Can anyone explain this?

Edit: here is an simplified example spreadsheet similar to what I am working with 

Material    Req
CD 50S      21
CD FG20A    49
CD USP50    25

Upvotes: 0

Views: 1501

Answers (2)

Iamblichus
Iamblichus

Reputation: 19309

If I understood you correctly, you want to delete the rows in which the cell in column A contains "OX USPE". If that's the case, you can do this:

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Fast Fill');
  var textToFind = "OX USPE";
  var firstRow = 1;
  var column = 1;
  var numRows = sheet.getLastRow() - firstRow + 1;
  var snipLoop = sheet.getRange(firstRow, column, numRows).getValues().map(function(row) {
    return row[0];
  });
  for(var i = snipLoop.length - 1; i >= 0; i--){
    if(snipLoop[i].indexOf(textToFind) === 0){
      sheet.deleteRow(i + firstRow);
    }
  }
};

The main problem in your code is that getValues returns a two-dimensional array. When using Array.prototype.indexOf() to snipLoop you are looking for an element in the outer array that matches the string OX USPE. Because each element in the outer array is actually another array (the inner array) this cannot work. It will not delete any row.

To check if a cell value contains some string, you should use String.prototype.indexOf() on each value in the inner array instead. To do that, you can (1) first, use map to transform the 2D array into a one-dimensional array (you can do this because you are only retrieving column A, so each element in the outer array only contains an inner array with only one value, and (2) apply the i index to the resulting one-dimensional array.

Also, there might be a problem with the index of the rows to delete. Indexes of arrays start at 0 (snipLoop), but row indexes start at 0. Because of this, you should do this instead: sheet.deleteRow(i + firstRow);

Finally, I have modified your range a bit, so that the range is specified by indicating the firstRow, the column and the number of rows (see getRange(row, column, numRows)) instead of using the A1 notation. This way, the script will only iterate through the rows with content, and it's easier to change the range to retrieve. I've also removed the setActiveSheet part, because there is no need to use this method. You can just retrieve the sheet you want by name.

Reference:

Upvotes: 0

ADW
ADW

Reputation: 4247

Here are a couple of things you may want to consider:

  1. If a cell does not contain "ox uspe", snipLoop.indexOf("ox uspe") will give a value of -1. To stop the if statement from deleting that row, you may need to change the statement to

    if(snipLoop[i].indexOf("ox uspe") > -1){ [Edited]

  1. When deleting rows, it is best to start from the bottom.

This is because of the following problem in deleting from the top: say both rows 10 and 11 need to be deleted. After row 10 has been deleted, row 11 becomes row 10. The script is reading the data from an array fetched before row 10 was deleted. So it takes row 11 needs to be delete too. But at this point, all rows have moved one up as row 10 was deleted. So instead of row 11, row 12 gets deleted.

I would suggest the following instead:

for (var i = (snipLoop.length - 1); i >= 0; i--){

Upvotes: 1

Related Questions