Delete row in google spreadsheet from a specific range based on values from a specific cell range

1
votes

I need a little help with my script.

First, here is a link to the ss: https://docs.google.com/spreadsheets/d/1TgG64irjoxkV9wKlT5bEnaNEWlGyOlNxVoLxOmQZ2BA/edit?usp=sharing

I want to delete rows from sheet "Team 1" (range: A15:A41), based on values from sheet "Overview" (range: C4:C12).

Basically, if any value from C4:C12 is found in A15:A41, the row where is found should be deleted.

Ex. for my ss: C4 = 3, that means in sheet "Team 1", row 17 should be deleted.

My script so far:

function deleteRows() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s1 = ss.getSheetByName('Overview');

  var s2 = ss.getSheetByName('Team 1');

  var r1 = s1.getRange("C4");

  var v1 = r1.getValue();

  var r2 = s2.getRange("A2:A28");

  var v2 = r2.getValues();

 for(var i=28;i>0;i--)

    if(v1[i]=v2)

      s2.deleteRow(i+1);
};

But the only thing I get is to delete all the rows, the criteria is not taken into consideration.

Thanks.

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

I believe your goal as follows.

  • You want to delete the rows of the sheet Team 1 by comparing the cell "C4" in the shee Overview with the cells "A2:A28" in the sheet Team 1.

For this, how about this modification?

Modification points:

  • In your script, v1 is a value which is not an array. v2 is 2 dimensional array.
    • In this case, at v1[i]=v2, the 2 dimensional array is tried to put to undefined. By this, v1[i]=v2 is always existing. So s2.deleteRow(i + 1); is always run. I think that this is the reason of your issue.
  • When you want to compare the values, please use == and/or === instead of only = in the if statement.
  • The 1st index of the array is 0.
  • When s2.getRange("A2:A28") is used, the offset is required to be added to the delete row number.

When above points are reflected to your script, it becomes as follows.

Modified script:

for (var i = 28; i > 0; i--)
  if (v1[i] = v2)
    s2.deleteRow(i + 1);

for (var i = v2.length - 1; i >= 0; i--)
  if (v2[i][0] == v1)
    s2.deleteRow(i + 2);
  • v2.length is used for retrieving the length of array.

References: