I see a few syntax errors -
1. Remove the semicolon (;) after each if statement.
2. Add curly braces ({}) to group the code blocks within each if statement.
3. Add an additional if block to check if the edited cell is in the correct sheet and column -
(s.getName() == "Main" && r.getColumn() == 6)
4. Create a new variable 'status' to store the value of the edited cell, which will be used to determine the correct target sheet (Completed, Pending, Main).
5. Change the logic to set the targetSheet based on the status value returned.
6. Add a check to ensure a valid target sheet is found when selected in dropdown.
7. Uncommented the s.deleteRow(row); line as you specified that the line should not be deleted from the Main sheet.
The following should work fine -
function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if (s.getName() == "Main" && r.getColumn() == 6) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet;
var status = r.getValue();
if (status == "Completed") {
targetSheet = ss.getSheetByName("Completed");
} else if (status == "Pending") {
targetSheet = ss.getSheetByName("Pending");
} else if (status == "Hold") {
targetSheet = ss.getSheetByName("Hold");
}
if (targetSheet) {
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
}
}
}
Make sure to save the script and enable the corresponding trigger in the Google Sheets interface.