Run Script to add new row via selected value in drop down and clear selected range in that selected row

Question

I'm using this script I found and it's working fine in inserting a new row, I just add a new line to clear the row clearContent().

I wanted to modify this so that it will run when I selected a value in dropdown (e.g. SUBMITTED) and then clear only selected range in that newly added row (e.g. C to F column but maintain the values copied in A and B column).

Is this possible?

function addRow() {   
    var sh = SpreadsheetApp.getActiveSheet(), lRow = sh.getLastRow();
    var lCol = sh.getLastColumn(), range = sh.getRange(lRow, 1, 1, lCol);   

    sh.insertRowsAfter(lRow, 1);   
    range.copyTo(sh.getRange(lRow + 1, 1, 1, lCol), {contentsOnly : false});   
    sh.getRange(lRow + 1, 1, 1, lCol).clearContent(); 
}

I managed to run it via selected drop down value using this script:

var SHEET = "Tracker";
// The value that will cause the row to hide. 
var VALUE = "SUBMITTED"; 
// The column we will be using 
var COLUMN_NUMBER = 6

function onEdit(e) { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet. 
    if(SHEET == activeSheet.getName()){ 
        var cell = ss.getActiveCell() 
        var cellValue = cell.getValue();

        //Ensure we are looking at the correct column.
        if(cell.getColumn() == COLUMN_NUMBER){
            //If the cell matched the value we require,insert a new row. 
            if(cellValue == VALUE){
                addRow()
            };
        };
    }; 
}

The problem is how can I clear selected range only based on the newly added row, clear C to F columns and retain the values in column A and B?

---

EDIT. I used a new function/script, working with no issues.

function copyRows(numRows = 1) {
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getActiveSheet();
    const lRow = sheet.getLastRow();
    const lCol = sheet.getLastColumn();
    const maxRow = sheet.getMaxRows();
    
    if (lRow === maxRow) 
        sheet.appendRow(['']);
    sheet.getRange(lRow - numRows + 1, 1, numRows, lCol)
        .copyTo(sheet.getRange(lRow + 1, 1));
    sheet.getRange(lRow + 1, 3).clearContent()
    sheet.getRange(lRow + 1, 4).clearContent()
    sheet.getRange(lRow + 1, 5).clearContent()
    sheet.getRange(lRow + 1, 6).clearContent()
}

Answer 1

Full script:

//**GLOBALS**
// Sheet the data is on.
var SHEET = "Tracker";
// The value that will cause the row to hide. 
var VALUE = "SUBMITTED";
// The column we will be using 
var COLUMN_NUMBER = 6

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  
  //Ensure on correct sheet.
  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();
    
    //Ensure we are looking at the correct column.
    if(cell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,insert a new row. 
      if(cellValue == VALUE){
        copyRows()
        activeSheet.hideRow(cell);
      };
    };
  };
}

function copyRows(numRows = 1) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();
  const lRow = sheet.getLastRow();
  const lCol = sheet.getLastColumn();
  const maxRow = sheet.getMaxRows();
  if (lRow === maxRow) sheet.appendRow(['']);
  sheet.getRange(lRow - numRows + 1, 1, numRows, lCol)
    .copyTo(sheet.getRange(lRow + 1, 1));
  sheet.getRange(lRow + 1, 3).clearContent()
  sheet.getRange(lRow + 1, 4).clearContent()
  sheet.getRange(lRow + 1, 5).clearContent()
  sheet.getRange(lRow + 1, 6).clearContent()
}