Source undefined or event.source.getActiveRange issues

Question

I have a spread sheet that has several pages. (Working on, Missed, Submitted) When working with a row they have checkboxes to mark them to be moved based on the boxes checked. Submitted moved to submitted page and so on. When I check the box I get the error shown below.

TypeError: Cannot read property 'source' of undefined at onEdit(Code:9:17)

First part of the if statement checks the page the event took place, checks that the edit happened in the correct column, if yes move to correct page and delete row from active sheet. Below is the code im using


function onEdit(event) {
  // assumes source data in sheet named Submited Routes source
  // target sheet of move to named Sumbited Routes
  // getColumn with check-boxes is currently set to column 9 or i
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var r = event.source.getActiveRange();


  if(s.getName() == "Working on" && r.getColumn() == 9 && r.getValue() == TRUE) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Submited Routes");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "Submited Routes" && r.getColumn() == 9 && r.getValue() == FALSE) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Working on");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

function onCheck(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var r = SpreadsheetApp.getActiveRange();
  
  if(s.getName()== 'Working on' && r.getColumn() == 11 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Missed");
    var target = targetSheet.getRange(targetSheet.getLastRow() +1,1);
    s.getRange(row, 1,1,numColumns).moveTo(target);
    s.deleteRow(row); 
  }else if(s.getName() == "Missed" && r.getColumn() == 11 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Working on");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

EDIT: The answer below is the prefect fix for my code.

Needs debugging details: https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas — TheMaster, Aug 17 '22 at 20:10

score 1 · Accepted Answer · answered Aug 17 '22 at 20:23

Try it this way:

function onEdit(e) {
  var sh = e.range.getSheet();
  if(sh.getName() == "Working on" && e.range.columnStart == 9 && e.value == "TRUE") {
    var numColumns = sh.getLastColumn();
    var tsh = e.source.getSheetByName("Submited Routes");
    var trg = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart, 1, 1, numColumns).moveTo(trg);
    sh.deleteRow(e.range.rowStart);
  } else if(sh.getName() == "Submited Routes" && e.range.columnStart == 9 && e.value == "FALSE") {
    var numColumns = sh.getLastColumn();
    var tsh = e.source.getSheetByName("Working on");
    var trg = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart, 1, 1, numColumns).moveTo(trg);
    sh.deleteRow(e.range.rowStart);
  }
}

This was perfect, works exactly how I wanted it to. This pointed out what I was doing wrong also. Appreciate the help. — zbayle, Aug 18 '22 at 15:05

Source undefined or event.source.getActiveRange issues

1 Answers1