Trying to combine two onEdit scripts (Google Sheets)

1
votes

Was wondering if anyone can help.

I currently have two scripts that I need to combine (to work on single spreadsheet) the first moves a completed row to new sheet;

function onEdit(event) {
  // assumes source data in sheet named Current
  // target sheet of move to named Completed
  // test column with yes/no is col V
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Current" && r.getColumn() == 24 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Complete");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

and the second auto-sorts rows based on a value;

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 14;
  var tableRange = "A3:X150"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}

In an attempt to combine the two I've come up with this....

function onEdit(event) {
  myFunction1(event);
  myFunction2();
}

function myFunction1(event) {
  // assumes source data in sheet named Current
  // target sheet of move to named Completed
  // test column with yes/no is col V
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Current" && r.getColumn() == 24 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Complete");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
}

function myFunction2() {
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 14;
  var tableRange = "A3:X150"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}
}

The first script works but the second doesn't (won't auto-sort), can anyone tell me where I'm going wrong?

Link to spreadsheet here;

2
google-apps-scriptgoogle-sheetstriggers
Should I assume the extra "}" is not in your actual code? That is to say, that right now, Function2 is inside Function1. – EvSunWoodard

2 Answers

4
votes

Right now, myFunction2() is inside myFunction1(). If you move it to it's own outside function, onEdit() should be able to call it. If you have variables that need to be used in myFunction1() and then called again in myFunction2(), you are right in keeping myFunction2() as a nested function.

You will, however, need to have myFunction1() call myFunction2() at the very end before the last bracket. In this case, you would then only need onEdit() to call myFunction1() and the whole code will run (seeing as onEdit() calls myFunction1(); myFunction1() calls myFunction2()

Therefore, your two options should look like this:

Unnested:

function onEdit(event) {
  myFunction1(event);
  myFunction2();
}

function myFunction1(event) {
  // start of code
  if(condition == met) {
    //operation to follow
  } //closes if statement operations
} //***this is your misplaced bracket***

function myFunction2() {
  //start of code
  if(condition == met){   
    //operation to follow
  }
}

Nested:

function onEdit(event) {
  myFunction1(event);
}

function myFunction1(event) {
  // start of code
  if(condition == met) {
    //operation to follow
  } //closes if statement operations; no close to myFunction1()

  function myFunction2() {
    //start of code
    if(condition == met){   
      //operation to follow
    } //closes if statement operation
  } //closes myFunction2()
  myFunction2(); //calls myFunction2() so that it will run after myFunction1() code
} // closes myFunction1()

In short, you've set and defined everything for myFunction2() but have not officially told the code to perform those operations.

-2
votes

try this

function onEdit(event) {
  myFunction1(event);
  myFunction2(event); // you forgotten to pass the event here
}
.
.
.
function myFunction2(event) {  // you forgotten to pass the event here
.
.
.
}