Using script to add new row below current row when cell in A is changed causes a row lower down to be deleted

Question

Here is the sheet I am working in: Before Script Runs

This is a bid sheet I am making. I repeated categories just to show what happens the farther down an entry is made. When I enter an item in the dropdown lists in column A I need it to add a new line that directly below it, but above the next category, where another item can be entered.

The script that I have entered for it is:

function onEdit() {
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  if (ss.getName() === "Entry") {
  var refRow = ss.getActiveCell().getRow();
  var newRow = refRow + 1;
  var refCol = ss.getDataRange().getLastColumn();
  var RefRow = ss.getRange(refRow,1,refRow,refCol);

  RefRow.copyTo(ss.getRange(newRow, 1));
  ss.getRange("A"+newRow).clear();
}}

This script does add the line directly below, but it also deletes a line farther below.

The distance the deleted line is below the added line increases the farther down the sheet a change is made.

My question is how to make this work where the only thing that happens is that a row is added just below the row where a value is entered in column A and nothing is deleted or changed farther down in the sheet.

Thanks for your help!

Answer 1

The third argument to getRange specifies the number of rows in the range. Since refRow is the row number of the cell, the behavior may be unintended.

For example, how about modifying it like this...

from:

var RefRow = ss.getRange(refRow,1,refRow,refCol);

to:

var RefRow = ss.getRange(refRow, 1, 1, refCol);