Issue with multiple conditions in script editor

Question

I've created a script editor for a google sheet that has multiple tabs. One if statement I can't seem to get working is - If sheet "Employee Evolution" column 8 EQUALS "Disqualified" AND column 13 is NOT EQUAL to "NO DATA", move the row to sheet "Disqualified" I've tried so many different ways to rearrange and can't get it to work.

 **function onEdit(event) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = event.source.getActiveSheet();
      var r = event.source.getActiveRange();
      if(s.getName() == "Employee Evolution" && r.getColumn() == 8 && r.getValue() == "Disqualified" && r.offset.getValue(0,5) != "NO DATA") {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Disqualified");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1,numColumns).moveTo(target);
    s.deleteRow(row);** 

I have no coding experience, so I'm having a difficult time understanding javascript documents that explain this stuff. Please help!

Below is the link to my spreadsheet https://docs.google.com/spreadsheets/d/1vp46hMbmB5968cRW2BGhS66qqNhl91Llk8xeknlRuQc/edit#gid=0

Right now I only have the first if statement and else if statement set up with multiple conditions, but that is not working. When I populate the 8th column (H) with Disqualified and populate the 13th column (M) with anything at all, nothing happens. And, if I populate column H with Qualified and populate column M with Paid Search, nothing happens.

Basically I want the row to move to either the PPC tab or the Disqualified tab. However, I don't want the row to move until both columns H and M are populated with specific text. If column H says "Qualified" AND column M says "Paid Search" the row should move to the PPC tab. If column H says "Disqualified" AND column M says anything other than NO DATA (even Paid Search), the row should move to the Disqualified tab.

The problem I can't get past is that I need to have each if statement look at both columns before executing true.

I hope this makes sense and thank you for your help.

Answer 1

It's not possible to define if that if should be working or not based just on the code as we don't have that spreadsheet you're using.

But I do notice some things that maybe are wrong on yout assumptiong, for this break that if into :

• s.getName() == "Employee Evolution" => Checks if the current sheet that you have active is called "Employee Evolution"
• r.getColumn() == 8 => checks if the current column that you have active is column number 8 (column h)
• r.getValue() == "Disqualified" => checks if the current cell that you have active is equal to Disqualified (must matcha case as well)
• r.offset.getValue(0,5) != "NO DATA") => Checks if column offset by 5 (will be equal column 13 indeed) is different then "NO DATA" (also must match case)

And of course as this function is onEdit, so this code will only run when you change something on the spreadsheet.

So I suppose by reading that code is that whenever someone changes column 8 to "Disqualified" (must match the capital letters as well) and all the other criterias match, it should be moved to "Disqualified" sheet. Pay attention to all the case sensitive scenarios you have.

I think overall the code seems fine. Share the spreadsheet so we can check what might be going wrong.

PS: by something being active I mean that your cursor is clicked/selected that thing