Move excel row based on cell content-CodePudding

I have an excel sheet and I want to move the entire row to another sheet and remove it from the original one based on the cell content Here is my code:

function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s =event.source.getActiveSheet();
var r =event.source.getActiveRange();
if(s.getName()=="Main" && r.getColumn ()== 7&& r.getValue () == "Completed"){
var row =r.getRow();
var numColumns= s.getLastcolumn();
var targetsheet= ss.getSheetByName("Completed");
var target=targetSheet.getRange(targetSheet.getLastRow()  1, 1);
s.getRange( row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
if(s.getName () == "Main" && r.getColumn () == 7 && r.getValue () == "Pending"){
var row =r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Pending");
var target = targetSheet.getRange( targetSheet.getlastRow ()  1, 1);
s.getRange( row, 1, 1, numColumns).moveTo(target);
s.deleteRow( row);
}
if(s.getName ()=="Main" && r.getColumn ()==7 && r.getValue () == "Hold") {
var row= r.getRow( );
var numcolumns =s.getLastColumn()
var targetSheet=ss.getSheetByName("Hold" );
var target = targetSheet.getRange(targetSheet.getLastRow()  1, 1);
s.getRange(row ,1,1,numColumns).moveTo(target);
s.deleteRow(row);
}
}

Iam trying to edit the onEidit function However this code is not working for me here is the "Main" sheet

CodePudding user response：

Description

I simplified your script to suit my tastes. This works for me. You have to be careful to make sure you type the correct sheet name in column 7. I would have used a Data Validation drop down list to prevent typos.

Script

function onEdit(event) {
  try {
    let sheets = ["Completed","Pending","Hold"];
    let spread = event.source;
    let source = event.range.getSheet();
    if( source.getName() === "Main" ) {
      if( event.range.getColumn() === 7 ) {
        if( sheets.indexOf(event.value) < 0) return;
        let row = event.range.getRow();
        let numColumns = source.getLastColumn();
        let target = spread.getSheetByName(event.value);
        target = target.getRange(target.getLastRow() 1,1);
        source.getRange(row,1,1,numColumns).moveTo(target);
        source.deleteRow(row)
      }
    }
  }
  catch(err) {
    console.log(err);
  }
}

Reference

Simple trigger event object