1

using google apps script, I have written a big code that take high execution time whenever you increase the amount of sheets in the spreadsheet, i want to overcome the problem by dividing the custom function into 6 custom functions, each will run quickly on its own, but in order to do that, I will assign an onOpen() trigger through a menu on the UI that this specific user can only click and execute its related function. my question is if I have 6 users per 6 functions, how many times a single user can open a sheet and use that menu daily? i.e., user1 will have to use 5 spreadsheets daily, in each spreadsheet he will access 30-50 sheets.. that's a total of 250 sheets (max). by doing this, will he go over the triggers daily limit? this will surely make my code run smoothly, but will consume many simple triggers per user.

Samy
  • 63
  • 7
  • 1
    Please read https://developers.google.com/apps-script/quotas – Rubén Oct 02 '19 at 22:46
  • 1
    @Rubén Is there an unasked question here... the [trigger quota](https://developers.google.com/apps-script/guides/services/quotas) is 20/user/script. So, a spreadsheet with an onOpen script can't be opened by the _same_ user more than 20 times a day. But if the onOpen script merely displays a menu, and subject to execution time limits, can that user open the spreadsheet once and then execute any menu option as many times as they might wish without affecting the quota limit on triggers. That is, does the onOpen trigger run once, even though it creates a menu that can be used many times? – Tedinoz Oct 03 '19 at 03:50

1 Answers1

2

Disclaimer: Not tested

Quotas in questionref:

  • Triggers: 20 per user per script
  • Total trigger run time: 90min/day(upto 6hr/day)

Inferences/Assumptions:

  • The Triggers refer to the number of triggers created and not the number of times each trigger is run. i.e., A user can open a spreadsheet as many times as he possibly can and still have the onOpen() trigger(1 trigger) execute, provided the user doesn't cross other quotas.

  • Simple onOpen() has a limit of 30 seconds. At a max runtime of 90 minutes/day and assuming a simple trigger runs for 30 seconds and simple triggers count towards 90minute quota, Users can open a spreadsheet upto 180 times and can still expect the onOpen() to run.

  • Manual menu clicks don't count towards total trigger runtime. So, I don't think it counts towards triggers run time or the number of triggers created(unless the script programmatically creates a trigger)

Workarounds:

Highly optimized scripts have, rarely a reason, to split up a script due to execution time. Make sure you've followed all the best practices. Related Questions:

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thank you so much for the insight!! very helpful... one more question, do buttons created from a drawing and assigned to a script count as a trigger? or is it excluded like custom menu clicks? – Samy Oct 03 '19 at 17:06
  • and can you explain further please how many triggers can I put for the same script to run per every sheet in a spreadsheet? – Samy Oct 03 '19 at 17:07
  • @Samy I don't think Drawings clicks count as triggers. *per every sheet* doesn't matter. – TheMaster Oct 03 '19 at 18:17
  • I am confused about the per every sheet part, can you elaborate?... when it will matter? – Samy Oct 03 '19 at 18:57
  • 1
    @Samy Don't think sheet/tabs matter. One onOpen is enough for 1 spreadsheet. Number of menu clicks doesn't matter – TheMaster Oct 03 '19 at 21:25