2

I have created 50 spreadsheets for 50 students. Each spreadsheet contains 33 sheets, numbered 1 to 33, along with a couple of other sheets. Within each of these 33 sheets, there are many protected ranges. I accidentally protected a range that ought not to have been protected. So, with 50 students each having 33 sheets, I have over 1800 sheets to unprotected.

I want to loop through each sheet, 1 to 33, and remove protection from cell F15. Right now, I (the owner) am the only editor. Since I'm not an actual programmer, I've clearly not done this properly. Can someone offer a suggestion of how I can change my Google Script to make this work?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets(); 
for (var i=2; i  <sheets.length; i++) {
  var sheet = ss.getSheets()[i];
  sheet.activate();
  SpreadsheetApp.flush();
  var range = sheet.getRange('F15');
       var protection = range.protect().setDescription('Protected range');
       var me = Session.getEffectiveUser();
       protection.addEditor(me);
       protection.removeEditors(protection.getEditors());
       if (protection.canDomainEdit()) {
         protection.remove();
         } //end of if statement

}
0

Try this code:

function start() {
  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change

  deleteProtection(idFolder, sheetNames, strRange) 

}


function deleteProtection(idFolder, sheetNames, strRange) {

  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 app = SpreadsheetApp;

  while(contents.hasNext()) {
    file = app.openById(contents.next().getId());
    sheets = file.getSheets();

    for(var i = 0; i < sheets.length; i++) {
      sheet = sheets[i];
      sheetName = sheet.getName();
      protections = file.getProtections(app.ProtectionType.RANGE);

      for (var i = 0; i < protections.length; i++) {
        var protection = protections[i];
        var range = protection.getRange();
        var sheetName = range.getSheet().getName();
        strRangeProtect = range.getA1Notation();
        if (strRangeProtect == strRange && sheetNames.indexOf(sheetName) > 0) {
          protection.remove();
        }      
      }           
    }  
  }  
}

Befor you start:

First you need to paste all the files into one folder, then copy it's id:

enter image description here

and change this lines:

  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change
  • How might I adjust this so I can unprotect an entire sheet? – Neill Dorn Nov 1 '16 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.