How to allow shared users in google sheets to run script that accesses protected sheet

Question

I have a google spreadsheet in which I plan to share with over 50 users who will each have their own sheet. For security measures, I have some script that I would like to run which would allow a user to enter data into their sheet but prevent them from deleting that entry after. The code works fine on my end, but when I tried to test it out by sharing it to one of the users, the script either didn't run or is not allowed to run.

I have done my research on this matter for a while now and cannot seem to apply any of the solutions that I have seen posted on this forum and many others. I am using an onEdit() function which, to the best of my knowledge, is a simple trigger so it shouldn't cause this kind of error. The code is as follows:

function onEdit(event) {

var masterSheetName = "Blank" // sheet where the cells are protected from updates
  var helperSheetName = "Blank Copy" // sheet where the values are copied for later checking

  // range where edits are "write once": D18:Y157, i.e., rows 18-157 and columns 4-25
  var firstDataRow = 18; // only take into account edits on or below this row
  var lastDataRow = 157; // only take into account edits on or above this row
  var firstDataColumn = 4; // only take into account edits on or to the right of this column
  var lastDataColumn = 25; // only take into account edits on or to the left of this column

  var miscFirstDataColumnOne = 15; // only take into account edits on or to the right of this column
  var miscLastDataColumnOne = 15; // only take into account edits on or to the left of this column
  var miscFirstDataColumnTwo = 25; // only take into account edits on or to the right of this column
  var miscLastDataColumnTwo = 25; // only take into account edits on or to the right of this column
  var miscFirstDataRowTwo = 18;
  var miscLastDataRowTwo = 157;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getActiveSheet();
  var masterSheetMiscOne = ss.getActiveSheet();
  if (masterSheet.getName() != masterSheetName) return;
  if (masterSheetMiscOne.getName() != masterSheetName) return;

  var masterCell = masterSheet.getActiveCell();
  var masterCellMiscOne = masterSheetMiscOne.getActiveCell();

  if (masterCell.getRow() < firstDataRow || masterCell.getColumn() < firstDataColumn ||

    masterCell.getRow() > lastDataRow || masterCell.getColumn() > lastDataColumn) return;

  var helperSheet = ss.getSheetByName(helperSheetName);
  var helperCell = helperSheet.getRange(masterCell.getA1Notation());
  var newValue = masterCell.getValue();
  var oldValue = helperCell.getValue();

  var user = SpreadsheetApp.getActive().getEditors()[1];
  var permission = helperSheet.getSheetProtection();
  permission.addUser(user);
  helperSheet.setSheetProtection(permission);
  SpreadsheetApp.flush();

  if (oldValue == "") {
    helperCell.setValue(newValue);
  } else {
    masterCell.setValue(oldValue);
    Browser.msgBox('You can not delete this value');
  }

  if ((masterCellMiscOne.getRow() < firstDataRow || masterCellMiscOne.getColumn() < miscFirstDataColumnOne || masterCellMiscOne.getRow() > lastDataRow || 

    masterCellMiscOne.getColumn() > miscLastDataColumnOne) & (masterCellMiscOne.getRow() < firstDataRow || masterCellMiscOne.getColumn() < miscLastDataColumnOne ||

      masterCellMiscOne.getRow() > lastDataRow || masterCellMiscOne.getColumn() > miscLastDataColumnTwo)) return;

var miscCellValueOne = masterCellMiscOne.getValue();

  if (miscCellValueOne !== "")  {

    Browser.msgBox('Submission Needs To Be Authorized Before Being Added.');

  }

  SpreadsheetApp.flush();
  permission.removeUser(user)
  helperSheet.setSheetProtection(permission)
}
}

Each user has a sheet which will also have a copy, (in this code the users sheet is "Blank" and the copy is "Blank Copy"). The blank copy will be protected so they can not edit this because it will allow them to delete the data in their sheet (Blank). This code does work, but I just need for it to work when I share the spreadsheet.

All help is greatly appreciated and here is a link to a copy of the spreadsheet.

https://docs.google.com/spreadsheet/ccc?key=0AhBLjhwt88kUdFZ2cG9CVFNEQy1zVHdJYlp6ZEx5Unc&usp=sharing

Sourabh Choraria Sourabh Choraria · Accepted Answer · 2019-12-29T13:55:22

Short answer: Create a simple add-on with minimal code refactoring

Explanation

... but when I tried to test it out by sharing it to one of the users, the script either didn't run or is not allowed to run.

Unfortunately this would be the case as the "trigger" that you're currently using (i.e. the onEdit one) -

... cannot access services that require authorization.

Please refer to Simple Triggers > Restrictions.

Alternate solution

You can introduce an onInstall function and publish this code as an add-on with visibility set to Private if folks from only your domain are going to use the sheet/script.

Hope this helps.

How to allow shared users in google sheets to run script that accesses protected sheet

1 Answers

Explanation

Alternate solution