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

0
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:C12");
  var v1 = r1.getValues();
  var r2 = s2.getRange("A2:A28");
  var v2 = r2.getValues();

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

I don't know how to make it to compare data from the whole range, not just from a single cell.

Thanks.

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

I believe your goal as follows.

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

For this, how about this modification?

Modification points:

  • In this case, it is required to compare all values of "C4:C12" and "A2:A28". But when each value of v2 is compared with all values of v1 in the loop, the process cost will be high. So at first, it creates an object for searching the values, and the search is run using the object.

When your script is modified, it becomes as follows.

Modified script:

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

var obj = v1.reduce((o, [e]) => (Object.assign(o, {[e]: true})), {});
for (var i = v2.length - 1; i >= 0; i--)
  if (obj[v2[i][0]])
    s2.deleteRow(i + 2);

References: