Google 电子表格 - 根据用户类型显示表格 [英] Google Spreadsheet - Show sheets depending on type of user

问题描述

有没有一种方法可以使 Google 电子表格中的某个工作表对某种类型的用户可编辑和访问，而对另一种类型的用户不可访问?

我目前有一个 Google App Script 项目，它允许某个用户使用 HTML 表单输入某些参数，并且会自动为他创建一个 Google 电子表格(因此，电子表格中的所有内容都是以编程方式创建的).不同类型"的用户应该可以访问这个新电子表格.

类型"用户仅表示对电子表格具有特定权限的特定人员.

例如:有些用户可以编辑电子表格中的任何内容.还有其他用户只能阅读它(但不能编辑它).有些用户可以访问自定义菜单(允许他们执行与电子表格相关的特定操作)，而其他用户则不能.

目前，我通过拥有两种类型的用户解决了上述问题:

1) 被创建者授予可以编辑"电子表格访问权限的用户.此用户可以编辑电子表格中的任何工作表，并访问每个自定义菜单(因为这些菜单是在onOpen()"触发器中创建的，该触发器仅在用户具有可以编辑"权限时执行)

2) 刚刚获得电子表格可以查看"公共链接的用户.该用户只能阅读电子表格的每个工作表，而不能编辑任何工作表.他也无法访问任何自定义菜单.

这有一段时间了，但现在我有了新的要求，应该允许第一种类型的用户访问特定的工作表，而第二种类型的用户甚至不应该对它们具有读取权限.

<小时>

有没有办法使用 Google App Script 或 Google 电子表格中的其他功能来实现这一点?

我知道可以使工作表可见或不可见，但这不会影响每个用户，即使是那些我希望能够查看和编辑它们的用户?如果可能，我还想知道是否可以使用 Google App Script 以编程方式完成.

是否还有一种方法可以将更多功能限制到不同类型的用户?如果我希望特定用户能够编辑某个工作表，但无法访问特定的自定义菜单怎么办?

<小时>

如果不可能这样做，我能想到的唯一解决方案是为这些受限功能"中的每一个创建单独的电子表格，并在该电子表格中为我想要的每种类型的用户提供可以编辑"访问权限访问这些功能.然而，理想情况下，一切都应该在一个电子表格中完成，因为我希望所有信息都包含在一个访问点中，而不是分散在不同的电子表格中，彼此之间(可能)几乎没有关系.

谢谢

解决方案

现在我有了新的要求，应该允许第一种类型的用户访问特定的工作表

编辑者已经可以访问所有工作表.

<块引用>

第二种类型的用户甚至不应该拥有对它们的读取权限.

好吧，这将触发需要使用第二个电子表格导入您只希望可以查看"用户查看的数据/工作表，或者您可以创建一个仅显示该数据的网络应用程序只想坚持使用 1 个电子表格(但仍然是 2 个网址).

<块引用>

我知道可以使工作表可见或不可见，但这不会影响每个用户，甚至是那些我希望能够查看和编辑它们的用户?

这很容易手动测试.当查看器在另一个窗口中打开工作表时，让编辑器隐藏工作表.工作表将隐藏在两者上.

<块引用>

我还想知道是否可以使用 Google App Script 以编程方式完成.

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

<块引用>

是否还有一种方法可以限制不同类型的用户使用更多功能?如果您将用户及其权限存储在某个位置，则您可以根据他们的电子邮件控制运行哪些功能.

function myfunction() {var validUsers = ['[e-mail]', '[e-mail]'];如果(validUsers.indexof(Session.getEffectiveUser().getEmail())> = 0){//继续}}

<块引用>

如果我希望特定用户能够编辑某个工作表，但不能访问特定的自定义菜单怎么办?

使用与上述相同的技术过滤掉应该能够在 onOpen() 中看到菜单的人.

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

解决方案

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 = ['[e-mail]', '[e-mail]'];
  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().

这篇关于Google 电子表格 - 根据用户类型显示表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！