在Google表格中管理Google保护的单元格 [英] Managing Google Protected Cells in Google Sheet

问题描述

我有一个Google云端硬盘文件夹，其中包含30多个Google表格.在每个工作表中，我有5个以上的标签，并且每个标签都至少包含一组受保护的单元格，或者标签本身.我想知道，是否有可能将受保护单元的所有这些权限作为文本提供到一个Google表格中，以便能够快速查找并潜在地管理这些权限.然后，我的长期目标是直接从那个Google表格中管理受保护的单元格.我一直在寻找，但还没有找到任何资源可以将我送往正确的轨道.

I have a Google Drive folder that has 30+ Google Sheets in it. In each sheet I have 5+ tabs, and each of these tabs has at least one protected set of cells, or the tab itself. I was wondering, is it possible to feed all of these permissions for the protected cells into one Google Sheet, as text,to be able to quickly look and potentially manage the permissions. My long-term goal is then to manage the protected cells straight from that one Google Sheet. I have been looking, but haven't found any resources to send me down the right track.

推荐答案

我写了这个脚本来完成所需的任务，

I writed this script for make the task that you want,

要运行该脚本，您需要打开电子表格，或创建一个新的电子表格，然后转到工具->脚本编辑器进行创建，然后复制/粘贴代码.

to run the script you need to open a Spreadsheet, or ceate a new one an then Go to tool->script editor to create it, then copy/paste the code.

更改容器文件夹ID的" ######################## "，以确定文件夹的ID，您可以打开文件夹，然后复制ID对应的URL部分 https://drive.google.com/drive/folders/ #########################

Change the "#########################" for the ID of your container folder, to determine the ID of your folder you can open the folder an then copy the URL part corresponding to the ID https://drive.google.com/drive/folders/#########################

添加菜单后，需要刷新以查看它.

After you add the Menu, you need to refresh to see it.

使用:点击自定义实用程序->在此处获取权限列表，然后它将创建包含所有信息的"工作表编号"

Use: Click on Custom Utilities->Get permisions list Here, then it will create "sheet #" that will have all the information

下面是代码:

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Utilities').addItem('Get permisions list Here','testfunction').addToUi();
}

function testfunction() {
  //Add a new sheet in the current Spreadsheet
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet().activate();
  activeSheet.appendRow(['FileName','ID','Protection Description','Range','Type','Users']);
  activeSheet.getRange("A1:F1").setFontWeight('bold');

  //get all the Google Spreadsheet's files
  var files = DriveApp.getFolderById("#########################").getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
   var file = files.next();
   var ss = SpreadsheetApp.openById(file.getId());

   //get the permisions in the current file, and print the data to the previous created sheet
   var protectionsRange = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protectionsRange.length; i++) {
      var protection = protectionsRange[i];

      activeSheet.appendRow([file.getName(),file.getId(),protection.getDescription(),protection.getRange().getA1Notation(),protection.getProtectionType(),protection.getEditors().join(";")]);
      //Logger.log(file.getName() + " | " + file.getId() + " \n| " + protection.getDescription() + " | " + protection.getRange().getA1Notation() + " | " + protection.getProtectionType() + " | " + protection.getEditors().join(";"));
    }
    var protectionsSheet = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    for (var i = 0; i < protectionsSheet.length; i++) {
      var protection = protectionsSheet[i];
      activeSheet.appendRow([file.getName(),file.getId(),protection.getDescription(),protection.getRange().getA1Notation(),protection.getProtectionType(),protection.getEditors().join(";")]);
      //Logger.log(file.getName() + " | " + file.getId() + " \n| " + protection.getDescription() + " | " + protection.getRange().getA1Notation() + " | " + protection.getProtectionType() + " | " + protection.getEditors().join(";"));
    }
  }
}

这篇关于在Google表格中管理Google保护的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！