I am trying to create a function for Google Sheets but am getting confused with the comparison operators and elseif format.

The function points to a specific cell in my sheet and moves a range between worksheets based on the Status value: Screenshot

I want to do the following:

If Status value = "Pending" - move range to worksheet ORDERS | Active

If Status value = "In Hand" - move range to worksheet ORDERS | Complete

If Status value = "For Sale" - move range to worksheet SALES | Active

If Status value = "Sold" - move range to worksheet SALES | Complete

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.columnStart != 2 || r.rowStart == 1 || e.value == ("In Hand"));
  const dest = e.source.getSheetByName('ORDERS | Complete');
  src.getRange(r.rowStart,1,1,12).moveTo(dest.getRange(dest.getLastRow() 1,1,1,12));
  src.deleteRow(r.rowStart);
}

Thanks

CodePudding user response：

There seem to be a few issues with your (incomplete) code that could be to blame:

1. You probably want to use r.getColumn() and r.getRow() in place of r.columnStart and r.rowStart as discussed in n the comments.
2. The || operator in your if statement is an OR operator, which I don't believe you want. You likely want to be using the && (AND) operator, as this means the body of the if statement will only execute when the starting row/column are correct AND the cell value is correct.
3. It seems like you probably want to use a == operator for r.getColumn() rather than !=, as you want to be checking the value of the cell only if it's the status column (column B, aka column 2).
4. Your if statement doesn't appear to actually be doing anything since it ends with a semicolon. In JavaScript (and Google Apps Script), a semicolon ends the statement, so your if statement is being immediately terminated without the chance to execute any logic. We can fix this by moving the other statements into a group of curly braces:

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.columnStart != 2 && r.rowStart == 1 && e.value == ("In Hand")) {
    const dest = e.source.getSheetByName('ORDERS | Complete');
    src.getRange(r.rowStart,1,1,12).moveTo(dest.getRange(dest.getLastRow() 1,1,1,12));
    src.deleteRow(r.rowStart);
  }
}

5. We could use several if-else statements here to implement logic for each of the possible status values, but that would require a lot of unnecessary code repetition. A switch-case seems like the better way to approach this:

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.getColumn() == 2 && r.getRow() == 1 && r.getWidth() == 1 && r.getHeight() == 1) {
    var sheetName;
    switch (e.value) {
      case "Pending":
        sheetName = "ORDERS | Active";
        break;
      case "In Hand":
        sheetName = "ORDERS | Complete";
        break;
      case "For Sale":
        sheetName = "SALES | Active";
        break;
      case "Sold":
        sheetName = "SALES | Complete";
        break;
    }
    if (sheetName) {
      const dest = e.source.getSheetByName(sheetName);
      src.getRange(r.getRow(),1,1,12).moveTo(dest.getRange(dest.getLastRow() 1,1,1,12));
      src.deleteRow(r.getRow());
    }
  }
}

The above switch-case compares the cell value to the valid statuses and sets the appropriate sheet name if the status is valid. Then, we can manipulate that sheet in any way we want.

As a side note: I added two additional conditions to the if statement to check if the range size is 1x1, as e.value won't be available if the range is bigger than a cell.