I'm still a newbie to coding and scripting and will appreciate any help on this. I'm working on a task where I basically need 2 onEdit functions to run on the same Google sheet and move rows to the applicable tab when the status (column field) of a record changes:
Goal:
1st Function: When the status of a query is changed to "Resolved", it moves the entire record to the a tab in the sheet called "Completed"
2nd Function: When the status of a query is changed to "On Hold" or "Awaiting Feedback", it moves the entire record to a separate tab in the sheet called "On Hold / Awaiting Feedback"
Both onEdit() functions work independently, however, when I try to combine the two, only the second onEdit2(event)function works. I tried searching online for a solution but I've had no success in getting both functions to work when the statuses of a record is changed as specified. I'd appreciate some guidance and help with this please
My Code
function onEdit(event) {
// assumes source data in sheet named Needed
// target sheet of move to named Acquired
// test column with yes/no is col 4 or D
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "General Maintenance" && r.getColumn() == 9 && r.getValue() == "Resolved") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Completed");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
function onEdit2(event) {
// assumes source data in sheet named Needed
// target sheet of move to named Acquired
// test column with yes/no is col 4 or D
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "General Maintenance" && r.getColumn() == 9 && r.getValue() == "On Hold" || s.getName() == "General Maintenance" && r.getColumn() == 9 && r.getValue() == "Waiting On Feedback" ) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("On Hold / Awaiting Feedback");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
Two ways you could do this - if you really want two separate functions, you can just create one onEdit function, and call each of them separately.
However, a lot of your code within the two functions is the same. There is a principle of coding called DRY - it stands for Don't Repeat Yourself. No need to have the same code in two separate places, when you can have it in just one. You can reduce the duplicate coding by combining your code into an nested if statement, using the parts of your IF statement that were the same - like this:
function onEdit(event) {
// assumes source data in sheet named Needed
// target sheet of move to named Acquired
// test column with yes/no is col 4 or D
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "General Maintenance" && r.getColumn() == 9) {
var keepProcessing = false;
var targetSheet;
if (r.getValue() == "Resolved") {
keepProcessing = true;
targetSheet = ss.getSheetByName("Completed");
} else if ((r.getValue() == "On Hold") || (r.getValue() == "Waiting On Feedback")) {
keepProcessing = true;
targetSheet = ss.getSheetByName("On Hold / Awaiting Feedback");
}
// If 'Resolved', 'On Hold', or 'Waiting on Feedback', then keepProcessing was set to true, so can process the items
if (keepProcessing) {
// This was the code that was the same in both functions - The only difference now is that targetSheet is set above in the IF statement.
// So this way, it is only typed out once instead of twice.
var row = r.getRow();
var numColumns = s.getLastColumn();
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
}