Trying to run two scripts on same sheet and getting error

Question

I found a code to run the two different scripts online, however i keep getting an error

Missing ; before statement.

Original

function onEdit(e) {
   oneditfx1(e);
   oneditfx2();
   }

function oneditfx1(e) {
 var ss = e.source,
       sheet = ss.getActiveSheet(),
       range = e.range,
       targetSheet,
       columnNumberToWatch = 8; // column A = 1, B = 2, etc
   if (sheet.getName() === "Call Submitted" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Completed"
   } else if (sheet.getName() === "Call Submitted" && e.value === "Creating Link" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Creating Link"
   } else if (sheet.getName() === "Call Submitted" && e.value === "Unlock Submitted" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Buyout/Unlock"
   } else if (sheet.getName() === "Call Submitted" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Faded"
   } else if (sheet.getName() === "Creating Link" && e.value === "Call Submitted" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Submitted"
   } else if (sheet.getName() === "Creating Link" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Submitted"
   } else if (sheet.getName() === "Creating Link" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Faded"
   } else if (sheet.getName() === "Creating Link" && e.value === "Unlock Submitted" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Buyout/Unlock"
   } else if (sheet.getName() === "Buyout/Unlock" && e.value === "Ported" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Ported"
   } else if (sheet.getName() === "Faded" && e.value === "Call Submitted" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Submitted"
   } else if (sheet.getName() === "Faded" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Submitted"
   } else if (sheet.getName() === "Faded" && e.value === "" && e.range.columnStart === columnNumberToWatch) {
       targetSheet = "Call Submitted"
   }
   ss.getSheetByName(targetSheet)
       .appendRow(sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastColumn())
           .getValues()[0])
   sheet.deleteRow(e.range.rowStart);
}

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

 var columnToSortBy = 8;
 var tableRange = "A3:P"; // What to sort.

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

Execution Error Execution failed: ReferenceError: "e" is not defined.

UPDATED changes eranges and value

function onEdit(e) {
   oneditfx1(e);
   oneditfx2();
   }

   function oneditfx1(e) {
     var ss = SpreadsheetApp.getActiveSpreadsheet(),
         sheet = ss.getActiveSheet(),
         range = range = SpreadsheetApp.getActiveRange(),
         targetSheet,
         columnNumberToWatch = 8; // column A = 1, B = 2, etc
     var value = range.getValue();
     var columnStart = range.getColumn();
     var rowStart = range.getRow();
     if (sheet.getName() === "Call Submitted" && value === "Call Completed" && columnStart === columnNumberToWatch) {
        targetSheet = "Call Completed"
     } else if (sheet.getName() === "Call Submitted" && value === "Creating Link" && columnStart === columnNumberToWatch) {
         targetSheet = "Creating Link"
     } else if (sheet.getName() === "Call Submitted" && value === "Unlock Submitted" && columnStart === columnNumberToWatch) {
         targetSheet = "Buyout/Unlock"
     } else if (sheet.getName() === "Call Submitted" && value === "Faded" && columnStart === columnNumberToWatch) {
         targetSheet = "Faded"
     } else if (sheet.getName() === "Creating Link" && value === "Call Submitted" && columnStart === columnNumberToWatch) {
         targetSheet = "Call Submitted"
     } else if (sheet.getName() === "Creating Link" && value === "Call Completed" && columnStart === columnNumberToWatch) {
         targetSheet = "Call Submitted"
     } else if (sheet.getName() === "Creating Link" && value === "Faded" && columnStart === columnNumberToWatch) {
         targetSheet = "Faded"
     } else if (sheet.getName() === "Creating Link" && value === "Unlock Submitted" && columnStart === columnNumberToWatch) {
         targetSheet = "Buyout/Unlock"
     } else if (sheet.getName() === "Buyout/Unlock" && value === "Ported" && columnStart === columnNumberToWatch) {
         targetSheet = "Ported"
     } else if (sheet.getName() === "Faded" && value === "Call Submitted" && columnStart === columnNumberToWatch) {
         targetSheet = "Call Submitted"
     } else if (sheet.getName() === "Faded" && value === "Call Completed" && columnStart === columnNumberToWatch) {
         targetSheet = "Call Submitted"
     } else if (sheet.getName() === "Faded" && value === "" && columnStart === columnNumberToWatch) {
         targetSheet = "Call Submitted"
     }      
     ss.getSheetByName(targetSheet)
         .appendRow(sheet.getRange(rowStart, 1, 1, sheet.getLastColumn())
             .getValues()[0])
     sheet.deleteRow(rowStart);
}  

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

     var columnToSortBy = 8;
     var tableRange = "A3:P"; // What to sort.
       if(editedCell.getColumn() == columnToSortBy){   
         var range = sheet.getRange(tableRange);
         range.sort( { column : columnToSortBy, ascending: true } );
  }
}

Updated EXECUTION TRANSCRIPT

[19-07-22 16:55:59:075 PDT] SpreadsheetApp.getActiveRange() [0 seconds]
[19-07-22 16:55:59:075 PDT] Range.getRow() [0 seconds]
[19-07-22 16:55:59:075 PDT] Range.getLastRow() [0 seconds]
[19-07-22 16:55:59:075 PDT] Range.getColumn() [0 seconds]
[19-07-22 16:55:59:075 PDT] Range.getLastColumn() [0 seconds]
[19-07-22 16:55:59:075 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-07-22 16:55:59:077 PDT] Starting execution
[19-07-22 16:55:59:081 PDT] Spreadsheet.getActiveSheet() [0 seconds]
[19-07-22 16:55:59:135 PDT] Sheet.getName() [0.053 seconds]
[19-07-22 16:55:59:135 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:136 PDT] Sheet.getName() [0 seconds]
[19-07-22 16:55:59:137 PDT] Spreadsheet.getSheetByName([undefined]) [0 seconds]
[19-07-22 16:55:59:204 PDT] Execution failed: TypeError: Cannot call method "appendRow" of null. (line 42, file "Code") [0.057 seconds total runtime]

Answer 1

You're missing the text "function" in front of oneditfx1 and oneditfx1. Also, your huge if/else if statement could be simplified by using a switch statement and nesting furthers ifs within that.

Answer 2

The error

Execution failed: ReferenceError: "e" is not defined.

typically occurs when a function (typically a simple trigger) that has e as argument is ran from the script editor.

If you are running a simple or installable triggered function from the Script editor, instead of calling it directly you should use a function to declare an object that will emulate the event object that is sent when the trigger is activated.

For a beginner, the simplest solution is to reproduce the conditions that activate the trigger.

An intermediate solution is to remove the use of the e variable when this were possible. I.E.:

replace

 var ss = e.source,

by

 var ss = SpreadsheetApp.getActiveSpreadsheet(),

and

range = e.range,

by

 range = SpreadsheetApp.getActiveRange(),

After columnNumberToWatch = 8; // column A = 1, B = 2, etc and before the first if add

var value = range.getValue();
var columnStart = range.getColumn();
var rowStart = range.getRow();

then replace

e.value

by

value

and replace

e.range.columnStart

and

e.range.rowStart

by

columnStart

and

rowStart

respectively

Note: The above isn't an exhaustive list of things to change on your code. I hope that this answer will help you on your journey to learn how Google Apps Script and JavaScript works. If you are confused, then maybe you should take some steps back and read some stuff like https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/JavaScript_basics

Related

• Running a script from inside a sheet cell

Answer 3

The error

Execution failed: TypeError: Cannot call method "appendRow" of null.

occurs because the value of targetSheet is null. It's null because nothing was assigned when it was declared. It was declared on this code lines:

var ss = SpreadsheetApp.getActiveSpreadsheet(),
         sheet = ss.getActiveSheet(),
         range = range = SpreadsheetApp.getActiveRange(),
         targetSheet,
         columnNumberToWatch = 8; // column A = 1, B = 2, etc

You could replace

       targetSheet,

by

       targetSheet = 'Target Sheet Name',

where Target Sheet Name is the name of the target sheet.

If you didn't do this yet, please read https://developers.google.com/apps-script/guides/support/troubleshooting. There you will find tips and basic tools included on the Script Editor that will help to debug errors like this one.