Adjusting A Script That Deletes Duplicates On Other Sheets

I was given this script that deletes all duplicates from Sheets 1, 2 & 3 as they come into Sheet4. I need to adjust it so it does not delete duplicates if, when the Job Number comes into ColA Sheet4, it reassigns it from MO123456 to MO123456^1 in Col B. Anything over or equal to ^2 should be treated a a duplicate that needs deleted from Sheets 1, 2 & 3.

In other words when it assigns it ^1 that is actually an original job number and any job numbers >= ^2 is a job number that has been submitted more then once.

In lines 46-52, I tried grabbing the incremental number from the second field of the array defined by split (^) and then adding a test to see if the incremental number was greater then 1. However this did not work. What changes do I need to make?

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];
      const baseJobNumberVer = row[0].split("^")[1];
      if (baseJobNumber === jobNumber && baseJobNumberVer > 1) {
        rowsToDelete.push(index + 1);
      }
    });

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

I finally figured it out. To review, I wanted each Job number that comes into Sheet 4 ColA to be assigned a version number in Sheet4 ColB. Ver 1 would represent original numbers while ver >1 represented numbers that were duplicates of numbers further up the column. If ver >1, then delete matching job numbers from Sheets 1,2,& 3.

I extracted the version number and added a nested if statement to test the version number before it executed the function removeMatchingRows(jobNumber);

Here the working 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);

      const verNum = +versionedJobNumber.split("^")[1]
      if(verNum >1) 

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

'''