google-apps-script - open specific tab in read-only view

Question

We have a staff roster in a Google Sheet. We display one week per tab

Staff have read-only access The managers have write access.

I want to make a apps script which looks up the date and when you open the Sheet, it opens to the correct tab.

However, it seems the simple and installable (onopen) triggers will not run when then employees open it in read-only mode.

How can I do this is there is no onopen trigger that will run for read only users?

The pain is the roster always opens on week 1, and staff have to scroll to find the correct week.

How can I attack this?

Answer 1

You can reorder the tabs in descending order, so when you open the file, you will get always the 1st tab as the last week so.

Reorder can be triggered then as per week.

Answer 2

Making a script to do this is pretty simple, but yes they'd have to have editor privileges.

You could make everyone an editor, then protect each sheet and lock them out under the protect sheet permissions (or at least throw a warning about editing fields if you trust them and just want to keep people from accidentally making changes).

Answer 3

You are right about simple triggers and installable triggers. These triggers do not run if a file is opened in read-only mode.

With this, you may want to try using setActiveSheet(sheet) wherein you can set the given sheet to be the active sheet in the spreadsheet. The spreadsheet UI will display the chosen sheet unless the sheet belongs to a different spreadsheet.

The suggested solution in this related SO post might help.