Goggle Sheets - Add Row and sort

Question

Total beginner here so please dumb down your answers lol - How do I add a blank row to a specific row in google sheets that is being sorted without the row moving before I can edit it? I'm happy to change my system as needed.

Basically, I'm building an internal workflow program and have an auto-sort script for a list of jobs, when the jobs are completed the staff change row 1 from "A" to "Z" which pushes that row/job to the bottom of the sheet. However, the problem we have is when we add new work by inserting a row and start typing....the row moves before we can put data into it - it's a pain - what ideas does everyone have where I can improve this?

Thoughts / Ideas?

This is the sort script I have at the moment.

function onEdit(event){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Large")
  var editedCell = sheet.getActiveCell();

 var columnToSortBy = 1;
 var tableRange = "A9:M100";

 if(editedCell.getColumn() == columnToSortBy){   
   var range = sheet.getRange(tableRange);
   range.sort( { column : columnToSortBy } );
 }
}

Answer 1

I could find two possible solutions:

1- Use the cell's oldValue, it's value is undefined whenever you add a value to the cell for the first time. Using this solution will sort the table if you change the value a second time:

function onEdit(event){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Large")
  var editedCell = sheet.getActiveCell();
 
 var columnToSortBy = 1;
 var tableRange = "A9:M100";
 if(editedCell.getColumn() == columnToSortBy && event.oldValue != undefined){   
   var range = sheet.getRange(tableRange);
   range.sort( { column : columnToSortBy } );
 }
}

2- Sort only if the the cell is changed to Z, update the condition to:

if(editedCell.getColumn() == columnToSortBy && event.oldValue === "Z"){ 

Reference:

• Event Objects