How to allow users to edit protected range through script?

Question

I have a sheet with a protected range which permissions are set to only allow me to edit it. I have a function in the sheet's script which edits cells in the protected range. I rigged up the function to fire on edit which allows users other than myself to run the function successfully since it runs using my permissions.

So that scenario is all fine and dandy, however I complicate things by wanting to display a loading message while the function is running. To do this I created the html file below:

<script>
  function onSuccess() {
    google.script.host.close();
  }

  function onFailure() {
    var div = document.getElementById('result');
    div.innerHTML = '<div>Data failed to load.</div>';
  }

  google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).process();
</script>

<div id='result'><img src="https://c4a54d10381f750e81dcc323aed21e2c95725815.googledrive.com/host/0Bwyqwd2fAHMMallsNkNOV0RfcTg/wait_progress.gif"></div>

The html is displayed when the following function is triggered by the on edit event:

function refresh() {
  var html = HtmlService.createHtmlOutputFromFile('loading').setSandboxMode(HtmlService.SandboxMode.IFRAME).setWidth(100).setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(html, 'Processing...');
}

The user receives an error saying that he/she cannot edit a protected cell. I assume this happens because the refresh() function runs with my permissions but the process() function runs with his/her permissions. Any ideas on how I can make this work for me?

Edit - I tried to use the following code to solve my problem:

function refresh() {
  setPermission(1);
  SpreadsheetApp.flush();

  var html = HtmlService.createHtmlOutputFromFile('loading').setSandboxMode(HtmlService.SandboxMode.IFRAME).setWidth(100).setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(html, 'Processing...');

  SpreadsheetApp.flush();
  setPermission(0);
}

function setPermission(flag)
{
  var me = Session.getActiveUser();
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var protection = protections[0];

  if (flag == 1)
  {
    protection.addEditor(me);
  } else {
    protection.removeEditor(me);
  }
}

It starts to work because the process() function does change some cells in the protected range but then the same error saying the user cannot edit the protected range shows up. I believe it's because showModalDialog doesn't suspend the script so before process() can finish the user's permission to edit the protected range is taken away.

Zig Mandel Zig Mandel · Accepted Answer · 2015-03-25T05:31:21

There are a couple ways to do this.
1) if you know how to do the oauth calls manually (an oauth2 apps script library is out there to help) you could have your account refresh token saved and use it to make the sheet api calls in your name (through urlFetch).
2) publish another script that runs in your name with anonymous which does the changes by receiving url parameters. Call that script from your original script with urlFetch. this is easier to code that (1) but might me slower.
you might also need to use a custom installed onEditTrigger if you have permission issues with urlFetch or other apis.

How to allow users to edit protected range through script?

1 Answers