Best Practices for Multiple OnEdit Functions

by

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:

More Related Contents:

  1. Google app script timeout ~ 5 minutes?
  2. Google app script timeout ~ 5 minutes?
  3. Faster way to find the first empty row in a Google Sheet column
  4. Performance optimization strategies of last resort [closed]
  5. What is the best way to set a register to zero in x86 assembly: xor, mov or and?
  6. Why are loops always compiled into “do…while” style (tail jump)?
  7. One could use a profiler, but why not just halt the program? [closed]
  8. How do I choose grid and block dimensions for CUDA kernels?
  9. Long processing time likely due to getValue and cell inserts
  10. How are x86 uops scheduled, exactly?
  11. Merging or Combining two onEdit trigger functions
  12. What methods can be used to efficiently extend instruction length on modern x86?
  13. What setup does REP do?
  14. Google Sheets OnEdit() – You do not have permission to call a service
  15. When, if ever, is loop unrolling still useful?
  16. Is ADD 1 really faster than INC ? x86 [duplicate]
  17. Why is a conditional move not vulnerable to Branch Prediction Failure?
  18. Can modern x86 implementations store-forward from more than one prior store?
  19. How to debug Google Apps Script (aka where does Logger.log log to?)
  20. onEdit simple trigger never seems to be triggered
  21. SUMIFS function in Google Spreadsheet
  22. “SpreadsheetApp.getUi() cannot be called from this context”
  23. How to allow onEdit function to affect protected cell in a Google Sheet?
  24. use onedit() trigger on a specific sheet within google scripts for google sheets
  25. Why do these goroutines not scale their performance from more concurrent executions?
  26. Memory Allocation/Deallocation Bottleneck?
  27. We’re sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED
  28. How can the rep stosb instruction execute faster than the equivalent loop?
  29. Failure of calling Google App Script onEdit function many times in a second
  30. e.range.getA1Notation() unable to track changes caused by formula update

Leave a Comment