Lock a single sheet per spreadsheet in a folder full of spreadsheets

Question

I work at a university, and have set up 52 spreadsheets for 52 students. Each week, I need to lock a single sheet on each of these 52 spreadsheets. The sheets are all contained in a single folder. Each student's spreadsheet has sheets named '1' through '33' (representing 33 weeks), along with a couple of additional sheets.

I am thinking of having another spreadsheet with a script that lets me lock or unlock a week. On this spreadsheet, I would set it up as follows:

+---+-----------+---------------------+---------------------+
|   |     A     |        B            |    C                |
+---+-----------+---------------------+---------------------+
| 1 | 1         |[email protected] |[email protected] |
| 2 | Lock      |                     |                     |
+---+-----------+---------------------+---------------------+

A1 will be where I type which week to lock. In this example, it's week '1'. Row 1, Cells B1 through N1 will contain the email addresses of other university professors who should continue to have editing access. B2 will be where I indicate either "Lock" or "Unlock"

How can I loop through each file in this folder and set the editing permissions? Here is what I am unsuccessfully trying. It is a modification of another script that someone helped me with for the purpose of looping through and editing a specific range in each students' spreadsheet.

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);

So I have made some good progress. Here is where I am as of 10/27.

Issue 1: It now will protect the sheet '1', so that I am the only editor. I was thinking that I can remove everyone else first, and then add back the people in the editors array. However, they don't get added back as I was hoping with the statement protection.addEditors(editors); The error says: Invalid user: "[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]".

Issue 2: The other issue is that it doesn't loop through all student files. Instead, I noticed the arrays changesheets and editors keep increasing in dimensions. After passing through the while loop a few times, the changesheets array is changesheets[36][36][36] and editors is editors[13][13][13].

function increaseProtection(idFolder, sheetNames) {

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);    
}  

}

Answer 1

To get the B1:N1 editor range from the sheet, I'd do something like this.

 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

You'll also want to use addEditors rather than addEditor if you're going to pass the array rather than iterating through.

---

I think this one works but some of the details (like are editors set on the main sheet or on each ss) confuse me a bit.

   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;
}