How to implement rangeProtect() over multiple tabs - google spreadsheet

Question

I'd like to implement a script to implement range protections on the different tabs of the spreadsheet when someone create a new spreadsheet by copying my template.

The issue I encounter is when the script run through the protection implementation.

My code seems to be fine as per the debug tool does not find anything wrong, yet, my script implements only the first protected range.

Here the sample:

function rangeProtect() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if(s.getName()=='Overview') {
    var range = s.getRange('A1:K20');
    var protection = range.protect().setDescription('Overview');
    var me = Session.getEffectiveUser();
    var eds = protection.getEditors();
    protection.addEditor(me);
    protection.removeEditors(eds);
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  }
  else if(s.getName()=='Picklist (Buying Ops)') {
    var range = s.getRange('A1:H254');
    var protection = range.protect().setDescription('Picklist');
    var range2 = s.getRange('C5:H254')
    var protection2 = range2.protect().setDescription('Buying Ops');
    var me = Session.getEffectiveUser();
    var eds = protection.getEditors();
    var add = raw[11];
    protection.addEditor(me);
    protection.removeEditors(eds);
    protection2.addEditor(add);
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  }
 ...

Could someone help me on that as I don't see what is wrong here.

Thanks

var protection2 = range2..protect().setDescription('Buying Ops'); Remove one dot on this line. Also seems like **raw[11]** is not defined. — Akshin Jalilov, Jun 04 '15 at 09:51
@AkshinJalilov, thanks for flagging this. But my issue is anyway still the same, the script wont run through — AnDou, Jun 04 '15 at 10:24
Change **s.getName()== ** to **s.getName()=== ** in both statements. You can read [here](http://stackoverflow.com/a/359509/2589810) about the difference. — Akshin Jalilov, Jun 04 '15 at 10:27

score 0 · Accepted Answer · answered Jun 04 '15 at 17:05

Answering my question,

The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function

//main script - where to protect - who can edit
function Protect_UnProtect( myrange,  mysheet,  mydescription, startRow, colnumber, numRows, columncount) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mysheet);
  var range1 = ss.getRange(myrange);
  var protection1 = range1.protect().setDescription(mydescription);
  var me = Session.getEffectiveUser();
  var eds1 = protection1.getEditors();
  protection1.addEditor(me);
  protection1.removeEditors(eds1);
  //Incase we dont want to run this part of the code, then send the StartRow parameter with -1 value
  // and the rest of the parameteres with 0
  //startRow = first raw where to fetch datas
  //colnumber = column number where to fetch datas - column A = 1
  //numRows = number of rows where to fetch datas
  //columncount = number of columns where to fetch datas
  if(startRow > 0) {
    var dataRange = ss.getRange(startRow, colnumber, numRows, columncount)
    var data = dataRange.getValues();
    for (i in data) {
      var row = data[i];
      var emailAddress = row[0];
      protection1.addEditor(emailAddress);
    }
  }
  if (protection1.canDomainEdit()) {
    protection1.setDomainEdit(false);
    }
}

The second script is recalling the main script and indicate where to apply it.

function rangeProtect() { //script to recall the main script

      Protect_UnProtect("A1:H", 'Overview', '0-Overview', -1, 0, 0, 0);
      Protect_UnProtect("A1:B", 'Picklist (Buying Ops)', '1- Picklist 1', -1, 0, 0, 0);
      Protect_UnProtect("C1:H2", 'Picklist (Buying Ops)', '1- Picklist 2', 5, 11, 2, 1);
      Protect_UnProtect("C3:H4", 'Picklist (Buying Ops)', '1- Picklist 3', -1, 0, 0, 0);
      Protect_UnProtect("I1:K", 'Picklist (Buying Ops)', '1- Picklist 4', -1, 0, 0, 0);
      Protect_UnProtect("C5:H", 'Picklist (Buying Ops)', '1- Buying Ops', 5, 11, 2, 1);
      Protect_UnProtect("A1:O", 'Dashboard (View Only)', '2- Dashboard', -1, 0, 0, 0);
      ...
        }

How to implement rangeProtect() over multiple tabs - google spreadsheet

1 Answers1