CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Alejandra Petro
Alejandra Petro

Reputation: 13

Google Sheets - How can I move a row to another sheet based on multiple criteria?

I am trying to move a row from sheet "Loads" to sheet "Invoiced Loads" based on multiple criteria. The criteria is:

If the load has been Delivered and Invoiced (which is a number greater than 0) move the load/row to sheet "Delivered".

I have seen many people have asked this question and I found a code that works, but only with one criteria. I have tried to modify the code so it checks the two conditions but I can not make it work. I ran out of options. Please if someone can help me I will really appreciate it. I know the solution must be really simple but I am a beginner on this and I am not sure which path to take please!!. The code I got at the moment is the below:

function onEdit(event) {

// assumes source data in sheet named Loads
// target sheet of move to named Invoiced Loads
// test column with "Delivered" is, col 19 or S
// test column with invoice number is, col 27 or AA

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if((s.getName() == "Loads" && r.getColumn() == 19 && r.getValue() == "Delivered") && 
 (s.getName() == "Loads" && r.getColumn() == 27 && r.getValue() > 0)) {

var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Invoiced Loads");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}

Upvotes: 1

Views: 265

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

  • The main issue with the if statement is that r.getColumn() can't be 19 and 27 at the same time but also r.getValue() can't be "Delivered" and >0 at the same time. As a result, the if condition will always evaluate to false.
  • Also you don't need to evaluate twice for the same condition. The if condition in your code evaluates twice the expression s.getName() == "Loads" which is unnecessary. One is enough.
  • This is an onEdit function which means that every time you make a change, event.range.getRow() and event.range.getColumn() will give you the coordinates of the cell that was edited. There is no need of using event.source.getActiveRange();.

Solution:

function onEdit(event) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const row = event.range.getRow();
  const col = event.range.getColumn();
  const as = event.source.getActiveSheet();
  const numColumns = as.getLastColumn();
  const targetSheet = ss.getSheetByName("Invoiced Loads");
  const target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

  if(as.getName() == "Loads" && as.getRange(row,19).getValue() == "Delivered" 
  && as.getRange(row,27).getValue() >0) {

   as.getRange(row, 1, 1, numColumns).moveTo(target);
   as.deleteRow(row);
}
}

Upvotes: 1

Related Questions