0

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]

9
  • both these scripts work fine one its only one at a time, however when doing both, no luck Jul 22, 2019 at 18:12
  • On this site OPs could and it's expected that they edit their questions to make them clearer or to share their progress to find the solution but the revisions should not remove content that already was referred in helpful answers because that invalidates those answers. If adding progress updates makes the question too convoluted for new readers, then it could be better to post a new question.
    – Rubén
    Jul 22, 2019 at 21:39
  • did i do something wrong in the updated code? Jul 22, 2019 at 21:47
  • 1
    @YosiefEllaham Would you please clarify - what, if any, problem do you still have? Is the code and Execution transcript in your question the latest, if not, please update it.
    – Tedinoz
    Jul 23, 2019 at 21:37
  • 1
    Despite all 12 "IF" statements, "targetSheet" is never assigned a value; that's why you get this error Spreadsheet.getSheetByName([undefined]). Time to trouble sheet: insert this line of code - Logger.log("target sheet = "+targetSheet); immediately after each and every targetSheet = statement (that is, insert it into the code 12 times). Run the code and then view the log. It'll tell you the value (if any) of "targetSheet". You can move forward from there.
    – Tedinoz
    Jul 24, 2019 at 11:50

3 Answers 3

2

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.

4
  • I tried that which seemed like it did something! However it still wont run the 2 scripts at the same time - None ran when i did this as opposed to only the last one Jul 22, 2019 at 17:30
  • ill look into that switch function too! I was thinking there was something easier haha Jul 22, 2019 at 17:32
  • What error are you getting when you try to run it now? Is it still the missing semicolon? Jul 22, 2019 at 17:39
  • so im not getting an error now; it saves. However once saved neither of the scripts run - i posted the updated script im using Jul 22, 2019 at 17:54
0

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

5
  • now im getting an error on row 40, im assuming because it shows the e.range that was to be taken out ``` ss.getSheetByName(targetSheet) .appendRow(sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastColumn()) .getValues()[0]) sheet.deleteRow(e.range.rowStart); } ``` what would i edit that too? Jul 22, 2019 at 18:55
  • Execution transcript:: [19-07-22 14:51:30:279 EDT] Execution failed: TypeError: Cannot call method "appendRow" of null. (line 40, file "Code") [0.278 seconds total runtime] Jul 22, 2019 at 18:56
  • @YosiefEllaham I edited my answer.
    – Rubén
    Jul 22, 2019 at 20:42
  • Thank you so much for your help with this ruben! I feel like the code is almost cracked. Ive got another execution error about line 38 which is the apend row line Jul 22, 2019 at 21:26
  • ive edited the post to reflect the code Jul 22, 2019 at 21:26
0

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.

5
  • bookmarked, thanks a lot ruben. so in this case if I wanted to target the whole sheet what would I have to put? Jul 23, 2019 at 0:56
  • I can’t find anything online on how to do this Jul 23, 2019 at 14:50
  • @YosiefEllaham What do you mean by "to do this"? Do you mean debugging code?
    – Rubén
    Jul 23, 2019 at 14:54
  • On how to target the multiple tabs in one sheet, idk what I would need to put beside target sheet = to target the entire doc Jul 23, 2019 at 17:04
  • @YosiefEllaham I think that you should post a new question here or on Stack Overflow starting from scratch. I suggest that in your new question you include minimal and verifiable example and a brief but complete description of what it should do.
    – Rubén
    Jul 23, 2019 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.