Ok I'm very new at SO and and reasonably new with JS and Sheets. I am writing a sheet for work that runs over 3 shifts with a front page and 4 sheets for each shift. Each corresponding sheet for every shift is identical except for the shift time, AM, PM, and NT.
Having to deal with some fat fingered people I thought it would be best to protect the sheets and open ranges that can be filled in. On start up only the front page shows. I supply a menu with Start and End shift options. Run Start shift and the appropriate shift pages are shown, and then run End Shift and they are hidden.
The issue is that I don't run this show/hide and as the sheets are protected neither can the user. Is it possible to have a section of JS run with owner privilege regardless of who invokes it via the menu?
It may be possible from an installable trigger but I have not yet hunted down how from a menu item.
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
回答1:
I tried creating a menu item which changes a cell value. Unfortunately it won't trigger the onEdit event handler. So, I'm afraid you can do it from a menu item. A workaround would be adding validation rule to a cell to create a drop-down menu.
To those interested in the steps:
First, the workflow:
- The spreadsheet has a cell called "Shift"
- The user can change the value to "Start", "End", or ""
- Set the appropriate protection when the value of "Shift" changes
Here are the steps:
- Create a function, say
unprotect()
, which checks if "Shift" is the active cell and sets the appropriate protection - In your project script editor, go to Resources > Current project's triggers
- Create the following rule: run unprotect from spreadsheet on edit
- Since
unprotect()
is an installed trigger, it will run with the owner permission.