我在一所大学工作,为52名学生设置了52个电子表格。每周,我需要在这52张电子表格中分别锁定一张表格。所有工作表都包含在一个文件夹中。每个学生的电子表格都有从"1"到"33"(代表33周)的表格,还有一些额外的表格。
我想有另一个电子表格的脚本,让我锁定或解锁一个星期。在这个电子表格中,我将按如下方式设置它:
+---+-----------+---------------------+---------------------+
| | A | B | C |
+---+-----------+---------------------+---------------------+
| 1 | 1 |user1@university.edu |user2@university.edu |
| 2 | Lock | | |
+---+-----------+---------------------+---------------------+
A1将是我输入哪个星期要锁定的地方。在本例中,是第1周。第1行,单元格B1到N1将包含应该继续具有编辑权限的其他大学教授的电子邮件地址。B2将是我指示"锁定"或"解锁"的地方
如何遍历该文件夹中的每个文件并设置编辑权限?这是我没有成功的尝试。这是对另一个脚本的修改,有人帮助我循环并编辑每个学生的电子表格中的特定范围。
var idFolder = 'xxxxxxxxxxxxxxxxZLaXVPZzQ';
var folder = DriveApp.getFolderById(idFolder);
var contents = folder.getFiles();
var file;
var sheet;
var sheets;
var sheetName;
var range;
var strRangeProtect;
var protections;
var protection;
var editors = [???????? Range of B1:N1??????????];
var app = SpreadsheetApp;
var currentweekneedstobedefined = app.getActiveSheet().getRange(1, 1);
var thisweek = currentweekneedstobedefined.getValue();
while(contents.hasNext()) {
file = app.openById(contents.next().getId());
sheets = file.getSheets();
var thisweek = currentweekneedstobedefined;
protections = file.getProtections(app.ProtectionType.SHEET);
protection = protections(thisweek);
protection.addEditor(editors);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
所以我已经取得了一些很好的进步。这是我在10月27日的位置。
问题1:它现在将保护表'1',所以我是唯一的编辑。我想我可以先删除其他所有人,然后在编辑器数组中添加人。然而,它们并没有像我希望的那样被添加回语句protection.addEditors(编辑);错误提示:无效用户:"ixxxx@universityedu, nxxxxr@universityedu, sxxxxx0@universityedu, sxxxxx2@universityedu, nxxxxxt@universityedu, gxxxxx4@universityedu, yxxxxxv@universityedu, kxxxxx7@universityedu, emxxxxxs@universityedu, kpxxxxx3@universityedu"。
问题2:另一个问题是它没有遍历所有学生文件。相反,我注意到数组更改表和编辑器的维度在不断增加。经过几次while循环后,changesheets数组为changesheets[36][36][36], editors为editors[13][13][13]。
function function function function function function function function function function
var folder = DriveApp.getFolderById(idFolder);
var contents = folder.getFiles();
var file;
var app = SpreadsheetApp;
var currentweekneedstobedefined = app.getActiveSheet().getRange(1, 1);
var thisweek = currentweekneedstobedefined.getValue();
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get spreadsheet
var sheet = ss.getActiveSheet();
var editors = sheet.getRange('B1:N1').getValues(); //get values of B1:N1 as array
while(contents.hasNext()) {
file = app.openById(contents.next().getId());
var fname = file.getName();
var changesheets = file.getSheets();
var thisweeksSheet = file.getSheetByName(thisweek);
var me = Session.getEffectiveUser();
var permissions = thisweeksSheet.getSheetProtection();
var protection = thisweeksSheet.protect().setDescription('Supervisors Only');
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
protection.addEditors(editors);
}
}
要从工作表中获得B1:N1编辑器范围,我会这样做。
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get spreadsheet
var sheet = ss.getActiveSheet(); //get sheet
var editors = sheet.getRange('B1:N1').getValues()[0]; //get values of B1:N1 as array
如果你要传递数组而不是迭代,你也需要使用addEditors
而不是addEditor
。
我认为这个是有效的,但是一些细节(比如编辑器设置在主表上还是在每个ss上)让我有点困惑。
function myFunction() {
var idFolder = '0B_9l84KvUJBWRXhPd0lLRUN1WWs';
var folder = DriveApp.getFolderById(idFolder);
var contents = folder.getFiles();
var currentWeek = SpreadsheetApp.getActiveSheet().getRange(1, 1).getValue();//get week value from this main control sheet
while(contents.hasNext()) {
var ss = SpreadsheetApp.openById(contents.next().getId());//gets the ss in the folder
var sheet = ss.getSheetByName(currentWeek); //get sheet by week name
var editors = cleanArray(sheet.getRange('B2:N2').getValues()[0]); //get values of B1:N1 as array --- not sure if you set this on the local sheet or the main control one
var protection = sheet.protect().setDescription('no Dana only Zuul');
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
protection.addEditors(editors);
sheet.getRange('A1').setBackground('red'); //just a visual marker to see it went through all the spots
}
}
//cleans empty slots from array http://stackoverflow.com/questions/281264/remove-empty-elements-from-an-array-in-javascript
function cleanArray(actual) {
var newArray = new Array();
for (var i = 0; i < actual.length; i++) {
if (actual[i]) {
newArray.push(actual[i]);
}
}
return newArray;
}