google-apps-script

Deleting Rows with Event Triggers for Data That Is Automatically added

I have a working script with onEdit that assigns a version number in Col B Sheet4 (using an array formula) to every job number that comes into Col A of Sheet 4. If I manually edit the last row of Col B, any job numbers with a version >1 get deleted from Sheets 1, 2 & 3.

However, the job numbers are coming in automatically from outside software so the script is not triggering. I've read in other post where this is a known problem, but have seen several work-a-rounds such as detecting when the number of rows change, but don't have the experience to be able to add that to my current script. What's further confusing me is I'm seeing a successful trigger in the executions log. (And this is with onEdit and On Change(Insert_Row)). Does this mean the trigger is working, but my script is not seeing it?

If such a work-a-round does exist, could someone show me how to combine that with my current script?

'''

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const rangeA= sheet.getRange(sheet.getLastRow(),1).getValue();
  const rangeB= sheet.getRange(sheet.getLastRow(),2).getValue();

//Logger.log("rangeB: "+rangeB);
//Logger.log("rangeA:"+rangeA);
if (sheet.getName() === "Sheet4") {
if(rangeB =='string'); {

    const jobNumber = rangeA;
    const jobNumberVer = rangeB

   // Logger.log("jobNumber:"+jobNumber);
    if (jobNumberVer) {
      
     const verNum = +jobNumberVer.split("^")[1];
    // Logger.log("verNum:"+verNum);
      if(verNum >1) 
    
      removeMatchingRows(jobNumber);
      }
    }
  }
} 
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);
    });
  });
}

'''

Solution

  • To ensure your script detects and responds to changes when rows are added or modified by external software, you can use an installable trigger with the onChange event. This detects changes in the sheet structure, including row additions, and can process the data accordingly

    Here’s how you can modify your script:

    function onChange(e) {
  const sheet = e.source.getActiveSheet();
  const rangeA = sheet.getRange(sheet.getLastRow(), 1).getValue();
  const rangeB = sheet.getRange(sheet.getLastRow(), 2).getValue();

  // Only act on "Sheet4"
  if (sheet.getName() === "Sheet4") {
    if (rangeB && typeof rangeB === "string") {
      const jobNumber = rangeA;
      const jobNumberVer = rangeB;

      const verNum = +jobNumberVer.split("^")[1];
      if (verNum > 1) {
        removeMatchingRows(jobNumber);
      }
    }
  }
}

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

    To setup the trigger: