Move Rows with Keyword

moveRowsWithKeywordToNewSheet.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Move Rows with Keyword', 'moveRowsWithKeywordToNewSheet')
    .addToUi();
}

function moveRowsWithKeywordToNewSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  
  // Prompt the user for a keyword
  var keyword = Browser.inputBox('Enter keyword to search for:');
  
  // Get all data in the sheet
  var data = activeSheet.getDataRange().getValues();
  
  // Filter rows that contain the keyword
  var matchingRows = data.filter(function(row) {
    return row.some(function(cell) {
      return cell.toString().toLowerCase().includes(keyword.toLowerCase());
    });
  });
  
  if (matchingRows.length === 0) {
    Browser.msgBox('No matching rows found.');
    return;
  }
  
  // Create a new sheet or increment the sheet number if it already exists
  var newSheetName = getUniqueSheetName(ss, 'MatchingRows');
  var newSheet = ss.insertSheet(newSheetName);
  
  // Iterate through matching rows and move each one to the new sheet
  for (var i = matchingRows.length - 1; i >= 0; i--) {
    var rowIndex = data.indexOf(matchingRows[i]);
    
    if (rowIndex !== -1) {
      // Delete the row from the original sheet
      activeSheet.deleteRow(rowIndex + 1); // Adding 1 because row indices start from 1 in Sheets
      // Append the row to the new sheet
      newSheet.appendRow(matchingRows[i]);
    }
  }
}

// Function to get a unique sheet name by incrementing a number
function getUniqueSheetName(ss, baseName) {
  var sheetName = baseName;
  var sheetNumber = 1;
  
  while (ss.getSheetByName(sheetName) !== null) {
    sheetNumber++;
    sheetName = baseName + '_' + sheetNumber;
  }
  
  return sheetName;
}