Using two onEdit() functions on the same worksheet (Google Sheets)

Question

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);
  }
}

You can only have one `onEdit()` function. What you need to do is evaluate the event source in a similar way you're doing now and then run either one of two functions you have there. — Dmitry Kostyuk, Aug 23 '21 at 13:03
@DmitryKostyuk thanks for that input. If I'm understanding you correctly, you're saying that I should combine the 2 functions as an "OR" scenario? Apologies if I'm misunderstanding. — Rookie_In_Training, Aug 23 '21 at 13:13
Exactly. If condition is a, run onedit1, else if it's b, then run onedit2 — Dmitry Kostyuk, Aug 23 '21 at 15:42

score 3 · Answer 1 · answered Aug 23 '21 at 14:50

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);    
    }
  }
}

Using two onEdit() functions on the same worksheet (Google Sheets)

1 Answers1