CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
The_Taylor13
The_Taylor13

Reputation: 3

Google Sheets Move Row Multiple Criteria

I currently have a working function that is taking a row from one sheet (Active Orders) and moving it to another (Needs Entered) when a column is marked as TRUE. Here's that code:

function moverow(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();  
  if(s.getName() == "Active Orders" && r.getColumn() == 23 && r.getValue() == 1) 
   {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Needs Entered");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Right now if column 23 is marked TRUE, the row moves to 'Needs Entered' sheet. I'd like this to be a little smarter and if column 23 is marked TRUE AND column 19 = "Canceled" then the row is moved to 'Canceled' sheet, else the code continues as normal to move the row to 'Needs Entered' sheet. I'm not sure how to change my current IF statement to check multiple criteria and then how to add the else statement to continue the code as normal if the new IF statement isn't met.

Just not wrapping my head around all this yet, have tried a few things that I thought would work, but just end up breaking stuff.

Upvotes: 0

Views: 180

Answers (1)

Cooper
Cooper

Reputation: 64032

Try this:

function moverow(e) {
  const sh=r.range.getSheet();
  if(sh.getName()=="Active Orders") {
    if(sh.getRange(e.range.rowStart,23).getValue()=='TRUE' && sh.getRange(e.range.rowStart,19)!='Canceled') {
      let tsh=e.source.getSheetByName('NeedsEntered');
      let vs=sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).getValues();
      tsh.getRange(tsh.getLastRow()+1,1,1,vs[0].length).setValues(vs);
      sh.deleteRow(e.range.rowStart);
    }
    if(sh.getRange(e.range.rowStart,23).getValue()=='TRUE' && sh.getRange(e.range.rowStart,19)=='Canceled') {
      let tsh=e.source.getSheetByName('Canceled');
      let vs=sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).getValues();
      tsh.getRange(tsh.getLastRow()+1,1,1,vs[0].length).setValues(vs);
      sh.deleteRow(e.range.rowStart);
    }
  }
}

Upvotes: 1

Related Questions