Unprotecting protected sheet so that others can run script and then protect the sheet again

Question

I have compiled a list of scripts that I run on a sheet. I'm not a programmer I'm still learning so I have used some code from other people.

The following are the only unprotected ranges B2:C2,N5:N43 but for the other scripts to run the whole sheet needs to be unprotected and protected again.

What protection rules do you have in your sheet? I assume you have a rule for protecting the whole sheet, and then two rules that unprotect the ranges you just described. Is this correct? — carlesgg97
I'm protecting the sheet simply with google's sheet protection and have given the people access through this to those cell to input data. So im not using any code for this. — Willnes
Hey @Willnes, was the answer I published useful to you? I'd love to know how you moved on with the issue you originally presented. Cheers! — carlesgg97

carlesgg97 carlesgg97 · Accepted Answer · 2019-12-10T15:52:44

Using Google Apps Script, you can modify your scripts so that before running they unprotect your sheets and ranges, and after running they re-protect them. You could use code such as the following:

function unProtectAndProtect() {
  var sheetProtections = SpreadsheetApp.getActive().getProtections(SpreadsheetApp.ProtectionType.SHEET);
  var rangeProtections = SpreadsheetApp.getActive().getProtections(SpreadsheetApp.ProtectionType.RANGE);

  var protectionData = {
    sheetProtections: [],
    rangeProtections: []
  };

  for (var i=0; i<sheetProtections.length; i++) {
    var protection = {};
    protection['editors'] = sheetProtections[i].getEditors();
    protection['description'] = sheetProtections[i].getDescription();
    protection['range'] = sheetProtections[i].getRange();
    protection['unprotected ranges'] = sheetProtections[i].getUnprotectedRanges();
    protection['candomainedit'] = sheetProtections[i].canDomainEdit();
    protection['iswarningonly'] = sheetProtections[i].isWarningOnly();

    sheetProtections[i].remove();
    protectionData.sheetProtections.push(protection);
  }

  for (var i=0; i<rangeProtections.length; i++) {
    var protection = {};
    protection['editors'] = rangeProtections[i].getEditors();
    protection['description'] = rangeProtections[i].getDescription();
    protection['range'] = rangeProtections[i].getRange();
    protection['unprotected ranges'] = rangeProtections[i].getUnprotectedRanges();
    protection['candomainedit'] = rangeProtections[i].canDomainEdit();
    protection['iswarningonly'] = rangeProtections[i].isWarningOnly();

    rangeProtections[i].remove();
    protectionData.rangeProtections.push(protection);
  }

  try {
    /**
    *
    *  HERE YOU CAN RUN YOUR SCRIPT
    *
    **/
  catch(e) {
    Logger.log("Caught exception: " + e.toString());
  }

  for (var i=0; i<protectionData.sheetProtections.length; i++) {
    var sheet = protectionData.sheetProtections[i]['range'].getSheet();
    var protection = sheet.protect()
                          .setDescription(protectionData.sheetProtections[i]['description'])
                          .setRange(protectionData.sheetProtections[i]['range'])
                          .setUnprotectedRanges(protectionData.sheetProtections[i]['unprotected ranges'])
                          .setDomainEdit(protectionData.sheetProtections[i]['candomainedit'])
                          .setWarningOnly(protectionData.sheetProtections[i]['iswarningonly']);

    var protectionEditors = protectionData.sheetProtections[i]['editors'];

    // add Editors
    for (var j=0; j<protectionEditors.length; j++) {
      protection.addEditor(protectionEditors[j]);
    }
  }

  for (var i=0; i<protectionData.rangeProtections.length; i++) {
    var range = protectionData.rangeProtections[i]['range'];
    var protection = range.protect()
                          .setDescription(protectionData.rangeProtections[i]['description'])
                          .setDomainEdit(protectionData.rangeProtections[i]['candomainedit'])
                          .setWarningOnly(protectionData.rangeProtections[i]['iswarningonly']);

    var protectionEditors = protectionData.rangeProtections[i]['editors'];

    // add Editors
    for (var j=0; j<protectionEditors.length; j++) {
      protection.addEditor(protectionEditors[j]);
    }
  }

}

The idea would be to actually run your script's code where the HERE YOU CAN RUN YOUR SCRIPT comment is located, which is the point at which the protections are removed from the Sheet and saved in memory. Afterwards, they are retrieved from memory and put back into the Sheet.

You must be careful, however, of the actual script exceeding the runtime limit (see quotas). If this situation occurs, the script will halt without re-setting your protections.

In case you are interested about protections in Google Apps Scripts, I suggest you check out the following link:

Class Protection

Unprotecting protected sheet so that others can run script and then protect the sheet again

1 Answers