How can I write a script to remove whole rows if a cell in a column matches a value in another in go-CodePudding

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?

CodePudding user response：

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);
}

CodePudding user response：

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  );
    }
  })
}