谷歌工作表保护 - 基本需求不起作用

Question

我有一个基本问题，一直在努力解决。

我想要一个谷歌工作表只为任何有链接的人查看。我希望能够作为所有者编辑任何内容（duh）我想保护一个工作表，除了某些单元格（比如A1）是可编辑的，并添加一个拥有谷歌帐户的人（比如[e-mail])因此他们只能编辑A1。

我的所有尝试都导致匿名用户只能根据需要查看，但是[e-mail]可以编辑每个单元格，并且不限于A1。

我可能做错了什么，还是这种情况根本不可能发生？

我试过很多指南，其中包括：https://support.google.com/docs/answer/144687?hl=enhttp://www.appscare.com/2015/02/set-permissions-protected-sheets/

谢谢！

Answer 1

试试这个。我在我的样本表上设置了共享，任何有链接的人都可以查看，然后添加了可以编辑的电子邮件地址。电子邮件地址的用户可以编辑"A1"当然，我可以编辑所有内容。

function protectionTest(){
 // Protect the active sheet except A1, then remove all other users from the  list of editors.
 var sheet = SpreadsheetApp.getActiveSheet();
 var protection = sheet.protect().setDescription('Sample protected sheet');
 var unprotected = sheet.getRange('A1');
 protection.setUnprotectedRanges([unprotected]);
 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon   removing the group.
 var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }}

这花了一段时间，但这将识别彩色范围并取消对它们的保护。设置设置可以如上所述进行编辑。

 function cellsToUnprotect(){
 //Find cells of specific color and create array of ranges.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = ss.getSheetByName("Sheet1")// Change Sheet Name as needed.
 var rng = sheet.getRange("A1:C10").getBackgrounds()// Adjust Range as  needed.
 var arrayBG=[]
{
  for(var i=0;i<rng.length;i++){
  for(var j=0;j<rng[1].length;j++){
  //if(rng[i][j] != "#ffffff"){ //Any background color not equal to white. I  prefer this.
  if(rng[i][j] == "#ffff00"){ //background color defined ("#ffff00" is  yellow)
  var r=i+1
  var c=j+1
  var range=sheet.getRange(r, c)
  arrayBG.push(range)
    }}}
  unprotect(arrayBG)
  }}
function unprotect(arrayBG){
//Protect Sheet1 and unprotect ranges in passed array.
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("Sheet1") // Change Sheet Name as needed.
var protection = sheet.protect().setDescription('Sample protected sheet');
protection.setUnprotectedRanges(arrayBG);
// Ensure the current user is an editor before removing others. Otherwise,if  the user's edit
// permission comes from a group, the script will throw an exception upon     removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}}

Answer 2

对不起，我花了这么长时间才回答，我正在度假。这将处理多张图纸。如果这对你有效，请批准答案。

function cellsToUnprotect(){
//Find cells of specific color and create array of ranges.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = SpreadsheetApp.getActiveSheet();
 var sheetNameToWatch=[] //Array of Sheet names
 var names = ss.getSheets()
  for( j=0;j<names.length;j++) {
    var n= names[j].getSheetName();
      if(n!="Done"){ //If Sheet name not "Done" add to array. Change sheet  name to exclude sheets. 
       sheetNameToWatch.push(n)
      }}
   for(var k=0;k<sheetNameToWatch.length;k++){
      var rng =  ss.getSheetByName(sheetNameToWatch[k]).getRange("A1:C10").getBackgrounds()//  Adjust Range as needed.
      var arrayBG=[]
{
  for(var i=0;i<rng.length;i++){
  for(var j=0;j<rng[1].length;j++){
  //if(rng[i][j] != "#ffffff"){ //Any background color not equal to white. I   prefer this.
  if(rng[i][j] == "#ffff00"){ //background color defined ("#ffff00" is   yellow)
     var r=i+1
     var c=j+1
     var range=ss.getSheetByName(sheetNameToWatch[k]).getRange(r, c)
  arrayBG.push(range)
    }}}
  unprotect(arrayBG,sheetNameToWatch,k)
}}}
function unprotect(arrayBG,sheetNameToWatch,k){
 //Protect Sheet1 and unprotect ranges in passed array.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = ss.getSheetByName(sheetNameToWatch[k]) // Change Sheet Name as needed.
var protection = sheet.protect().setDescription('Sample protected sheet');
protection.setUnprotectedRanges(arrayBG);
// Ensure the current user is an editor before removing others. Otherwise,if   the user's edit
// permission comes from a group, the script will throw an exception upon   removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}}