Google Sheets - Moving row to another sheet based on specific column

Question

Very new to the Google Sheets script editing, but I'm trying to move rows between sheets. I've done this successfully using getLastRow however now I'm attempting to do this based on a specific column. The reason for this is that the sheet the info is heading to has preset data validation fields and checkboxes, resulting in the row going to row 1000 every time.

I'm looking for when an option is selected on the dropdown menu on the current sheet, it will find the first free row based on a certain column, and replace that row (which is essentially empty except for the preset fields).

Currently I have as follows:

function onEdit(event) {
  // Move rows to different sheets based on action selected

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();

  if(range.getColumn() == 5 && range.getValue() == "Active") {
    var row = range.getRow();
    var numColumns = sheet.getLastColumn();
    var targetSheet = spreadsheet.getSheetByName("Active");
    var target = targetSheet.getRange(targetSheet.getLastRowSpecial(8) + 1, 1);
    sheet.getRange(row, 1, 1, numColumns).moveTo(target);
    sheet.deleteRow(row);

and I found the below and other examples when googling but nothing seems to work:

function getLastRowSpecial(column){
  // Get the last row with data for the whole sheet.
  var numRows = targetSheet.getLastRow();

  // Get all data for the given column
  var data = targetSheet.getRange(1, column, numRows).getValues();

  // Iterate backwards and find first non empty cell
  for(var i = data.length - 1 ; i >= 0 ; i--){
    if (data[i][0] != null && data[i][0] != ""){
      return i + 1;
    }
  }
}

Any help is greatly appreciated!

Welcome. Please follow the tour, read How to Ask and developers.google.com/apps-script/guides/sheets. P.S. getLastRowSpecial isn't a method of Class Sheet — Rubén

Cooper Cooper · Accepted Answer · 2020-04-20T22:56:00

function onEdit(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast(e.range.columnStart, e.value)
  var sh=e.range.getSheet();
  if(e.range.columnStart==8 && e.value=="Active") {
    var tsh=e.source.getSheetByName("Active");
    var target=tsh.getRange(getColumnHeight(8,tsh,e.source) + 1, 1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
    sh.deleteRow(e.range.rowStart);
  }
}

function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var s=0;
  var h=0;
  if(sh.getLastRow()) {
    var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r[0];});
    v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  }
  return (h-s);
}

Google Sheets - Moving row to another sheet based on specific column

1 Answers