Google sheets, running a function as owner

2019-08-09 03:07发布

问题:

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:

  1. Create a function, say unprotect(), which checks if "Shift" is the active cell and sets the appropriate protection
  2. In your project script editor, go to Resources > Current project's triggers
  3. Create the following rule: run unprotect from spreadsheet on edit
  4. Since unprotect() is an installed trigger, it will run with the owner permission.