Search for Values with Wildcard Character-CodePudding

Below is a script that I am running that searches for items stored in an annex location based on the value in column 2 on sheet LX03SB. We currently have 10 annex locations so I added a search for each one, but as we expand this is becoming more and more tedious. Is there a way to modify the valueToWatch function to find any value that starts with Annex instead of needed Annex01, Annex02, etc.

function MoveAllRightRows1(){

  var columnNumberToWatch = 2; 
  var valueToWatch = "ANNEX01"; 
  var sheetNameToMoveTheRowTo = "OFFSITE";      

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("LX03SBD");
  var row = 1;
  var len = sheet.getRange(1 , columnNumberToWatch, sheet.getLastRow() - 1).length;
  var range = sheet.getRange(row,2,sheet.getLastRow(),1).getValues(); 
  var len = range.length;
  var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

  for (row=len; row> 0 ; row--){
  if (range[row-1][0]== valueToWatch) { 
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
      sheet.getRange(row, 1, 1,sheet.getLastColumn()).moveTo(targetRange);
      sheet.deleteRow(row);
     }}}


function MoveAllRightRows2(){

  var columnNumberToWatch = 2; 
  var valueToWatch = "ANNEX02"; 
  var sheetNameToMoveTheRowTo = "OFFSITE";      

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("LX03SBD");
  var row = 1;
  var len = sheet.getRange(1 , columnNumberToWatch, sheet.getLastRow() - 1).length;
  var range = sheet.getRange(row,2,sheet.getLastRow(),1).getValues(); 
  var len = range.length;
  var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

  for (row=len; row> 0 ; row--){
  if (range[row-1][0]== valueToWatch) { 
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
      sheet.getRange(row, 1, 1,sheet.getLastColumn()).moveTo(targetRange);
      sheet.deleteRow(row);
     }}}

CodePudding user response：

I believe your goal is as follows.

You want to reflect the script for searching the values like Annex01, Annex02 and so on from the column "B" of "LX03SBD" sheet in your script.

In this case, how about using TextFinder? When this is reflected to your script it becomes as follows.

Modified script:

function MoveAllRightRows3() {
  var columnNumberToWatch = 2;
  var valueToWatch = "ANNEX";
  var sheetNameToMoveTheRowTo = "OFFSITE";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("LX03SBD");
  var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
  
  // Search the values of `Annex01`, `Annex02` and so on from the column "B" of "LX03SBD" sheet.
  var ranges = sheet.getRange(1, columnNumberToWatch, sheet.getLastRow()).createTextFinder(`^${valueToWatch}`).useRegularExpression(true).findAll();

  ranges.reverse().forEach(r => {
    var targetRange = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    var row = r.getRow();
    sheet.getRange(row, 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(row);
  });
}

When this script is run, the values of Annex01, Annex02 and so on are searcged from the column "B" of "LX03SBD" sheet. And, the searched values are used in the loop.

Reference:

createTextFinder(findText) of Class Range