Delete Matching Rows from 3 Sheets After Detecting Duplicate Job Numbers in the 4th

I have a multi-sheet spreadsheet that contains job numbers in column A of sheets 1, 2, 3, & 4 When a duplicate number comes into Sheet4 Col A, a formula copies the number to Col B adding a ^ {vers number}. So MO123456 in Col A becomes MO123456^1 in Col B (which increments for each additional duplicate of that number).

For each number that is flagged as a duplicate, (in Sheet4 Col B) I want the script to delete the rows of the matching numbers in Sheets 1, 2, & 3.

I found a script here that I was able to modify to look only at the last row of Col B. But instead of comparing this cell against the other three sheets with indexOf, I need it to detect numbers with the ^ but return the job number only for the CheckForDup function. If an original number comes in, then it will copy to Col B with no ^ and the script should ignore it.

Finally I need to convert the script to onEdit so when a new number comes into the last row of Col B, the script automatically triggers.

`

function removeDupsInOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1").getDataRange().getValues();  
  var s2 = ss.getSheetByName("Sheet2").getDataRange().getValues(); 
  var s3 = ss.getSheetByName("Sheet3").getDataRange().getValues(); 
  var s4s = ss.getSheetByName("Sheet4");
  var s4 = s4s.getRange(s4s.getLastRow(),2,2,s4s.getLastColumn()).getValues()

  // iterate s4 and check in s1 & s2 & s3 if duplicate values exist
  var nS1 = [];
  var nS2 = [];
  var nS3 = [];
  var s4Col2 = [];// data in column2 of sheet4
  for(var n in s4){
    s4Col2.push(s4[n][0]);
  }
  for(var n in s1){ // iterate sheet1 and test col 1 vs col 2 lastRow in sheet4
    var noDup1 = checkForDup(s1[n],s4Col2)
    if(noDup1){nS1.push(noDup1)};// if not present in sheet4 then keep
  } 
  for(var n in s2){  // iterate sheet2 and test col 1 vs col 2 lastRow in sheet4
    var noDup2 = checkForDup(s2[n],s4Col2)
    if(noDup2){nS2.push(noDup2)};// if not present in sheet4 then keep
  }
  for(var n in s3){  // iterate sheet2 and test col 1 vs col 2 lastRow in sheet4
    var noDup3 = checkForDup(s3[n],s4Col2)
    if(noDup3){nS3.push(noDup3)};// if not present in sheet4 then keep
  }
 // Logger.log(nS1);// view result
 // Logger.log(nS2);
  ss.getSheetByName("Sheet1").getDataRange().clear();// clear and update sheets
  ss.getSheetByName("Sheet2").getDataRange().clear();
  ss.getSheetByName("Sheet3").getDataRange().clear();
  ss.getSheetByName("Sheet1").getRange(2,1,nS1.length,nS1[0].length).setValues(nS1);
  ss.getSheetByName("Sheet2").getRange(2,1,nS2.length,nS2[0].length).setValues(nS2);
  ss.getSheetByName("Sheet3").getRange(2,1,nS3.length,nS3[0].length).setValues(nS3);
}

function checkForDup(item,s){
//  Logger.log(s+' = '+item[0]+'  ?')
    if(s.indexOf(item[0])>-1){
      return null;
    }
  return item;
}

//string.slice(0,string.indexOf('^'));
//REGEXEXTRACT("^[^--]+")

Automatically Delete Matching Rows in 3 Sheets Upon Detecting Duplicate Job Numbers in Sheet 4

When a job number is entered in Sheet4, Column A, the script automatically generates a versioned job number in Column B. If the Job number appears again, its version in Column B will increment accordingly.

The script uses the onEdit trigger, so whenever a change is made in Sheet4, Column A, it automatically updates Column B with the correct version and deletes the matching rows in Sheet1, Sheet2, and Sheet3.

You can try this code:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;


  if (sheet.getName() === "Sheet4" && range.getColumn() === 1) {
    const jobNumber = range.getValue();

    if (jobNumber) {

      const versionedJobNumber = generateJobNumber(jobNumber);
      sheet.getRange(range.getRow(), 2).setValue(versionedJobNumber);

      removeMatchingRows(jobNumber);
    }
  }
}

function generateJobNumber(jobNumber) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4");
  const data = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  let version = 0;

  data.forEach((row) => {
    const existingJobNumber = row[0];
    if (existingJobNumber && existingJobNumber.split("^")[0] === jobNumber) {
      version++;
    }
  });

  return `${jobNumber}^${version}`;
}

function removeMatchingRows(jobNumber) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetsToCheck = ["Sheet1", "Sheet2", "Sheet3"];

  sheetsToCheck.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;

    const data = sheet.getDataRange().getValues();
    const rowsToDelete = [];


    data.forEach((row, index) => {
      const baseJobNumber = row[0].split("^")[0];
      if (baseJobNumber === jobNumber) {
        rowsToDelete.push(index + 1);
      }
    });

    rowsToDelete.reverse().forEach(rowIndex => {
      sheet.deleteRow(rowIndex);
    });
  });
}

Sample Sheet:(Before)

Sample Sheet:(After)

Reference:

onEdit(e)

includes()

forEach()

if/else