How can I write a script to remove whole rows if a cell in a column matches a value in another in google sheets?

Question

Been very frustrating to figure this out. Lets say I have 5 columns in google sheets and there's 10,000 data points in column B, while column E only has 500 and each column has random strings of numbers (i.e. b2=46, b3=75, e2=177, e3=1, and so on). I want to iterate a loop that goes through column e, and if at any point a cell value matches that in column B it deletes the whole row in column B. I'm very stumped unfortunately, could someone help me out with this?

Answer 1

The easiest way to do this is probably to get all the values, use Array.filter() to leave just the rows that do not match any of the keys in E2:E, and finally use Range.setValues() to write the remaining rows back to the spreadsheet, like this:

/**
* Deletes rows in A2:D where the value in column B equals
* at least one value in E2:E.
*/
function deleteMatchingRows() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const keysToDelete = sheet.getRange('E2:E').getValues().flat();
  const range = sheet.getRange('A2:D');
  const searchColumn = 1; // zero-indexed; column A = 0, B = 1...
  const values = range.getValues();
  const filtered = values.filter(row => !keysToDelete.includes(row[searchColumn]));
  range.clearContent();
  range.offset(0, 0, filtered.length, filtered[0].length).setValues(filtered);
}

Answer 2

Try this:

function delRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let d = 0;
  const bvs = sh.getRange(1, 2, sh.getLastRow()).getValues().flat();
  sh.getRange(1, 5, sh.getLastRow()).getValues().flat().filter(e => e).forEach(e => {
    let idx = bvs.indexOf(e);
    if(~idx) {
      sh.deleteRow(idx + 1 - d++);
    }
  })
}