Prevent editing of cells while script is running

Question

I have a small Google Apps script that runs for a couple of secounds after certain cells within a spreadsheet are edited.

My question is if it's possible to prevent the current user of doing further changes to the spreadsheet while the script is running.

So it should be like this:

cell is edited
script is running, user interactions/changes for the current user are blocked
script is done
user interactions/changes for the current user are unblocked

I would recommend using a non-dismissable UI dialog that explains what is happening and is then programmatically closed when the script finishes running. — Sam Scholefield, Dec 13 '16 at 10:42

utphx · Answer 1 · 2016-12-13T01:42:16.600

4

You can use Class Protection: https://developers.google.com/apps-script/reference/spreadsheet/protection

Adapted from above link you can try:

function pro(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var protection = sheet.protect().setDescription('Sample protected sheet');

  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script will throw an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }

  //YOUR CODE TO RUN

  SpreadsheetApp.flush();
  protection.remove();  
}

EDIT

This worked for me:

function onEdit(){
  //Adapted from:  https://developers.google.com/apps-script/reference/spreadsheet/protection
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var me = Session.getActiveUser().getEmail();

  if (me != 'owner email'){
    var protection = sheet.protect().setDescription('Sample protected sheet');
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }

    //YOUR CODE TO RUN
    SpreadsheetApp.flush();
    protection.remove(); 
  }
}

edited Dec 13 '16 at 01:42

answered Dec 12 '16 at 16:23

utphx

1,287
1
8
19

This will not work for the effective user. It will lock out every other editor (not the owner though) but will not stop the user from making edits while the script runs. – Spencer Easton Dec 12 '16 at 22:03
@SpencerEaston I believe it does work, may not be the best solution but it works. You can try it here: https://docs.google.com/spreadsheets/d/1cxiG2olk8FxZV30lCxv7aNnwoqC1c6NWICTq4UMaHzI/edit#gid=0 – utphx Dec 13 '16 at 01:45
1

Yes that works if the user edits slowly and they don't get worried about an error box. Also think of a the scenario where the code removes all editors to block the UI then fails/times out/quotas out before removing the updated permission. Really the correct solution is "Don't lock the user out of the UI", but if you have to use a more graceful solution like a modal dialog box. – Spencer Easton Dec 13 '16 at 19:01
@SpencerEaston yes a modal dialog box works if the user edits slowly and they don't get annoyed by constant dialogs. Also think of a the scenario where the code fails/times out/quotas out before removing the non-dismissable UI dialog. In either case the OP can add more code to account for these cases. Anyway I think we have explored some possible solutions for the OP to choose from, let's leave it at that. – utphx Dec 14 '16 at 15:52

Prevent editing of cells while script is running

1 Answers1