Porting Over Google Sheets Simple Code to Google Apps Scripts-CodePudding

Very beginner level here, I hope this question makes sense. Essentially I'm trying to create an action on Google Sheets where if someone picks a specific item from a dropdown menu, it will take the entire row and move it to a second sheet/tab, and then delete the row from the first sheet. Second action basically the same but moving it to a 3rd sheet. Basically things where the dropdown selection is "Resolved" will move over to the Resolved tab and things selected as "Non-Issue" will move to the Non-Issue tab. I think I have the code correct however when I went to add it to the Google Sheet I found there is no more script menu item and I have to add it to Google Apps Script...it seems like this means I need to then tweak the code so it knows I'm trying to execute this on a spreadsheet but that's where I'm getting stuck. Here is the code I have:

function onEdit(e) {

var ss = e.source;

var activatedSheetName = ss.getActiveSheet().getName();

var activatedCell = ss.getActiveSelection();

var activatedCellRow = activatedCell.getRow();

var activatedCellColumn = activatedCell.getColumn();

var activatedCellValue = activatedCell.getValue();

var Tracker = ss.getSheetByName("Bug and Issue Tracker"); // source sheet

var Resolved = ss.getSheetByName("Resolved"); // target sheet 1

var NonIssue = ss.getSheetByName("Non-Issue"); // target sheet 2

 

// if the value in column B is "Resolved", move the row to target sheet 1

if (activatedSheetName == Tracker.getName() && activatedCellColumn == 2 && activatedCellValue == "Resolved")

{

  // insert a new row at the third row of target sheet 1

  Resolved.insertRows(3,1);

  // move the entire source row to the third row of target sheet

  var rangeToMove = Tracker.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ Tracker.getMaxColumns());

  rangeToMove.moveTo(Resolved.getRange("A3"));

  // delete row from source sheet

  Tracker.deleteRows(activatedCellRow,1);

}

// if the value in column B is "Non-Issue", move the row to target sheet 2

if (activatedSheetName == Tracker.getName() && activatedCellColumn == 2 && activatedCellValue == "Non-Issue")

{

  // insert a new row at the third row of target sheet 2

  NonIssue.insertRows(3,1);

  // move the entire source row to the third row of target sheet 2

  var rangeToMove = Tracker.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ Tracker.getMaxColumns());

  rangeToMove.moveTo(NonIssue.getRange("A3"));

  // delete row from source sheet

  Tracker.deleteRows(activatedCellRow,1);

}

 

}

When I try to run this in Google Apps Script I get the following error:

Error TypeError: Cannot read property 'source' of undefined onEdit @ Code.gs:3

Any help would be greatly appreciated!

Edit: Here is a link to a copy of the document if helpful

https://docs.google.com/spreadsheets/d/18jF-oWv-sUn4INDT4vurVRYUoqVXy7zafC6rqPhfT4M/edit?usp=sharing

After posting a copy of the spreadsheet to help diagnose the issue I see the code is actually working perfectly now! It looks like I don't actually need to be able to run it from the Google Apps Script window, I just paste it in and save and then when I take the action in the spreadsheet it runs the code above fine. Hope this helps someone else out there!

CodePudding user response：

Try it this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  var Tracker = e.source.getSheetByName("Bug and Issue Tracker");
  const shts = ["Resolved","Non-Issue"];
  if (sh.getName() == "Bug and Issue Tracker" && e.range.columnStart == 2 && ~shts.indexOf(e.value) ) {
    var tsh = e.source.getSheetByName(e.value);
    tsh.insertRows(3);
    var rangeToMove = Tracker.getRange(e.range.rowStart, 1, 1, Tracker.getLastColumn());
    rangeToMove.moveTo(tsh.getRange("A3"));
    Tracker.deleteRow(e.range.rowStart);
  }
}

CodePudding user response：

When trying to add some junk data to a copy of the sheet for posting here, I found the above code actually works fine. So it looks like I don't need to be able to run it from the Google Apps Script window like I assumed. I just had to save the code and then perform whatever action inside the spreadsheet and it executed as is. Solved! Thanks for the help everyone!