Google Sheets appendrow() function viewing dropdowns as cell content even though they are empty

Question

I'm trying to use the script function appendrow() to move rows to sheet that has drop-down and checkbox data validation (data validation is present on the source sheet too and mirrors the target sheet's data validation). The problem I'm having is that rows are being appended all the way down at row 1000 on the target sheet. When I remove the drop-downs, it appends the rows correctly. I need the data validation to be present though, in case changes need to be made to those cells. The data validation is applied to several columns (e.g. F2:F).

I need to know how to insert the row after the header because the dropdowns and checkboxes are apparently viewed as content according to the appendrow() function.

Current script:

function onEdit(e) {

var ss = e.source,
sheet = ss.getActiveSheet(),
    range = e.range,
    targetSheet,
    columnNumberToWatch = 14;
if (sheet.getName() === "Pending Bookings" && e.value === "CONFIRMED" && 
e.range.columnStart === columnNumberToWatch) {
    targetSheet = "Confirmed Bookings"
} else if (sheet.getName() === "Confirmed Bookings" && e.value === "" && 
e.range.columnStart === columnNumberToWatch) {
    targetSheet = "Pending Bookings"
}
ss.getSheetByName(targetSheet)
.appendRow(sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastColumn())
.getValues()[0])
sheet.deleteRow(e.range.rowStart);
}

Answer 1

Appending Rows with Validation Columns

function AppendingARow() {
  var sh=SpreadsheetApp.getActiveSheet();
  var vA=sh.getRange(2,1,sh.getMaxRow()-1,1).getValues();//save the validation
  sh.getRange(2,1,sh.getMaxRows()-1,1).clear();//clear the validation
  var vB=sh.getRange(2,3,sh.getMaxRows()-1,1).getValues();
  sh.getRange(2,3,sh.getMaxRows()-1,1).clear();
  sh.appendRow(['','Yes','','Maybe','No Way', 'Come back tommorow and try again']);//append the row
  sh.getRange(2,1,vA.length,1).setValues(vA);//restore the validation
  sh.getRange(2,3,vB.length,1).setValues(vB);
}

Here's what my spreadsheet looks like:

I put it into a function:

function AppendRow(cA,rowA,tsh) {
  var cObj={cA:[]};
  cObj.cA=cA;
  cObj.cA.forEach(function(c){
    cObj[c]=tsh.getRange(2,c,tsh.getMaxRows(),1).getValues();//save validations
    tsh.getRange(2,c,tsh.getMaxRows(),1).clear();//clear validation
  });
  cObj.lr=tsh.getLastRow();//get and save last row
  cObj.cA.forEach(function(c){
    tsh.getRange(2,c,cObj[c].length,1).setValues(cObj[c]);//return validations        
  });
  tsh.getRange(cObj.lr+1,1,1,rowA.length).setValues([rowA]);//Insert new row. This approach preserves the values in the inserted row
}

cA is the array of columns of validation data

rowA is the row that your trying to append. It's a one dimensional array.

tsh is the targetsheet

I tested this on my simple example but lets see what happens on yours.

Answer 2

You can do this by moving the data from one sheet to the other with a smarter Range selection.

With a sheet like this:

You can use a script:

function appendWithValidation() {
  var source = SpreadsheetApp.getActive().getSheetByName("Source");
  var destination = SpreadsheetApp.getActive().getSheetByName("Destination");

  var newData = source.getDataRange().getValues();

  while (destination.getDataRange().getValues().length>destination.getMaxRows()) {
    destination.deleteRow(destination.getMaxRows());
  }

  for (var i=0; i<newData.length; i++) {
    destination.appendRow(newData[i]);
  }

  source.getDataRange().clear();
}

That will create the following result: