Google脚本-为多个以上的电子表格设置工作表保护(具有不受保护的单元格)

我正在尝试将一次不受保护范围内的工作表保护设置为一次以上的工作表。 我觉得我已经接近要管理它,但是myVariables部分似乎是错误的。 变量protections0,protects1,...的增量部分不起作用。

非常感谢您的宝贵支持。

function UpdateProtection() 
{

var protections0 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections1 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')  
var protections2 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxE/edit').getSheetByName('Ratenprogramm') 
var protections3 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections4 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections5 = Spreadsheetapp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 

var myVariables = {};
myVariables["protections"] = 1

  for(i = 0; i <= 5; i++)
  {  
    myVariables[ ("protections" + i) ] = i;

    var sheet = myVariables;
    var protection = sheet.protect().setDescription('Protection');
    var unprotected = [];

    unprotected[0] = sheet.getRange('A1:A2');
    unprotected[1] = sheet.getRange('D10:F40');
    unprotected[2] = sheet.getRange('F10:G20');  

    protection.setUnprotectedRanges(unprotected);

    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) 
    {
      protection.setDomainEdit(false);
    }  
  }
}
LiJ86 回答:Google脚本-为多个以上的电子表格设置工作表保护(具有不受保护的单元格)

您根本不需要myVariables数组:

function UpdateProtection() {
var protections0 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections1 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')  
var protections2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxE/edit').getSheetByName('Ratenprogramm') 
var protections3 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections4 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections5 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')

  for(i = 0; i <= 5; i++) {  
    var sheet = eval("protections" + i);
    var protection = sheet.protect().setDescription('Protection');
    var unprotected = [];

    unprotected[0] = sheet.getRange('A1:A2');
    unprotected[1] = sheet.getRange('D10:F40');
    unprotected[2] = sheet.getRange('F10:G20');  

    protection.setUnprotectedRanges(unprotected);

    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }  
  }
}

我使用eval()函数[1]将字符串中的变量名称转换为实际的变量值。

下次,您可以将所有URL放入一个数组中,并在循环中使用它们,如下所示:

var sheet = SpreadsheetApp.openByUrl(UrlArray[i]).getSheetByName('Ratenprogramm') 

[1] https://www.w3schools.com/jsref/jsref_eval.asp

本文链接:https://www.f2er.com/3163719.html

大家都在问