Google Sheets Sheet Protection

Question

I have a spreadsheet with multiple sheets within. I have this spreadsheet shared with multiple users, and I need to protect the sheets so that certain users can only view and edit one sheet of this spreadsheet. As follows:

• User 1 (one location manager) needs to view and edit Sheet 1 (that location's payroll)

• User 2 (another location manager) needs to view and edit Sheet 2 (that location's payroll), but NOT Sheet 1

• User 3 (district manager) needs to view and edit both Sheet 1 AND Sheet 2, but NOT Sheets 3 & 4.

• User 4 (regional manager) needs to be able to view and edit Sheets 1, 2, 3, and 4.

Google Sheets only offers protection against editing, but I am wondering if there is any third-party app, script, or workaround that allows this functionality of protection against viewing?

Answer 1

Short answer = No. If you can't find this method via an API or script, there's no way a third party will offer it (They have access to the same APIs we do).

Slightly longer solution: You can create one master spreadsheet that contains all this data, then have 4 other spreadsheets (One for each user) that pulls the data from the correct sheet(s) into their personal spreadsheet.

For the slightly longer solution, you're looking at an 'onOpen' trigger in that specific users spreadsheet, so when they open the spreadsheet, it copies the right sheets from the Master spreadsheet to the spreadsheet they've just opened. Methods such as 'openByID' (To allow you to get another spreadsheet) and 'copyTo' (to copy another spreadsheets sheet to the current spreadsheet) are your friend here.

A very, very simple version of this might be:

var ss = SpreadsheetApp.openById("ID-FOR-MASTER-SHEET");
var sheet = ss.getSheets()
var destination = SpreadsheetApp.openById("ID-FOR-USERS-SHEET");
sheet[0].copyTo(destination);

Otherwise, if you have access to a Google Spreadsheet, you can always view all sheets within.

Answer 2

Idea

Split the tabs into separate sheets. Create a folder and place all documents in it. Share each sheet only with the owner that needs it and then share the folder with the regional manager.

Bonus

Try importrange command.

You can do: =IMPORTRANGE(A1,A2)

• A1 = Sheet URL
• A2 = Tab!Range ie Store1!A1:Q500

Use it to create a master sheet that pulls data from all of the individual stores, that can give the regional manger an easy snapshot. You can even mirror the original tab structure.

Answer 3

Comment on Separate Workbook architecture and ImportRange

I would caution against splitting the application into separate workbooks. Use of the ImportRange() requires you set sharing permissions to "Anyone with link" rather than the more secure "Only specific users".

I just re-architected an expense authorization application away from the separate (six) workbooks to a single master workbook because of this security issue.

Alternatives: Content Service

In my application, I use Content Service to do edit operations that only the owner of the database can do. The authorized users can only edit specific ranges in specific sheets. Then to do the magic, they execute macros using the content service that runs as the database owner.

Controlling Viewing

Conceivably you could program the workbook to hide sheets dependent on who the user is. This requires they authenticate themselves (you cannot do this in onOpen() for example. The problem with this is when you get collaborators viewing the workbook simultaneously. It would be interesting to see how Sheets would handle concurrent views of the same document. Knowing Google, I bet it would work.