CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
rockinfreakshow
rockinfreakshow

Reputation: 29878

Pass on a Column range of values to a variable in Apps Script

So I have this script which deletes rows if it regex matches one value ('/exe' here). I would like to pass on an entire column (A2:A) of values from another sheet (lets say Sheet1) to regex variable so that all the values are compared and deleted in Sheet2. Anyhelp would be apprecaiated.

SAMPLE SHEET HERE

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var toDelete = [];

var re = new RegExp('/exe'); 
  for (var row = 0; row < values.length; row++) { 
  for(var column = 0;column<values[row].length;column++){ 
  if (re.exec(values[row][column])){
  toDelete.push(row); } } }


  for(var deleteRow = toDelete.length-1; deleteRow >= 0;deleteRow--){
    sheet.deleteRow(toDelete[deleteRow]+1);
  }

  SpreadsheetApp.flush();
};

Upvotes: 1

Views: 510

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

You can concatenate them in a regexy way. within delete rows you can add

var pattern = "(" + ss
    .getSheetByName("Sheet1")
    .getDataRange()
    .getValues()
    .map(function(row) {return row[0];})
    .filter(function(row, rowI) {return rowI > 0 && row !== "";})
    .join("|") + ")";

Which gets the data from Sheet1,, extracts the first column as an Array, removes the header and empty rows and concatenates them with |, finally we wrap it in parens.

Then you can var re = new RegExp(pattern);

Upvotes: 1

Related Questions