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

问题描述

有没有办法使Google Spreadsheets中的某张工作表可被某些类型的用户编辑和访问，而另一种类型的用户则无法访问?

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

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

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

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.

是否可以使用Google App脚本或Google Spreadsheets中的其他功能来实现这一目标?

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

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.

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

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

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.

谢谢

推荐答案

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

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.

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

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.

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

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

是- 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?

使用与上述相同的技术来过滤哪些人应该可以在onOpen()中查看菜单.

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

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