Moving row to new sheet from drop down in Google Sheets

Question

I'm trying to move new hires from a list of open positions in sheet "Open Roles" to "Hired" based on selecting the option "Hired" from a drop down list in column H/8 ("Stage"). I created asample doc here.

I'm new at this and put the below into script editor (that I got from another thread) but when I hit run it returns "TypeError: Cannot read property 'source' of undefined" and "Open Roles" appears red as if it doesn't recognize it.

Can anyone help identify what I'm doing wrong or missing please? Thanks so much in advance!

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (src.getName() != "Open Roles" || r.columnStart != 8 || r.rowStart == 1) return;
  const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hired");
  src.getRange(r.rowStart,1,1,8).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8));
  src.deleteRow(r.rowStart);
}

Answer 1

However, remember the debugger is your friend. I suggest you set a variable to src.getName(), set a breakpoint to the following line and inspect the value.

var nme = src.getName();

If it is not the name of the tab then you know that is the issue.

Strange though, I made a copy of your sheet and it seems to move rows to the Hired tab. It moves any one that is changed though, not just of it is marked Hired.

I added an check to see if the value is "Hired"

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (src.getName() !== "Open Roles" || r.columnStart !== 8 || r.rowStart == 1) return;
  const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hired");
  if (src.getRange(r.rowStart,8).getValue() == "Hired") {
    src.getRange(r.rowStart,1,1,8).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8));
    src.deleteRow(r.rowStart);
  }
}

I reworked the code a bit, not sure if it will solve the undefined error but it is cleaner, I prefer one return whenever possible.

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  var sheetName = src.getName();
  if (src.getName() == "Open Roles" && r.columnStart == 8 && r.rowStart !== 1) {
    const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hired");
    if (src.getRange(r.rowStart,8).getValue() == "Hired") {
      src.getRange(r.rowStart,1,1,8).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8));
      src.deleteRow(r.rowStart);
    }
  }
}

This script does not rely on the object "e" passed to onEdit()

function onEdit(e) {
  //const src = e.source.getActiveSheet();
  const src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open Roles");
  Logger.log(src);
  var activeRange = src.getActiveRange();
  const currRow = activeRange.getRowIndex();
  const currCol = activeRange.getColumn();
  Logger.log(currRow);
  Logger.log(currCol);
  var sheetName = src.getName();
  if (src.getName() == "Open Roles" && currCol == 8 && currRow !== 1) {
    const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hired");
    if (src.getRange(currRow,8).getValue() == "Hired") {
      src.getRange(currRow,1,1,8).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8));
      src.deleteRow(currRow);
    }
  }
}

Answer 2

Findings:

The reason why you're getting the TypeError: Cannot read property 'source' of undefined is because you can't run the onEdit(e) function directly from the Apps Script editor as it needs to pass data for the parameter e which will only happen when you perform an edit action on your sheet. You can view this explanation for more details.

Recommendation:

You can try & run this sample script instead without the parameter e:

function onEdit() {
  var sheet = SpreadsheetApp.getActive();
  var selectedCell = sheet.getActiveSheet().getCurrentCell().getA1Notation(); //Get selected cell location on Column H (in A1 notation)
  var value = sheet.getActiveSheet().getRange(selectedCell).getValue(); //Get the selected value from dropdown
  const dest = sheet.getSheetByName("Hired"); //The destination sheet

  if(value.toString().includes("Hired")){ //Check if the selected value from the dropdown was "Hired"
    var selectedRange = selectedCell.split(""); //Gets the range of the selected cell 
    Logger.log("\"Hired\" found on range "+ "A"+selectedRange[1]+":"+selectedCell); //Logs the result for review. Range starts in column A to H based on selected row number
    sheet.getActiveSheet().getRange("A"+selectedRange[1]+":"+selectedCell).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8));
    SpreadsheetApp.getActiveSpreadsheet().toast("Done moving data from "+"A"+selectedRange[1]+":"+selectedCell+" to \"Hired\" sheet");
    sheet.getActiveSheet().deleteRow(selectedRange[1]); //Delete the row of the selected cell from the "Open Roles" sheet
  }else{
    //Do nothing if the selected dropdown value is not "Hired"
  }

}

RESULT:

Sample "Open Roles" sheet:

When H2 was set to "Hired," you'll see a toast message on "Open Roles" sheet:

Sample "Hired" sheet:

Here's the Execution Logs: