Enable not authorized user to run the script

Question

I'm new to this area, but I'm currently working on a lock sheet for the user. The script is working if I'm the owner so the only problem is if I run it on the user side. I read somewhere that I need to use a Web App or doGet function for the sheet to read the script as the owner. I tried these solutions on these threads, but I still can't make it work. I don't know if the script is the problem or I'm the problem so that's why I'm asking this here. I'm hoping that someone can help me with this matter.

Here a replicate spreadsheet that I'm working on: Spreadsheet

Threads that I tried:

Here's my script for the Lock function

function Lock(sheetName1) {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
  var protection = sheet1.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection && protection.canEdit() && sheet1.protect().setDescription('Sample protected range')) {
    protection.remove();
  }
  var confirm = Browser.msgBox('Confirmation','Are you sure you want to lock this sheet?',Browser.Buttons.YES_NO);
  if(confirm=='yes'){
    LockSheet(sheet1);
    Browser.msgBox("The sheet is locked!");
  }
}

function LockSheet(sheet1) {
  var protection = sheet1.protect().setDescription('Sample protected sheet');
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Cooper Cooper · Accepted Answer · 2020-12-04T06:33:34

This could be the html for a simple webapp for locking a sheet. YOu select the sheet name and the press the button.

When you publish the webapp set it to execute as you. And let it be run by anyone.

This is probably not exactly what you want but it would work and you can modify it anyway you wish. I tested it as a dialog so I'm sure it works.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select id="sel1">
      <option value="Sheet1">Sheet1</option>
      <option value="Sheet2">Sheet2</option>
      <option value="Sheet3">Sheet3</option>
    </select>
    <input type="button" value="Lock" onClick="locksheet();" />
    
    <script>
    function showsheet() {
      google.script.run.lockSheet(document.getElementById('sel1').value);
    }
    console.log('MyCode');
    </script>
  </body>
</html>

The use of a doGet() is required for the webapp to run. I'll leave that for you to figure out. Note: I didn't test any of your locking code with it since you said that it works, I see no need.

Enable not authorized user to run the script

1 Answers