1

I am having trouble creating a script that can populate multiple sheets from a single main tracker spreadsheet. More specifically, the Main tracker spreadsheet has a column named outcome in which a clients outcome is recorded. I want to be able to simply enter the outcome in the main tracker and have the entry of that value trigger the creation of a new row in a separate sheet that contains only information about that single outcome.

For example, in the master sheet I would entered " Added to waitlist" in the outcome column, which would thereby generate a copy of that record in another sheet with the same file called "Clients added to waitlist". I want to be able to do that for about 5 differentoutcomes.

Currently I have been using the following script for each of my 5 outcomes and 5 tabs to complete this task but I have yet to be successful:

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() == "Main Tracker" && r.getColumn() == 8 && r.getValue() == "Added to Waitlist") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Clients Added to Waitlist");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }
}

Whenever, I have more than one of the above script entries , only the very first one entered into the editor works, while the rest simply don't take. If some could help me reconcile this problem that would be great.

shavonb900
  • 13
  • 2

1 Answers1

0

You can't use more than one onEdit function in your script. Function names must be unique. You can have multiple conditional statements within the function, though. 

if (event.value == "Added to Waitlist") {
    // do something 
} 
if (event.value == "Another thing") {
    // do something else
}

Or better yet, use a switch statement:

switch (event.value) {
  case "Added to Waitlist":
    // do something
    break
  case "Another thing":
    // do something else
    break

Note that event.value gives you the value entered on edit; you don't need to call getValue on the active cell to get it.

Similarly, r = event.source.getActiveRange(); could be simply r = event.range;