Automatic row transfer in Google Sheets using trigger function

Other Questions Google Sheets
1

I’m trying to set up an automatic system in Google Sheets that moves entire rows when a specific cell value changes. Previously I used Excel macros with VBA, but now I need to work with Google Apps Script and JavaScript which is completely new to me.

I found some sample code online and modified it for my needs, but I keep getting an error saying it can’t read the “source” property on line 6. The script should monitor column E for the word “Complete” and then transfer the whole row to another sheet.

Any help would be great since I’m still learning JavaScript!

function onEdit(e) {
  // main sheet is called "Task List"
  // destination sheet is "Completed Tasks"
  // checking column 5 (E) for "Complete"
  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = e.source.getActiveSheet();
  var editedRange = e.source.getActiveRange();
  
  if(activeSheet.getName() == "Task List" && editedRange.getColumn() == 5 && editedRange.getValue() == "Complete") {
    var currentRow = editedRange.getRow();
    var totalColumns = activeSheet.getLastColumn();
    var destinationSheet = workbook.getSheetByName("Completed Tasks");
    var newLocation = destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1);
    activeSheet.getRange(currentRow, 1, 1, totalColumns).moveTo(newLocation);
    activeSheet.deleteRow(currentRow);
  }
}
2

The problem is how you’re accessing the event object properties in Google Apps Script. Don’t use e.source.getActiveSheet() and e.source.getActiveRange() - work directly with e.range instead. Replace those lines with var activeSheet = e.range.getSheet(); and var editedRange = e.range;. This works better because the event object structure changes depending on how the trigger fires. I ran into the same frustration coming from VBA - event handling works completely differently here. Also heads up: onEdit triggers only fire on manual edits, not programmatic changes. So if you’re testing by having another script modify cells, it won’t trigger the function.

3

yeah, that error means the event object isn’t getting passed right. add if (!e) return; at the top to check if the event exists first. also, swap e.source.getActiveSheet() for e.range.getSheet() - way cleaner. if you copy/pasted the code, the trigger might not fire properly. delete and recreate the function.

4

Had the same problem switching from Excel VBA to Google Apps Script. Your error’s probably happening because the event object works differently than you’d expect. Don’t use e.source.getActiveSheet() and e.source.getActiveRange() - use e.range for the edited range and e.range.getSheet() for the sheet instead. Google Sheets triggers have a range property that gives you the edited range directly, then you can grab the sheet from there. Also check that your trigger’s installed properly - onEdit won’t get the event object if the trigger’s messed up. Throw in some console.log statements to see what properties your event object actually has.