google-sheetsgoogle-apps-scriptinsertaddition

Sort a column automatically when an entry is added/deleted and directly create/delete a related row


I'm trying to create a google sheet that sorts new entries automatically in the "Name" column (see image1).

I found the sorting script online, and it works as needed regarding the "Name" sorting. However, if I typed a new name in the "Name" column (for example "N"), it sorts it automatically and creates a new row for "S1", but not for "S2" (See image2); likewise, if I deleted a name (for example "K"), it shifts up the data below it and moves the related cells of "Name" and "S1" to the bottom in a new row, but it doesn't do that for "S2" (See image3). So the data is messed up whenever a new entry or a delete is made.

How to make the code creates a new row for S1 and S2 whenever a name is added, and how to make it automatically deletes the whole row if a name is deleted.

Here is my script:

function autoSort(e){
  const row = e.range.getRow();
  const column = e.range.getColumn()  ;
  const ss = e.source;
  const currentSheet = ss.getActiveSheet();
  const currentSheetName = currentSheet.getSheetName();

  if(!(currentSheetName === "Scores" && column === 2 && row >=2)) return

  const range = currentSheet.getRange(2,2, currentSheet.getLastRow()-1,2);

  range.sort({column: 2, ascending: true});
 }

 function onEdit(e){
  autoSort(e)
 }

[image1]

[image2]

[image3]


Solution

  • Change the last parameter of this line from

    const range = currentSheet.getRange(2,2, currentSheet.getLastRow()-1,2);
    

    to

    const range = currentSheet.getRange(2,2, currentSheet.getLastRow()-1,3);
    

    Reference