How to make a protected range in Google Sheet accessible for a javascript function from App Script

Question

I shared a Google Sheets but protected certain range from editing. Now, I cannot edit the range programmatically from App Script, ex. set a value to a cell. Question: Is there a way to keep the range protection (prevent users from editing the protected range) and edit it via script? Thank you!

NaziA NaziA · Accepted Answer · 2021-01-05T23:25:30

I haven't found what you are trying to find, but I thought of a workaround that should work almost the same with what you are trying to do.

Try removing the range protection via script then modify the protected range and adding the protection again.

function main() {
  // Remove protection before editing
  removeProtectionToColumn();
  // Call your function that modifies the protected range in between
  modifySheetDataFunction(); 
  // Add protection again
  addProtectionToColumn();
}

function addProtectionToColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('A:A');
  var protectSs = range.protect().setDescription('Protect column A');
  var me = Session.getEffectiveUser();
  protectSs.addEditor(me);
  protectSs.removeEditors(protectSs.getEditors());
  if (protectSs.canDomainEdit()) {
    protectSs.setDomainEdit(false);
  }
}

function removeProtectionToColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    if (protections[i].getDescription() == 'Protect column A') {
      protections[i].remove();
    }
  }
}

There should be a very little window time here and should be feasible to whatever you are trying to work unless you are modifying a very very very big set of data. You can always test this behavior with your colleagues.

For more details regarding protection, please see this documentation

How to make a protected range in Google Sheet accessible for a javascript function from App Script

1 Answers