隐藏用户的Google工作表标签 [英] Hiding google sheet tab from users

问题描述

我有一个带有两个标签的google表格文档，一个标签称为内部"，另一个称为外部".如何向其他用户隐藏内部标签?已经无法使用的锁定功能还不够好，我只希望公司里的人能够看到两个选项卡，客户只能看到外部选项卡.

I have a google sheets document with two tabs one called called internal and the other called external. How can i hide the internal tab from other users? the lock function already avialble is not good enough I only want people from my company to be able to see both tabs, clients should only be able to see the external tab.

function validUsers() { 
String[] adminUsers = {"[e-mail]","[e-mail]","[e-mail]"};

if (adminUsers.indexOf(Session.getEffectiveUser().getEmail()) >= 0) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Internal').showSheet() 
else
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Internal').hideSheet() 
} 

}

推荐答案

问题:

• 您要根据访问电子表格的用户来隐藏或显示电子表格中的工作表.

您可以执行以下操作:

• 安装 onOpen 触发器，该触发器执行一个函数(请致电每次用户打开电子表格时都 fireOnOpen ).
• 函数 fireOnOpen 应该检查哪个用户正在访问电子表格，并根据此内容隐藏或显示某个工作表(称为 Internal ).
• 为了检查当前访问电子表格的用户，您可以使用 getEffectiveUser()，它将返回安装触发器的用户.
• Install an onOpen trigger which executes a function (let's call it fireOnOpen) every time a user opens the spreadsheet.
• The function fireOnOpen should check which user is accessing the spreadsheet, and hide or show a certain sheet (called Internal) depending on this.
• In order to check the current user accessing the spreadsheet, you can use getActiveUser() (instead of getEffectiveUser(), which will return the user who installed the trigger).
• 可以手动 或以编程方式.要以编程方式执行此操作，请将此函数复制到脚本编辑器中并执行一次:
• The trigger can be installed either manually or programmatically. To do it programmatically, copy this function to your script editor and execute it once:

function createOnOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("fireOnOpen")
  .forSpreadsheet(ss)
  .onOpen()
  .create();
}

• 这将导致每次用户访问电子表格时执行 fireOnOpen . fireOnOpen 函数可能是这样的:
• This will result in fireOnOpen being executed every time a user accessed the spreadsheet. The fireOnOpen function could be something like this:

function fireOnOpen() { 
  const adminUsers = ["[e-mail]","[e-mail]","[e-mail]"];
  const currentUser = Session.getActiveUser().getEmail();
  const internalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Internal");
  if (adminUsers.includes(currentUser)) internalSheet.showSheet();
  else internalSheet.hideSheet();
} 

重要注意事项:

• 您不能为某些用户隐藏工作表，而不能为其他用户隐藏工作表.隐藏的工作表对所有用户都隐藏，可见的工作表对所有用户都可见.因此，这仅在内部和外部用户不同时访问电子表格时才有效.如果这样做，外部用户可能可以访问内部工作表.
• getActiveUser()并不总是填充，您可以在此答案上看到，因此请确保所有管理员用户都来自同一G Suite域.否则，这将无法正常工作.
• 如果内部工作表的隐私至关重要，并且此时内部和外部用户都可以访问电子表格，则我不推荐此解决方案.

Important notes:

• You cannot hide sheets for some users but not for others. A hidden sheet is hidden for all users, and a visible sheet is visible for all users. Therefore, this will only work if internal and external users don't access the spreadsheet at the same time. If they do, external users might be able to access the Internal sheet.
• getActiveUser() is not always populated, as you can see on this answer, so please make sure that all admin users are from the same G Suite domain. Otherwise, this won't work.
• If the privacy of the Internal sheet is critical and there is a possibility of internal and external users accessing the spreadsheet at the time, I would not recommend this solution.

如评论中所述，对于管理员和非管理员用户当时访问文件的情况，可能的解决方法如下:

As mentioned in comments, a possible workaround for the occasions when admin and non-admin users access the file at the time could be the following:

• 管理员用户访问文件时，请存储发生时间.
• 创建一个时间驱动的触发器以定期执行功能(假设每隔5分钟)，该功能将检查管理员是否在不久前(例如30分钟)访问了该文件.如果管理员已这样做，请删除其他非管理员域的权限.如果不是这种情况，请重新添加这些权限.
• 在这种情况下，需要启用驱动器高级服务.

function fireOnOpen() { 
  const adminUsers = ["[e-mail]","[e-mail]","[e-mail]"];
  const currentUser = Session.getActiveUser().getEmail();
  const internalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Internal");
  if (adminUsers.includes(currentUser)) {
    internalSheet.showSheet();    
    const documentProperties = PropertiesService.getDocumentProperties();
    documentProperties.setProperty("lastAdminAccess", new Date().getTime()); // Store time of admin access
  } else internalSheet.hideSheet();
} 

function createOnOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("fireOnOpen")
  .forSpreadsheet(ss)
  .onOpen()
  .create();
}

function updatePermissions() {
  const fileId = SpreadsheetApp.getActive().getId();
  const lastAdminAccess = PropertiesService.getDocumentProperties().getProperty("lastAdminAccess"); // Last time of admin access in ms
  const now = new Date().getTime(); // Current time in milliseconds
  const thirtyMinutes = 1000 * 60 * 30; // 30 minutes in milliseconds
  if (now - lastAdminAccess < thirtyMinutes) {
    const currentPermissions = Drive.Permissions.list(fileId)["items"];
    const publicPermissionIds = currentPermissions.filter(permission => permission["type"] === "anyone")
                                                  .map(permission => permission["id"]);
    publicPermissionIds.forEach(permissionId => Drive.Permissions.remove(fileId, permissionId));
  } else {
    const resource = {
      type: "anyone",
      role: "reader"
    }      
    Drive.Permissions.insert(resource, fileId);
  }
}

function createTimeTrigger() {
  ScriptApp.newTrigger("updatePermissions")
  .timeBased()
  .everyMinutes(5)
  .create();
}

这篇关于隐藏用户的Google工作表标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！