2

Problem

  • I have 6 OnEdit Functions, which work as intended individually, but when together they don't work as intended. By this I mean some simply don't trigger.

Properties of the Script

  • They have different names - function onEdit(e) {code}, function onEdit1(e1) {code}, function onEdit2(e2) {code}, function onEdit3(e3) {code}, function onEdit4(e4) {code}, function onEdit5(e5) {code}
  • They are all in the same .gs tab
  • Some of them have the same variables. For example OnEdit has var range = e.range; and OnEdit5 has var range = e5.range;

My Understanding

  • I believe that you can run multiple OnEdit functions within the same .gs tab. Is this correct? Or do I need to somehow create new .gs tabs?
  • I believe that my onEdit functions should be named differently, so they are called correctly. Is this correct, or should I be getting rid of the different functions and putting them into one massive function? (I imagine this would lead to slower execution and more cases of not being able to isolate incorrect code).
  • I believe that the variables that are created within each function are specific to that function. Is this true? Or are they impacting each other?

Why I'm asking this

Iterations of this question seem to have been asked before. But people generally give advice on integrating two functions into one big one, rather than preparing someone to integrate 10-20 different OnEdit functions. Nor do they give a clear indication of best coding practices.

I've spent hours reading through this subject and feel that people new to scripts, like me, would greatly benefit from knowing this.

Thank you in advance for any contributions!

Rubén
  • 34,714
  • 9
  • 70
  • 166
Adam Hurrell
  • 37
  • 7
  • Best practice would depend on what each function does. – TheMaster Mar 02 '20 at 17:38
  • All are If statements. As a quick summary they do the following **e** = .clearContent(), .uncheck(), .getFilter().sort. **e1** = .getFilter().sort. **e2** = .setValue(new Date()). **e3** = .copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false), .deleteRow(range2.getRow(). **e4** = .clearContent(), .getFilter().sort(12, true). **e5** = .clearContent(), .uncheck(), .getFilter().sort(12, true). – Adam Hurrell Mar 02 '20 at 17:41
  • I think it's best to put all of your onEdit functions together into one function and I prefer to use an installable trigger. And I also prefer to do as little as possible in them. It doesn't take much traffic to make them pretty much useless. – Cooper Mar 02 '20 at 18:21

1 Answers1

2

Notes:

  • There can only be one function with a same name. If there are two, the latter will overwrite the former. It's like the former never existed.
  • A function named onEdit is triggered automatically on (You guessed it!)edit
  • There's no simple trigger for other names like onEdit1 or onEdit2....
  • Simple triggers are limited to 30 seconds of execution
  • So, in a single code.gs file or even in a single project, there can only be one function named onEdit and trigger successfully.
  • If you create multiple projects, onEdit will trigger in each project asynchronously. But there are limits to number of projects that can be created and other quotas will apply.
  • Alternatively, you can use installed triggers: which doesn't have limit of 30s. You can also use any name for your function.
  • The best way to optimize functions is to never touch the spreadsheet unless it is absolutely necessary. For example, sorting various values inside the script is better than repeatedly calling .sort on the multiple ranges multiple times. The lesser the interaction between sheets and scripts, the better. A highly optimized script will only require two calls to spreadsheet: one to get the data and the other to set the data.
  • After optimizing the number of calls to sheet, you can optimize the script itself: Control the logic such that only the necessary amount of operations are done for each edit. For example, if the edit is in A1(A1,B1 are checkboxes, if clicked clears A2:A10,B2:B10 respectively), then you should check if A1 is clicked and If clicked, clear the range and exit and not to check for B1 again. Script optimization requires atleast a basic knowledge of JavaScript objects. Nevertheless, this isn't as effective as reducing the number of calls-which is the slowest part of any apps script.

References:

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • 1
    This is exactly the sort of advice I was hoping for. Thank you. I appreciate the extra reading too. – Adam Hurrell Mar 03 '20 at 08:05
  • I've spent a good amount of time re-writing my functions and merging them in line with your recommendations and now I've finished I can say it works together as intended. I have managed to optimise some of the code (It makes fewer calls). But I still need to reduce the calls and read more on installable triggers, in order to implement these. once again, thank you for spending the time. I know that as my ability scales, so will my ability to implement all that you have written. – Adam Hurrell Mar 03 '20 at 15:23
  • @Adam Glad to help – TheMaster Mar 03 '20 at 16:27