2

Is there a way, to make a certain sheet in Google Spreadsheets, be editable and accessible to a certain type of user, while not be accessible to another type of user?

I currently have a Google App Script project, that allows a certain user to use an HTML form to input certain parameters, and a Google Spreadsheet is automatically created for him (thus, everything in the spreadsheet is created programatically). This new spreadsheet should have access by different "types" of users.

A "type" of user just means a specific person that has specific authority over the spreadsheet.

For example: There are users who would be able to edit anything in the spreadsheet. There are other users who can only read it (but not edit it). There are users who would be able to access custom Menus (that allow them specific actions related to the spreadsheet), while other users should not.

For now, I solved the above problem by having 2 types of users:

1)A user that is given "Can Edit" access to the spreadsheet by the creator. This user can edit any sheet in the spreadsheet, and access every custom menu (since those are created in the "onOpen()" trigger, which executes only when the user has "can edit" authority)

2)A user that is just given a "Can View" public link to the spreadsheet. This user can only read each sheet of the spreadsheet, but not edit any. He also has no access to any custom menus.

This worked for a while, but now I have new requirements that should allow the 1st type of user, to have access to specific sheets, while the 2nd type of user should not even have read access to them.

Is there a way, using either Google App Script, or other functionality from Google Spreadsheets, to make this happen?

I know sheets can be made visible or invisible, but doesn't that affect every user, even those I want to be able to view and edit them? If it's possible, I also want to know if it can be done programatically using Google App Script.

Is there also a way to have more functionality restricted to different types of users? What if I want a specific user to be able to edit a certain sheet, but not be able to access specific custom menus?

If it is not possible to do so, the only solution I can think of is to create separate spreadsheets for each of these "restricted features", and give "Can Edit" access in that spreadsheet to each type of user I want to have access to those features. However, ideally everything should be done in a single spreadsheet, since I want all the information contained in a single access point, and not scattered in different spreadsheets with (maybe) little relation between each other.

Thanks

gonzaw
  • 771
  • 4
  • 17

1 Answers1

4

now I have new requirements that should allow the 1st type of user, to have access to specific sheets

Editors already have access to all sheets.

the 2nd type of user should not even have read access to them.

Well, that would trigger the need for either using a 2nd spreadsheet that imports the data / sheets that you only want the "Can View" users to see or you could go with creating a web app that only displays that data if you just want to stick with 1 spreadsheet (but still 2 urls).

I know sheets can be made visible or invisible, but doesn't that affect every user, even those I want to be able to view and edit them?

This is fairly easy to test manually. Have an editor hide a sheet while a viewer has it open in another window. The sheet will be hidden on both.

I also want to know if it can be done programatically using Google App Script.

yes - SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name).hideSheet()

Is there also a way to have more functionality restricted to different types of users? If you have the users and their permissions stored some where, then you can control what functions run based on their email.

function myfunction() {
  var validUsers = ['ex1@ex.com', 'ex2@ex.com'];
  if (validUsers.indexof(Session.getEffectiveUser().getEmail()) >= 0) {
    // continue
  }
}

What if I want a specific user to be able to edit a certain sheet, but not be able to access specific custom menus?

Use the same technique as above to filter out who should be able to see menus in onOpen().

Bryan P
  • 5,031
  • 3
  • 30
  • 44
  • So there's no possible way to prevent a non-editor from seeing a sheet that's visible to an editor, without creating a new spreadsheet or creating a web app? – gonzaw Mar 27 '14 at 22:00
  • The "getEffectiveUser().getEmail()" trick seems neat. Is there a restriction on when you can use it? I think I've read that certain functions run knowing the user that executes them, while others don't. For example, can you use it on an "onEdit()" trigger? – gonzaw Mar 27 '14 at 22:04
  • Even if there was, viewers can always make a copy of the ss, then unhide any hidden sheets in their own copy to see them. Throw a Logger.log() in with onEdit() to see if the email goes thru – Bryan P Mar 28 '14 at 01:25
  • A crazy workaround would be to programatically move sheets into the ss when editors need them, then remove them when you know viewers are going to be viewing it. A function could remove the viewers when the editors sheets are moved in. – Bryan P Mar 28 '14 at 01:32
  • In that case, where is the information kept? If there is a cell where I put my salary, how do I know programatically which value it has when I have to "move" the sheet? – gonzaw Mar 31 '14 at 01:12
  • I'm kind of sad this didn't receive more answers. I presume it's because there's nothing more to add? – gonzaw Apr 03 '14 at 01:54
  • Create another post once you run into a specific coding spot that's holding you back. The multiple answers I provided should give you some direction on getting started. Good luck. – Bryan P Apr 03 '14 at 21:55
  • Mostly I guess it's because there is indeed no workaround, like you posted in these comments (to being able to have a hidden sheet from non-editors but visible to editors). That was the most important bit I was looking for. – gonzaw Apr 03 '14 at 22:37